posted on Tuesday, March 20, 2007 7:44 PM by Obi

Scripting Sql Server - with caution!

Couple days ago, our development team scripted the entire development database (including all the objects i.e. tables, views, stored procs, functions etc.). While this exercise seems trivial enough, a word of caution is warranted here.  We overlooked Table Scripting Options to script indexes and primary keys.  As a result, when we tried to populate our tables, it just took forever. So much so, that it came to a point where the server could not handle the excessive load and literally came to a screeching halt.  To get a better perspective, a couple thousand records took up to 15 mins.  It was only later that we realized the flaw and corrected the problem by assigning a clustered index to the primary key column.  The advantage with using a clustered index is that even the disordered records are displayed in sequence by SQL.  In other words, adding a clustered index to the table physically reorders the data pages, putting them in physical order based on the indexed column. 

A nonclustered index on the other hand is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. So e.g. for a table which contains a single row is a good candidate for non-clustered index.  Remember, while clustered-indexes enhance performance for large tables, but there is an overhead on the server.


Obi Oberoi