SQL Server first determines existence of a index. Then query optimizer—the component responsible for generating the optimal execution plan for that query— then it determines is scanning a table or using the index is more efficient for accessing data.
SQL Server accesses data in one of two ways:
- By scanning all the data pages in a table, which is called a table scan.How it Works:-
- Starts at the beginning of the table.
- Scans from page to page through all the rows in the table.
- Extracts the rows that meet the criteria of the query.
- By using indexes. When SQL Server uses an index, it:How it works:-
- Traverses the index tree structure to find rows that the query requests.
- Extracts only the needed rows that meet the criteria of the query.
Full-Text Search:- is new to SQL Server 2008. In SQL Server 2008, full-text search is fully integrated into the database engine. By using the full-text engine directly into the SQL core, SQL Server 2008 resolves the challenges that were presented by previous releases, reduces the cost of managing and deploying SQL Server, while improves the performance of searching unstructured data.