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 the 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 the 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 the query condition.
Reference : Pinal Dave (https://blog.sqlauthority.com)
107 Comments. Leave new
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?
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.
>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 ?
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.
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)?
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,
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.
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.
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.
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
Hi,
Is there a way to capture the table scan queries in SQL Server 2005?
Thanks & Regards,
Guru
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 , 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.
Hi Pinal,
Your explanation is very good. It cleared all my doubts.
thank u very much
–Kishore
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.
Use charindex or patindex
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
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!
How does Sql Server know about the percentage of required rows in result without executing it.
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
table scan and index scan is not the same thing.
look at https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2005/administrator/aa964133(v=sql.90)
Table scan (called clustered index scan in the case of an index-based table) is faster than table scan of a heap table
Regards
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.