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 readingTag: indexes
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.
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 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 readingUnderstanding the RID Lookup in SQL Server: How It Affects Performance
If you’ve spent any time working with heaps in SQL Server, you may have seen the RID lookup in the query plan. And if you haven’t, chances are it’s because you either didn’t look at the query plan, or the queries used full table scans.
In any case, it pays to understand what a RID lookup is and how it affects query performance. If we ever see RID lookup in the query plan, it could be a good opportunity to see if we can make changes that will improve performance.
Continue readingUnderstanding the Covering Index in SQL
In SQL, adding an index to a table can provide us with a significant performance boost, especially if it’s a covering index for commonly run queries.
But what exactly is a covering index?
Let’s find out.
Continue readingFix Error 1989 “Cannot enable foreign key constraint … as index … on referenced key is disabled” in SQL Server
If you’re getting SQL Server error 1989 that reads something like “Cannot enable foreign key constraint ‘FK_Jobs_Employees’ as index ‘PK__Employee__7AD04FF1A39ECAB1’ on referenced key is disabled“, it’s probably because you’re trying to enable a foreign key, when the index that it references is disabled.
For example, if you’ve disabled a primary key on the parent table, and then try to enable a foreign key that references that primary key, then you’ll get this error.
To fix this issue, enable the index for the primary key/referenced key before enabling the foreign key.
Continue readingCreate an Index in SQL Server
Indexes play an important role in SQL database performance. We can use them to speed up commonly run queries, so that users don’t have to sit and wait for their results to come in.
In SQL Server, indexes can be created in several different situations. For example, when we create a primary key or a UNIQUE constraint, an index is created behind the scenes for us. However, we can also create indexes explicitly using the CREATE INDEX statement.
“Incorrect syntax” Error When Creating a Filtered Index in SQL Server due to an Unsupported Operator
If you’re getting the “Incorrect syntax…” error in SQL Server when trying to create a filtered index, it could be that you’re using an unsupported operator.
Filtered indexes in SQL Server only support simple comparison operators. If you use an unsupported operator or expression, you’ll likely get the dreaded “Incorrect syntax” error.
To fix, either use a supported operator, or create a view.
Continue reading