what could be the datatype for the column having weblinks.

Question

hi all,

One curious question. if i have a table with column with weblinks then what could be the datatype nvarchar or varchar. and what could be the size of that datatype?

Answer 1

I'd say varchar(1000) would be enough (unless you're going to store some Amazon URLs, of course) :). You don't need nvarchar because national URLs are experimental and are eventually converted to Latin with special characters.

Typically Web servers set fairly generous limits on length for genuine URLs e.g. up to 2048 or 4096 characters.

So, if you want to be safe and still don't want to use varchar(max), you can use varchar(2048) and varchar(4096), respectively.

Answer 2

For data with embedded URLs, you can use either varchar or nvarchar. The only difference between nvarchar and varchar is nvarchar is a varchar that natively supports unicode data. Also, the storage space is larger: varchar is 8-bit, while unicode is 16-bit, so double the space.

Answer 3

In general, use nvarchar.
http://stackoverflow.com/questions/35366/varchar-vs-nvarchar-performance

RFC2616 says there's no maximum length of a URL, but 2000 is probably safe.
http://stackoverflow.com/questions/417142/what-is-the-maximum-length-of-an-url

Answer 4

You should use nvarchar since chinese national characters were allowed in URL names and varchar can't handle those. Maximum URL size is 2083 characters (at least in IE), but you don't see those quite often. If you want to be completely sure that you can handle all URLs you shuold use nvarchar(2083).

Answer 5

A future-proof solution would be nvarchar, since recent movements toward full unicode domain names are noticable, e.g. Russia Begins Registering Domains in Cyrillic.

Answer 6

URLs are subject to RFC1738:

URLs are written only with the graphic printable characters of the
US-ASCII coded character set. The octets 80-FF hexadecimal are not
used in US-ASCII, and the octets 00-1F and 7F hexadecimal represent
control characters; these must be encoded

This would place all 'weblinks' safely in the VARCHAR camp. With SQL Server 2008 R2 though you need not to worry anymore, since Unicode Compression is available (on Enterprise and DataCenter Editions).