SQL SERVER - Index Seek Vs. Index Scan (Table Scan) « Journey to SQL Authority with Pinal Dave

SQL SERVER - Index Seek Vs. Index Scan (Table Scan)

March 30, 2007 by pinaldave

Index Scan retrieves all the rows from the table. Index Seek retrieves selective rows from the table.

Index Scan:
Since a scan touches every row in the table whether or not it qualifies, the cost is proportional to the total number of rows in the table. Thus, a scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate.

Index Seek:
Since a seek only touches rows that qualify and pages that contain these qualifying rows, the cost is proportional to the number of qualifying rows and pages rather than to the total number of rows in the table.

Index Scan is nothing but scanning on the data pages from the first page to the last page. If there is an index on a table, and if the query is touching a larger amount of data, which means the query is retrieving more than 50 percent or 90 percent of the data, and then optimizer would just scan all the data pages to retrieve the data rows. If there is no index, then you might see a Table Scan (Index Scan) in the execution plan.

Index seeks are generally preferred for the highly selective queries. What that means is that the query is just requesting a fewer number of rows or just retrieving the other 10 (some documents says 15 percent) of the rows of the table.

In general query optimizer tries to use an Index Seek which means that optimizer has found a useful index to retrieve recordset. But if it is not able to do so either because there is no index or no useful indexes on the table then SQL Server has to scan all the records that satisfy query condition.

Source: Pinal Dave (http://www.SQLAuthority.com)

Posted in Pinal Dave, SQL, SQL Authority, SQL Download, SQL Error Messages, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Tips and Tricks, Software Development, T SQL, Technology | 31 Comments

31 Responses to “SQL SERVER - Index Seek Vs. Index Scan (Table Scan)”

on March 30, 2007 at 10:49 am Nicholas Paldino [.NET/C# MVP]

You say for an index (table) scan, that if most of the rows qualify for the predicate, it’s inefficient. Don’t you mean that the other way around? If most of the rows DON’T qualify, then you don’t want a table scan?
on March 30, 2007 at 11:59 am pinaldave

Than you Nicholas.
You are correct. Index scan is an efficient strategy if the table is small or if most of the rows qualify for the predicate. That is what I am tring to imply above.
on May 4, 2007 at 3:51 am Lara

>Index Scan is nothing but scanning on the data pages >from the first page to the last page.

SO what’s the difference b/w index scan and Table scan ?
on May 4, 2007 at 8:47 am pinaldave

Hi Lara,

There is absolutely no difference. They are same thing.

Regards,
Pinal Dave
(http://www.SQLAuthority.com)
on May 5, 2007 at 1:05 am vikranth

Hi,
Do Index have their own pages, and data rows have their own pages.
Index scan searches Index pages and table scan searches Data rows pages.
on May 28, 2007 at 3:15 am Shantanu

Hi
It seems from your article that Index Seeks are always desirable.
If no of Index Seeks are increasing after optimisation, does it mean the query is optimised (to some extent atleast)?
on May 28, 2007 at 7:18 am pinaldave

Hi Shantanu,

It depends:
If from Index Scan it is converted to Index Seek it is optimized.
If due to some reason number of Index scan are same but only index seek are increased , it shows not optimization.

Regards,
Pinal Dave (http://www.SQLAuthority.com)
on May 28, 2007 at 10:29 am satinath

Hi Pinal:
I have a table with 7 million rows with an index and updated stats.
When I use a query with in clause, SQL server seems to use index seek upto 70 values in the in clause. Anything more than that it uses index seek.
I have two questions:
i) How do I tell it to continue to use seek instead of choosing index scan.
ii) Is there anything I can do better other than keeping the statistics up-to-date.
Thanks in advance,
on May 28, 2007 at 11:14 am pinaldave

Hi Satinath,

SQL Server decides based on execution plan either to use Index Seek or Index Scan. User can not specify that. It also depends on how many numbers of records it is retrieving based on how many rows there are in table.

Following things will help to improve the performance:
Keep upto date stats.
Add more index if possible (extra condition in where or join which is indexed)
Partition Table
Replication and have read only database.

Though all the suggestion will take time to implement but that is the next thing to do.

Regards,
Pinal Dave (http://www.SQLAuthority.com)
on June 27, 2007 at 2:05 pm Mark Solomon

Hi all,

From testing, I’ve found that there can be a big difference between an non-clustered index scan versus a table scan or clustered index scan.

Non-clustered indexes are like mini-tables.
They just contain the data you’ve indexed in it’s own seperate bucket. So, if all the data you need is in this bucket, you’ll be saving I/Os when you retrieve data from this bucket–versus either a table scan or clustered index scan which contains all the data spread out over more data pages/extents. That’s because you’ll be reading less data; additionally you’ll be reading from data pages that are closer to each other–versus farther away (i.e. reading all the data from each row in the table versus just a few select columns in an index).

Scans may not ideal. However, non-clustered index scans can save you some serious I/O–versus clustered scan or table scan.
on July 24, 2007 at 6:28 am Virendra

SEEK means that a useful index exists that is helpful for finding a match for the given query. Only data with pointers matching the index need to be retrieved.
ex index on emp_lname
select xx,xx where emp_lname=’bhindi singh’
adv: Less data and fast

A index SCAN reads all of the pointers of the index to find the match and occurs when an index exists that is only partially helpful. The index might only be partially helpful if there is relatively low selectivity in the distribution of the data. Data that is relatively unique is said to have a higher degree of selectivity whereas data that is less unique has a lower degree of selectivity.
ex index on emp_lname
select xx,xx where emp_lname like ‘bhindi%’
adv: more data and mix and match of search
disadv: more time

A TABLE SCAN occurs when no useful indexes exist. A TABLE SCAN reads all data, row by row, to find the match.

select xx,xx where emp_lname like ‘bhindi%’
select xx,xx where emp_lname = ‘bhindi singh’
only good for small table with less records.

In general, SEEKing is more efficient than SCANning; however, a SCAN might be faster than a SEEK if the table is relatively small and cached in memory.
on August 12, 2007 at 6:24 am Sura

Pinal,

Is IndexScan as same as TableScan? I am confused by your reply to Lara.

My understanding is that, in an IndexScan, SQL server reads all pages in the index and only MATCHING rows from data table.
on August 12, 2007 at 8:27 am pinaldave

Hi Sura,

Yes, Index Scan is same as Table Scan.

Regards,
Pinal Dave (SQLAuthority.com)
on August 24, 2007 at 2:17 am Kavi

hi pinal,
I got some difficulties in understanding Selection using indices. It says tht search algorithm tht use an index are:
-A3(primary index, equality on key)
-A4(primary index, equality on nonkey)
-A5(secondary index, equality)

wht i’m not sure here, is the key…
wht it means by equality on key(wht it means by key in this context?)- is it refering to sorted datas?

Pls help me, i hv browse thru few books, but still didnt manage to understand..

Hope to knw it frm u…

Thanks.

Regards,
Kavi
on September 19, 2007 at 1:59 am Neerajan

Hi,

Is there a way to capture the table scan queries in SQL Server 2005?

Thanks & Regards,
Guru
on October 4, 2007 at 5:07 pm Stephen J Anderson

Pinal,

Are you sure that an Index Scan is the same as a Table Scan in all cases? If you refer to this MSDN article (http://msdn2.microsoft.com/en-us/library/aa964133.aspx), it makes a differentiation between three different types of lookup:

* A table scan, where there exist no clustered indices on the table. Offers the worst performance.

* A clustered index scan, which is confusingly sometimes also referred to as a table scan. Can be faster than a table scan because it may have to load less information.

* An index seek.
on November 28, 2007 at 6:30 pm pinaldave

@Stephen J Anderson,
That is great article, I will re-write my post with addition from that article.

@Neerajan,
Use SQL Profiler.

Regards,
Pinal Dave ( http://www.SQLAuthority.com )
on December 12, 2007 at 5:49 pm Kishore

Hi Pinal,

Your explanation is very good. It cleared all my doubts.

thank u very much

–Kishore
on May 13, 2008 at 7:34 pm Judy

I need a SQL query that will Select a Field name from a Table, compare the contents of that field with a TEXT file, and if there is a match, change the contents of another field associated with that field.
on May 21, 2008 at 12:48 pm Ram

Table 1 - User (IDUser (int) pk, Name (varchar), Female (bit))
Table 2 - Club (IDClub (int) pk, Name (varchar))
Table 3 - User_Club (IDUser_Club (int) pk, IDUser Fk, IDClub Fk)

Goal : To attend all the clubs where the number of female is atleast 10% of entire student population.

Improve the efficiency of the below sql.

SELECT [Name] FROM
Club c
INNER JOIN
(
SELECT c.IDClub, COUNT(uc.IDClub) AS FCount FROM
User_Club uc INNER JOIN Club c ON uc.IDClub = c.IDClub
INNER JOIN [User] u ON uc.IDUser = u.IDUser
GROUP BY c.IDClub, Female
HAVING Female = 1
) tab
ON c.IDClub = tab.IDClub
CROSS JOIN
(
SELECT CAST(COUNT(*) AS DECIMAL(19,4)) AS UCount FROM [User]
) tab2
WHERE ((tab.FCount / tab2.UCount * 100)) > 10

please, help me
on May 28, 2008 at 12:01 am Aaron

Pinal:

How does SQL Server 2005 issues a search inside a page that belongs to a clustered index? Does it scan the page or simply performs a binary search (I mean, since the records are ordered in a clustered index)?

Best regards!
on May 30, 2008 at 5:21 pm Seema

How does Sql Server know about the percentage of required rows in result without executing it.
on June 17, 2008 at 12:12 pm Amit Jain

Hi Pinal,

I am currently working on migrating queries from DB2 to SQL Server 2005.

Please refer to the following DB2 CREATE INDEX Query:

create index XFK_ACTV_CTC on ACTIVITY (
CNTX_TYP_CD ASC
)
allow reverse scans;

In IBM-DB2 the syntax “ALLOW REVERSE SCANS” - Specifies that an index can support both forward and reverse scans; that is, scanning of the index in the order that was defined at index creation time, and scanning in the opposite order.

I have been trying to search for an equivalent syntax in SQL Server 2005, however I am not able to do so.

Kindly help me in searching it.

Thanking you in anticipation.

Best Regards,
Amit
on June 20, 2008 at 3:02 pm Evald

table scan and index scan is not the same thing.
look at http://msdn.microsoft.com/en-us/library/aa964133.aspx

Table scan (called clustered index scan in the case of an index-based table) is faster than table scan of a heap table

Regards
on July 14, 2008 at 9:55 pm Smummert

Remember the data size folks. If an index has 40 bytes but the table has 400 bytes per row, the IO increases by a factor of 100 if all the data is together (read reorg). Index will always be faster from a pure IO perspective because the IO buffers fetch more ‘qualifying’ data per physical IO read than a table scan.
on August 5, 2008 at 4:30 pm Mohanraj

Hi Pinal Dave,

I am trying to implement index by using substring function but its throwing an error so could u suggest me for better solution.

Generally, implementing this substring function in index for getting the index seek on the query.

Below is the syntax.

create nonclustered index IX_CreatedBy on TransDetails
(substring(createdby, charindex(’\',CreatedBy) + 1, len(CreatedBy)))

in the db having the value like \
at this moment cannot change the table structure for this query alone.

Regards,
Mohanraj Jayaraman
on September 18, 2008 at 9:28 pm sunil

Pinal,

Your explainations are really amazing.

I have seen couple of times with SQL Server 2000 that for some reason, query optimizer do not use index on its own and query finishes in about 8 min while if I give hint(explicit) then it returns output in less than a min. I checked index frag and update stats.. all are looking fine.

Can you please advice ?

Thanks,
sunil
on September 18, 2008 at 9:44 pm sunil

Pinal,

Here’s the query i was talking about.. I tested it in SQL 2000 and SQL 2005 both…

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = ‘2008-09-11 16:00:00′
SET @EndDate = ‘2008-09-12 00:00:00′
SELECT * FROM PCHDotCom.dbo.BillMeEntries WITH (NOLOCK, INDEX=IX_BME_CREATED)
WHERE
Created >= @StartDate AND
Created < @EndDate
ORDER BY
1 DESC

If I remove the index hint, the query plan will decide to do clustered index scan and the query takes drastically longer

Please help ASAP.

Thanks,
Sunil
on September 22, 2008 at 8:28 pm Sunil

Hi,

Here is my Select statement

UPDATE FCE
SET
–FCE.RECAT_CREDIT_CMT = RIID.CREDIT_CMT,
FCE.RECAT_DEAL_ID = RIID.DEAL_ID,
FCE.RECAT_STATUS = RIID.STATUS,
FCE.RECAT_STATUS_DT = RIID.CLM_STATUS_DT,
FCE.RECAT_EUV_FLAG_ITEM = RIID.EUV_LINE_FLAG,
FCE.RECAT_EUV_FLAG = RIID.EUV_FLAG,
FCE.M_CODE = RIID.MCODE,
FCE.RECAT_EUV_SUBMIT_DT =RIID.EUV_SUBMIT_DT,
FCE.RECAT_EUV_CONTROL_DT =RIID.EUV_CONTROL_DT,
FCE.STAKO_DEAL=RIID.STAKO_DEAL,
FCE.MMP_FLAG=RIID.MMP_FLAG
FROM FinClaimEntries FCE, RECAT_ITEMS RIID
where FCE.CLAIM_ID = RIID.ECLAIM_ID
AND (FCE.recat_prod_item_id = RIID.ECLM_ITEM_ID
OR FCE.recat_bdl_item_id = RIID.ECLM_ITEM_ID
OR FCE.CLAIM_ITEM_ID = RIID.ECLM_ITEM_ID
)
AND RIID.CLM_ID = (SELECT MAX(CLM_ID) FROM RECAT_ITEMS RIID2 where FCE.CLAIM_ID = RIID2.ECLAIM_ID )

Here is Number of Records Details

FinClaimEntries - 78,589,25
RECAT_ITEMS - 97,256,86

Please let me know yours suggestion for Index Creation & Joins

Thanks,
Sunil
on October 20, 2008 at 2:17 pm Johnny - Greece

Hi to all,

I should optimize a very simple query (select) where the actual execution plan presents a clustered index cost of 56 % due to estimated cost operator. how can i reduce this cost??

thanks
on October 24, 2008 at 4:18 am Virgilio

Hi Pinal,
Could you explain the Previous syntax:
SELECT * FROM PCHDotCom.dbo.BillMeEntries WITH (NOLOCK, INDEX=IX_BME_CREATED)
WHERE
Created >= @StartDate AND
Created < @EndDate
ORDER BY
1 DESC
=====
Is the Inde = index name a Valid syntax if so how does it help performance wise??