March Madness – SQL Server System Tables – sysindexes

Remember how I told you we would talk about the sysindexes system table? Well today is the day!

Two quick notes: First, this system table is scoped at the database level (not the instance level). Second, this system table will be removed from a future version of SQL Server.

What is this table for?

This table will contain one row for each index that has been created in the current database. Go ahead and run this code and examine the results:

SELECT *
FROM sysindexes

See anything interesting? This table (sysindexes) also happens to be very useful for migrating to the cloud because every user table there needs a clustered index there. So this code could be useful for you as in the future:

SELECT name
FROM sysobjects
WHERE type = 'U'
AND id NOT IN
(SELECT id FROM sysindexes WHERE indid = 1)

This way you can easily find the tables that need to have a clustered index defined prior to migrating them to the cloud.

Why should you care?

Because you need to care about your indexes. If there is one thing you should have learned by now with my March Madness series it is that I am meticulous about being able to answer the question “what changed”. So if that means I need to script out the names and types of indexes that exist in a database each night, I will. Believe me, it is a huge time saver when you are trying to troubleshoot a performance issue and you are able to find out that a clustered index is missing because the developer thought their code would be faster by dropping several indexes without thinking they may actually be useful for others. (It is also a great opportunity to explain to their manager why such developers shouldn’t be allowed access to the database, or any database.)

Unfortunately the older sysindexes system table doesn’t give you a lot to work with and that is why you should know…

Where else is this information?

Remember how I said this system table will be removed from a future version of SQL Server? Well, there is something else you should know about the sysindexes system table: XML indexes are not supported. As such, you should be using the sys.indexes catalog view instead. It makes life easier to find things like the current FILLFACTOR:

SELECT name, type_desc, fill_factor
FROM sys.indexes

Which was not possible with sysindexes (you could get the original FILLFACTOR, but not an updated one). Or perhaps you want to know if you have any filtered indexes? Simple enough, run this code:

SELECT name, type_desc
FROM sys.indexes
WHERE has_filter = 1

Start using the sys.indexes catalog view now and investigate a little bit more about the environment in which you work.