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

Question

I am trying to put an index on a column that will hold URLs. Since the maximum length of a URL an be over 2000 characters long 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 records by URL i will need an index on my URL Column. Is there a way around this limitation?

Answer 1

You could create a computed column for the checksum of the URL and then use the checksum in the query. Checksums will not be unique, but it will quickly narrow down the number of possible matches.

First, add a computed column to your table, like this:

Alter Table YourTableName Add URL_HASH As CheckSum(URL)

Now index the column like this:

Create Index idx_YourTableName_URL_HASH On YourTableName(URL_HASH)

Now you can write a query that will do an index seek 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 want partial matches, you're better off using the full text search functionality.

Answer 2

SQL Server Full Text search is what you'll probably want.

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

You have to jump through some minor hoops with setting it up vs a simple index but it shouldn't be super hard.