space required for VARCHAR - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

space required for VARCHAR

Author

Topic

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-08-02 : 16:31:31

Hi, if I set an item in a table as Varchar(50), and
the item being held is actually 30 characters,

does this mean, the space taken up by the item is actually
30 bytes?

If so, why not set varchar to 100 to avoid any problems?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-02 : 16:39:53

What problems would you encounter? Why would you pick 100? The size of the varchar column would be the maximum value's size. So if you maximum value is 50, then you use 50.

Tara

SQLError
Yak Posting Veteran

63 Posts

Posted - 2004-08-02 : 16:48:48

Hi, but, what I need to know is, if you set it to 50, and I store
something in that column with 30 characters, does the database
only use 30 bytes and not 50?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-02 : 16:58:18

Yes 30. But it will be different for each row. Take this example

Column1 varchar(50)
-------
Tara
Duggan
SQLError

The varchar lengths are 4, 6, and 8.

Tara

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-08-03 : 05:55:30

Hence the question: why not make them all varchar(8000)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-08-03 : 07:13:00

One answer is that most people object to this sort of thing happening:


CREATE TABLE RowWidthTest (
  pk int PRIMARY KEY,
  t1 varchar(8000),
  t2 varchar(8000)
)
-- warning, but ok

INSERT INTO RowWidthTest
SELECT 1, REPLICATE('x', 7900), ''
-- ok

UPDATE RowWidthTest
SET t2 = REPLICATE('y', 200)
WHERE pk = 1
-- blam! "Cannot create a row of size 8117 [...]"

BTW, If you're wondering "Why 8117 bytes?"
2 bytes of status information
2 bytes containing the size of the fixed-sized data in the row
2 bytes containing the number of columns
1 byte of NULL bitmap
4 bytes for the integer pk
2 bytes for the number of variable width columns
4 bytes for the two offsets to the variable length column data
7900 bytes for t1
200 bytes for t2

I'd love to know why SQL Server stores the size of the fixed sized data, the number of columns, the number of variable width columns and the first variable width column offset on every row. That seems like wasting 8 bytes per row to me.

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-08-03 : 07:41:56

Because it's bad design and that should be enough of an answer. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-08-03 : 07:54:43

Like Arnold mentions, the size limitation on a row (8060) is a big factor. Also, I'd imagine having larger varchars would be more likely to cause page splits in the indexes when data is updated, especially when the new string is longer than the existing one. Can anybody shed any light on how larger varchars affect data and index pages?

OS

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-03 : 17:39:44

speaking of datalengths, so I don't start a new topic, if a column is char(30), and the value is null, how much space is it taking up? and what about for varchar(30), too.

- RoLY roLLs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-03 : 17:50:01

Neither would be taking up any room if it's NULL. See for yourself:

CREATE TABLE Table1
(
Column1 CHAR(30) NULL
)

INSERT INTO Table1
SELECT Null
UNION ALL
SELECT 'Tara'

SELECT DATALENGTH(Column1)
FROM Table1

DROP TABLE Table1

Just run the code twice, once for VARCHAR, once for CHAR.

Tara

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-03 : 18:31:14

Not true - depending on how many columns you have.
see
http://www.nigelrivett.net/PageStructure.html

null bit map and col offset array still takes up room but doesn't depend on the value.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-03 : 18:44:25

Yeah, I shouldn't have said "any room".

Now if only I could decipher the information that you have on your site.

Tara

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-03 : 20:07:10

Thanks gals (women first), and guys. Was wondering if a table with about 10,000 records and several NULLs in particular columns would be better to be CHAR'ed or to be VARCHAR'ed.

Thanks again.

- RoLY roLLs

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-08-04 : 12:24:06

You only use CHAR if the values are fixed. So as an example, state abbreviations in the U.S., you would use CHAR(2). But if the lengths can be varying, you use varchar.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-04 : 12:40:19

I had thought we had a discussion once that ended up like

If it 10-15 chars or less, make it char(n), else make it varchar(n)

Brett

8-)

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-08-04 : 17:13:28

Yes Tara, thanks, am aware of that. Just wondered if a null value affected space if the column was char(30).

Brett, interesting thought. But what are the reasons for that?

Thanks.

- RoLY roLLs

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2004-08-04 : 17:29:06

quote:

If it 10-15 chars or less, make it char(n), else make it varchar(n)

At one shop I worked the infrastructure and network team demanded that any domain less than 25 characters be of type CHAR. Sectors and tracks or some other pseudo-technical babble they reckoned....

DavidM

"Always pre-heat the oven"

X002548
Not Just a Number

15586 Posts

Posted - 2004-08-05 : 11:36:59

quote:
Originally posted by byrmol

quote:

If it 10-15 chars or less, make it char(n), else make it varchar(n)

At one shop I worked the infrastructure and network team demanded that any domain less than 25 characters be of type CHAR. Sectors and tracks or some other pseudo-technical babble they reckoned....

DavidM

"Always pre-heat the oven"

Hadn't heard that one before....just that it was to buy an infintesimal performace gain...since sql server doesn't have to figure out the length...so...if you had a lot of these type of columns....

Brett

8-)

nr
SQLTeam MVY

12543 Posts

Posted - 2004-08-05 : 11:51:24

quote:
Originally posted by byrmol

quote:

If it 10-15 chars or less, make it char(n), else make it varchar(n)

At one shop I worked the infrastructure and network team demanded that any domain less than 25 characters be of type CHAR. Sectors and tracks or some other pseudo-technical babble they reckoned....

DavidM

"Always pre-heat the oven"

That is babble - might be valid for direct disk access for individual data items but sql server doesn't write 25 bytes to disk if you update a 25 byte field

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources