SQL Server Index Listing
-
Working on some code to manage and audit SQL indexes.
I am having a problem with the SQL Server sys.index_columns table. I am attempting to enumerate the key columns of all non-clustered indexes.
Notably, that in every index, it reports the presence of the clustered key columns whether they are there or not.
Consider the following table:
id (CK), Name, TacoFlavor.If I create an index on Name, that index will show in the DB as having key columns ID and Name.
First reaction is that one of the other fields should be able to be used to differentiate the CK content. Nope. Not that I can find.
This is directly in contradiction to the following TechNet note:
"Columns implicitly added because they are part of the clustering key are not listed in sys.index_columns."
Anyone have any idea WTF?
-
@weng
Assuming you're lucky enough to be working with SQL 2008 or newer, I would highly recommend sp_BlitzIndex - they already have a pretty nice set of scripts for pulling existing indexes and missing index requests out of the DMVs.From a physical perspective, the clustered key columns are present in every index, whether they're explicitly included by the definition or not. I think the TN note is wrong, but the actual behavior of sys.index_columns is intended. I know that sp_BlitzIndex also winds up calling out your CI columns in NCIs.
-
@izzion Yeah I know why they're there, but I'm trying to figure out how to pull them apart. You'd think there would be a flag...
BlitzIndex likely won't fit into the tooling, but I suppose I can pull it apart to see how it does it.
