varchar actual size

Question

varchar actual size

klini

SSC-Addicted

Points: 458
More actions
December 30, 2008 at 4:50 pm

#203393

I create a table: create table xyz (field1 varchar(8000))
Insert a record: insert into xyz values('a')
How much storage space have I used? I have read the description of a varchar, but still am a bit confused. I would think I just ate at least 8000 bytes + misc stuff. But 'Help' seems to indicate it may only store the actual 'a' + misc stuff. There's a bit difference after a million of records or so.
My second question is how does SQL keep track of where records are? I don't need to know, but I'm the curious sort.
Thanks for your time, everyone!

Viewing 15 posts - 1 through 15 (of 23 total)

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

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Varchars only store that space that you are actually using.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 2

RBarryYoung (12/30/2008)
Varchars only store that space that you are actually using.

+ 2 bytes to store the length of data;)

P.

...and your only reply is slàinte mhath

klini SSC-Addicted Points: 458 More actions · Answer 3

Thanks.

I've meticulously tried to get my varchars to match what I expect the data to look like. I'll now just set everything to 8000 and save myself a few seconds of thinking.

I suppose that may be extreme, but I shouldn't get upset with other table-creators that do similar, eh?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 4

klini (12/30/2008)
Thanks.
I've meticulously tried to get my varchars to match what I expect the data to look like. I'll now just set everything to 8000 and save myself a few seconds of thinking.
I suppose that may be extreme, but I shouldn't get upset with other table-creators that do similar, eh?

So, you won't mind when you get an error that you can't save data to the table because it exceeds the maximum length allowed.

And yes, I would get upset with other table-creators that do similar. If you don't need 8000 characters to store the data, don't size the column that large. Remember, whatever size you allow is the size that is going to be used - whether or not it actually makes sense.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 5

Piotr Rodak (12/30/2008)
RBarryYoung (12/30/2008)
Varchars only store that space that you are actually using.
+ 2 bytes to store the length of data;)
P.

Also don't forget that if you have other columns or multiple varchars and you have varchars that exceed the 8KB page limit, you'll start storing the varchar in row overflow pages.

Interestingly, you can't store more than 8KB of fixed char columns, but you can store more than 8KB variable columns.

Random Technical Stuff[/url]

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 6

klini (12/30/2008)
Thanks.
I've meticulously tried to get my varchars to match what I expect the data to look like. I'll now just set everything to 8000 and save myself a few seconds of thinking.
I suppose that may be extreme, but I shouldn't get upset with other table-creators that do similar, eh?

If that's what you are trying to achieve, have a look at VARCHAR(MAX). It might be what you are looking for.

Random Technical Stuff[/url]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

What the heck... no table design needed... just store it all in one big ol' nasty EAV and call it a day... just think how easy indexing will be. :P;):hehe:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

klini SSC-Addicted Points: 458 More actions · Answer 8

RBarry says "Varchars only store that space that you are actually using"

but

Jeffrey says "whatever size you allow is the size that is going to be used"

Could I have some clarification?

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 9

Jeffrey meant that if you give room for a mistake to happen, it will happen. You shouldn't make VARCHAR any bigger than necessary or you stand a chance or rows being rejected when the 8060 max byte count is violated. And, no, you shouldn't use VARCHAR(MAX) if you don't have to because it has some "off page" overhead.

Neither Barry nor Jeffrey contradicted each other.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ta.bu.shi.da.yu Hall of Fame Points: 3985 More actions · Answer 10

Jeff Moden (12/30/2008)
Jeffrey meant that if you give room for a mistake to happen, it will happen. You shouldn't make VARCHAR any bigger than necessary or you stand a chance or rows being rejected when the 8060 max byte count is violated. And, no, you shouldn't use VARCHAR(MAX) if you don't have to because it has some "off page" overhead.
Neither Barry nor Jeffrey contradicted each other.

I just want to state for the record that I'm not recommending varchar(MAX) if you don't need it. However, varchar(MAX) has a number of excellent features that make it good for storing large amounts of text data.

However, I'm not sure what is meant by "You shouldn't make VARCHAR any bigger than necessary or you stand a chance or rows being rejected when the 8060 max byte count is violated." Jeff, can you clarify? If you store more than 8KB of varchars (for instance a varchar(6000) and a varchar(5000) on one table) then you won't get that error. Instead, SQL Server will store the data in the row overflow pages. Of course if you try define more than approx. 8KB of fixed chars in a table you'll get that error...

Note that again, for the record, I don't recommend storing greater than what you need. Having to read more than one page at a time could hurt performance.

As they say, store only the amount that you need to store. If you can store less than 8KB of data per row, it would be better.

Random Technical Stuff[/url]

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 11

ta.bu.shi.da.yu (12/30/2008)
If you store more than 8KB of varchars (for instance a varchar(6000) and a varchar(5000) on one table) then you won't get that error. Instead, SQL Server will store the data in the row overflow pages.

Not true... run this and see.

CREATE TABLE #TooBig

(

ColA VARCHAR(5000),

ColB VARCHAR(6000)

)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 12

Jeff Moden (12/31/2008)
ta.bu.shi.da.yu (12/30/2008)
If you store more than 8KB of varchars (for instance a varchar(6000) and a varchar(5000) on one table) then you won't get that error. Instead, SQL Server will store the data in the row overflow pages.
Not true... run this and see.
CREATE TABLE #TooBig
(
ColA VARCHAR(5000),
ColB VARCHAR(6000)
)

Ack... crud... my bad... I was running this in SQL Server 2000... not 2005. Sorry. You don't get the same error in 2005. (Man, I gotta get out of the habit ;))

Still, like VARCHAR(MAX), there is a bit of overhead involved with "overflow" pages. It is best to stay within the page limits of 8060 bytes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 13

Even though SQL Server 2005/2008 will store excess values past the 8060 limit when dealing with varchar,etc., you will get performance overhead to go and do that extra retrieve. I'd be sure I needed it rather than toss the idea of database design out the window & store everything in flipping huge, unformed, strings.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

klini SSC-Addicted Points: 458 More actions · Answer 14

klini

SSC-Addicted

Points: 458

December 31, 2008 at 6:58 am

#919610

Thanks everyone!