SQL SERVER – Identify Numbers of Non Clustered Index on Tables for Entire Database

Here is the script which will give you numbers of non clustered indexes on any table in entire database.

SELECT COUNT(i.TYPE) NoOfIndex,
[schema_name] = s.name, table_name = o.name
FROM sys.indexes i
INNER JOIN sys.objects o ON i.[object_id] = o.[object_id]
INNER JOIN sys.schemas s ON o.[schema_id] = s.[schema_id]
WHERE o.TYPE IN ('U')
AND
i.TYPE = 2
GROUP BY s.name, o.name
ORDER BY schema_name, table_name

Here is the small story behind why this script was needed.

I recently went to meet my friend in his office and he introduced me to his colleague in office as someone who is an expert in SQL Server Indexing. I politely said I am yet learning about Indexing and have a long way to go. My friend's colleague right away said - he had a suggestion for me with related to Index. According to him he was looking for a script which will count all the non clustered on all the tables in the database and he was not able to find that on SQLAuthority.com.

I was a bit surprised as I really do not remember all the details about what I have written so far. I quickly pull up my phone and tried to look for the script on my custom search engine and he was correct. I never wrote a script which will count all the non clustered indexes on tables in the whole database. Excessive indexing is not recommended in general. If you have too many indexes it will definitely negatively affect your performance. The above query will quickly give you details of numbers of indexes on tables on your entire database. You can quickly glance and use the numbers as reference.

Please note that the number of the index is not a indication of bad indexes. There is a lot of wisdom I can write here but that is not the scope of this blog post. There are many different rules with Indexes and many different scenarios. For example - a table which is heap (no clustered index) is often not recommended on OLTP workload (here is the blog post to identify them), drop unused indexes with careful observation (here is the script for it), identify missing indexes and after careful testing add them (here is the script for it). Even though I have given few links here it is just the tip of the iceberg. If you follow only above four advices your ship may still sink. Those who wants to learn the subject in depth can watch the videos here after logging in.

Note: You change where condition type to 6 for nonclustered column store index.

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

8 thoughts on “SQL SERVER – Identify Numbers of Non Clustered Index on Tables for Entire Database

  1. Pingback: SQL SERVER – Identify Most Resource Intensive Queries – SQL in Sixty Seconds #029 – Video « SQL Server Journey with SQL Authority

  2. Sir,
    One of our client isn’t able to log on to their ERP that is running a SQL Server 2005 database. When we tried to know the reason we found the following message in event log:

    “Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online.”

    Also we found that the SQL Server service has got stopped. When we tried to re-start the
    Service from Control Panel – Services, it showed a 3417 error.

    The client hasn’t taken db backup since May 2012 and this is sort of a riddle for us. It is very urgent to help the client out of this hole. Can you please suggest something?

    Thanks in advance. Your suggestions would be highly appreciated.

    Anindya Basu

  3. Hi,
    I have experienced some problems on index.
    1->I have a Employee table with 3 columns EmpID(Identity column),Reg no(clustered index),name. Now the table is not sorted physically based on the regno,the clustered index column.It is sorted only based on the identity.Why ?

    2->I was able to create a non clustered index as well as clustered index for the same column.How might that be helpful ?

    3->I can create “CREATE INDEX Index_Reg_NO ON Employee(RegNo)”.
    What type of index have I created ? How does it differ from clustered or non clustered index ?

    Thanks in advance….
    Regards
    Midhun,Prakash

  4. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

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