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
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
In case of non clustered index, index key columns and data in table are stored on different pages. Firstly index key columns are scanned and the column key value which meets the criteria in the predicate condition is then referred to obtain data from a table based on row . So full scanning of table does not take place…Please correct me if I am wrong and If this is the case what is the difference in seek and scan.
May I understand that Index Scan and Table Scan are the same thing? If an Index Scan is performed, it means that the index/s are not being used for that particularly query?. Is this correct?
Hi Pinal,
Can i partitioned a table which is already used in Merge replication or Peer to Peer replication as an article.
i.e. Table XYZ is replicated using merge or Peer to Peer replication from server S1 to Server S2. Table is of 700Gb and i want to partitioned the tables.
Please, let me know how to do it. What is the effect of that on publisher and subscriber in merge replication ?
Hi Pinal,
how can we decide that there should be a index scan or index seek?
Hi Pinal,
one of the best solution for the Index scan / Table scan and seek
can any one please provide me one of live example of indexes and with out indexes, Many Thanks
in
select * from MYTABLE
WHERE SyrogatePrimaryKolumn=’X’ and NonClusteredIndexColumn=’Y’ and NoIndexColumn=’Z’
the optimizer will use use SEEK on Primary Clustered Index
but in
select * from MYTABLE
where SyrogatePrimaryKolumn>=1 and SyrogatePrimaryKolumn=1 and SyrogatePrimaryKolumn=’X’ and NonClusteredIndexColumn=’Y’ and NoIndexColumn=’Z’
in this case it will use SEEK of NonClusteredIndex because column of that index has = oprator and KEY LOOKUP on Primary Clustered Index, because in select list is *, not specifed columns of NonClusteredIndex, or INCLUDE list of NonClustered Index.
when column value = then it will use SEEK of index of that column, when it is not = (>=, <=, ,or not specified it will use KEY LOOKUP or SCAN on Primary Clustered Index or SEEK of PrimaryClusteredIndex)
Futher, which index will be used depends what is in SELECT list.
SELECT NonClusteredIndexColumn1, NonClusteredIndexColumn2, NonClusteredIndexColumn3 FROM MYTABLE WHERE NonClusteredIndexColumn1=1
WILL use SEEK of NonClusteredIndex with this 3 Columns
Also it will depend is it ColumnX part of IncludeColumnList of NonClusteredIndex
SELECT NonClusteredIndexColumn2, NonClusteredIndexColumn3, ColumnX FROM MYTABLE WHERE NonClusteredIndexColumn1=1
WILL use SEEK of NonClusteredIndex with this Columns and ColumnX in ICLUDE list of NonClusteredIndex
Litle confusing if you have many indexes, Queries
So I’m thinking only to leftt SurrogatePrimaryKey and replace
Even more if there is NonClustered Index, if I add SyrogatePrimaryKolumn>=1 and SyrogatePrimaryKolumn=1 and SyrogatePrimaryKolumn<9999999999 there is no column with = operator
If you have just ClusteredIndex , there is no INCLUDE list, and it will use SCAN not SEEK of ClusteredIndex, if in WHERE list beside SyrogatePrimaryKolumn>=1 and SyrogatePrimaryKolumn<9999999999 there is no column with = operator
It is wrong conclusion with = operator, it will work with SEEK for any operator , but I make mistake with SyrogatePrimaryKolumn<999999999(9) it should be value in my case for int column 2147483647
Index choice would be dependent on data distribution also.
I don’t know why, but comments I wrote are different after Post Comment, that was the reason that I put several of them, many times. Please be kind to put last 5 in one comment and to delete other ones. There are still some mistakes, but than I will be make corrections
please put the first one of last 5
Hi Pinal,
It’s very useful
Thanks
Paval
Hi pinal,
i think index scan definition above is not always correct. As index/Table scan can touch only some rows and leave rest of the rows, Thanks for the query optimizer team, for example semi join applied on the nested loop join or final Row Goal applied etc.
Interesting. Do you have any sample code?
If a table has 3 columns and every column has an index associated with it. If I query the table with all the columns specified in the ‘Where’ clause. So how many index will SQL server use.
If Index Scan is Table scan then whats the difference between them .
Index Scan will scan the pages which are part of index. In Table scan, it has to read ALL data pages of the table.
Hi Pinal,
i have one question here, when i plan to set non-clustered indexes for my table.
suppose i have a table called test and has 4 columns like col1, col2, col3,col4. when my application is running, it may send queries to database as below.
1. select * from test where col1=123
2. select * from test where col1=123 and col2=’1
3. select * from test where col1=123 and col2=1 and col3=2
4. select * from test where col1=123 and col2=1 and col3=2 and col4=true
from above, what is best option to create non-clustered index for the above table. should i create separate create indexes for 4 columns or all at once.
what is best method for doing so?
Thanks,
Srinivas