Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL Teaser PASS Special: Table Size

Status
Not open for further replies.

SQLDenis

Programmer
Oct 1, 2005
5,575
US
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
Code:
--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?
Code:
--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
 
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
 
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
 
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
 
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]
 
>>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
Code:
use pubs
go

dbcc traceon(3604)

dbcc page(pubs,1,153,1)


this will print something like this (and more)

Code:
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
 
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.
 
interesting!


- Paul
- If at first you don't succeed, find out if the loser gets anything.
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top