2

I have table which contains two columns. When I tried to create index for the NVARCHAR(2000) column, it is showing warning message like:

Warning! The maximum key length is 900 bytes

But the index was created for this table in SQL Server:

Create Table Test
(
     Id Int primary key,
     ProdName Nvarchar(2000)
)

Inserted 1 million records.

Created index ix_Test on Test(ProdName)

Will SQL Server use this index on the ProdName column in where condition? Because it created with warning message.

Usually does the Like operator not use the index?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ram
  • 727
  • 2
  • 16
  • 33
  • 2
    Do you really need an index on an `nvarchar(2000)`? If you're really going to have values that long then you might want to look at a full text index. – Thom A Dec 16 '18 at 11:36

1 Answers1

5

When creating index SQL Server is checking column's metadata. Maximum index length for SQL Server 2012 is 900 bytes and for SQL Server 2016(1700 bytes).

For NVARCHAR(2000) maximum size in bytes is 4000 and it exceeds index's maximum limit.

Create Table Test(Id Int primary key,ProdName Nvarchar(2000));
INSERT INTO Test VALUES (1, REPLICATE('0123456789',45));
Create index ix_Test ON Test(ProdName);
INSERT INTO Test VALUES (2, REPLICATE('0123456789',46));
-- Operation failed. The index entry of length 920 bytes for the index    
--'ix_Test' exceeds the maximum length of 900 bytes.

db<>fiddle demo

Warning! The maximum key length is 900 bytes. The index 'ix_Test' has maximum length of 4000 bytes. For some combination of large values, the insert/update operation will fail.

It means that inside your column you have string values that are shorter than 450 characters(900 bytes).

SELECT MAX(DATALENGTH(ProdName))
FROM Test;
-- should be lower than 900

As for second question index will be used as long as condition is SARGable:

SELECT *
FROM Test
WHERE ProductName = 'ABC' -- index usage;

SELECT *
FROM Test
WHERE ProductName = 'AB%'; -- index usage

SELECT *
FROM Test
WHERE ProductName LIKE '%B%'; -- no index seek, index/table scan instead
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Thank you .For the first question,index is created for the table with warning message.so will it not use this index for this column value searching in where condition?Am i Correct? – Ram Dec 16 '18 at 11:59
  • 1
    @Ram It will use indexes for SARGable condition. Simply you won't be able to create index or insert/update for string that are longer than 450 characters. – Lukasz Szozda Dec 16 '18 at 12:00
  • 1
    fine print note: A condition like `... where Foo like '%Bar%'` will not result in an index _seek_, but may benefit from an index _scan_. More complex conditions, e.g. `... where ShoeSize = '9EEE' and Foo like '%Bar%'` might benefit from more complex indexes, e.g. an index on `ShoeSize` including `Foo` or a _covering index_. Hence, "no index usage" may be a little harsh. – HABO Dec 16 '18 at 17:20
  • @HABO Yes, you are right. Query would run a bit faster. Of course not as fast as with index seek but always a small gain is better than none :) – Lukasz Szozda Dec 16 '18 at 19:44