Sql-server – Non clustered index on multiple columns

sql server

I have a table which has around 50 columns. But there are only 6 columns on which we perform regular search. When I am trying to create a non clustered index on it I am getting the warning of 900 bytes. This would be a problem as we also do bulk update/inserts. So can onyone help me in finding a solution?

If I put few columns out of those 6 in INCLUDE, will that cause any performance issue?

Lets say the datatypes for the columns are – 

     ColumnA (nvarchar(255),null)
     ColumnB (Int, null)
     ColumnC (nvarchar(255),null)
     ColumnD (Datetime,Null)
     ColumnE (nvarchar(255),null)
     ColumnF (nvarchar(Max),null)

Primarily these columns are used in WHERE to pull out data.

Thanks,
Unmesh

Best Answer

The size of the nvarchar(255) columns is 510 bytes each. Even two of them will cause a warning and potential issues with insert/update operations.

What you want to do is find a combination of one (or less) of the nvarchar columns plus the int and datetime columns that will give you the most unique values. Then index on that combination including the other columns in order to create a covering index.

If you need more than one of the nvarchar(255) columns actually indexed in order to create a highly selective index (lots of unique values) then you are down to two options.

If you are certain that you will never have more than ~445 characters (remember these are unicode so there are 2 bytes per character and you need room for the datetime and int columns) in the combination of the 3 nvarchar(255) columns then you can create the index and keep your fingers crossed that you aren't wrong.

If you can't be sure they won't go over that size (and if you can you should think about shrinking the size of the columns) then your best bet is to do what @MichaelJSwart suggested in the comments above and create a hashed column to index on. The biggest down side to this option is that it will require changing your code. On an up note while you are creating your hash column(s) and changing your code you can work in an index on the nvarchar(max) column (by hashing it).

Related Question