Sql Server - Index on nvarchar field

sql sql-server nvarchar
Parminder picture Parminder · Feb 7, 2014 · Viewed 16.8k times · Source

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

Kaspars Ozols picture Kaspars Ozols · Feb 7, 2014

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

Related questions

Determining Nvarchar length

I've read all about varchar versus nvarchar. But I didn't see an answer to what I think is a simple question. How do you determine the length of your nvarchar column? For varchar it's very simple: my Description, for example, …

Read More
For Nvarchar(Max) I am only getting 4000 characters in TSQL?

This is for SS 2005. Why I am i only getting 4000 characters and not 8000? It truncates the string @SQL1 at 4000. ALTER PROCEDURE sp_AlloctionReport( @where NVARCHAR(1000), @alldate NVARCHAR(200), @alldateprevweek NVARCHAR(200)) AS DECLARE @SQL1 NVARCHAR(Max) SET @SQL1 = 'SELECT DISTINCT VenueInfo.VenueID, …

Read More
What's the SQL national character (NCHAR) datatype really for?

As well as CHAR (CHARACTER) and VARCHAR (CHARACTER VARYING), SQL offers an NCHAR (NATIONAL CHARACTER) and NVARCHAR (NATIONAL CHARACTER VARYING) type. In some databases, this is the better datatype to use for character (non-binary) strings: In SQL Server, NCHAR is …

Read More