posted on Tuesday, March 20, 2007 7:44 PM
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.