SQL Server String column as a unique key

Question

I am using a SQL server table to keep related information with a URL . So there is a Column as "URL" and its type is VARCHAR . In the application we have to use this URL as a unique key to query for information (We are using something like SELECT * FROM Table WHERE URL = "www.google.com\ig")

Is there any disadvantages or known drawbacks in using a URL as a unique key?

Answer 1

I would still create a clustered column key on the table e.g. An Auto number and then create a Unique Index on the URL column.

However I cant see why a URL is not unique and all should work as is.

Answer 2

Usually it is a better idea to have a numeric value rather than a string as a table key. See a discussion about this subject here: http://stackoverflow.com/questions/677823/database-primary-key-c-mapping-string-or-int.

As for using a URL, it should not pose you any problem provided that you have some basic rules to avoid inserting the same (equivalent) URL twice. That is, the database will interpret "www.google.com" and "http://www.google.com" as different strings, so you should have a rule like "URLs will never have the protocol identifier" or "URLs will never end with a slash", or whatever makes sense for your design.

Answer 3

You may benefit from numeric primary key if paging is needed. But still you can add numeric indexer in future. So there's no obstacle to make URL a PK.

Answer 4

As the others have said - I would definitely not use a long string like an URL as the primary/clustering key on a SQL Server table - but of course, you should feel free to put a unique constraint on that column, to make sure you don't get any duplicates!

You can either do a UNIQUE CONSTRAINT or a UNIQUE INDEX - the end result is pretty much the same (the unique constraint will also be using an index behind the scenes). The plus side for a UNIQUE INDEX is that you can reference it as a foreign key in a separate table, so I almost always use that approach:

CREATE UNIQUE NONCLUSTERED INDEX UIX_YourTable_URL ON dbo.YourTable(urlField)

If you should ever try to insert a value that's already in the table, that insert statement will be rejected with a SQL error and nothing bad can happen.