¿Cuál es la diferencia entre buscar y escanear en SQL Server?

What is the difference between seek and scan in SQL Server?

Explanation. An index scan or table scan is when SQL Server has to scan the data or index pages to find the appropriate records. A scan is the opposite of a seek, where a seek uses the index to pinpoint the records that are needed to satisfy the query. Here is a simple query that we can run.

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.

If indexes are not properly created, SQL Server has to go through more records in order to retrieve the data requested by a query. Therefore, it uses more hardware resources (processor, memory, disk, and network) and obtaining the data lasts longer.

Checking the execution statistics of the query, you will see the large number of reads, the long duration and the high CPU cost, as shown below: From the upper part of the Execution Plan, you will see, in green, a CREATE INDEX T-SQL statement for a recommended index that will enhance the performance of the query, as shown below:

The three signs derived from the previous plan direct us to the main cause of the query’s bad performance, which is the absence of an index, in the EMP_Salary table, that can speed up the data retrieval process from that table.

I have a relatively large table (currently 2 million records) and would like to know if it’s possible to improve performance for ad-hoc queries. The word ad-hoc being key here. Adding indexs is not an option (there are already indexs on the columns which are queried most commonly).