If you’re getting an error that reads something like “Cannot create index on view “demo.dbo.SalesSummary” because it uses the aggregate COUNT. Use COUNT_BIG instead” in SQL Server, it’s because you’re trying to create an index on a view that uses the COUNT() function.
Tag: t-sql
Clustered vs Nonclustered Index: What’s the Difference?
Indexes can play a crucial role in optimizing SQL query performance. A well designed index can help a query perform much faster than it otherwise might. While there are many types of indexes, two common index types are clustered and nonclustered indexes.
Understanding the difference between clustered and nonclustered indexes can have a significant impact on our database design and query optimization strategies.
In this article, we’ll take a look at clustered and nonclustered indexes as implemented in SQL Server, explore their characteristics, and examine when to use each type.
Continue readingFix “Cannot DROP TABLE because it is being referenced by object” in SQL Server (Error 3729)
If you’re getting an error that reads something like “Cannot DROP TABLE ‘dbo.t1’ because it is being referenced by object ‘v1’” in SQL Server, it looks like you’re trying to drop a table that’s referenced by a schema-bound object (such as a schema-bound view).
Continue readingUnderstanding the CONTINUE Keyword in SQL Server WHILE Loops
WHILE loops are a fundamental construct in T-SQL (and in programming in general) for executing repetitive tasks. Within these loops, the CONTINUE keyword serves as a powerful control flow tool, allowing developers to skip iterations and optimise loop execution.
This article explores the usage and benefits of the CONTINUE keyword in SQL Server WHILE loops.
Fix “Computed column … in table … is not allowed to be used in another computed-column definition” (Error 1759)
If you’re getting an error that reads something like “Computed column ‘c2’ in table ‘t1’ is not allowed to be used in another computed-column definition” in SQL Server, it appears that you’re trying to create a computed column that uses another computed column.
We can’t use other computed columns in our computed column definitions.
Continue readingIndexed Views in SQL Server: A Complete Guide
Indexed views can significantly enhance the performance of complex queries in SQL Server. However, they come with several prerequisites and considerations that need to be carefully understood before implementation.
In this article, I provide an overview of indexed views, discussing their utility, prerequisites, and best practices, along with performance considerations and a simple example.
Continue readingFix “ALTER TABLE DROP COLUMN failed because one or more objects access this column” in SQL Server (Error 4922)
If you’re getting an error that reads something like “ALTER TABLE DROP COLUMN c2 failed because one or more objects access this column” in SQL Server, it looks like you’re trying to drop a column that’s accessed by another object.
This can happen when we try to drop a column that’s referenced by a schema-bound view. It can happen even when we only try to alter the columns too.
Continue readingImplementing ON UPDATE CASCADE for SQL Server Foreign Keys
ON UPDATE CASCADE is a referential integrity constraint option that we can use in SQL Server when creating foreign keys. It automatically updates foreign key values in child tables when the corresponding primary key in the parent table is updated.
Fix “Cannot create index on view. It contains text, ntext, image, FILESTREAM or xml columns” in SQL Server (Error 1942)
If you’re getting an error that reads something like “Cannot create index on view ‘demo.dbo.v1’. It contains text, ntext, image, FILESTREAM or xml columns” in SQL Server, it appears that you’re trying to create an index on a view that uses an invalid data type.
Not all data types are supported when indexing views.
Continue readingFix “The new name is already in use as a COLUMN name and would cause a duplicate that is not permitted” in SQL Server (Error 15335)
If you’re getting an error that reads something like “Error: The new name ‘c1’ is already in use as a COLUMN name and would cause a duplicate that is not permitted” in SQL Server, it appears that you’re trying to rename a column with a name that already exists in that table.
Basically, there’s already a column of that name in the table.
Continue reading