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
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
how does an index seek knows “qualifying rows”? does it scan that before-hand ?
Hai sir,
Thanks for the info.
How can a index scan converts to index seek?
Hi sir,
Thanks for the info
how to reduce table scan where condition in sql server 2005
You need to use indexed column in thw where clause without applying functions over them
hi pinal dave, i am your deepest fan in the world. i have question, could you clear me or suggest me……………how to reduce clustered index delete cost? how to convert index seek to clustered index seek ? how to convert predicate to seek predicate………..kindly give me suggestion……….highly appreciated
In Our Production server 32 GB RAM, Last week In C drive total size is 72 GB and
free space 23 GB Size But This week suddenly 10 GB of resource(disk space) occupied by SQL resources So Now total size is 72 GB and free space is 13 GB only. Is any Query to find which SQL server objects(view/table/sp/triggers etc) consumes this 10 GB of Disk space ? How to sort out in future? any method available ?
Quick suggestions are highly appreciated………………..
Note:
this week no installation or update/delete/insert had done in cpu and sql server.
EXEC sp_spaceused ‘table name’
Can we use same table column for clustered index and non clustered index? is it useful or not?
Not possible. Why do you want to do this?
Thanks a lot it really help me.
If I have many unused index, (last_user(system)_scan(seek)) is null
Is it better to remove the index.
Hey Pinal plz can you tell me the answer of the given question
There is 1 table ‘T’ as follows
id ch na flag
2 A jack Y
5 b amy N
1 $ adams N
3 * anna Y
7 @ rose N
4 & smith Y
6 ! sue Y
Create nonclustered index nc_T on T(id,ch,na)
Which of the below ‘select’ statements does NOT perform an index seek?
• select na from T where ch = ‘!’
• select na from T where id = 6 and ch = ‘!’
• select na from T where ch = ‘!’ and id = 6
• select na from T where na = ‘sue’ and id = 6 and ch = ‘!’
• Leave blank
select na from t where ch=’!’ is not perfor an index seek
here when ever we apply on where conditon that time that column jonly datatype is string type .so that query is not erform index seek
which index work with substring function clustared or non clustared..?
If your SUBSTRING function is inside WHERE clause, then answer is none. In that case, SQL Server has to retreive all records and then perform substring on them.
To avoid this situation you should create persisted calculated field and then create nonclustered index on it
Hi Pinal. Thanks for this great website where we can all share knowledge.
Can someone please help me improve the performance of a simple query? There are two tables, MAIN and DETAIL, and each have a clustered PK called ID (bigint). DETAIL has an indexed column cMAIN that is the ID from MAIN (basically a foreign key without being explicitly set as such). When I query MAIN based on parameters such as date, results are instantaneous.
When I use an INNER JOIN or sub-query to link MAIN and DETAIL, SQL Server uses a Clustered Index Scan (62%) on the PK of DETAIL and a Hash Match (38%) to return records from DETAIL. MAIN has 48 million rows (8 GB data, 3 GB index) and DETAIL has over 50 million rows (5 GB data, 3 GB index). The clustered index scan checks all 50 million rows even though the result set contains less than 1000 rows. The where clause is based on MAIN only, and the query is similar to SELECT [DETAIL].* FROM [MAIN] INNER JOIN [DETAIL] ON [MAIN].[ID] = [DETAIL].[cMAIN] WHERE [MAIN].[StartDate] BETWEEN… AND … All columns from DETAIL are needed in the result set.
Is there anything that can be done to force or trick SQL Server to use an index seek instead of a scan? In my opinion, the query is very selective and SQL Server should use a seek on the available index. No suggestions are presented in the actual execution plan. The database is a replication with writes coming from the publisher only. I am using SS 2005 64-bit on Windows Server 2003 R2 64-bit. My hardware is unlikely to change (maximum ram already @ 32 GB, SSD on PCIe, two quad core XEON 5335 from 2007). Any help would be greatly appreciated.
Try using covered index etc, or if you are using this query in a function and not stored procedure then this will happen, if you still require a function and cannot convert it into sp then use a view for that query
How do I know whether the query is doing a table scan or seek? (I’m using SQL server 2008)
If you execute the query and look at the actual execution plan, or estimated execution plan you will see the seeks or scans in the execution plan. Alternatively, you can look in the cached plans for the execution plan
You can find it using the Execution plan