Indexing question - 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 2000 Forums

SQL Server Development (2000)

Indexing question

Author

Topic

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-16 : 14:27:42

Lets say I have...


create table test (
    pk int not null,
    dt smalldatetime null,
    constraint pk primary key nonclustered (pk) )
create unique clustered index cx on test(pk,dt)

create table test2 (
    pk int not null constraint fk references test(pk),
    status char(1) not null default 'a',
    constraint pk2 primary key nonclustered (pk))

...and then I create...


create unique clustered index cx_2 on test2(status,pk)

...and execute....


select
    test.pk
from
    test
    inner join test2
        on test.pk = test2.pk
where
    test2.status = 'a' and
    dt is null

This does a index scan on test.pk and a seek of test2.pk2, however if instead of the above cx_2, I create...


create unique clustered index cx_2 on test2(status,pk)

...it does an index seek on test.pk and a seek on test2.cx_2.

Why is this? I always thought that a multi column index should be create in selectivity order, and in this case pk (being unique) should be more selective than status. However, (pk,status) index causes the optimizer to not consider the cx_2 index at all and rather use a scan of the nonclustered pk2 index.... Is this because I don't have any data in the table and thus no valuable statistics, or am I missing something? SQL Server 7....

<O>

nr
SQLTeam MVY

12543 Posts

Posted - 2002-07-17 : 18:34:14

>> Why is this? I always thought that a multi column index should be create in selectivity order

The filter item should appear first.

first case
create unique clustered index cx_2 on test2(status,pk)
second
create unique clustered index cx_2 on test2(status,pk)

I guess one of these was wrong (unless I'm missing the difference).

It will try to optimise the query to produce the least number of reads.
A non-clustered index scan will be scanning a copy of the indexed fields and so may be faster than a clustered index scan - depending on whether it has to access data pages and how many.

It doesn't have anything which can service dt = null so has to scan the clustered index (data pages) to get it. test(pk,dt) is not a useful index for this.

The question is whether the server thinks it will do less reads by scanning for dt then joining to test2 or seeking for test2.pk and then joining to test.

You could try a nonclustered index test(dt, pk) then it wouldn't access the data pages at all.
Or if there aren't many recs for test2.status then non clustered test(pk, dt) and non clustered test2(status, pk)
This would give covering indexes with no data page access - if the optimiser gets it right.

==========================================
Cursors are useful if you don't know sql.
Beer is not cold and it isn't fizzy.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-18 : 08:49:36

quote:

first case
create unique clustered index cx_2 on test2(status,pk)
second
create unique clustered index cx_2 on test2(status,pk)

No wonder I wasn't getting any bites on this line..... The first cx_2 was supposed to be (pk, status)....if there was a [rolleyes] icon, it would go here...

<O>

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-07-18 : 09:09:02

The above ddl represents a standard used in this organization. dt is like an IsActive bit column, except it is DateInactivated. NULL means the record is active, a date means the record is inactivated (simulating deletion, without removing the record).

My thinking is that while pk is the primary key for the table, since just about all the queries in the system will look for dt is null, I would make the clustered index on (pk,dt) or (dt,pk). A non-clustered index, while it would cover this particular query, would result in a book-mark lookup of whatever the clustered index is, to get additional fields.

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources