Menu
Is it possible to create a primary key or unique index on an NVARCHAR(4000) column used to hold URLs? There appears to be a 900 byte limitation on unique indexes in SQL 2008.
519
Limitations and Restrictions. A unique index, UNIQUE constraint, or PRIMARY KEY constraint cannot be created if duplicate key values exist in the data.
A unique index can be clustered or non-clustered. In this syntax: First, specify the name of the unique index after the CREATE UNIQUE INDEX keywords. Second, specify the name of the table to which the index associated and a list of columns that will be included in the index.
No, you dont have to index it again. When you specify UNIQUE KEY , the column is indexed. So it has no difference in performance with other indexed column (e.g. PRIMARY KEY) of same type. However if the type is different, there will be little performance difference.
It is better to create a prefixed index on, say, first 50 characters for fast lookups and a UNIQUE index on the MD5 hash (or another hash which is unique enough).
CREATE TABLE urls (url NVARCHAR(4000) NOT NULL, url_prefix AS LEFT(url, 50), url_hash AS HashBytes('MD5', url))
CREATE INDEX ix_urls_prefix ON urls (url_prefix)
CREATE INDEX ix_urls_hash ON urls (url_hash)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
