What is the best column type for URL?

Question

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

Type: VARCHAR or NVARCHAR?

Length?

Similar question for MySQL.

Biotype

Answer 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).

Riffraff

2083 is the maximum supported by IE, which is the most restrictive of the mainstream browsers. But they don't make it clear whether that is pre- or post-URL encoding... – Riffraff

So? What would the length that you recommend? – Biotype

Large nvarchar or varchar columns can cause indexing and row size problems in SQL Server. It depends on a lot of factors - what you are trying to achieve, what else is in the table. Hard to give a catch-all answer. – Riffraff

If you want to cover your bases, then go for nvarchar(2083) and don't URL encode. – Riffraff

Are there any drawbacks to just using varchar(max) / nvarchar(max) ? – Bowden

The drawback to using max would be 'masking an underlying problem'. It could cause problems with your users but would not show up in any error log. Restricting to 2083 would help you catch and diagnose the cause or source of URLs that exceeded browser limits. – Lovato

Answer 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.

Boffin

One of the usage would be for OpenID URLs, so it'll be open for any valid URL. – Biotype

The RFC for HTTP has no maximum length for a URL. Microsoft have an MSDN KB at support.microsoft.com/kb/q208427 that states max length is 2083 (at least for all IE up to IE8; no word on max length in IE8). For other browsers, see boutell.com/newfaq/misc/urllength.html although this is out-of-date (doesn't cover Firefox 2 or 3), it should indicate that there is no standard length in use by browsers. – Knipe

Answer 3

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

Baptism

Depends on what the URL is storing. If I'm only using it to point to other sites, I'll usually make it 300 characters. If I'm going to be using it to point deeper into a site or as links to files, etc, I usually go with 2000. I've never run into situations where a URL ran more than that many characters (or even close to it), but again I always err on the side of caution. Drive space is pretty cheap nowadays and I don't concern myself nearly as much with space constraints as I once did. – Baptism

Answer 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.

Switchback