THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Denis Gobo

SQL Teaser PASS Special: Table Size

What will be the outcome of this script?
First we create a table with a total of 6000 bytes
Next we increase col2 from 1000 to 2000 bytes, this will give us a total of 7000 bytes
Finally we add col3 which has 1000 bytes, this will give us a total of 8000 bytes

First run these two statements
--Total size = 6000
CREATE TABLE TestSize (Col1 char(5000),col2 char(1000))
GO
 
--total size = 7000
ALTER TABLE TestSize
ALTER COLUMN col2 char(2000)
GO
 

Now what do you think will happen when you run this?
--total size should be 8000 bytes (5000 + 2000 + 1000)
ALTER TABLE TestSize
ADD Col3 char(1000)
GO
 
Now for bonus points. What book have I been reading.

Hint: the author is at PASS

Published Friday, September 21, 2007 9:58 AM by Denis Gobo

Comments

 

AaronBertrand said:

Denis, the final ALTER TABLE should raise the row size warning because the initial ALTER COLUMN does not re-use the space initially allocated to the column (in part because it must create the new column first and then copy the data).

I am not sure which book this tidbit come from, I am leaning towards Kalen's book on the storage engine.

September 21, 2007 6:29 PM
 

Denis Gobo said:

Aaron,

you nailed it (including bonus question)  ;-)

September 21, 2007 7:55 PM
 

Brad said:

Denis/Aaron,

Where do the "7 bytes of internal overhead" come from?

Is this the same side-effect that Tony Rogerson touched on in his post about how dropping a column on a table does not reclaim the space?

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2007/08/27/alter-table-drop-column-does-not-reclaim-the-space-the-column-took-it-s-a-meta-data-change-only.aspx

September 21, 2007 8:06 PM
 

Denis Gobo said:

I think those 7 bytes are

1 byte: Status Bits A

1 byte:  Status Bits B

2 bytes:  Fixed Length Size

2 bytes:  number of columns

1 byte; null bitmap

September 24, 2007 1:02 PM
New Comments to this post are disabled

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication

Privacy Statement