Clustered Index Scan vs. Table Scan

Clustered Index Scan vs. Table Scan

Post by BenignVanill » Thu, 04 Dec 2003 18:33:11



This is a follow up post to the "Are Table Scans Always Bad?" post I posted
last week. I am in the process of re-indexing some tables, and I have been
cleaning things up so execution plans show no table scans. Performance seems
to be helped not hurt, so I was just curious, is it safe to say:

A Clustered Index Scan is always better then a Table Scan?

--
BV.
WebPorgmaster - www.IHeartMyPond.com
Work at Home, Save the Environment - www.amothersdream.com

 
 
 

Clustered Index Scan vs. Table Scan

Post by David Brown » Thu, 04 Dec 2003 18:40:56



Quote:> This is a follow up post to the "Are Table Scans Always Bad?" post I
posted
> last week. I am in the process of re-indexing some tables, and I have been
> cleaning things up so execution plans show no table scans. Performance
seems
> to be helped not hurt, so I was just curious, is it safe to say:

> A Clustered Index Scan is always better then a Table Scan?

They are basically the same thing.

If a table has a clustered index, then the only way to scan it is through a
Clustered Index Scan.   If some subset of your row restrictions are the
leading columns in the clustered index, then the Clustered Index Scan will
likely be much faster than a table scan.  Otherwise you are reading the
whole table either way.

David

 
 
 

Clustered Index Scan vs. Table Scan

Post by Aaron Bertrand - MV » Thu, 04 Dec 2003 18:46:11


Quote:> A Clustered Index Scan is always better then a Table Scan?

Yes.  Exporting to Excel and using a conditional formula is probably better
than a table scan.  :-)

--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/

 
 
 

Clustered Index Scan vs. Table Scan

Post by Delbert Glas » Sat, 06 Dec 2003 00:55:13


Quote:>... is it safe to say:
>A Clustered Index Scan is always better then a Table Scan?

[slap]

Apparently not ;-)

-----

A Clustered Index Scan is
[ ] always
[x] sometimes
[ ] never
better then a Table Scan.

Say we have two tables.
One table is clustered.
One table is non-clustered.
One table has many rows and the rows are wide.
One table has few rows and the rows are narrow.

A query might:
do a Clustered Index Scan
and bookmark lookups against the non-clustered table
or:
do a Table Scan
and bookmark lookups against the clustered table

Which way is better is depends on whether
the big table is the clustered table or
the small table is the clustered table.

Bye,
Delbert Glass


Quote:> This is a follow up post to the "Are Table Scans Always Bad?" post I
posted
> last week. I am in the process of re-indexing some tables, and I have been
> cleaning things up so execution plans show no table scans. Performance
seems
> to be helped not hurt, so I was just curious, is it safe to say:

> A Clustered Index Scan is always better then a Table Scan?

> --
> BV.
> WebPorgmaster - www.IHeartMyPond.com
> Work at Home, Save the Environment - www.amothersdream.com

 
 
 

1. TABLE SCAN Vs INDEX SCAN/SEEK

assume i have a tabel called Customer with the following column

 cuLastName, nvarchar(30)

and that there is an index on the column.

Can anyone explain why SQL Server does a table scan for
 SELECT * FROM Customer WHERE cuLastName LIKE 'k%'

but does an Index seek/scan for
 SELECT * FROM Customer WHERE cuLastName LIKE 'ka%'

the addition of one more letter triggers SQL to use the index where
i think the index should be used in both situations......

2. Usage of Client ID

3. Index scan vs Full table scan

4. Problems with Container inside a Grid

5. Table scan, Table scan, Table scan

6. Data written to Log Device ???

7. Index Fast Full Scan vs Index Full Scan?

8. excel date (internal) to pick format (interal)

9. Index Full Scan Vs. Index Range Scan

10. clustered index seek vs. Scan

11. full table scan vs index path for 1block table

12. question about seq scan and index scan

13. Index Scans become Seq Scans after VACUUM ANALYSE