I added a column to a table that has varchar(1500) and when was I trying to create an index on that column it returned the following error:

 

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

Answer : 

The storage size for varchar is the actual length of the data entered + 2 bytes. Even though the column itself has that 2 byte overhead, you can put up to 900 byte varchar values into a column which is indexed.

The maximum number of bytes in a clustered index key cannot exceed 900 in SQL Server. For a nonclustered index key, the maximum is 1700 bytes.

You can define a key using variable-length columns whose maximum sizes add up to more than the limit. However, the combined sizes of the data in those columns can never exceed the limit.

In a nonclustered index, you can include extra non-key columns, and they do not count against the size limit of the key. The non-key columns might help some queries perform better.

In practice, you can create an index on a column larger than 900 bytes in size, but you will have a problem if you actually try to insert something larger than 900 bytes:

create table test ( col varchar(1000));

create index test_index on test (col);

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

insert into test select cast(replicate('x', 899) as varchar(1000)); -- Success
insert into test select cast(replicate('y', 900) as varchar(1000)); -- Success

insert into test select cast(replicate('z', 901) as varchar(1000)); -- Fail

-- Msg 1946, Level 16, State 3, Line 8

-- Operation failed. The index entry of length 901 bytes for the index 'test_index' exceeds the maximum length of 900 bytes.

So as you can see that the 900-byte limit includes all columns of a given index key, as this example shows:

create table test (
      col varchar(1000)
    , otherCol bit -- This column will take a byte out of the index below, pun intended
);

create index test_index on test (col, otherCol);

insert into test select cast(replicate('x', 899) as varchar(1000)), 0; -- Success
insert into test select cast(replicate('y', 900) as varchar(1000)), 0; -- Fail
insert into test select cast(replicate('z', 901) as varchar(1000)), 0; -- Fail

So basically we need to keep the size of the value in the column below 900 or you can generate a hashcode as explained below:

For indexing columns that have Large sizes ,sql server indexes only columns that have size up to 900 bytes.

To solve this problem

1. I added a column hashCol to generate hashcode of Type SHA1 for MyCol

alter table myTable
    add 
    hashCol AS HASHBYTES('SHA1', CONVERT(VARCHAR(90), MyCol))

2. I added a unique constrain for hashCol to uniquely Identify MyCol

 ALTER  TABLE  myTable
     ADD CONSTRAINT hashCol_Unique UNIQUE (hashCol)

By this way I overcame the problem of Indexing columns that have large size

references

Generate Unique hash for a field in SQL Server in-sql-server.

 

How to calculate the Size of an Index Key:

When you design an index that contains many key columns, or large-size columns, calculate the size of the index key to make sure that you do not exceed the maximum index key size. SQL Server retains the 900-byte limit for the maximum total size of all index key columns. This excludes nonkey columns that are included in the definition of nonclustered indexes.

To calculate the size of an index key, follow these steps.

  1. Display the properties of the table columns on which the index will be based. You can do this by using the sys.columns catalog view.
  2. Sum the length of each column that will be defined in the index key.

    For example, the following statement aggregates the max_length column of the sys.columns catalog view for the specified columns in the Person.Address table.

USE AdventureWorks2008R2;
GO
SELECT SUM(max_length)AS TotalIndexKeySize
FROM sys.columns
WHERE name IN (N’AddressLine1′, N’AddressLine2′, N’City’, N’StateProvinceID’, N’PostalCode’)
AND object_id = OBJECT_ID(N’Person.Address’);

Ref: Calculating the Size of an Index Key