Friday 26 January 2007

Disabling / Disable an index (sql 2005+)

'Why?' was my first thought, but then I remembered that when loading data to a table, it is faster without indexes in place

-- disable an index
ALTER INDEX IX_Contact_EmailAddress ON Person.Contact DISABLE

-- re-enable the index (by rebuilding)
ALTER INDEX IX_Contact_EmailAddress ON Person.Contact REBUILD

-- see what indexes are disabled
SELECT [NAME], [TYPE_DESC], [IS_DISABLED] FROM SYS.INDEXES WHERE [IS_DISABLED] = 1

No comments: