Create or Update Index

MAY

2021

Create or Update Index

ORIGINALLY POSTED TO: https://blog.simontimms.com/2021/05/08/drop-existing-index

Of course the SQL server syntax for this doesn't quite jive with what I want but you can use the clause WITH (DROP_EXISTING = ON) to have SQL server handle updating an existing index keeping the old index live until the new version is ready. You use it like

CREATE NONCLUSTERED INDEX idxMonthlyParkers_vendor_expiry_issue
ON [dbo].[tblParkers] ([VendorId],[LotTimezoneExpiryDate],[LotTimezoneIssueDate])
INCLUDE ([HangTagCode],[FirstName],[LastName])
 WITH (DROP_EXISTING = ON)

However that will throw an error if the index doesn't exist (of course) so you need to wrap it with an if

if exists (SELECT * 
FROM sys.indexes 
WHERE name='idxMonthlyParkers_vendor_expiry_issue' AND object_id = OBJECT_ID('dbo.tblMonthlyParker'))
begin
    CREATE NONCLUSTERED INDEX idxMonthlyParkers_vendor_expiry_issue
    ON [dbo].[tblParkers] ([VendorId],[LotTimezoneExpiryDate],[LotTimezoneIssueDate])
    INCLUDE ([HangTagCode],[FirstName],[LastName])
    WITH (DROP_EXISTING = ON)
end
else 
begin
    CREATE NONCLUSTERED INDEX idxMonthlyParkers_vendor_expiry_issue
    ON [dbo].[tblParkers] ([VendorId],[LotTimezoneExpiryDate],[LotTimezoneIssueDate])
    INCLUDE ([HangTagCode],[FirstName],[LastName])
end

Simon Timms

Web

Twitter

GitHub

RSS

Looking for someone else?

You can find the rest of the Western Devs Crew here.