Sql Server - Index on nvarchar field

Question

What is the good approach to keep a nvarchar field unique. I have a field which is storing URLs of MP3 files. The URL length can be anything from 10 characters to 4000. I tried to create an index and it says it cannot create the index as the total length exceeds 900 bytes.

If the field is not indexed, it's going to be slow to search anything. I am using C#, ASP.net MVC for the front end.

Answer 1

You could use CHECKSUM command and put index on column with checksum.

--*** Add extra column to your table that will hold checksum
ALTER TABLE Production.Product
ADD cs_Pname AS CHECKSUM(Name);
GO

--*** Create index on new column
CREATE INDEX Pname_index ON Production.Product (cs_Pname);
GO

Then you can retrieve data fast using following query:

SELECT * 
FROM Production.Product
WHERE CHECKSUM(N'Bearing Ball') = cs_Pname
AND Name = N'Bearing Ball';

Here is the documentation: http://technet.microsoft.com/en-us/library/ms189788.aspx

Answer 2

You can use a hash function (although theoretically it doesn't guarantee that two different titles will have different hashes, but should be good enough: MD5 Collisions) and then apply the index on that column.

MD5 in SQL Server

Answer 3

You could create a hash code of the url and use this integer as a unique index on your db. Beware of converting all characters to lowercase first to ensure that all url are in the same format. Same url will generate equal hash code.