Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Storage Size


Storage Size

Author
Message
Amit-695544
Amit-695544
Mr or Mrs. 500
Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)Mr or Mrs. 500 (527 reputation)

Group: General Forum Members
Points: 527 Visits: 16
hi all !!

nice discussion but what is the right answer !!

Hehe
Daniel Casado
Daniel Casado
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1003 Visits: 244
Try this in Management Studio:

CREATE TABLE #tbPersonalDetails(
PK_ID INT NOT NULL PRIMARY KEY,
[Name] NVARCHAR(50) NOT NULL,
Married BIT NOT NULL,
DOB SMALLDATETIME NOT NULL,
BachelorsDegree BIT NOT NULL,
MastersDegree BIT NOT NULL,
Profession NVARCHAR(100) NOT NULL,
Retired BIT NOT NULL,
Age AS (DATEDIFF(yy,DOB,GETDATE())),
ColWith8000Bytes VARCHAR(8000) NOT NULL
)
GO

CREATE INDEX IX_#tbPersonalDetails ON #tbPersonalDetails
(
PK_ID
,[Name]
,Married
,DOB
,BachelorsDegree
,MastersDegree
,Profession
,Retired
--,Age
,ColWith8000Bytes
)

DROP TABLE #tbPersonalDetails

And you get following warnings:

Warnung: Die maximale Schlüssellänge beträgt 900 Bytes. Der 'IX_#tbPersonalDetails'-Index hat eine maximale Länge von 8309 Bytes. Bei einigen Kombinationen hoher Werte schlägt der INSERT-/UPDATE-Vorgang fehl.

Warnung: Die maximale Länge der Zeile überschreitet das zulässige Limit von 8060 Bytes. Bei einigen Kombinationen hoher Werte schlägt der INSERT-/UPDATE-Vorgang fehl.

In short english words:
Index 'IX_#tbPersonalDetails' has a maximum length of 8309 ...

8309 - 8000 (cColWith8000Bytes) = 309
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8337 Visits: 11580
Dr. Diana Dee (4/9/2008)
Hugo is almost correct.


Hi Diana,

That is true. But not for the reason you mention. My mistake was forgetting the two bytes to store the number of variable length columns. I first noticed this when I saw the post by Steven Cameron, who did include these two bytes.

He put in one byte for the nulls. None of the columns allow nulls.


Whether columns allow NULLS or not is not relevant. The NULL bitmap is always stored. This is documented in Books Online, and in Kalen Deaney's "Inside SQL Server 2005: The Storage Enngine". Plus, I just confirmed this by testing.

I get a maximum length of 319.


Which is 3 bytes short of the actual amount. One for the NULL bitmap, and two for the number of columns (which is, for some weird reason, counted as part of the NULL bitmap in BOL).

If there were a column allowing null, there would be a 2-byte block to store the number of null columns followed by a null bitmap indicating whether each individual column is null (1 byte per 8 columns). So if just 1 column allowed a null, you would have to add 3 bytes to the above.

Reference: Microsoft course 2279 module 2 page 11.


I happen to have that course book (it's marked as 2279-A, but I assume it's the same). The page you reference does describe the NULL bitmap as being two bytes for the number of columns plus 1 byte for each grop of up to 8 columns, but it doesn't say that the NULL bitmap is omitted if no nullable columns exist. I believe that this has been the case in earlier versions of SQL Server, but not in SQL Server 2005 (and I think not in SQL Server 2000 either, but I'm not sure of that).


Anyway, since I'm still not sure whether the computed column is included in the NULL bitmap, I decided to run a quick test, creating the table, inserting some rows, and examining the actually stored data by running DBCC PAGE. Here is the output for the row with all variable length columns filled with maximum length:

DECLARE @DB varchar(20)
SET @DB = DB_NAME();
DBCC PAGE (@DB,1,45,3); -- 45 is the page for this table (based on sysindexes), 3 is verbose output


(snip)

Slot 2 Offset 0xb2 Length 322

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS

Memory Dump @0x6135C0B2

00000000: 30000d00 03000000 9f0000ac 8e080000 †0...............
00000010: 02007a00 42016100 61006100 61006100 †..z.B.a.a.a.a.a.
00000020: 61006100 61006100 61006100 61006100 †a.a.a.a.a.a.a.a.
00000030: 61006100 61006100 61006100 61006100 †a.a.a.a.a.a.a.a.
00000040: 61006100 61006100 61006100 61006100 †a.a.a.a.a.a.a.a.
00000050: 61006100 61006100 61006100 61006100 †a.a.a.a.a.a.a.a.
00000060: 61006100 61006100 61006100 61006100 †a.a.a.a.a.a.a.a.
00000070: 61006100 61006100 61006200 62006200 †a.a.a.a.a.b.b.b.
00000080: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
00000090: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
000000A0: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
000000B0: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
000000C0: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
000000D0: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
000000E0: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
000000F0: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
00000100: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
00000110: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
00000120: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
00000130: 62006200 62006200 62006200 62006200 †b.b.b.b.b.b.b.b.
00000140: 6200†††††††††††††††††††††††††††††††††b.

Slot 2 Column 0 Offset 0x4 Length 4

PK_ID = 3

Slot 2 Column 1 Offset 0x16 Length 100

Name = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa

Slot 2 Column 2 Offset 0x8 Length 1 (Bit position 0)

Married = 1

Slot 2 Column 3 Offset 0x9 Length 4

DOB = Jan 1 2000 12:00AM

Slot 2 Column 4 Offset 0x8 Length 1 (Bit position 1)

BachelorsDegree = 1

Slot 2 Column 5 Offset 0x8 Length 1 (Bit position 2)

MastersDegree = 1

Slot 2 Column 6 Offset 0x7a Length 200

Profession = bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb

Slot 2 Column 7 Offset 0x8 Length 1 (Bit position 3)

Retired = 1


I have highlighted some points of interest:
* Length 322 - This confirms my latest computation, corrected after Steven's post;
* 0800 - The number of columns (remember that SQL Server swaps the bytes, so this is actually the value 0x0008, or 8 in decimal). This confirms that the computed column is not counted.
* 00 - The actual NULL bitmap. I also ran a test with a persisted calculated column instead of a non persisted one, and that did indeed cause the number of columns to go up to 9 and the NULL bitmap to take an extra byte.
* 0200 - The start of the variable length portion. These first two bytes is the number of variable length columns, again byte-reversed - 0x0002, or 2 in decimal. After that follow two sets of two bytes with the end position of each of the varying length values: 0x007a (122) and 0x0142 (322).

Not included in this output are the two bytes each row needs in the row offset table at the end of the page, but we can see that by changing the output type to 2. The end of the results then look like this:

6117DFE0: 4410f40f ac0fc408 78082c08 e0079407 †D.......x.,.....
6117DFF0: 50071007 c8068006 4006b200 70006000 †P.......@...p.`.

OFFSET TABLE:

Row - Offset
2 (0x2) - 178 (0xb2)
1 (0x1) - 112 (0x70)
0 (0x0) - 96 (0x60)


The 6 bytes for the three rows in the offset table have, once more, been marked by me.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8337 Visits: 11580
imrandn (4/9/2008)
Thats make sence but what about the Primery Key constrain?


Hi imrandn,

That will cause the table's data pages to be organized as a B-tree, with the PK value used in the root and intermediate pages. It does not affect the amount of bytes of storage required per row.

If the table had a clustered index on a nonunique column, row size would be affected as a result of the uniquifier that SQL Server has to add for nonuniquue values.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8337 Visits: 11580
Daniel Schmitz (4/10/2008)
Try this in Management Studio:

(snip)

8309 - 8000 (cColWith8000Bytes) = 309


Hi Daniel,

That is the data length, not the amount of bytes actually used to store the data.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
john.arnott
john.arnott
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1468 Visits: 3059
The calculated age question was QOTD at the start of March.
http://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx

Interestting that it's come back to life..........
Imran Ud Din
Imran Ud Din
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 19
hi SSCommitted,

I was confussed with Index key for indexes as your index key also take space in the row and effects of total size of the row (I think the index key can be maximum 900).

Thanks
Hugo Kornelis
Hugo Kornelis
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8337 Visits: 11580
imrandn (4/10/2008)
hi SSCommitted,

I was confussed with Index key for indexes as your index key also take space in the row and effects of total size of the row (I think the index key can be maximum 900).

Thanks


Hi imrandn,

The index key is the PK_ID column, which is already counted in the space calculations. It's not stored another time as part of the index structure. (At least not in the data pages),

The 900 byte limit is (I guess) related to the effectiveness of the B-tree structure WRT root and intermediate pages. These pages only hold the value of indexed columns and a pointer.


Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search