Varchar sizing question

Question

Post reply

Varchar sizing question

Dave Morrison

SSCrazy

Points: 2017
More actions
June 11, 2010 at 7:34 am

#225480

Hi all,
when using a varchar (or nvarchar for that matter) what is the difference between hypothetically storing, say a 10 character string in a varchar(10) and a varchar(8000) for example?
What diference does it make how large you define a varchar or nvarchar field to be (other than the obvious ability to store more data :rolleyes: and the whole MAX thing)
I think in terms of storage, indexing, overheads etc?
I wanted ask this quick question and I feel like a bit of a numpty for asking it to be honest :ermm:
I really feel like I should know this and can find no information on it anywhere
Thanks in advance

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

i think the biggest impact on varchar sizes has to do with the ability to index the column.

if the column is bigger than 900 bytes, the index rasies an error, so you can't index big monster columns:

create table biggerVarchar(id int identity(1,1) not null primary key,

sometext varchar(1500) )

create index ix_sometext on biggerVarchar(sometext)

Warning! The maximum key length is 900 bytes.

The index 'ix_sometext' has maximum length of 1500 bytes.

For some combination of large values, the insert/update operation will fail.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dave Morrison SSCrazy Points: 2017 More actions · Answer 2

Lowell (6/11/2010)
i think the biggest impact on varchar sizes has to do with the ability to index the column.
if the column is bigger than 900 bytes, the index rasies an error, so you can't index big monster columns:
create table biggerVarchar(id int identity(1,1) not null primary key,
sometext varchar(1500) )
create index ix_sometext on biggerVarchar(sometext)
Warning! The maximum key length is 900 bytes.
The index 'ix_sometext' has maximum length of 1500 bytes.
For some combination of large values, the insert/update operation will fail.

Thanks for your input

So other than indexing restrictions, there is no difference if I declare a varchar(8000) and a varchar(10) to store a 10 character string?

Seems odd as why even bother giving the ability to size varchar fields? why not just have a couple of different varchar variants that are of certain sizes?

Lowell SSC Guru Points: 323504 More actions · Answer 3

Stumpy D (6/11/2010)[hrSo other than indexing restrictions, there is no difference if I declare a varchar(8000) and a varchar(10) to store a 10 character string?
Seems odd as why even bother giving the ability to size varchar fields? why not just have a couple of different varchar variants that are of certain sizes?

well, as soon as you implement the one-size-fits-most plan with some variants of certain max sizes, someone comes along and says, well i need one MORE variant so i can make sure they don't put in too many digits for a SSN/License number/Purchase Order/etc;

the current design lets you put max size that is related directly to your data for your specific business purpose.

storage wise, you already know it's the actual data size plus a little more (a two byte pointer and a char(0) character? i think) so letting you be flexible for values between 1 and 8000 is more flexible than say, increments of 10 or 100 or something.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Dave Morrison SSCrazy Points: 2017 More actions · Answer 4

Lowell (6/11/2010)
Stumpy D (6/11/2010)[hrSo other than indexing restrictions, there is no difference if I declare a varchar(8000) and a varchar(10) to store a 10 character string?
Seems odd as why even bother giving the ability to size varchar fields? why not just have a couple of different varchar variants that are of certain sizes?
well, as soon as you implement the one-size-fits-most plan with some variants of certain max sizes, someone comes along and says, well i need one MORE variant so i can make sure they don't put in too many digits for a SSN/License number/Purchase Order/etc;
the current design lets you put max size that is related directly to your data for your specific business purpose.
storage wise, you already know it's the actual data size plus a little more (a two byte pointer and a char(0) character? i think) so letting you be flexible for values between 1 and 8000 is more flexible than say, increments of 10 or 100 or something.

I can see your point on the sizing thing I suppose.

Yeah I know the storage of a varchar in bytes is the number of characters + 2, and the obvious overhead of sizing the storage to fit the data etc.

Thanks for your input

🙂