InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 04:24:14 GMT20http://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspx[quote][b]imrandn (4/10/2008)[/b][hr]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[/quote]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.Fri, 11 Apr 2008 01:08:01 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxhi 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).ThanksThu, 10 Apr 2008 22:23:12 GMTImran Ud Dinhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxThe calculated age question was QOTD at the start of March. http://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspxInterestting that it's come back to life..........Thu, 10 Apr 2008 17:25:35 GMTjohn.arnotthttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspx[quote][b]Daniel Schmitz (4/10/2008)[/b][hr]Try this in Management Studio:(snip)8309 - 8000 (cColWith8000Bytes) = 309[/quote]Hi Daniel,That is the data length, not the amount of bytes actually used to store the data.Thu, 10 Apr 2008 02:14:35 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspx[quote][b]imrandn (4/9/2008)[/b][hr]Thats make sence but what about the Primery Key constrain?[/quote]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.Thu, 10 Apr 2008 02:13:39 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspx[quote][b]Dr. Diana Dee (4/9/2008)[/b][hr]Hugo is almost correct.[/quote]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.[quote]He put in one byte for the nulls. None of the columns allow nulls.[/quote]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.[quote]I get a maximum length of 319.[/quote]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).[quote]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.[/quote]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"][size="2"]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 [b]Length 322[/b]Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNSMemory Dump @0x6135C0B200000000: 30000d00 03000000 9f0000ac 8e[u]0800[/u][i]00[/i] †0............... 00000010: [b]0200[/b]7a00 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 4PK_ID = 3 Slot 2 Column 1 Offset 0x16 Length 100Name = aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa Slot 2 Column 2 Offset 0x8 Length 1 (Bit position 0)Married = 1 Slot 2 Column 3 Offset 0x9 Length 4DOB = 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 200Profession = bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbSlot 2 Column 7 Offset 0x8 Length 1 (Bit position 3)Retired = 1 [/size][/font]I have highlighted some points of interest:* [b]Length 322[/b] - This confirms my latest computation, corrected after Steven's post;* [u]0800[/u] - 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.* [i]00[/i] - 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.* [b]0200[/b] - 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"][size="2"]6117DFE0: 4410f40f ac0fc408 78082c08 e0079407 †D.......x.,..... 6117DFF0: 50071007 c8068006 4006[b]b200 70006000[/b] †P.......@...p.`. OFFSET TABLE:Row - Offset [b]2 (0x2) - 178 (0xb2) 1 (0x1) - 112 (0x70) 0 (0x0) - 96 (0x60) [/b][/size][/font]The 6 bytes for the three rows in the offset table have, once more, been [b]marked[/b] by me.Thu, 10 Apr 2008 02:09:51 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxTry 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)GOCREATE INDEX IX_#tbPersonalDetails ON #tbPersonalDetails( PK_ID ,[Name] ,Married ,DOB ,BachelorsDegree ,MastersDegree ,Profession ,Retired --,Age ,ColWith8000Bytes)DROP TABLE #tbPersonalDetailsAnd 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) = 309Thu, 10 Apr 2008 01:05:41 GMTDaniel Schmitzhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxhi all !!nice discussion but what is the right answer !!:hehe:Thu, 10 Apr 2008 01:03:39 GMTAmit-695544http://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxThats make sence but what about the Primery Key constrain?Wed, 09 Apr 2008 23:19:51 GMTImran Ud Dinhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxHugo 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 columns300 - 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____319If 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 DeeWed, 09 Apr 2008 21:48:30 GMTDr. Diana Deehttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxAnyone 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 maxlenFROM sys.indexesWHERE 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?Wed, 09 Apr 2008 15:55:57 GMTmojo-168709http://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspx[quote][b]Stephanie Giovannini (4/9/2008)[/b][hr]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![/quote]Indeed. But that discussion has been done to death as a result of a previous QotD. See [url=http://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx]http://www.sqlservercentral.com/Forums/Topic464861-1181-1.aspx[/url]. You'll find lots of techniques in that thread. Many incorrect, some correct.[quote]Age AS (DATEDIFF(year, DOB, GETDATE()) - (SELECT CASE WHEN DATEPART(dy, DOB) > DATEPART(dy, GETDATE()) THEN 1 ELSE 0 END))[/quote]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:Wed, 09 Apr 2008 09:55:33 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxOh 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.Wed, 09 Apr 2008 09:43:17 GMTStephanie Giovanninihttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspx[quote]If I remove the SELECT CASE, then it works, but is also still incorrect. Further testing reveals that SELECT CASE is not allowed as part of a computed column. [/quote]Why don't you try it with just CASE instead of SELECT CASE.You a VBer?Wed, 09 Apr 2008 09:34:41 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxI 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!I tried to correct the Age computed column with the following expression:Age AS (DATEDIFF(year, DOB, GETDATE()) - (SELECT CASE WHEN DATEPART(dy, DOB) > DATEPART(dy, GETDATE()) THEN 1 ELSE 0 END))However, this doesn't work.Msg 1046, Level 15, State 1, Line 4Subqueries are not allowed in this context. Only scalar expressions are allowed.If I remove the SELECT CASE, then it works, but is also still incorrect. Further testing reveals that SELECT CASE is not allowed as part of a computed column. I decided there had to be a mathematical way to implement the logic. I was quite proud of my next attempt:Age AS (DATEDIFF(year, dob, GETDATE()) + FLOOR((CAST(DATEPART(dy, GETDATE()) AS FLOAT) - CAST(DATEPART(dy, dob) AS FLOAT)) / 1000))I tested it and it worked in almost all cases. In fact, I would have posted it as accurate, except that this year is a leap year! It incorrectly identified someone born on 4/10/87 as 21 years of age, not 20. Selling that kid beer one day before their 21st birthday (in the US) is a crime! Can't have that...Finally, I think I have something that works:Age AS (DATEDIFF(year, dob, GETDATE()) + FLOOR((CAST(DATEPART(dy, CONVERT(SMALLDATETIME, CAST(DATEPART(mm, GETDATE()) AS CHAR(2)) + '/' + CAST(DATEPART(dd, GETDATE()) AS CHAR(2)) + '/' + CAST(DATEPART(year, dob) AS CHAR(4)), 101)) AS FLOAT) - CAST(DATEPART(dy, dob) AS FLOAT)) / 1000))To determine whether or not someone's birthday had occurred yet this year, I used the month and day of today combined with the birth year to determine the day of year. Thus, the day of year is always compared within the same year and leap year variations (or birthdays on leap day) are handled correctly.If there is a cleaner way to accomplish this, I'd love to see it!Here's some test code:CREATE TABLE TestAge( ID INT NOT NULL PRIMARY KEY, DOB SMALLDATETIME NOT NULL, Age AS (DATEDIFF(year, dob, GETDATE()) + FLOOR((CAST(DATEPART(dy, CONVERT(SMALLDATETIME, CAST(DATEPART(mm, GETDATE()) AS CHAR(2)) + '/' + CAST(DATEPART(dd, GETDATE()) AS CHAR(2)) + '/' + CAST(DATEPART(year, dob) AS CHAR(4)), 101)) AS FLOAT) - CAST(DATEPART(dy, dob) AS FLOAT)) / 1000)))INSERT INTO TestAgeSELECT 1, '4/09/1987'UNION ALLSELECT 2, '4/10/1987'UNION ALLSELECT 3, '4/09/1988'UNION ALLSELECT 4, '4/10/1988'UNION ALLSELECT 5, '2/29/1988'SELECT * FROM TestAgeWed, 09 Apr 2008 09:28:53 GMTStephanie Giovanninihttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxIf you take the statement provided in the QotD and run it to create the table, you will end up with no table and an error message. (the command in the article is GCREATE, not CREATE). So, the real correct answer is zero.Myself, I chose the 316 answer, since I saw the two NVarchars and realized that they alone would be > 300, and with only one answer > 300 I chose that one.Wed, 09 Apr 2008 09:07:01 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxDoh! Changed the answer and explanation, but didn't mark it as correct.Re-awarding points again. That was my bad. Apologies all around.Wed, 09 Apr 2008 09:02:42 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxHi all..If in case of declaring an variable as NVarchar(100), it consumes 50 charecters for storing values?? Am i right?Because 2 bytes are used for each NVARCHAR byte..Thats why i answered as 316 bytes.. Can any one explain the storage method in this kind of scenario????Like : [Name] NVARCHAR(50) NOT NULLThanks in advance..Wed, 09 Apr 2008 09:02:29 GMTRamkumar.Khttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspx[quote][b]Steve Jones - Editor (4/9/2008)[/b][hr]apologies. The question has been changed (and answers) and everyone to this point in time is awarded points.[/quote]Except that I just answered it... and it's saying 159 bytes?Even the answer description differs from the "correct" answer.[quote]Up to 8 bit type columns take only 1 byte per row in table. So all bit columns take 1 byte, Name + profession can take max 308 (2 bytes for each NVARCHAR byte, + 2 for the overhead), DOB takes 4, and no space for age column as it is computed column. [/quote]Following the math from the answer I get 313, which isn't on the list.1 (bit fields) + 102 (nvarchar(50)) + 202 (nvarchar(100)) + 4 (Int) + 4 (Datetime) = 313.1 + 102 + 202 + 4 + 4 != 159Wed, 09 Apr 2008 08:44:21 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspx[quote][b]magarity kerns (4/9/2008)[/b][hr]I picked the 312 answer because it was closest to what I calculated and the thing told me I was wrong and that 159 was the correct answer. At the same time it said that ~70% of people were correct and that ~70% had picked the 312 answer! Is someone editing behind the scenes to try to fix the answer and I clicked at the wrong millisecond??[/quote]Well Yes, sort of. If you read the prior posts, Steve awarded back points to the earlier people who answered the question and got it wrong.Wed, 09 Apr 2008 08:33:01 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxI picked the 312 answer because it was closest to what I calculated and the thing told me I was wrong and that 159 was the correct answer. At the same time it said that ~70% of people were correct and that ~70% had picked the 312 answer! Is someone editing behind the scenes to try to fix the answer and I clicked at the wrong millisecond??Wed, 09 Apr 2008 08:29:20 GMTmagarity kernshttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspx[quote][b]Steven Cameron (4/9/2008)[/b][hr]I appreciate the difficulty in creating questions that can withstand the pounding on these forums. I do find it ironic when posters slam the question for being wrong and then post a wrong answer themselves. Give the guy a break and chill. The QotD is not life and death, its a learning experience. I learned a lot from the page below:See: [url=http://msdn2.microsoft.com/en-us/library/ms189124.aspx]http://msdn2.microsoft.com/en-us/library/ms189124.aspx[/url]Null_Bitmap = 2 + ((Num_Cols + 7) / 8)Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4So, row size = 9 + 306 + 4 + 4 = 323And, add 14 bytes if the row has row versioning information.[/quote]I completely agree - it's much better etiquette to respond helpfully rather than by slamming (even if there have been issues with several questions in a row). For this particular question, it seems that there are many wrong answers that may seem correct, and it may be easy to point out that a given answer is wrong only to provide another wrong answer instead. As I recall, Steve once posted [url=http://www.sqlservercentral.com/articles/Miscellaneous/pagesize/497/][b]a detailed article[/b][/url] on something he discovered about row size. It seems incredibly arcane given how important it is and how obvious one might assume it is to calculate. So despite the frustration with the previous questions, it seems that some extra patience is required over this one.webrunnerWed, 09 Apr 2008 08:23:11 GMTwebrunnerhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxMy interpretation of the phrase "maximum size of a row" was in the context of the 8060-byte limit. There was no mention of storage size in the question, so I don't think there should be any storage overhead etc. included in the calculation. MTCWWed, 09 Apr 2008 08:20:01 GMTWILLIAM MITCHELLhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxI couldn't get my calculations to match any of the answers even after I used 50 and 100 for the nvarchar fields. I forgot that all the bit fields would be in 1 byte for this example. So my math was using the BOL formulaTotal row size (Row_Size) = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap +4 Total row size (Row_Size) = 12 (Int + smalldatetime + 4 bits) + (2+ (2*2) + 150) + (2 + (( 6 + 7) / 8 )) + 4 thus my row size calculation was Row size would be 175. Because I knew all the answers were incorrect, I chose the closest answer, 166. If I had remembered the bits should be only 1 byte I still would end up with 172. :rolleyes:Using the formula and the real size of the variable length columns Total row size (Row_Size) = 9 + (2+ (2*2) + 300) + 3 + 4 so if my calcs are right based on BOL it should be 322 :crazy:It is a great question and unfortunately we again had no correct answer from which to choose; however, it seems that these questions generate more conversation and thought than do the ones with the correct answers. We have many knowledgable people in this forum that are able to generate good discussions. It is usually in these discussions where I learn something new. Thanks to all that contribute and especialy Steve. Now coffe break is over back on my head :hehe:Wed, 09 Apr 2008 08:18:11 GMTQ -631159http://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxI just figured it had to be larger than 300 and guessed since I knew someone would provide a good explanation of how to calculate it precisely. Thanks Hugo for providing such a thorough explanation. And thanks for the question.Wed, 09 Apr 2008 08:14:00 GMTCliff Joneshttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxYou can use this approach: [u][url=http://msdn2.microsoft.com/en-us/library/ms175991.aspx][/url][/u]Wed, 09 Apr 2008 08:09:33 GMTVobr Jirihttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxI appreciate the difficulty in creating questions that can withstand the pounding on these forums. I do find it ironic when posters slam the question for being wrong and then post a wrong answer themselves. Give the guy a break and chill. The QotD is not life and death, its a learning experience. I learned a lot from the page below:See: [url=http://msdn2.microsoft.com/en-us/library/ms189124.aspx]http://msdn2.microsoft.com/en-us/library/ms189124.aspx[/url]Null_Bitmap = 2 + ((Num_Cols + 7) / 8)Variable_Data_Size = 2 + (Num_Variable_Cols x 2) + Max_Var_Size Row_Size = Fixed_Data_Size + Variable_Data_Size + Null_Bitmap + 4So, row size = 9 + 306 + 4 + 4 = 323And, add 14 bytes if the row has row versioning information.Wed, 09 Apr 2008 08:08:57 GMTSteven Cameronhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxThe funny thing is, the author obviously realized that nvarchar takes 2 bytes per character, judging by the explanation:[quote]... Name + profession can take max 308 ...[/quote]To mis-quote Simon Cowell on the Simpsons:"That's right - and 308 is a [i]bigger[/i] number than 159. Do you see how that works?" ;)[url]http://en.wikipedia.org/wiki/Smart_and_Smarter[/url]Wed, 09 Apr 2008 07:57:39 GMTricharddhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxI think lots of people post their comment without reading. No worries, glad to see them posting ;)However I think I'll take a short moratorium on user questions. At least in publishing. We typically have 3-5 weeks scheduled out (doing May now), but I'll move around some of the user questions until I can go through them a bit more carefully.I can see how this happened. The user picked a table, was thinking about the bit and computed column values and didn't think through the varchar, much less the "N" values. Actually glanced at it last night and saw "varchar" myself and the quick thumbnail seemed that one of the answers would be right.apologies. The question has been changed (and answers) and everyone to this point in time is awarded points.Wed, 09 Apr 2008 07:56:12 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxHow is it that so many people can notice all kinds of issues with QOTDs, yet fail to notice their point already previously made in the ensuing discussion thread? Is this a case of selective scrutiny?Not just an issue with this thread, of course.....Wed, 09 Apr 2008 07:46:13 GMTmajorbloodnockhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxNone of the answers are correct.PK_ID - 4Name - 102Profession - 202all the bit fields - 1DOB - 4Total = 313nvarchar fields take 2 x the max length + 2 bytes.Wed, 09 Apr 2008 07:45:53 GMTKathy-82899http://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspx[quote][b]Hugo Kornelis (4/9/2008)[/b][hr]Hi all,I think this is a great question, though thwarted by an incorrect answer, unfortunately.The question is great because hardly anyone knows exactly how this works. That is shown by the fact that of the "correct" answers posted thus far in this thread, none is actually correct. (And boy, do I expose myself to ridicule now should my answer prove to be wrong as well :D)But the incorrect answer options result in this QotD being more of a missed opportunity than a learning experience. Sadly.So, here are the things that are wrong in either the answer provided as "correct", or in the replies I have seen so far....[/quote]Wow. Thank you for the detailed follow-up to this Question of the Day. This seems to be a question type that requires even more review than the others - I had some idea that row size is complicated to calculate, but I had no idea it was so involved that even gurus such as yourself might not be 100% confident of the exact answer (you sound 98% confident -- about 90% more than I was :-) -- but not 100%). Amazing.Thanks again!webrunnerWed, 09 Apr 2008 07:41:52 GMTwebrunnerhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxLike others, I saw the nvarchar problem but went ahead anyway. The one that threw me was the calculated column not using storage. It's a no-brainer, but mine was on vacation.Wed, 09 Apr 2008 07:36:01 GMTTom Garthhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxum.....nvarchar(n)Variable-length Unicode character data of n characters. n must be a value from 1 through 4,000. Storage size, in bytes, is two times the number of characters entered. The data entered can be 0 characters in length. The SQL-92 synonyms for nvarchar are national char varying and national character varying.http://msdn2.microsoft.com/en-us/library/aa276823(SQL.80).aspx(50 + 100) *2 = 300All are invalidWed, 09 Apr 2008 07:33:28 GMTJon-363128http://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxwhats up with the wrong answers to questions lately? This question obviously had NO correct answers. Just simply summing up max_length from sys.columns gives you 316 and that's without adding in the 2 bytes per nvarchar.I think everyone should get credit for their answer.Someone needs to check these questions before they get posted. This is getting silly.Wed, 09 Apr 2008 06:54:43 GMTRob Lee-453123http://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxHugo, Thanks for the great explanation. It's humbling to know that my best guess wasn't good enough. This knowledge is [i]far more valuable[/i] than points.Thanks!Wed, 09 Apr 2008 06:50:04 GMTAnthony K. Valleyhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxI ended up with the correct-correct data size, and remembered the 2 extra bytes for variable columns, but missed a few of the overhead numbers that Hugo had. Good question, and (Hugo) good answer. Too bad they weren't connected. But it did make me think and did end up pointing out that, even if the correct answer had been presented, I still would have missed it (because of the row-overhead).Wed, 09 Apr 2008 06:43:32 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxI thought the QotD was an interesting topic. I would also like to thank Hugo for his in-depth explanation. Points or not, I learned something new.CheersWed, 09 Apr 2008 06:40:51 GMTJeff Delucahttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxOnce I determine that the question (as many ppl have pointed out) didn't know the difference between nvarchar and varchar, I treated those two columns as varchar...Unfortunately, I didn't get the answer correct, because I didn't group the bit columns. So it was a good question. I do love the forums, I find them more often more informative than the QoD itself :)Wed, 09 Apr 2008 06:38:05 GMTSean Walkerhttp://www.sqlservercentral.com/Forums/Topic482000-1246-1.aspxSince I knew the answer was none of the above before I even bothered to do the calculation, I strongly considered waiting until tomorrow to answer :) However, I couldn't get into this conversation then. Since I didn't save it, I saw a post recently that showed a function that would give the actual rowsize I thought. I can't recall the name of that function, or if that is really what it did. Can anyone remember if there is such a function/procedure?**DATALENGTH() vs LENGTH() is what I was thinking about.**Wed, 09 Apr 2008 06:18:42 GMTBob Fazio