OBJECT_ID() will take out a shared schema lock (SCH_S) on the table we pass into it. This method functions well, but it uses the OBJECT_ID() function. Option 1: Query sys.indexes with the OBJECT_ID() Function Sometimes you have to go all Game of Thrones on an index.
#Dbartisan 2016 code#
We have to check if the index exists, or our code will blow up. The operation failed because an index or statistics with name ‘ix_halp’ already exists on table ‘agg.FirstNameByYear’. If we just re-run the create statement, we get the error: Let’s say we’re running code that is going to create the index if it does NOT exist. INCLUDE (FirstNameId, Gender, NameCount) We want to know if an index named ix_halp exists on the table agg.FirstNameByYear in the SQLIndexWorkbook database– now renamed to BabbyNames.
Examples of why simpler code doesn’t do the trick.This uses less locking, but is wordier code. Example code to check if an index exists just using joins.The code is simpler, but it requires a shared schema lock on the table you’re checking. Example code to check if an index exists using OBJECT_ID.But there’s no simple function to test if an index exists in SQL Server. Checking if an index exists is a pretty frequent task. Whenever you set up a script to create or drop an index, you want a safety check in there. But it’s not.Ĭode Should be Rerunnable - So You Need to Check if Indexes Exist
0 kommentar(er)
