March 4, 2010 at 11:24 am
Hey,
I need to store an URL in my SQL Server 2005 database... which data type should I use? I was thinking in use nvarchar, but I'm not sure about the "size"...
Thanks in advance.
March 4, 2010 at 2:25 pm
March 4, 2010 at 2:27 pm
If the url is known to contain non-unicode characters then varchar would be fine, if you are not certain, then you'll have to stick with nvarchar to be sure to cover your bases, as far as size goes http://msdn.microsoft.com/en-us/library/ms187752.aspx lists the size of varchar as being the length of the data plus 2 bytes. Where nvarchar is the (length of the data times 2 )plus 2 bytes. So nvarchar would be roughly twice the size, but you would be covered for unicode characters.
A little further googling found the longest url at around 1700 characters. This equates to the largest single size of your field at 3.4k as nvarchar and 1.7k as varchar.
Some more found the average character length of url at around 34 characters.
Hope that helped.
March 9, 2010 at 6:51 am
dias.nanda (3/4/2010)
I need to store an URL in my SQL Server 2005 database... which data type should I use? I was thinking in use nvarchar, but I'm not sure about the "size"...
The RFC does not specify a maximum length, but 2083 is the maximum length in Internet Explorer (reference: http://support.microsoft.com/kb/208427).
You might like to create an alias data type for it...
USE tempdb;
GO
CREATE TYPE URI FROM VARCHAR(2083) NULL;
GO
DECLARE @URL URI;
SET @URL = 'http://support.microsoft.com/kb/208427';
SELECT url = @URL;
GO
DROP TYPE URI;
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 10, 2010 at 4:45 am
The RFC does not specify a maximum length, but 2083 is the maximum length in Internet Explorer
Every other browser allows much longer URLs, of course...according to this site, Firefox, Safari et al. are all quite comfortable with URLs of 80,000 characters or more:
http://www.boutell.com/newfaq/misc/urllength.html
Probably a bit of a pain to have to use a varchar(max) field just to accommodate that, though!
March 10, 2010 at 4:51 am
For me the question isn't how big can they get but what are YOU using them for. The nvarchar(1000) suggestion may be overkill for what you are doing. So that is my question, what are you trying to accomplish?
CEWII
March 10, 2010 at 8:33 am
If I understand data types correctly (that's a huge assumption) then it doesn't really matter what the length is set to, nvarchar(max) would be exactly the same size (in bytes) that nvarchar(15) would be with a url like http://www.123.com.
Is that correct?
March 10, 2010 at 7:18 pm
Wesley Norton (3/10/2010)
If I understand data types correctly (that's a huge assumption) then it doesn't really matter what the length is set to, nvarchar(max) would be exactly the same size (in bytes) that nvarchar(15) would be with a url like http://www.123.com. Is that correct?
Yes, and no 😉
In the example given, yes the storage would look exactly the same. If the data stored exceeded 8000 bytes, it would be stored as a LOB of course. However, depending on the rest of the data stored in the same row, the MAX column might be moved off-row into a LOB allocation unit at smaller sizes: it's all related to the maximum row length of 8060 bytes.
There are two other (related) differences though, regardless of storage considerations. SQL Server treats MAX data types differently, since they might conceivably reach 2GB in size. In particular, operations on MAX types tend to be slower. The second difference relates to query plans, which again must be constructed to take the maximum possible size into account.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 11, 2010 at 9:17 am
Thanks, Paul. I have a long way to go. And you guys make it a whole lot easier.
March 11, 2010 at 9:53 pm
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply