Query Plan

[vbkris]

Hi Guys,

I have a table with the following columns:

Column1
Column2
Column3

Column Description:
Column 1 is an identity column with a primary key
Column 2 is a foreign key (bigint)
Column 3 is a varchar field

Indexes:
Column1 being the primary key has the Clustered index
Cloumn 2 AND Column 3 combination has a Unique Index (Not constraint) defined on them

All indexes are created on a separate file group (Maintained just for indexes).

Data Count:
The table currently has not more than 3 rows (but this is transactional in nature)

Query Plan:
When i give the following query:

select Column1 from Table

The query plan uses an INDEX (Second one) scan and NOT a clustered index scan.

Is this because of lack of data or wrong design?

Known is handfull, Unknown is worldfull

 

[ptheriault]

I wouldn't worry to much about which index is used for a table with 3 rows. In fact it may be fasted to drop all the indexes and let it perform a table scan. The query optimizer will chooce the index that cost the least.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[vbkris]

>>The query optimizer will chooce the index that cost the least.

That table is a transactional table and will have a lot of rows soon.

Its just that i thought that a Clustered Index Scan was faster than an Index Scan. Thats what has got me confused. The second index does not even incldue Column 1 (Even though the index refers the key at leaf level).

You think that is normal behavior???

Known is handfull, Unknown is worldfull

 

[ptheriault]

>>You think that is normal behavior???
Yes because you don't have a where clause. An index scan is an index scan. There is no way to be sure of which query plan will get used in your senerio. Unless of course you provide a query hint to help the optimer choose the index you want. But that is usually not recomemded.
What you really want here is an index seek! Put a where clause like "WHERE Column1 < 100" and see what happens to your Query Plan

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[ptheriault]

One other point here.
You only have 3 rows in the table. I'm sure the total cost for clustered index scan and the column2/column3 index scan is most likely the same. Like I said, at this point it would cheepest for a table scan.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[vbkris]

>>I'm sure the total cost for clustered index scan and the column2/column3 index scan is most likely the same

ok, am confused again, the BOL says that Clustered index scan is faster than the normal index scan. so i thought it would prefer that as the sql statement specifically states Column2.

but then again i will try to populate some sample data and get back to you guys...

Known is handfull, Unknown is worldfull

 

[ptheriault]

>>ok, am confused again, the BOL says that Clustered index scan is faster than the normal index scan. so i thought it would prefer that as the sql statement specifically states Column2.

That is true, But, you only have 3 rows. Add 3 million and it will make a huge difference.

My whole point here is you don't have enough data for it to matter what index is used.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[vbkris]

ok, gotcha. will try to populate some rows and get back to you...

Known is handfull, Unknown is worldfull

 

[Lamprey13]

A possible reason for using Index 2 is that SQL may chose that index because it has more data in it and will not have to perform a Bookmark Lookup, thus possible being more effecient that a Index 1 (Clustered) scan.

-Ryan

 

[vbkris]

but a clustered does not need a bookmark right? i still guess that all costs are right now the same. so SQL Server is picking one at random...

Known is handfull, Unknown is worldfull