Presenting the Index Creator Script


Born of a need to originally script out indexes in a way similar to how SSMS creates them, I created a script to do just that. The original was very quick-n-dirty as can be seen here: http://ask.sqlservercentral.com/questions/16646/create-script-for-indexes
I had not accounted for very much, other than the indexes as they were. All options, schema information etc. was ignored, as we have nothing special at work and I really needed those indexes quickly.

Since posting on ASK, I have tinkered on-and-off with the script for a while. I am now at a point where I think other people could really profit from it and no longer have it stuck to some hard-coded schemas etc.

Presenting the Index Creator Script v1.0! This script will go through the current database, finding all indexes (optionally system indexes too) and supply you with create index scripts.

It is clever enough to spot the difference between / usage of :

– Primary Keys
– Unique Constraints
– Clustered and Non-Clustered Indexes
– Filtered Indexes (produces the filter too)
– Included Columns
– Partitioned Tables/Indexes (although the partition schema and functions are not produced – yet!)
– Data Compression (on a partition level if used – yes this is possible!)
– Fill Factor
– Index Padding
– Locking (Row and Page)

I have supplied two versions of the code; one for SQL 2005 and one for SQL 2008 and above. This is done as SQL 2008 offers Data Compression, which is implemented in the indexes and partitions. Some of the script relies on this information and would not be backwards compatible.

It has been very interesting coding this script, as it has enlightened me on the structures in SQL server with regards to indexes. For example, as of SQL 2005, regardless of edition, SQL Server creates indexes using partitions. Although partitions cannot be used by editions lower than Enterprise/Developer Edition, all indexes are created with at least one partition. This makes sense, as that would mean there would have to be a separate structures depending upon edition. This way, regardless of edition, the storage engine works the same, you just don’t get the option of creating partitions on an edition lower than Enterprise/Developer. As soon as you migrate a database to Enterprise Edition, you get the possibility of then splitting the indexes on to multiple partitions.

Even better than that, I found out that indexes can be compressed by partition. I sort of knew this already, but in writing the script I saw this in even more clarity. Each partition of an index can use a different level of compression. This can be very interesting, especially if the costs of compression are high, but the benefits in storage are high too. Think of a CPU bound system where some partitions are accessed often and would need a lower compression to reduce CPU load, with other partitions that are accessed rarely which can benefit from the higher compression ratio.

I hope these scripts are of some benefit. If you have comments/questions/suggestions, please get in touch.

Index Creator Script – This is a zip file. Download, change file extension and open in your favourite ZIP manager (damn you wordpress!). There are 2 .sql files in there (one for 2005 and one for 2008). Disclaimer – use at your own risk, I am not responsible if it breaks your PC/Server.

In reality, this script can’t break things, but you have been warned!

UPDATE: Thanks to @Fatherjack for the quick heads-up on a syntax error. Things should look good now though! 🙂

Advertisements

10 thoughts on “Presenting the Index Creator Script

  1. Pingback: Tweets that mention Presenting the Index Creator Script « William Durkin's Blog -- Topsy.com

  2. Guten Tag William, I am very interested in your Index-Creator script(s) but the link appears to lead only to a corrupted word document. (?) Would you be willing to help me get a copy of the actual script zip?

    Vielen Dank,
    /Chuck

  3. Err, nevermind — I understand now that I merely need to change the extension to .zip and everything is fine. Well, maybe this mistake will help someone else. 🙂

    Thanks so much for providing this to the community!
    /Chuck

    • Hi Chuck,

      glad you got the file to work, it is a limit of the WordPress engine (free one at least) that stops attaching zip files.

      I’m also pleased that the script has helped you out.

      Regards,

      William

      • It is helping me out a lot! I am using it to generate DROP & ReCREATE scripts for the indexes in our database as part of my scripted process to convert the entire database from varchar to nvarchar. Yours is a very comprehensive, yet elegant script and exactly what I needed. I extended it a bit to produce separate DROP statements and to handle primary XML indexes and will combine it with other scripts to manage DEFAULT constraints, fkeys, computed-columns etc. and am working towards a completely automated conversion process. Thank you for sharing your great script. Vielen Dank!!

        /Chuck

  4. Pingback: Reducing data footprints on test systems « William Durkin

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