2

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

  • What 6 columns are you trying to index? What are their datatypes? – Mark Sinkinson Oct 1 '14 at 9:45
  • Hi Mark - Lets say ColumnA (nvarchar(255),null) ColumnB (Int, null) ColumnC (nvarchar(255),null) ColumnD (Datetime,Null) ColumnE (nvarchar(255),null) ColumnF (nvarchar(Max),null) – Unmesh Oct 1 '14 at 10:01
  • 1
    Size of index key will be less that 900 bytes, check technet.microsoft.com/en-us/library/ms191241(v=sql.105).aspx – vijayp Oct 1 '14 at 10:13
  • 1
    If you'll only doing equality searches, you could consider hashing the fields in question and then indexing that: michaeljswart.com/2013/11/hashing-for-indexes It does involve altering the table to add a new column. – Michael J Swart Oct 1 '14 at 12:46
  • 1
    You cannot index a nvarchar(max) column - one of the great drawbacks of the "max" datatypes.... – marc_s Oct 1 '14 at 18:30
3

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).

| improve this answer | |
  • Ok. If I keep few columns in INCLUDE will it slow down the performance. We use the first five columns in the same query. So is it advisable to keep few under include. Thanks Unmesh – Unmesh Oct 6 '14 at 8:15
  • If adding the columns to INCLUDE will make the index a covering index (all columns in the query(s) for that table are in the index) then it will increase performance. If not then you probably don't need to mess with the INCLUDE at all. – Kenneth Fisher Oct 6 '14 at 11:44
0

It's the NVARCHAR(MAX) (Column F) that is generating the error. The largest possible value for it is above the 900 bytes.

You could create a non-clustered index that doesn't use that column. Or you could implement fulltext index for that column.

But you mention searching on those fields most often. Do all queries use all of those fields? Or do some use some fields and others use different fields? The best index may not necessarily be using all of those fields.

| improve this answer | |
  • 3
    Even without that the key length is still potentially over 900 bytes. (3 * 255 = 765) but nvarchar is 2 bytes per character so double that. – Martin Smith Oct 3 '14 at 20:17
  • I wont include CloumnF as of now(I will implement full text index on that later). However the error is still there when I am creating the non clustered index on the rest 5 columns. Yes all those fields are in the same query. Will it hamper the performance if I create five separate indexes? – Unmesh Oct 6 '14 at 8:11
  • SQL Server won't be able to use multiple indexes to satisfy the query like that. But you might try looking at your query and determining if the index is truly going to be beneficial. How are the values of the index laid out? What kinds of data are in the various columns? A column on the Datetime field alone may prove of sufficient benefit. – Jonathan Fite Oct 7 '14 at 15:26
0

One question, do you really need all the 6 columns in the same index? This would mean that the query you're using will have all those columns in the where clause, and that the data distribution in the columns is such that you really need all the columns to limit the data (and it's not "like '%xxx%'" kind of criteria). If your data is really unicode strings with 255 characters, it sounds like just separate indexes for each of the fields would be ok for normal search operations. If the data contains a lot of same strings, so that indexing just one field still returns a lot of rows, maybe you should put the strings into a separate table and use id numbers. And just to be sure... the data really needs nvarchar(255) columns? Sometimes developers / tools seem to create too long fields just in case, and sometimes it's not even necessary to have unicode.

| improve this answer | |
  • Yes, I need the first five columns to be indexed as they are the search columns in the query. On columnF as it is a text field, I will create a FULL TEXT SERACH on it. We need nvarchar(255) columns because in past we have seen values upto that length. If i create separate indexes on each of those 5 columns, will it slow down the search? Thanks Unmesh – Unmesh Oct 6 '14 at 8:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.