Clarification requested for 'Estimating the Size of a Table' (Estimating the Size of a Heap)

I am currently engaged in determining the estimated size of a table given a potentially high growth rate. I am following the instructions found in the SQL2000 BOL article entitled "Estimating the Size of a Table". I need clarification regarding the instructions specified for item #4, determining the Variable_Data_Size variable. Basically, the table I am using has 25 TEXT fields in it (please don't ask, I didn't design it), and I'm not certain how these play into the Variable_Data_Size calculation. None of these columns will EVER store anywhere near the potential size for the data type. This table has the 'text in row' option enabled, and is set to store up to 256 characters for each text field 'in row'. (On a side note, I think the original designer thought doing this actually set a limit on the size of the text field to 256 chars, but I can't be certain). If it helps you to visualize, here is the table schema:

CREATE TABLE [picaweb].[temp_response] (
[ResponseID] [varchar] (30) NOT NULL ,
[Term] [varchar] (5) NOT NULL ,
[Subject] [varchar] (4) NOT NULL ,
[Course] [varchar] (4) NOT NULL ,
[Sect] [varchar] (3) NOT NULL ,
[MidEndFlag] [varchar] (3) NOT NULL ,
[SID] [varchar] (9) NOT NULL ,
[TemplateID] [varchar] (30) NOT NULL ,
[LastModified] [datetime] NULL ,
[College] [varchar] (30) NULL ,
[Classification] [varchar] (10) NULL ,
[CourseRequired] [varchar] (3) NULL ,
[ExpectedGrade] [varchar] (10) NULL ,
[Sex] [varchar] (6) NULL ,
[ItemAnswer1] [varchar] (1) NULL ,
[ItemComments1] [text] NULL ,
[ItemAnswer2] [varchar] (1) NULL ,
[ItemComments2] [text] NULL ,
[ItemAnswer3] [varchar] (1) NULL ,
[ItemComments3] [text] NULL ,
[ItemAnswer4] [varchar] (1) NULL ,
[ItemComments4] [text] NULL ,
[ItemAnswer5] [varchar] (1) NULL ,
[ItemComments5] [text] NULL ,
[ItemAnswer6] [varchar] (1) NULL ,
[ItemComments6] [text] NULL ,
[ItemAnswer7] [varchar] (1) NULL ,
[ItemComments7] [text] NULL ,
[ItemAnswer8] [varchar] (1) NULL ,
[ItemComments8] [text] NULL ,
[ItemAnswer9] [varchar] (1) NULL ,
[ItemComments9] [text] NULL ,
[ItemAnswer10] [varchar] (1) NULL ,
[ItemComments10] [text] NULL ,
[ItemAnswer11] [varchar] (1) NULL ,
[ItemComments11] [text] NULL ,
[ItemAnswer12] [varchar] (1) NULL ,
[ItemComments12] [text] NULL ,
[ItemAnswer13] [varchar] (1) NULL ,
[ItemComments13] [text] NULL ,
[ItemAnswer14] [varchar] (1) NULL ,
[ItemComments14] [text] NULL ,
[ItemAnswer15] [varchar] (1) NULL ,
[ItemComments15] [text] NULL ,
[ItemAnswer16] [varchar] (1) NULL ,
[ItemComments16] [text] NULL ,
[ItemAnswer17] [varchar] (1) NULL ,
[ItemComments17] [text] NULL ,
[ItemAnswer18] [varchar] (1) NULL ,
[ItemComments18] [text] NULL ,
[ItemAnswer19] [varchar] (1) NULL ,
[ItemComments19] [text] NULL ,
[ItemAnswer20] [varchar] (1) NULL ,
[ItemComments20] [text] NULL ,
[EssayQuestionAnswer1] [text] NULL ,
[EssayQuestionAnswer2] [text] NULL ,
[EssayQuestionAnswer3] [text] NULL ,
[EssayQuestionAnswer4] [text] NULL ,
[EssayQuestionAnswer5] [text] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I would appreciate any insight anyone might be able to provide. My calculations were wrong somewhere, and I believe it was on the Variable_Data_Size variable determination.

Best,
B.

Have you checked in the 2K5 version of Books Online? I'm not sure if it was updated...

http://msdn2.microsoft.com/en-us/library/ms187445.aspx

|||Yes, it's pretty much the same as for 2000. Now that I've really thought about it, I suppose the size of the data to be stored "in row" should be treated like a fixed-length field, especially since it will reduce row density per page. I think that's what I'll do.

Question answered.

Best,
B.

Checking KPI value for divide-by-zero

We've got some potentially complex KPI value definitions, which can involve division of two cell values. I want to put in a check to make sure we don't do a divide-by-0. If I do, is there any performance implication due to the calculation of the denomerator happening twice (once in the first IIf clause, again in the actual value). For example, if I have a value that's essentially:

[Measure1] / (Aggregate([A Set], [Measure 2])

and I want to make it

IIf( (Aggregate([A Set], [Measure 2]) > 0, [Measure1] / (Aggregate([A Set], [Measure 2]), Null)

Is there a performance hit for doing this? Does the aggregate happen twice? Is there a smarter way of doing this check?

Yes, Aggregate will happen twice. The best way to handle it is

CREATE HIDDEN AggASet = Aggregate([A Set], [Measure 2])

and then you can say

IIf( AggASet > 0, [Measure1] / AggASet, Null)

|||Thanks Mosha. But can I specify a composite expression like that in a KPI value expression?|||No - the CREATE HIDDEN statement should be somewhere inside the MDX Script.|||Shoot, that's what I was afraid you were going to say. The problem is that these KPIs are being generated by a GUI tool, and so far we've done everything inside the KPI definition itself, without having to muck with the MDX script. Changing that is going to involve some major code changes, sadly.

Is there any other way to handle this condition gracefully?|||

Your choices as I see them are:

1. Do nothing - take perf hit, and hope that in the next version query optimizer will automatically detect such expressions and will do the "right thing"

2. Change your logic to add hidden calculated measures to MDX Script

3. Don't check for 0 in denominator and let users see 1.#INF when such division occurs

4. Create another KPI which will hold the Aggregate expression and reference its through KPIValue function from your KPI expression. This way you won't need to change MDX Script. I am not sure, however, if it is possible to create hidden KPIs. So the drawback is that you will get an extra 'junk' KPI.

5. Something else ?

HTH,

Mosha (http://www.mosha.com/msolap)