SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Storage Size


Storage Size

Author
Message
majorbloodnock
majorbloodnock
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2397 Visits: 3064
I agree with Hugo; I thought this was a good question.

Being before my second morning cuppa, I misread nvarchar, so neglected to factor unicode into my calculations, and so got the question "right". However, I most certainly didn't think about the other "overhead" costs, and so I've learnt something new. Is it an important something? Probably not, given the fact that my table size estimates are based on far more approximate figures anyway (Microsoft don't have a 10% "For Luck" overhead included in their calculations Wink), but it's added to my understanding which I don't think can ever be a waste.

So thank you, Alok, for a good question, and commiserations that your answer choices were off the mark.

And a reminder to everyone having a moan that Steve did pose the far bigger challenge (if you're up for it) that you can submit QOTDs of your own. Basically, if you think you can do better, prove it.

Semper in excretia, sumus solum profundum variat
Evgeny G. Shymshyt
Evgeny G. Shymshyt
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 47
So, correct variant of question is:

What is the maximum data size of a row in the following table
(without additional overhead expenses bytes and for NVARCHAR type calculating only symbols, not bytes)

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()))
)
GO



Maarten Takens
Maarten Takens
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 80
I would have suggested 316 bytes max storageper row but i have read in this Forum ( special thanks to Hugo Kornelis ) that this should be 322 bytes per row ..
although it is a pity that the QOD does not provide the correct answers to this question the discussion in this Forum is great and you really can learn from it! ..
See yaa

Maarten
shamas saeed
shamas saeed
SSC Veteran
SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)SSC Veteran (214 reputation)

Group: General Forum Members
Points: 214 Visits: 27
Check ur Question Complete Context before post because this will missguid many peoples who do not know detail


In Your Qustion nvarchar used double bytes as length define and Computed column also use int datatype storage bytes for check create the mentioned table and use Alt+F1 to see its help
Bill Mell
Bill Mell
Right there with Babe
Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)Right there with Babe (751 reputation)

Group: General Forum Members
Points: 751 Visits: 63
I got it right only because I pretended that an nvarchar(50) only takes 50 bytes instead of the 100 it actually takes and that an nvarchar(100) only takes 100 bytes instead of the 200 that it actually takes.
You would think that you would have had the correct answer in there at least once.
Arno H. Janssen
Arno H. Janssen
SSC-Addicted
SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)SSC-Addicted (421 reputation)

Group: General Forum Members
Points: 421 Visits: 52
4 + 102 + 1 + 4 + 1 + 1 + 202 + 1 = 316
Bert-687390
Bert-687390
Mr or Mrs. 500
Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)Mr or Mrs. 500 (545 reputation)

Group: General Forum Members
Points: 545 Visits: 38
As others state: only the NVARCHAR columns already sum up to above the max answer. Therefore, I (wrongly Wink ) selected the maximum choosable Wink ...


------------------------
MCPD Web Developer, MCPD Windows Developer


Computers are made to solve problems you did not have in the days they didn't exist.

TraderSam
TraderSam
SSChasing Mays
SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)SSChasing Mays (655 reputation)

Group: General Forum Members
Points: 655 Visits: 565
Yes, great question, just not implemented properly.

I am curious to how the question author missed the length for the nvarchar columns. Or am I missing something?

If it was easy, everybody would be doing it!Wink
Bob Fazio
Bob Fazio
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2864 Visits: 683
Since 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 Smile 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.
**
Sean Walker
Sean Walker
SSChasing Mays
SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)SSChasing Mays (654 reputation)

Group: General Forum Members
Points: 654 Visits: 413
Once 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 Smile
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