Oracle Consulting Oracle Training Oracle Support Development
Oracle Books
SQL Server Books
IT Books
Job Interview Books
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle


Privacy Policy



SQL Server full scan vs. index scan

SQL Server Table vs. Index Scans

When are scans good? If the objects are small in physical size, it is usually more efficient for SQL Server to cache the object and scan it rather than utilizing an indexing strategy. If a query needs to return all the rows in a particular table and a covering index does not exist, a scan is the only door left open to SQL Server.

However, large table scans should be avoided as they take serious resources to perform and often flood the buffer cache with data pages that are not likely to be re-read. Indexing is usually the remedy for such situations; however, it must be ensured that the actual WHERE clause is not written in a way that negates the use of an index.

If an indexing strategy will not work and only a subset of a table’s data is needed, the DBA can investigate the use of partitioning to cut down on scan times. In SQL Server 7 and 2000, tables can be manually partitioned through a technique called horizontal partitioning, but with SQL Server 2005, full object partitioning is supported through DDL. Horizontal partitioning will be covered later in an upcoming section.

In any event, the savvy DBA should be on the lookout for SCAN operations that show up in the EXPLAIN plans versus SEEKs. SEEKs attempt to go directly to the rows necessary to fulfill a request; whereas scans read the whole object.

Finally, using some functions and expressions in WHERE predicates can totally negate the use of indexes that could otherwise be used by the optimizer. An example of a suppression WHERE predicate would be as follows:

   substring(patient_name,1,5) = 'JOHNS'

To utilize an index, the query could be rewritten in the following fashion:

   patient_name like 'JOHNS%'

The above book excerpt is from:

High-Performance SQL Server DBA
Tuning & Optimization Secrets

ISBN: 0-9761573-6-5
Robin Schumacher  

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster

Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   



 Copyright © 1996 -2017 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks

Hit Counter