What is Index Scan ,Index Seek and Table Scan?||SQLSERVER #index #sqlserver

Показать описание

An index scan or table scan is when SQL Server has to scan the data or index pages to find the appropriate records.

1.Index Seek
When your search criterion matches an index well enough that the index can navigate directly
to a particular point in your data,
that's called an index seek. It is the fastest way to retrieve data in a database.
The index seeks are also a great sign that your indexes are being properly used.

2. Index Scan
If your table has a clustered index and you are firing a query which needs
all or most of the rows
i.e. query without WHERE or HAVING clause,
then it uses an index scan. It works similar to the table scan,
during the query optimization process, the query optimizer takes a look at
the available index and chooses the best one, based on information provided in your joins and where clause, along with the statistical information database keeps.

3.Table Scan
A table scan is a pretty straightforward process.
When your query engine performs a table scan it starts
from the physical beginning of the table and goes through every row in the table.
If a row matches the criterion then it includes that into the result set.
Please watch full video for detail.

#interviewquestions #sqlserver #performanceimprovement #performance