Best database field type for a URL

varchar(max) for SQLServer2005

varchar(65535) for MySQL 5.0.3 and later

This will allocate storage as need and shouldn't affect performance.

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 CLOBs being far slower than a simple string datatype like VARCHAR.

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

• Obviously, the Twitter approach is nicer, but for my app's needs, recommending a URL shortener was sufficient.
• URL shorteners have their drawbacks, such as security concerns. In my case, it's not a huge risk because the URL's are not public and not heavily used; however, this obviously won't work for everyone. safe.mn appears to block a lot of spam and phishing URL's, but I would still recommend caution.
• Be sure to note that you shouldn't force your users to use a URL shortener. For most cases (at least for my app's needs), 500 characters is overly sufficient for what most users will be using it for. Only use/recommend a URL shortener for overly-long links.

1. Lowest common denominator max URL length among popular web browsers: 2,083 (Internet Explorer)

2. http://dev.mysql.com/doc/refman/5.0/en/char.html
   Values in VARCHAR columns are variable-length strings. The length can be specified as a value from 0 to 255 before MySQL 5.0.3, and 0 to 65,535 in 5.0.3 and later versions. The effective maximum length of a VARCHAR in MySQL 5.0.3 and later is subject to the maximum row size (65,535 bytes, which is shared among all columns) and the character set used.

3. So ...
   < MySQL 5.0.3 use TEXT
   or
   >= MySQL 5.0.3 use VARCHAR(2083)