SQL Teaser PASS Special: Table Size

[SQLDenis]

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

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[AlexCuse]

Spoiler

I'd imagine you will get an error regarding pushing your row size past 8060, but I haven't been able to find any official documentation from Microsoft speaking to this ;-)

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[chrissie1]

Spoiler

I think he thinks it's 9000. keeping the first 1000 from col 2. But I'm probably way of.

Christiaan Baes
Belgium

My Blog
"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett

 

[AlexCuse]

Spoiler

Chrissie - There are bytes used by the system as well, I believe for things like collation and what not (I'm not sure what exactly they're for, but I know they are there). This would be the largest you could make your table:

--Total size = 5039
--try making Col1 4040 (doesn't work for me, at least in 2000)
CREATE TABLE TestSize (Col1 char(4039),col2 char(1000))
GO

--total size = 6039
ALTER TABLE TestSize
ALTER COLUMN col2 char(2000)
GO

--total size should be 7039, but row size will be 8060
ALTER TABLE TestSize
ADD Col3 char(1000)
GO

[small]----signature below----[/small]
With all due respect, Don Bot, I don't think we should rely on an accident happening. Let's kill him ourselves.

Ignorance of certain subjects is a great part of wisdom

 

[LNBruno]

Spoiler

If I wrote it, you can bet it would cr@p out! (Max row length exceeded?)

< M!ke >
[small]I can say nothing, which is cowardly, I can lie, which is immoral, or I can tell the truth, which will upset people. - Tiki Barber[/small]

 

[SQLDenis]

>>Chrissie - There are bytes used by the system as well, I believe for things like collation and what not (I'm not sure what exactly they're for, but I know they are there).


96 bytes page header
this has things like
PageID
NextPage
Prevpage
SlotCnt
Level
Freecnt
tornBits
flagBits

etc etc


run this

use pubs
go

dbcc traceon(3604)

dbcc page(pubs,1,153,1)

this will print something like this (and more)

PAGE: (1:153)
-------------

BUFFER:
-------

BUF @0x00FBDD40
---------------
bpage = 0x4FA5A000        bhash = 0x00000000        bpageno = (1:153)
bdbid = 5                 breferences = 0           bstat = 0x9
bspin = 0                 bnext = 0x00000000        

PAGE HEADER:
------------

Page @0x4FA5A000
----------------
m_pageId = (1:153)        m_headerVersion = 1       m_type = 3
m_typeFlagBits = 0x0      m_level = 0               m_flagBits = 0x8020
m_objId = 357576312       m_indexId = 255           m_prevPage = (0:0)
m_nextPage = (0:0)        pminlen = 0               m_slotCnt = 1
m_freeCnt = 0             m_freeData = 8190         m_reservedCnt = 0
m_lsn = (4:320:20)        m_xactReserved = 0        m_xdesId = (0:539)
m_ghostRecCnt = 0         m_tornBits = 49518905     

Allocation Status
-----------------
GAM (1:2) = ALLOCATED     SGAM (1:3) = NOT ALLOCATED
PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL             DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ptheriault]

Spoiler

I think it would create the table but you would receive the warning about surpassing the max 8060 row limit. Inserts may fail.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

interesting!


- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[chrissie1]

Spoiler

What's with the tornbirds?

Christiaan Baes
Belgium

My Blog
"In a system where you can define a factor as part of a third factor, you need another layer to check the main layer in case the second layer is not the base unit." - jrbarnett