If you’re getting an error that reads something like “Cannot alter column ‘c2’ because it is ‘COMPUTED’” in SQL Server, it looks like you’re trying to alter a computed column.
We can’t alter computed columns.
Continue readingDatabase Management Systems
If you’re getting an error that reads something like “Cannot alter column ‘c2’ because it is ‘COMPUTED’” in SQL Server, it looks like you’re trying to alter a computed column.
We can’t alter computed columns.
Continue readingIf you’re getting an error that reads something like “Cannot create index on view ‘demo.dbo.v1’. The function ‘sysdatetime’ yields nondeterministic results…” and so on, in SQL Server, it looks like you’re trying to create an index on a view that returns nondeterministic results.
Indexes can only be created on views that return deterministic results.
Continue readingSQL Server provides various SET
options that control the behavior of our session. These options can impact query execution and results. Also, some features in SQL Server rely on certain SET
options to be configured in a certain way (for example indexed views).
Getting the current SET
options configuration is not as straightforward as one might think. We need to do a bit of trickery.
Let’s look at how to view our current SET
options.
If you’re getting an error that reads something like “Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.” in SQL Server, it looks like you’re trying to use either an aggregate function or a subquery in the GROUP BY
clause.
We can’t use aggregates or subqueries in the GROUP BY
clause.
To fix this issue, remove any aggregate functions or subqueries from your GROUP BY
clause.
When looking through the execution plan in SQL Server, you may occasionally see “key lookup” glaring back at you.
If you find this happening a lot, and on frequently run queries, then you might want to do something about it.
Continue readingIf you’re getting an error that reads something like “Cannot create index on view … because its select list does not include a proper use of COUNT_BIG…” and so on in SQL Server, it could be that you’re trying to create an index on a view that uses the GROUP BY
clause, but doesn’t have the COUNT_BIG()
function.
If the view contains a GROUP BY
clause, then it must also have COUNT_BIG(*)
.
To fix this issue, try adding COUNT_BIG(*)
to your SELECT
list.
In SQL Server, ON DELETE NO ACTION
is an option that we can apply to foreign key constraints to prevent the deletion of a row in the parent table if there are related rows in the child table.
Unlike ON DELETE CASCADE
, which would delete the related rows in the child table, NO ACTION
enforces that if a deletion would result in orphaned records in the child table, the deletion operation is prohibited, and an error is raised.
If you’re getting SQL Server error 7999 that reads something like “Could not find any index named ‘IX_LastName’ for table ‘Employees’“, it appears that you’re trying to create an index with the DROP_EXISTING
argument ON
, but there is no existing index of that name.
When we set the DROP_EXISTING
argument to ON
, SQL Server tries to look for the index to drop before it recreates it with the new definition. If it doesn’t exist, then we get the error.
You may have seen the term “RID lookup” when reading about heap tables, or perhaps when viewing the query plan for a query against a heap in SQL Server.
But what exactly is a RID lookup? And what exactly is a RID?
Let’s find out.
Continue readingIf you’re getting an error in SQL Server that reads “Incorrect syntax near the keyword ‘Order’“, it could be because you’re using the word Order
(or another reserved keyword) in your SQL code.
The 156 error itself merely indicates a syntax error, so there could be a multitude of reasons you might be getting this error. But when it refers to the keyword 'Order'
, this could suggest that you’re trying to use the word Order
as an identifier (such as a column name, table name, etc).