15

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.

Matthieu
  • 2,736
  • 4
  • 57
  • 87
Parminder
  • 3,088
  • 6
  • 37
  • 61

3 Answers3

31

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

Kaspars Ozols
  • 6,967
  • 1
  • 20
  • 33
  • isn't the following line in the documentation an issue? `CHECKSUM () does not guarantee unique results.` – Bertvan Aug 08 '19 at 11:27
  • 2
    @Bertvan This is why checking checksum is not enough and one still has to look at field of interest for exact match. Checksum just helps to narrow down row selection, so that only rows with collided checksum need to be checked for exact match. – Kaspars Ozols Aug 09 '19 at 10:28
5

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

Community
  • 1
  • 1
Joao Leal
  • 5,533
  • 1
  • 13
  • 23
2

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.

Oscar
  • 13,594
  • 8
  • 47
  • 75
  • Uh, URIs are case-sensitive… – binki Jan 23 '17 at 20:49
  • @binki Not really.. It depends of the backing server. RFC states "..When comparing two URIs to decide if they match or not, a client SHOULD use a case-sensitive octet-by-octet comparison of the entire URIs" but SHOULD is just a recomendation, so IIS is not case sensitive, Apache does. See this thread: http://stackoverflow.com/questions/15641694/are-uris-case-insensitive – Oscar Jan 24 '17 at 08:12
  • Then you're not being portable to OSes with case sensitive file systems or supporting remote storage on case sensitive file servers. There are reasons that RFCs recommend things… what if the MP3 is stored on a service like imgur or YouTube where the key is a base64 hash which is case sensitive? – binki Jan 24 '17 at 13:59
  • @binki I'm not saying you aren't right, but it doesn't have to do with the actual question, as long as this hash is used for a private implementation of and index, not to be exposed elsewhere. – Oscar Jan 24 '17 at 14:42