Naming standard for INCLUDE indexes?

Question

We use a fairly straightforward naming standard for indexes that I can validate programmatically, but I am struggling with naming indexes with INCLUDE columns. If I do it based on a simple formula, it's too easy to build names that are too long. Then my programmatic validation fails, and I end up with truncated names.

For those of you using INCLUDE columns, have you come up with a naming standard that you're happy with? If so, please share.

My current standard that I'm not happy with:

IX_<tableName>_<column1>_..._<columnN>_wIncl (wIncl translates as "with include")

The problem is that there is no indication of what columns are included.

Answer 1

I would not really add all that information to the index name...

What I normally have is

• PK_(table) for the primary key index
• IXnn_(table)_(indexname) for regular non-clustered indices
• UIXnn_(table)_(indexname) for regular non-clustered unique indices
• CIXnn_(table)_(indexname) for clustered unique index (if it's not the same as the PK)

That is good enough for my cases - if you want to specifically indicate that a non-clustered index also has included columns, you might want to think up a new prefix (instead of IX) - but I wouldn't go overboard and add all included columns to the name, too - too much information!

If you need to see what columns are in an index, and whether they're included columns or not, query the system catalog views:

SELECT
    OBJECT_NAME(i.object_id) 'Table name',
    i.name 'Index name',
    i.type_desc 'Index type',
    i.is_unique ,
    i.is_primary_key ,
    i.is_unique_constraint ,
    c.Name 'Column name', 
    ic.is_included_column
FROM
    sys.indexes i 
INNER JOIN 
    sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
INNER JOIN 
    sys.columns c ON ic.column_id = c.column_id AND ic.object_id = c.object_id
WHERE
    i.Name = '(your index name in question)'

Marc

Answer 2

If you really want to include the column names in the index name, you can simply use the same column naming structure:

IX_<table>_<col1>..._...<colN>_INCL_<col1>..._...<colN>

If this is too long, then for the included columns you could use ordinal number instead of name. (Or maybe you can investigate why you are including so many columns that you exceed the identifier length, or whether your column naming standards are too verbose).

However, you can always get the included columns from the catalog views, so I'm not sure why you would want to be so explicit in the name alone. I don't recall a scenario where I always and absolutely wanted to know every possible detail about an index by looking at the name alone. For included columns I think it is fine to look them up manually when you are in a "need to know" situation.