What is the best column type for a URL field for SQL Server?
Type: VARCHAR or NVARCHAR?
Length?
Similar question for MySQL.
+4
A:
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.
Andrew Hare
2009-07-21 15:27:54
One of the usage would be for OpenID URLs, so it'll be open for any valid URL.
Adrian Godong
2009-07-21 15:28:51
Then use nvarchar.
Andrew Hare
2009-07-21 15:31:55
How about length?
Adrian Godong
2009-07-21 15:33:16
The RFC for HTTP has no maximum length for a URL. Microsoft have an MSDN KB at http://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 http://www.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.
Chris J
2009-07-22 09:46:38
+1
A:
For something like that I'd always err on the side of caution and use the nvarchar.
BBlake
2009-07-21 15:29:40
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.
BBlake
2009-07-21 16:12:42
+1
A:
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.
Matt Sach
2009-07-21 15:29:44
+4
A:
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).
David M
2009-07-21 15:34:25
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...
David M
2009-07-21 15:36:26
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.
David M
2009-07-21 15:39:17
If you want to cover your bases, then go for nvarchar(2083) and don't URL encode.
David M
2009-07-21 15:49:11