Indexing strategy for CTE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Indexing strategy for CTE

Author

Topic

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2008-06-05 : 15:27:11

What index would you implement to cover this query:


with cte(
    NaturalKey1,
    NaturalKey2,
    SomeValue,
    RowNumber)
as(
    select
        NaturalKey1,
        NaturalKey2,
        SomeValue,
        dense_rank() over(
            partition by
                NaturalKey1,
                NaturalKey2
            order by
                EffectiveDate desc) as 'RowNumber'
    from
        MyTable)
select
    NaturalKey1,
    NaturalKey2,
    SomeValue
from
    cte
where
    RowNumber = 1

Something like this, right:?


create nonclustered index ix_Cover
on MyTable(
    NaturalKey1,
    NaturalKey2,
    EffectiveDate)
include(
    SomeValue)

But why is that I can never get a plan that is favorable to this one's:


select
    NaturalKey1,
    NaturalKey2,
    SomeValue
from
    MyTable t1
where
    not exists (
        select 1
        from
            MyTable t2
        where
            t1.NaturalKey1 = t2.NaturalKey1 and
            t1.NaturalKey2 = t2.NaturalKey2 and
            t1.EffectiveDate < t2.EffectiveDate)

Is row number partitioning always not the right choice for effective dating?

Pondering ...

Jay
to here knows when

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources