What is the best column type for URL?

问题:

What is the best column type for a URL field for SQL Server?

Type: VARCHAR or NVARCHAR?

Length?

Similar question for MySQL.

回答1:

If you are prepared to always URL encode your URLs before you store them (an example turned up by Google was 中.doc URL encoding to %E4%B8%AD.doc) then you are safe sticking with varchar. If you want the non-ASCII characters in your URLs to remain readable in the database then I'd recommend nvarchar. If you don't want to be caught out, then go for nvarchar.

Since IE (the most restrictive of the mainstream browsers) doesn't support URLs longer than 2083 characters, then (apart from any considerations you might have on indexing or row length), you can cover most useful scenarios with nvarchar(2083).

回答2:

Will you be storing multilingual URLs? If so, use nvarchar, otherwise use varchar.

Edit: As for length, since IE limits URLs to being 2,083 characters you could use that as the maximum length of your field. In cases like these you want to use the lower common denominator as your URLs should be usable in all browsers. Really this is a practical cap on a field that most likely will never contain data the will get anywhere close to even IE's limits.

回答3:

For something like that I'd always err on the side of caution and use the nvarchar.

回答4:

For SQL Server, you'll be wanting to use NVARCHAR I'd have thought, as there are plans (if not action already) afoot for non-Roman characters in URLs. I can't really see any problems these days in the extra storage requirements for NVARCHAR over VARCHAR.