Geek Answers Handbook

Search and Scan in SQL Server

After googling, I found out that index search is better than crawling.

How to write a query that leads to a search instead of a scan. I am trying to find this on google, but at the moment no luck.

Any simple example with explanation will be appreciated.

thanks

+4
source share
3 answers
  • Search by primary key column
  • Search by columns (columns) with index (names) on them

An index is a data structure that improves the speed of data retrieval operations in a database table. Most dbs automatically create an index when the primary key is defined for the table. SQL Server creates the index for the primary key (composite or otherwise) as a “clustered index”, but it does not have to be the primary key — it can be other columns.

Note:

  • LIKE '%' + criteria + '%' will use no index; LIKE criteria + '%' will be

Related reading:

+6
source

Rexem Feedback Extension:

The idea of ​​a clustered index for pkeys is not arbitrary. This is just the default to group pkey. And clustered means that the values ​​will be physically placed next to each other on the Sql Server 8k page, thus assuming that if you select a single value using pkey, you will probably be interested in its neighbors. I do not think it is a good idea to do this for pkeys, as they are usually unique but arbitrary identifiers. It is better to cluster more useful data. One clustered index for each table.

In a nutshell: if you can filter your query in a clustered index column (which makes sense), all the better.

+3
source

Index search is when SQL Server can use binary search to quickly find a string. The rows in the index are sorted in a specific order, and your query must provide enough information in the WHERE clause so that SQL Server can use the sorted index.

Index scanning is when SQL Server cannot use the index sort order, but it can still use the index itself. This makes sense if the table rows are very large, but the index is relatively small. SQL Server will only have to read the smaller index from disk.

Take the phonebook table as a simple example:

id int identity primary key lastname varchar(50) phonenumber varchar(15)

Say there is an index (lastname). Then this query will search index :

 select * from phonebook where lastname = 'leno'

This query will scan the index :

 select * from phonebook where lastname like '%no'

The analogy with a real phone book is that you cannot search for people whose name ends with "no." You need to view the entire phone book.

+1
source

More articles:


All Articles