In SQL Server, we can use the DROP_EXISTING option of the CREATE INDEX statement to modify an existing index. It allows us to drop and rebuild an index in one atomic operation. This can be particularly useful when we need to make changes to an index, such as altering its columns, changing its properties, or rebuilding it with different options like fill factor or sort order.
Tag: indexes
Fix “Cannot create index on view. The function yields nondeterministic results” in SQL Server
If 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 readingUnderstanding the “Key Lookup” in SQL Server
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 readingFix “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 reading