April 9, 2008 at 9:43 am
Oh yes, the SELECT isn't required. You're right, of course.
No, I despise VB. I've been working almost exclusively in C# for several years and am not currently a DBA, so SQL is a bit rusty. CASE isn't at all like switch, so I just forgot the syntax. The CASE expression still has the leap year problem, but it can be massaged to be correct and just a bit cleaner than the final version I put in my post.
April 9, 2008 at 9:55 am
Stephanie Giovannini (4/9/2008)
I realize that the author threw in the Age computed column as a test of whether we knew that computed columns take up no space in a row. However, as a previous poster pointed out, it won't yield correct results.It turns out that it's not such a trivial exercise to construct an Age computed column!
Indeed. But that discussion has been done to death as a result of a previous QotD. See http://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx. You'll find lots of techniques in that thread. Many incorrect, some correct.
Age AS (DATEDIFF(year, DOB, GETDATE()) - (SELECT CASE WHEN DATEPART(dy, DOB) > DATEPART(dy, GETDATE()) THEN 1 ELSE 0 END))
When you remove the (SELECT and the matching ), you get a syntactically correct calculated column, but not the correct result in all cases. Remember that DATEPART(dy, xxxx) for any date in march until december will be one more in a leap year.
(edit) Well, duh! You had already discovered that and told us so in your post. Please just ignore me while I go hid under my rock. :Whistling:
April 9, 2008 at 3:55 pm
Anyone got 335?
I created the table in SQL 2000, and ran the following:
select xmaxlen from sysindexes where id = object_id('tbPersonalDetails')
I got 335.
I created the table in SQL 2005, and ran:
SELECT INDEXPROPERTY(object_id, name, 'maxlen') AS maxlen
FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.tbPersonalDetails');
I got 335 as well.
Kalen's Inside SQL 2005 book mentioned an undocumented INDEXPROPERTY(object_id, name, 'xmaxlen'). I got a NULL value with xmaxlen. But I got 335 back if I used maxlen.
I did another test in SQL 2000: I changed the Profession to nvarchar(4000), I got a SQL warning stating the maximum row size would be 8135. So 335 seems to be the one SQL reports.
Any comments?
April 9, 2008 at 9:48 pm
Hugo is almost correct.
He put in one byte for the nulls. None of the columns allow nulls.
I get a maximum length of 319.
4 - row header
4 - integer column
1 - the bit columns
300 - the nvarchar columns
6 - 2 bytes to describe how many variable length columns plus 2 bytes per column to point to the end of each variable-length column
4 - smalldatetime column
____
319
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.
Diana Dee
April 9, 2008 at 11:19 pm
Thats make sence but what about the Primery Key constrain?
April 10, 2008 at 1:03 am
hi all !!
nice discussion but what is the right answer !!
:hehe:
April 10, 2008 at 1:05 am
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
April 10, 2008 at 2:09 am
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:
[font="Courier New"]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 [/font]
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:
[font="Courier New"]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) [/font]
The 6 bytes for the three rows in the offset table have, once more, been marked by me.
April 10, 2008 at 2:13 am
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.
April 10, 2008 at 2:14 am
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.
April 10, 2008 at 5:25 pm
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..........
April 10, 2008 at 10:23 pm
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
April 11, 2008 at 1:08 am
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.
Viewing 13 posts - 46 through 57 (of 57 total)
You must be logged in to reply to this topic. Login to reply