Friday, September 21, 2007

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

No comments: