When extracting whole tables or large recordsets (say, at least 80% of the table), it is better to use Full Table Scan than use index. Full scan is more efficient in this case because it no longer need to search through the index for each record. It doesn’t need to because the whole table is going to be extracted anyways.

However, you need to tell SQL Server that you want to use full table scan. Otherwise, it will use an index depending on the where clause or join criteria you have on your query.

Force Table Scan Syntax
SELECT memberID, fname, lname
FROM members WITH (INDEX(0))
WHERE dept_cd = 100

SELECT dept_name, memberID, fname, lname
FROM members WITH (INDEX(0))
,department WITH (INDEX(0))
WHERE members.dept_cd = department.dept_cd

By the way, some DBAs are not aware of this (force table scan). I’m not a DBA but I worked on a lot of performance optimization projects before and has always been working with our DBAs. Their usual advice is to use the index which is correct in most cases. However, I think some of our queries should have use a force table scan to be more efficient.

One Thought on “Force Table Scan in SQL Server/Sybase

  1. Ruimin on May 13, 2015 at 7:34 pm said:

    Thanks for the helpful information.

Post Navigation