Index Statistics

I am toying around with the article below to try to get my brain around some
index tuning issues and understand how to apply the knowledge to our
database.
http://www.sql-server-performance.c..._statistics.asp
So I did a DBCC SHOW_STATISTICS on one of our larger indexes, and I don't
understand these results:
Range-Hi-Key Range-Rows Eq-Rows Distinct-Range-Rows Average-Range-Rows
BASFITSERVICES 664.69312 683.0 0
974.95349
If Range-Rows is how many items mapped to this index key and Eq-Rows
represents how many items are equal to this index key, then how can Eq-Rows
be greater, and why would the Average be even higher?
--
Peace & happy computing,
Mike Labosh, MCSD
"(bb)|(^b{2})" -- William ShakespeareHi Mike
I think you have not quite understood the values being returned!
From BOL:
RANGE_HI_KEY Upper bound value of a histogram step.
RANGE_ROWS Number of rows from the sample that fall within a histogram step,
excluding the upper bound.
EQ_ROWS Number of rows from the sample that are equal in value to the upper
bound of the histogram step.
DISTINCT_RANGE_ROWS Number of distinct values within a histogram step,
excluding the upper bound.
AVG_RANGE_ROWS Average number of duplicate values within a histogram step,
excluding the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for
DISTINCT_RANGE_ROWS > 0).
You may also want to look at the pages on this in "Inside SQL Server 2000"
by Kalen Delany ISBN 0-7356-0998-5
John
"Mike Labosh" wrote:

> I am toying around with the article below to try to get my brain around so
me
> index tuning issues and understand how to apply the knowledge to our
> database.
> http://www.sql-server-performance.c..._statistics.asp
> So I did a DBCC SHOW_STATISTICS on one of our larger indexes, and I don't
> understand these results:
> Range-Hi-Key Range-Rows Eq-Rows Distinct-Range-Rows Average-Range-Ro
ws
> BASFITSERVICES 664.69312 683.0 0
> 974.95349
> If Range-Rows is how many items mapped to this index key and Eq-Rows
> represents how many items are equal to this index key, then how can Eq-Row
s
> be greater, and why would the Average be even higher?
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "(bb)|(^b{2})" -- William Shakespeare
>
>|||Thanks, John!
Mike, you might also want to look at this whitepaper:
http://msdn.microsoft.com/library/d...r />
query.asp
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:D09E0695-CBD6-4B33-BF3F-E5DCF6B4B294@.microsoft.com...
> Hi Mike
> I think you have not quite understood the values being returned!
> From BOL:
> RANGE_HI_KEY Upper bound value of a histogram step.
> RANGE_ROWS Number of rows from the sample that fall within a histogram
> step,
> excluding the upper bound.
> EQ_ROWS Number of rows from the sample that are equal in value to the
> upper
> bound of the histogram step.
> DISTINCT_RANGE_ROWS Number of distinct values within a histogram step,
> excluding the upper bound.
> AVG_RANGE_ROWS Average number of duplicate values within a histogram step,
> excluding the upper bound (RANGE_ROWS / DISTINCT_RANGE_ROWS for
> DISTINCT_RANGE_ROWS > 0).
> You may also want to look at the pages on this in "Inside SQL Server 2000"
> by Kalen Delany ISBN 0-7356-0998-5
> John
>
>
> "Mike Labosh" wrote:
>|||Of course if you are in the UK on 8th or 10th Feb and wish to hear the
master herself then check out http://www.sqlserverfaq.com/
John
:):)
Kalen Delaney wrote:
> Thanks, John!
> Mike, you might also want to look at this whitepaper:
>
>
http://msdn.microsoft.com/library/d...l/statquery.asp
>
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
> news:D09E0695-CBD6-4B33-BF3F-E5DCF6B4B294@.microsoft.com...
histogram
the
step,
histogram step,
Server 2000"
around
our
http://www.sql-server-performance.c..._statistics.asp
I don't
Eq-Rows
can|||And thanks again...:-)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:1106676698.587786.264330@.f14g2000cwb.googlegroups.com...
> Of course if you are in the UK on 8th or 10th Feb and wish to hear the
> master herself then check out http://www.sqlserverfaq.com/
> John
> :):)
>
> Kalen Delaney wrote:
> http://msdn.microsoft.com/library/d.../>
atquery.asp
> histogram
> the
> step,
> histogram step,
> Server 2000"
> around
> our
> http://www.sql-server-performance.c..._statistics.asp
> I don't
> Eq-Rows
> can
>

Index On a View

Does anyone know of a trick that allows you to apply an index on the following view? I would like to index the license_id column. When I try to apply a unique clustered index I get the following error - Index on view 'dbo.v_lic_status' cannot be created because function 'getdate' yields nondeterministic results.

VIEW DDL:

SELECT license_id,
CASE
WHEN (term_date < GETDATE()) THEN 'Terminated'
WHEN (suspend_date < GETDATE()) THEN 'Suspended'
WHEN (expiration_date < GETDATE()) THEN 'Expired'
WHEN (effective_date < GETDATE()) THEN 'Active'
ELSE 'Pending'
END AS lic_status
FROM dbo.license

INDEX DDL:

create unique clustered index ux_v_lic_status_01 on dbo.v_lic_status (
license_id
)
with
fillfactor= 90
goYou have to create the view with schemabinding and both ANSI_NULLS and QUOTED_IDENTIFIER option must be on. For detailed information check BOL under the subject 'Creating an Indexed View'.|||Hmm...I tried that and still doesn't work. Here's the create statement for the view.

/*================================================= =============*/
/* View: v_lic_status */
/*================================================= =============*/
create view dbo.v_lic_status with schemabinding as
SELECT license_id,
CASE
WHEN (term_date < GETDATE()) THEN 'Terminated'
WHEN (suspend_date < GETDATE()) THEN 'Suspended'
WHEN (expiration_date < GETDATE()) THEN 'Expired'
WHEN (effective_date < GETDATE()) THEN 'Active'
ELSE 'Pending'
END AS lic_status
FROM dbo.license
go|||Books OnLine (article name:Creating an Indexed View) has this to say under Requirements for an Indexed View:

All functions referenced by expressions in the view must be deterministic. The IsDeterministic property of the OBJECTPROPERTY function reports if a user-defined function is deterministic. For more information, see Deterministic and Nondeterministic Functions.

As for how to work around this, I am not too sure. Looks like you would have to put a flag on a base table for when something goes expired, terminated, etc..|||Thanks for the reply, that's what I was afraid of. Didn't know if someone knew a trick or shortcut around this. It sux since I have an 'else' as a catch all in the case statement sql server doesn't consider this deterministic.|||An indexed view actually creates a persistent copy of the data in a virtual table. The data in the virtual table is automatically updated as data in the underlying tables is modified. So you see, creating an indexed view using a statement that references GETDATE would need to be updated continuously, hence it is not allowed.

I hope this help you understand some of the restrictions places upon indexed views.