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 ««12345»»»

Storage Size Expand / Collapse
Author
Message
Posted Wednesday, April 9, 2008 1:49 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 17, 2014 6:00 AM
Points: 1,049, Visits: 3,012
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 ;)), 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
Post #482096
Posted Wednesday, April 9, 2008 3:04 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Saturday, October 4, 2014 9:28 AM
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

Post #482124
Posted Wednesday, April 9, 2008 3:21 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: Tuesday, October 26, 2010 1:30 AM
Points: 518, 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
Post #482135
Posted Wednesday, April 9, 2008 3:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, May 24, 2008 7:30 AM
Points: 154, 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

Post #482149
Posted Wednesday, April 9, 2008 4:06 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Saturday, March 19, 2011 12:37 PM
Points: 603, 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.
Post #482162
Posted Wednesday, April 9, 2008 4:54 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 16, 2008 3:07 PM
Points: 393, Visits: 52
4 + 102 + 1 + 4 + 1 + 1 + 202 + 1 = 316
Post #482175
Posted Wednesday, April 9, 2008 5:04 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: Tuesday, March 8, 2011 2:31 AM
Points: 513, Visits: 38
As others state: only the NVARCHAR columns already sum up to above the max answer. Therefore, I (wrongly ;) ) selected the maximum choosable ;) ...


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


Computers are made to solve problems you did not have in the days they didn't exist.
Post #482178
Posted Wednesday, April 9, 2008 6:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 10, 2011 12:13 PM
Points: 307, 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!;)
Post #482206
Posted Wednesday, April 9, 2008 6:18 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, January 6, 2012 2:39 PM
Points: 954, 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 :) 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.
**
Post #482210
Posted Wednesday, April 9, 2008 6:38 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 6:47 AM
Points: 630, Visits: 365
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 :)

Post #482231
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse