So, I go further in my series of blogs on automating SQL Server 2005 scripting. This time its the tool of the performance sensitive DBA: the nonclustered index. These are pretty straightforward and should be easy to implement. The first formula is there to delete a previous version of the index should it exist. In your Excel sheet I have the table name in column A and B has the field or column name. Here are the formulae for columns D and E (I leave column C for notes):
=”if exists (select * from sys.indexes where name=N’index_”&A4&”_”&B4&”‘) drop index “&A4&”.index_”&A4&”_”&B4&”; “
=”create nonclustered index [index_"&A4&"_"&B4&"] on [dbo].["&A4&"](["&B4&"] asc) with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, online = off, allow_row_locks = on, allow_page_locks = on) on [primary];”
with “tbltest” and “foreignid_fk” in the A and B cells I get the following results:
if exists (select * from sys.indexes where name=N’index_tbltest_foreignid_fk’) drop index tbltest.index_tbltest_foreignid_fk;
create nonclustered index [index_tbltest_foreignid_fk] on [dbo].[tbltest]([foreignid_fk] asc) with (pad_index = off, statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, online = off, allow_row_locks = on, allow_page_locks = on) on [primary];
Note that I intentionally leave out the “drop_existing” clause in the index create. It can lead to errors and doesn’t seem to be very useful.
