VARCHAR(512)
(or similar) should be sufficient. However, since you don't really know the maximum length of the URLs in question, I might just go direct to TEXT
. The danger with this is of course loss of efficiency due to CLOB
s being far slower than a simple string datatype like VARCHAR
.
VARCHAR(512)
(or similar) should be sufficient. However, since you don't really know the maximum length of the URLs in question, I might just go direct to TEXT
. The danger with this is of course loss of efficiency due to CLOB
s being far slower than a simple string datatype like VARCHAR
.
Most web servers have a URL length limit (which is why there is an error code for "URI too long"), meaning there is a practical upper size. Find the default length limit for the most popular web servers, and use the largest of them as the field's maximum size; it should be more than enough.
Most web servers have a URL length limit (which is why there is an error code for "URI too long"), meaning there is a practical upper size. Find the default length limit for the most popular web servers, and use the largest of them as the field's maximum size; it should be more than enough.
varchar(max)
for SQLServer2005
varchar(65535)
for MySQL 5.0.3 and later
This will allocate storage as need and shouldn't affect performance.
You better use varchar(max) which (in terms of size) means varchar (65535)
.
This will even store your bigger web addresses and will save your space as well.
The max specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types. You can use the large-value data types to store up to 2^31-1 bytes of data.
See this article on TechNet about using Using Large-Value Data Types
This really depends on your use case (see below), but storing as TEXT
has performance issues, and a huge VARCHAR
sounds like overkill for most cases.
My approach: use a generous, but not unreasonably large VARCHAR
length, such as VARCHAR(500)
or so, and encourage the users who need a larger URL to use a URL shortener such as safe.mn
.
The Twitter approach: For a really nice UX, provide an automatic URL shortener for overly-long URL's and store the "display version" of the link as a snippet of the URL with ellipses at the end. (Example: http://stackoverflow.com/q/219569/1235702
would be displayed as stackoverflow.com/q/21956...
and would link to a shortened URL http://ex.ampl/e1234
)
Notes and Caveats
VARCHAR(512)
(or similar) should be sufficient. However, since you don't really know the maximum length of the URLs in question, I might just go direct to TEXT
. The danger with this is of course loss of efficiency due to CLOB
s being far slower than a simple string datatype like VARCHAR
.
Most browsers will let you put very large amounts of data in a URL and thus lots of things end up creating very large URLs so if you are talking about anything more than the domain part of a URL you will need to use a TEXT column since the VARCHAR/CHAR are limited.
VARCHAR(512)
(or similar) should be sufficient. However, since you don't really know the maximum length of the URLs in question, I might just go direct to TEXT
. The danger with this is of course loss of efficiency due to CLOB
s being far slower than a simple string datatype like VARCHAR
.
Most browsers will let you put very large amounts of data in a URL and thus lots of things end up creating very large URLs so if you are talking about anything more than the domain part of a URL you will need to use a TEXT column since the VARCHAR/CHAR are limited.
varchar(max)
for SQLServer2005
varchar(65535)
for MySQL 5.0.3 and later
This will allocate storage as need and shouldn't affect performance.
I don't know about other browsers, but IE7 has a 2083 character limit for HTTP GET operations. Unless any other browsers have lower limits, I don't see why you'd need any more characters than 2083.
You should use a VARCHAR with an ASCII character encoding. URLs are percent encoded and international domain names use punycode so ASCII is enough to store them. This will use much less space than UTF8.
VARCHAR(512) CHARACTER SET 'ascii' COLLATE 'ascii_general_ci' NOT NULL
Most web servers have a URL length limit (which is why there is an error code for "URI too long"), meaning there is a practical upper size. Find the default length limit for the most popular web servers, and use the largest of them as the field's maximum size; it should be more than enough.
You'll want to choose between a TEXT or VARCHAR column based on how often the URL will be used and whether you actually need the length to be unbound.
Use VARCHAR with maxlength >= 2,083 as micahwittman suggested if:
Use TEXT if :
varchar(max)
for SQLServer2005
varchar(65535)
for MySQL 5.0.3 and later
This will allocate storage as need and shouldn't affect performance.
Most browsers will let you put very large amounts of data in a URL and thus lots of things end up creating very large URLs so if you are talking about anything more than the domain part of a URL you will need to use a TEXT column since the VARCHAR/CHAR are limited.
This really depends on your use case (see below), but storing as TEXT
has performance issues, and a huge VARCHAR
sounds like overkill for most cases.
My approach: use a generous, but not unreasonably large VARCHAR
length, such as VARCHAR(500)
or so, and encourage the users who need a larger URL to use a URL shortener such as safe.mn
.
The Twitter approach: For a really nice UX, provide an automatic URL shortener for overly-long URL's and store the "display version" of the link as a snippet of the URL with ellipses at the end. (Example: http://stackoverflow.com/q/219569/1235702
would be displayed as stackoverflow.com/q/21956...
and would link to a shortened URL http://ex.ampl/e1234
)
Notes and Caveats
varchar(max)
for SQLServer2005
varchar(65535)
for MySQL 5.0.3 and later
This will allocate storage as need and shouldn't affect performance.
You better use varchar(max) which (in terms of size) means varchar (65535)
.
This will even store your bigger web addresses and will save your space as well.
The max specifier expands the storage capabilities of the varchar, nvarchar, and varbinary data types. varchar(max), nvarchar(max), and varbinary(max) are collectively called large-value data types. You can use the large-value data types to store up to 2^31-1 bytes of data.
See this article on TechNet about using Using Large-Value Data Types
I don't know about other browsers, but IE7 has a 2083 character limit for HTTP GET operations. Unless any other browsers have lower limits, I don't see why you'd need any more characters than 2083.
You'll want to choose between a TEXT or VARCHAR column based on how often the URL will be used and whether you actually need the length to be unbound.
Use VARCHAR with maxlength >= 2,083 as micahwittman suggested if:
Use TEXT if :
I don't know about other browsers, but IE7 has a 2083 character limit for HTTP GET operations. Unless any other browsers have lower limits, I don't see why you'd need any more characters than 2083.
You should use a VARCHAR with an ASCII character encoding. URLs are percent encoded and international domain names use punycode so ASCII is enough to store them. This will use much less space than UTF8.
VARCHAR(512) CHARACTER SET 'ascii' COLLATE 'ascii_general_ci' NOT NULL
Most browsers will let you put very large amounts of data in a URL and thus lots of things end up creating very large URLs so if you are talking about anything more than the domain part of a URL you will need to use a TEXT column since the VARCHAR/CHAR are limited.
Most web servers have a URL length limit (which is why there is an error code for "URI too long"), meaning there is a practical upper size. Find the default length limit for the most popular web servers, and use the largest of them as the field's maximum size; it should be more than enough.
Source: Stackoverflow.com