900 Bytes - Index Limit

SQL Server 2005 and 2008 place a limit on the maximum key size when creating a non-clustered index. The current limitation is 900 bytes. This means that the total size of the keyed columns included in the index definition cannot exceed 900 bytes. SQL Server will issue an error or a warning at the time of index creation depending on the columns specified.

Fortunately there are some options available to overcome this limitation.

INCLUDE COLUMNS

The first is to leverage the INCLUDE statement in CREATE INDEX. This feature enables us to remove some of the columns in the index key but still allows them to participate in the index. SQL Server places the data of these columns in the leaf level of the index. The benefits here are:
1) The Index can fully cover a query if the SELECT statement contains columns that could otherwise not participate in the index key.
2) The overall size of the index would be reduced.

HASHBYTES

The second option would be to add a column to the table of interest that would contain the hash value of the column to be searched on. This new column can then be indexed to provide better performance for searching. SQL Server provides a function, HASHBYTES, that returns the hash of the input provided. Algorithms supported by HASHBYTES include MD2, MD4, MD5, SHA, and SHA1. More detail on hash functions can be found here from RSA Labs

Updating the new column with the hash value can be accomplished like this:

UPDATE Table XYZ set ColA_Hash = HASHBYTE('SHA1',ColA)

With an index in place, filtering in the WHERE clause using the HASHBYTE of the interested text should result in improved performance.

WHERE
HASHBYTE('SHA1',text) = ColA_Hash

The use of HASHBYTES is an alternative but it should be noted that this will not provide a solution to searches filtered using the LIKE operator.