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 readingCategory: SQL Server
Fix “Cannot create index on view because its select list does not include a proper use of COUNT_BIG” in SQL Server (Error 10138)
If 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.
Explanation of ON DELETE NO ACTION in SQL Server
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.
Fix Error 7999 “Could not find any index…” in SQL Server
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.
Understanding the RID in SQL Server
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 readingFix “Incorrect syntax near the keyword ‘Order'” in SQL Server (Error 156)
If 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).
Understanding the ON UPDATE SET NULL Option for Foreign Keys in SQL Server
In SQL Server (and relational databases in general), a foreign key is a field (or collection of fields) in one table that uniquely identifies a row of another table.
This relationship is a cornerstone of relational database design, ensuring referential integrity between the related tables. When designing databases, it’s crucial to understand how changes to primary keys in parent tables can affect the foreign keys in child tables.
One of the various options available in SQL Server for handling such changes is the ON UPDATE SET NULL action.
Fix Error 156 “Incorrect syntax near the keyword ‘ON'” When Using DROP_EXISTING in SQL Server
If you’re getting an error that reads something like “Incorrect syntax near the keyword ‘ON’“, it appears that you’re trying to recreate an existing index, but you’re using the wrong syntax for the DROP_EXISTING argument.
The same error occurs when using the wrong syntax to set DROP_EXISTING to OFF; “Incorrect syntax near the keyword ‘OFF’“.
Either way, to fix this issue, be sure to use the right syntax.
Continue readingFix Error 4145 “An expression of non-boolean type specified in a context where a condition is expected” in SQL Server
If you’re getting SQL Server error 4145 that goes something like “An expression of non-boolean type specified in a context where a condition is expected…“, it’s probably because you’re using a boolean-like syntax in the wrong context.
This error occurs when a non-boolean expression is used in a context where a condition (i.e., something that evaluates to true or false) is expected. This often happens in IF statements, CASE expressions, or WHERE clauses when a value that isn’t a boolean is mistakenly used as a condition.
Even when we do use a boolean value, we can still get this error if we use it in the wrong context.
Continue readingFix Error 107 “The column prefix … does not match with a table name or alias name used in the query” in SQL Server
If you’re getting SQL Server error 107 that goes something like “The column prefix ‘e’ does not match with a table name or alias name used in the query“, look at your column prefix.
This error occurs when we use the asterisk wildcard (*) that’s incorrectly qualified with a column prefix. The prefix, if provided, must match a table name or alias name. If you’re using a table alias, then you must use that instead of the table name.