Data Type for URLs storage

Question

Post reply

Data Type for URLs storage

dias.nanda

SSC Veteran

Points: 245
More actions
March 4, 2010 at 11:24 am

#230463

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.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Dugi SSCoach Points: 17998 More actions · Answer 1

Dugi

SSCoach

Points: 17998

March 4, 2010 at 2:25 pm

#1128782

Yep what to say try nvarchar(1000) ! I think this is enough characters number that can be one URL.

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Wesley Norton SSCrazy Points: 2797 More actions · Answer 2

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.

Paul White SSC Guru Points: 150457 More actions · Answer 3

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

paul.knibbs SSCoach Points: 15320 More actions · Answer 4

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!

Elliott Whitlow SSC Guru Points: 102296 More actions · Answer 5

Elliott Whitlow

SSC Guru

Points: 102296

March 10, 2010 at 4:51 am

#1131262

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

Wesley Norton SSCrazy Points: 2797 More actions · Answer 6

Wesley Norton

SSCrazy

Points: 2797

March 10, 2010 at 8:33 am

#1131447

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?

Paul White SSC Guru Points: 150457 More actions · Answer 7

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