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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

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

Leave a Comment

(required) 
(required) 
Submit

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

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement