SQL Server: The total size of the index or primary key cannot exceed 900 bytes

Question

I am trying to put an index in a column that will contain urls. Since the maximum URL length is more than 2000 characters, I set the data type to NVARCHAR (3000). When I did this, I got the error The total size of an index or primary key cannot exceed 900 bytes . Since I may need to search for entries by URL, I will need an index in my URL column. Is there any way to limit this restriction?

Answer 1

You can create a calculated column for the checksum of the URL, and then use the checksum in the query. Checksums will not be unique, but they will quickly reduce the number of possible matches.

First add the calculated column to your table, for example:

 Alter Table YourTableName Add URL_HASH As CheckSum(URL) 

Now index the column as follows:

 Create Index idx_YourTableName_URL_HASH On YourTableName(URL_HASH) 

Now you can write a query that will search the index to find the row you are looking for:

 Select URL From YourTableName Where URL_HASH = CheckSum(N'google.com') And URL = 'google.com' 

This method should work very well for exact matches. If you need partial matches, you'd better use full-text search.

Answer 2

A full SQL Server search is what you probably want.

http://msdn.microsoft.com/en-us/library/ms142571.aspx

You need to jump over some small hoops by setting them against a simple index, but this should not be super difficult.