April 9, 2008 at 7:57 am
The funny thing is, the author obviously realized that nvarchar takes 2 bytes per character, judging by the explanation:
... Name + profession can take max 308 ...
To mis-quote Simon Cowell on the Simpsons:
"That's right - and 308 is a bigger number than 159. Do you see how that works?" 😉
April 9, 2008 at 8:08 am
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: http://msdn2.microsoft.com/en-us/library/ms189124.aspx
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 + 4
So, row size = 9 + 306 + 4 + 4 = 323
And, add 14 bytes if the row has row versioning information.
April 9, 2008 at 8:09 am
April 9, 2008 at 8:14 am
I 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.
April 9, 2008 at 8:18 am
I 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 formula
Total 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:
Q
Please take a number. Now serving emergency 1,203,894
April 9, 2008 at 8:20 am
My 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. MTCW
April 9, 2008 at 8:23 am
Steven Cameron (4/9/2008)
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: http://msdn2.microsoft.com/en-us/library/ms189124.aspx
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 + 4
So, row size = 9 + 306 + 4 + 4 = 323
And, add 14 bytes if the row has row versioning information.
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 a detailed article[/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.
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
April 9, 2008 at 8:29 am
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??
April 9, 2008 at 8:33 am
magarity kerns (4/9/2008)
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??
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.
April 9, 2008 at 8:44 am
Steve Jones - Editor (4/9/2008)
apologies. The question has been changed (and answers) and everyone to this point in time is awarded points.
Except that I just answered it... and it's saying 159 bytes?
Even the answer description differs from the "correct" answer.
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.
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 != 159
April 9, 2008 at 9:02 am
Hi 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 NULL
Thanks in advance..
Ramkumar . K
Senior Developer
######################
No Surrender... No Give Up....
######################
April 9, 2008 at 9:02 am
Doh! Changed the answer and explanation, but didn't mark it as correct.
Re-awarding points again. That was my bad. Apologies all around.
April 9, 2008 at 9:07 am
If 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.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 9, 2008 at 9:28 am
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!
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 4
Subqueries 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 TestAge
SELECT 1, '4/09/1987'
UNION ALL
SELECT 2, '4/10/1987'
UNION ALL
SELECT 3, '4/09/1988'
UNION ALL
SELECT 4, '4/10/1988'
UNION ALL
SELECT 5, '2/29/1988'
SELECT * FROM TestAge
April 9, 2008 at 9:34 am
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.
Why don't you try it with just CASE instead of SELECT CASE.
You a VBer?
Viewing 15 posts - 31 through 45 (of 57 total)
You must be logged in to reply to this topic. Login to reply