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.
Reference : Pinal Dave (http://blog.SQLAuthority.com)
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 Lara,
There is absolutely no difference. They are same thing.
Regards,
Pinal Dave
(http://www.SQLAuthority.com)
Hi Pinal,
Thanks for all you articles they are very useful.
If Table scan and index scan are the same then why we need to have two terms?
I am having non clustered index on the table as the selectivity of the query returns more rows optimizer choose for Table scan,eventhough I have index on the table why is that behaviour, my execution plan must show index scan right instead of table scan?
A table scan will occur when no index is available on that table. The table then becomes a heap. so if you do a SELECT * FROM without_index you will get a table scan. If you were to put a clustered index on the table you would get a clustered index scan when performing the same query.
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 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)
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 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)
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 Sura,
Yes, Index Scan is same as Table Scan.
Regards,
Pinal Dave (SQLAuthority.com)
Hi Pinal,
I would disagree….see my post: http://www.techtree.co.uk/2011/10/table-scan-vs-index-scan/
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 (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.
@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 )
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 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
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.
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
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
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
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
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
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??
Hi Pinal,
I have a table with abt 200K records. a “where clause” on a non-indexed field causes a “clustered index scan”. It works fine and fast, but perf degrades when concurrent users increase.
do you think a clustered index scan can be a good reason for perf hit with increase in concurrent users even in a table of this size. OR maybe i should keep looking further for other causes.
rgds
Ajay
@Ajay,
If you have sql server 2000 then
1. Why dont you run Index Tuning Wizard, analyze your query, result of which recommends any improvements can be done on indexes for that query.
If you have SQL Server 2005,
1. Use Database Engine Tuning Advisor. This does the same thing, it will give you recommendations about indexes for that particular query.
Regards,
IM.
Hi Pinal,
I wanted to ask if there is something similar to the horizontal partitioning mechanism(automatic physical distribution of partitioned table in ms SQL 2005 SE).
Would be very useful if you could suggest something similar to this mechanism(if the same is not available in MS SQL 2005 SE)
[...] SQL SERVER – Index Seek Vs. Index Scan (Table Scan) [...]
Is there a way to make mssql aware that it needs to do an index seek rather than a table scan? The proper indexes exist, and the statistics have been updated to my knowledge. On one system, a join between two tables results in a table scan in one table. One a separate database with the same schema/indexes, it does an index seek on both tables in the join. How do I make mssql aware it needs to do an index seek on the one system?
i have a query the where clause which is convered in non-clustered index and partial non-key column which is convered in the non-clustered index as well. But the execution plan show me the clustered index scan = 98 %, can i have any solution to tunning this scenerio ?
There is some difference between Index scan and Table scan, that is, when we have index scan sql engine locks only PK not whole ROW, and because of that is faster than table scan
Pinal,
An Index Scan is not the same as a table scan. A “CLUSTERED” index scan is the same as a table scan.
This is a wonderful topic and I’m glad I found it as it’s the exact answer to a quest that I had about a table today. It helped reduce my query time from 14 seconds to less than 1!
Great work!
Tim
Hi,
I have a select statement. How do I know if it is using the index which i created on one of the columns (and this column is included in the select list)
Easy. Insert the select statement to Sql Server Management Studio and press CTRL+L. Check Execution Plan if it’s using Index Seek or Index Scan.
Check this MSDN article also:
http://technet.microsoft.com/en-us/library/ms189562.aspx
These articles and their subarticles might help also:
http://technet.microsoft.com/en-us/library/ms191227.aspx
http://technet.microsoft.com/en-us/library/ms345417.aspx
how tune any query
hi Pinal ,
i have 10 ten queries on three tables with 10 where conditions on one table.in that 10 ten queries 3 queries uses
where condition below
where city= and state=? and country=?
how indexes i need to create on these table(one or more).
this table getting truncate abd loaded everyday.
Please advise.
Hi,
Have you tried Sql Server Database Tuning Advisor? It gives you nice list of indexes it thinks would be good. You can even tell it to suggest indexed views.
It’s hard to say which columns you should index just by your where-statement. You could try to index all of those three columns, index per column. After that check Execution Plan to see if indexes are being used or not.
Hi Kumar,
I think you can create a composite Index on the three columns instead of creating indexes on each and every column to reduce space and to optimize the query performance. Correct me any body if i am wrong.
Yes, you can. But if the columns aren’t selective enough you risk that your composite index isn’t used at all. Also the order of columns inside the index is significant. You have to test and test and test again to see if the index is used or not.
I would suggest you create the index and check execution plan to see if it’s used. Then wait couple of days so that database can collect enough statistics and check it again. Finally sys.dm_db_index_physical_stats function tells a lot what’s going on with your indexes.
For example I created four composite index in a single table on one of your databases. All of them were used initially but eventually two of them was rendered useless so I dropped them and DB is running like charm.
Why would sql suddenly change from an index seek configuration to an index scan configuration? We went from screaming fast to no go in one afternoon. What would trigger the change? Since there were no changes made to the database other than additional data, what would automatically trigger this change in sql?
Hi Pinal,
I have a base view on top of which there are several views.
One of the views which is based on the base view gives timeout errors.
In the execution plan it shows one index seek takes Cost 80%
There are appropriate indexes on the tables.
Can you suggest any more optimization.
Normally I go through your articles and I find it very useful. Could you please suggest when to use clustered index and when to use non-clustered index. What columns should be used for non-clustered index and what columns should be used for clustered index.
dear sir,
i am trying to get the i’th row record from the table in sql server 2000,
for example i give the query
select * from table_name where row_position=1
if it is possible to get 1′th record from the table?
Vimal,
Assuming you mean 1st (first) then just use
select top 1 * from table_name
i want to print number of days the student remain absent in aschool daywise(like sun mon tu……) in single row from a student table
[email removed]
You need to have a calender table which joins with your original table and calculate the absents
select datename(weekday,c.date),count(*) from calender_table as c left join your_table as t
on c.datecol=t.datecol where t.datecol is null
i have a table with 45000 rows with a column of amount.
i want to create index on it but with conditions.like i want to create an index for amount>98000 rows , so that when i search for amount >98000 , then it do not searches the whole 45,000 rows but only searches the index with amount>98000.
how do i do this?
good
Hi Pinal,
I have a table which has 5000 rows of data in it. When i try to use a simple query “Select * From Table” its taking 20 seconds of time. I did not use any where conditions in my query. Its a just “Select * From Table”. I have different data type columns in my table including bit datatypes. I dont know how to improve the performance of my query. Please, help me in this.
What happens when you run it for second time? Also check if the table is blocked by other processes
Did you try “Select * From Table with(nolock)” ?
I have a table with M pages .IIf i want to retreive its data without index .What will be the cost of processing – full table one row retireval,full table one column retrieval,subset of table one row retrieval….
If I use B+ index can i improve the query processing…Please answer urgently I am in dire need of this answer