Forum Discussion

MixMalvarkie's avatar
MixMalvarkie
Copper Contributor
Jun 06, 2024

Creating an Index with a lot of Columns

Hi all.

I have been working on SQL Environment that are really not well due to legacy system mixed with new systems. Need to ask bit of stupid question. I have run few different SQL Scripts to recommend Creating of indexes including BrentOzar FirstResponderKit. Mostly I use FirstResponderKit scripts. Here is where I am bit stuck or not sure what to do.

Normally I create Index with no more than 5 columns in a index. This time I am getting different recommendations. This is recommending adding between 15 - 30 columns. Avg Estimated Cost is between 100% or 65%.  Screen shot attach.

Has anyone added index with so many columns?

1 Reply

  • olafhelper's avatar
    olafhelper
    Bronze Contributor
    Jun 06, 2024

    MixMalvarkie , so many columns in a index are not usually, but for specially use case possibly, like indexex for flexible search over several entity properties.

    Keep in mind, an index entry can have maximum size limitation, see

    CREATE INDEX (Transact-SQL) - SQL Server | Microsoft Learn

    => column

    "The maximum allowable size of the combined index values is 900 bytes for a clustered index, or 1,700 for a nonclustered index. The limits are 16 columns and 900 bytes for versions before SQL Database and SQL Server 2016 (13.x)."