Indexes

Question

Indexes

dips1231

SSC-Addicted

Points: 401
More actions
May 10, 2004 at 1:29 pm

#107917

Hi,
I had a question regarding the usage of indexes. Lets say I have a table on which I have a primary key and then I have an composite index on 3 columns. So if in my "where clause" if I make use of 2 columns out of 3 on which I have the composite index, will the optimizer make use of the index?
- Deepak.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 1

If those two columns are the leading portion of the three-column composite index, then the optimizer should use the index while executing the query.

Regards,

Goce Smilevski.

Paul Heiner SSC Enthusiast Points: 166 More actions · Answer 2

I just loaded a database that a client had created 5 indexes for 5 columns and it was always my belief that SQL was smart enought to use a single column within a composite index no matter what column was used. I saw this email and did a little test and to me it looks like SQL DOES use any column within a composite key.

So creating multiple indexs will just create more work for SQL when inserting or deleting data because of the index maintenance. Plus if you have a clustered index ... extract from books online "It is important to define the clustered index key with as few columns as possible. If a large clustered index key is defined, any nonclustered indexes that are defined on the same table will be significantly larger because the nonclustered index entries contain the clustering key."

Based on the test below I could see that the index was being used even when the where clause was using the col4.

Note that this test is the best possible index based on values used. I am going to continue to test with col2 , col3 and col4 having diffirent values, also varchar columns should be tested also.

create table test(

col1 int not null,

col2 int not null,

col3 int not null,

col4 int not null

&nbsp

alter table test

add constraint PK_test Primary Key Clustered(col1)

declare @counter int

select @counter = 1

while @counter < 10001

begin

insert into test(col1, col2, col3, col4)

select @counter,@counter,@counter,@counter

select @counter = @counter + 1

continue

end

create index idx_test on test (col2, col3, col4)

--show execution plan for this code below

--On the Query menu, click Display Estimated Execution Plan or press CTRL+L.

select count(*)

from test

where col4 = 455

Optimist415 Valued Member Points: 66 More actions · Answer 3

SQL server now is able to figure out to use the composite index even if you only query on one or two fields. Sql Server 7 didn't do that. But the best way to convince yourself is to look at the execution plan.

Goce Smilevski SSCommitted Points: 1704 More actions · Answer 4

Ok, I agree that SQL Server scans the index to avoid full table scan and reduce the number of pages read in memory. But, there is a significant performance benefit if the WHERE condition includes the leading portion of a composite index.

Regards,

Goce Smilevski.