In Part I, I discussed a couple of scripts that could be used to help discover some vital information about all of the PKs in the database very quickly.  I also delved into a performance analysis of the two queries.

The scripts presented showed a very basic discovery of Primary Key information.  I decided that I could use more information at my fingertips than the data set provided in that article.  So, in this article, I want to demonstrate a few more scripts to be able to achieve my goals.  These scripts will cover some clustered index information.

In SQL Server, when you create a Primary Key, the default behavior is to create a Clustered Index on the Primary Key.  Though this is the default, it can be overridden.  Thus, one should know what tables have Clustered Indexes created on them.  It is also desirable to know upon which columns Clustered Indexes have been created.

To get to the Clustered Index Information, the Query is not much different than the one I used to retrieve the Primary Key information.  One could make the assertion that this is due to the default behavior of creating a Primary Key and Clustered Index in one fell swoop.  They are certainly closely knit together.

See the difference?

And…

Simple little change to get slightly more detailed information and something a little more intuitive about the database.  What does this tell me?  Well, I can now map these results to my PK results and see what PKs are also Clustered Indexes.  This is simply achieved by uncommenting the is_primary_key line in the script.  This small change will change the results to only show the Primary Keys and their Clustered Indexes.  Even better, the script could be altered as follows to just pull back the Clustered Indexes that are not on Primary Keys.  Simple modification, uncomment the is_primary_key line and change it from an equality to an inequality evaluation.

So, now we know more information about the Primary Keys and Clustered Indexes.  That is, we know that information for any table that has a Clustered Index.  It isn’t unheard of to have tables present in the database that do not have a Clustered Index at all.  Knowing that information is essential as well.  Again, this is all information that can be garnered by tedious investigation into each table.  Or, we could quickly run a little script and get a quick result gaining quicker insight into the data we manage.

If any results are returned, then it would also be useful to know record counts for each of the tables in the result set.  Knowing this might give some information as to usage of the table and impact of queries against the table that is a Heap.  I was able to achieve this with the following:

Armed with this information, it is a little easier to go into a new environment and speak to the databases and data.  The learning curve is shortened and one will be able to spend more time doing other tasks pertinent to maintaining the database environment and learning the database environment.