Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««23456

Storage Size Expand / Collapse
Author
Message
Posted Thursday, April 10, 2008 1:03 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, April 30, 2008 4:36 AM
Points: 527, Visits: 16
hi all !!

nice discussion but what is the right answer !!

Post #482803
Posted Thursday, April 10, 2008 1:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 1,003, Visits: 236
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
Post #482804
Posted Thursday, April 10, 2008 2:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 6,128, Visits: 8,393
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
Post #482819
Posted Thursday, April 10, 2008 2:13 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 6,128, Visits: 8,393
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
Post #482820
Posted Thursday, April 10, 2008 2:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 6,128, Visits: 8,393
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
Post #482821
Posted Thursday, April 10, 2008 5:25 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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..........
Post #483356
Posted Thursday, April 10, 2008 10:23 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, January 17, 2009 5:52 AM
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
Post #483403
Posted Friday, April 11, 2008 1:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 6,128, Visits: 8,393
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
Post #483459
« Prev Topic | Next Topic »

Add to briefcase «««23456

Permissions Expand / Collapse