In previous SQL Server versions, the total size of all index keys could not exceed the limit of 900 bytes. You were actually allowed to create a nonclustered index, even if the sum of the maximum length of all its key columns exceeded this limit. The limit affects only columns used as index keys; you can use very large columns in a nonclustered index as included columns.

To demonstrate this, we will create a sample table. Note that this code should be executed in a SQL Server 2014/2012/2008 instance:

USE tempdb; 
CREATE TABLE dbo.T1(id INT NOT NULL PRIMARY KEY CLUSTERED, c1 NVARCHAR(500) NULL, c2 NVARCHAR(851) NULL); 

As you can see in the code, the maximal data length of the column c1 is 1,000 bytes. Let's now try to create a nonclustered index on this column:

CREATE INDEX ix1 ON dbo.T1(c1); 

Since the table is empty, the command has been executed successfully with no errors, but with the following warning message:

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

As the message says, you can live with the index in harmony if the size of the actual data in the index columns does not exceed the limit of 900 bytes. The Query Optimizer will even use it in execution plans and it will behave as a normal index. Adding a row with data within the maximum key length will be successful, as shown in the following code:

INSERT INTO dbo.T1(id,c1, c2) VALUES(1, N'Mila', N'Vasilije'); 

However, when you try to insert or update a row with data longer than the key size limit, the statement will fail:

INSERT INTO dbo.T1(id,c1, c2) VALUES(2,REPLICATE('Mila',113), NULL); 

This action in SQL Server 2014 results in an error message and the INSERT statement fails, as follows:

Msg 1946, Level 16, State 3, Line 7
Operation failed. The index entry of length 904 bytes for the index 'ix1' exceeds the maximum length of 900 bytes.

In SQL Server 2016, the behavior remains the same with the difference that the maximum index key size for nonclustered indexes has been increased from 900 to 1,700 bytes. Let's repeat the previous steps, but this time in an instance running SQL Server 2016:

DROP TABLE IF EXISTS dbo.T1; 
CREATE TABLE dbo.T1(id INT NOT NULL PRIMARY KEY CLUSTERED, c1 NVARCHAR(500) NULL, c2 NVARCHAR(851) NULL); 
GO 
CREATE INDEX ix1 ON dbo.T1(c1); 

There is no warning after this action, since the new limit is 1,700 bytes. However, when you add an index on the c2 column, this won't work:

CREATE INDEX ix2 ON dbo.T1(c2); 

You get the well-known warning message, but with a different limit:

Warning! The maximum key length for a nonclustered index is 1700 bytes. The index 'ix2' has maximum length of 1702 bytes. For some combination of large values, the insert/update operation will fail.

As you can see, the only difference is the different maximum number of bytes.

Now, you can index wider columns than you could in previous versions. For instance, a text column with 500 Unicode characters can be used as a key in a nonclustered index in SQL Server 2016.