Improved SQL Server Index Scripting from Excel

I covered Index Scripts using Excel in an earlier post but I was obliged to make my spreadsheet do more over the past few workdays. Basically I needed to allow multiple indexed fields and multiple included fields. The following solution works for any situation between 0 and 3 of both.
My spreadsheet contains the following columns (so ‘Table’ is in cell A1):

  • Table
  • Field1
  • Order1
  • Field2
  • Order2
  • Field3
  • Order3
  • Include1
  • Include2
  • Include3
  • Notes (this field isn’t necessary but I like to use to remind me how I decided to use this particular index)
  • MultiField
  • MultiInclude
  • Index Name
  • Insert
  • Drops
  • Reorgs
  • Rebuilds

Your table name, of course, goes in the Table column (cell A2) and likewise we put in up to three Fields to combine for your index and their respective orders (ASC or DESC). Any included fields (only included in the leaf portion of the index and so contribute less size to the index) go in the the three Include columns. Those fields or includes you don’t use are left blank.
To break down the work a little I have the formulae broken up into the MultiField, MultiInclude, Index Name and Inserts columns.
Here’s the MultiField formula:

=IF(ISBLANK(B2),””,IF(ISBLANK(D2),”[“&B2&”] “&C2,IF(ISBLANK(F2),”[“&B2&”] “&C2&”,[“&D2&”] “&E2,”[“&B2&”] “&C2&”,[“&D2&”] “&E2&”,[“&F2&”] “&G2)))

The MultiInclude:

=IF(ISBLANK(H2),””,IF(ISBLANK(I2),”[“&H2&”]”, IF(ISBLANK(J2),”[“&H2&”],[“&I2&”]”, “[“&H2&”],[“&I2&”],[“&J2&”]”)))

The Index Name:

=”perfindex_”&A2&”_”&IF(ISBLANK(B2),””, IF(ISBLANK(D2),B2, IF(ISBLANK(F2),B2&”_”&D2,B2&”_”&D2&”_”&F2)))&IF(ISBLANK(H2),””, IF(ISBLANK(I2),”_incl_”&H2, IF(ISBLANK(J2),”_incl_”&H2&”_”&I2,”_incl_”&H2&”_”&I2&”_”&J2)))

And the Insert:

=”create nonclustered index [“&N2&”] on [dbo].[“&A2&”] (“&L2&”) “&IF(ISBLANK(H2),””,”include (“)&IF(ISBLANK(H2),””,M2)&IF(ISBLANK(H2),””,”) “)&”with (statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on) on [primary];”

The Drops, Reorganize and Rebuilds fields are for Index administration.
Formula for Drops:

=”drop index “&A2&”.”&N2&”;”

Formula for Reorgs:

=”alter index “&N2&” on “&A2&” reorganize;”

Formula for Rebuilds:

=”alter index “&N2&” on “&A2&” rebuild with (fillfactor = 90);”

Advertisements

About tgrignon

I came I saw I rented the DVD
This entry was posted in Miscellany and tagged , , . Bookmark the permalink.

One Response to Improved SQL Server Index Scripting from Excel

  1. tgrignon says:

    I changed some of the formulae today to make it more sensitive to missing includes as well as added the capability to generate drop, reorganize and rebuild scripts.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s