Index Scan :
Index Scan scans each and every record in the index. `Table Scan` is where the table is
processed row by row from beginning to end. If the index is a clustered index then an
index scan is really a table 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.
Hence, a scan is an efficient strategy if the table is small.
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.
Before we go over the concept of scan and seek we need to understand what SQL Server does before applying any kind of index on query. When any query is ran SQL Server has to determine that if any particular index can be applied on that particular query or not.SQL Server uses search predicates to make decision right before applying indexes to any given query.
Predicate is an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE clauses and HAVING clauses, the join conditions of FROM clauses, and other constructs where a Boolean value is required.
Suppose,
TableX has five columns : Col1,Col2,Col3,Col4,Col5
Index1 on TableX contains two columns : Col2,Col3
Query1 on TableX retrieves two columns : Col1,Col3
Now when Query1 is ran on TableX it will use search predicates Col1,Col3 to figure out if it will use Index1 or not. As Col1,Col3 of Query1 are not same as Col2,Col3 or Index1 there are good chances that Query1 will not use Index1. This scenario will end up in table scan. If Query1 would have used Index1 it would have resulted in table seek.
Index Scan scans each and every record in the index. `Table Scan` is where the table is
processed row by row from beginning to end. If the index is a clustered index then an
index scan is really a table 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.
Hence, a scan is an efficient strategy if the table is small.
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.
Before we go over the concept of scan and seek we need to understand what SQL Server does before applying any kind of index on query. When any query is ran SQL Server has to determine that if any particular index can be applied on that particular query or not.SQL Server uses search predicates to make decision right before applying indexes to any given query.
Predicate is an expression that evaluates to TRUE, FALSE, or UNKNOWN. Predicates are used in the search condition of WHERE clauses and HAVING clauses, the join conditions of FROM clauses, and other constructs where a Boolean value is required.
Suppose,
TableX has five columns : Col1,Col2,Col3,Col4,Col5
Index1 on TableX contains two columns : Col2,Col3
Query1 on TableX retrieves two columns : Col1,Col3
Now when Query1 is ran on TableX it will use search predicates Col1,Col3 to figure out if it will use Index1 or not. As Col1,Col3 of Query1 are not same as Col2,Col3 or Index1 there are good chances that Query1 will not use Index1. This scenario will end up in table scan. If Query1 would have used Index1 it would have resulted in table seek.
No comments:
Post a Comment