What are indexes in SQL Server for?

Question

For what the index serves, I know it improves performance, but what the database does behind that improves this performance. When is it recommended to use? And where should I use an index?

Answer 1

TL;DR: Imagine that the database is a postman who has just arrived in a city unknown to him, and that the data is the recipients of the letters he has to deliver. A simpler index would be like the postal code system (in Brazil, CEP) while more robust indexes would be like full addresses. Unlike the real-life postman, our SQL Server postman can find your target without the address - he will only need more time, because in that case he will check all residences one by one.

More elaborate answer:

Each record in a database system has an address in the storage (hard drive or SSD that is).

When you do a search in the bank that has search conditions (WHERE), by default SQL Server will lift all records to memory. Then he analyzes one by one, checking against the conditions of the research, to decide whether the record is part of the result or not.

As a table can be several times larger than the available RAM, this can cause a lot of slowness.

Here comes the indexing technique. Basically, you choose one or more columns to be indexes. Hence, two things can/will happen:

• The table will be reordered by those columns. In case of columns of textual type, the sort is alphabetical;
• There will be a copy of these columns in a special storage space. This copy does not need to be complete - it may only have some statistically relevant records. Depending on the intensity of use, this copy may be cached in RAM. Each index record also contains a pointer, which tells the address of the original record on the disk or SSD.

Imagine that you have a table with a million records, with a primary key that is integer and auto-incremental. The index could be in RAM memory and contain the ID and address of one every 100 records. Now let’s make a query with something like WHERE ID = X, for any X.

If you do the query without indexed ID, you will bring up to one million records to memory and check from one by one which has the desired ID.

If you query with an indexed ID, the bank will read the index first. Two things can happen:

• The bank finds the X ID directly in the index. With this it has the address of the record on the disk/SSD, and goes straight to it without any loss of time.

• The bank can’t find the ID in the index. In this case, it moves to the nearest storage ID and navigates back or forth (as appropriate) until it finds your record. This part of the search looks like the normal search that the bank would do without the index, but as the bank already knows the approximate address the search gets much faster.

How much speed you gain from this depends, varies from case to case. For a school design database with low-record tables, it might not make much difference. But for large databases with huge masses of data (telephone directories, for example), the difference is absurd. I can’t say a precise number, but in the largest databases I worked with, the indexes would reduce the consultation time from hours to milliseconds.

Another important issue: has a question in Stack Overflow about how much faster RAM is than a hard disk. That is relevant because the most accessed indexes are kept in RAM memory. The question is from 2009, but since then the magnitude of the speed disparities hasn’t changed much. For random research, RAM is somewhere between a hundred thousand to a million times faster than a hard drive. That is the main advantage of the index.

Answer 2

Indexes (Index) are disk structures associated with tables or views, which speed up the recovery process of the rows of a table or a view. These indexes may contain keys from one or more table columns or even from a view. These keys are therefore stored in the SQL Server structure called B-Tree, which enables SQL Server to find the line(s) associated with the key values quickly and effectively.

Read more in: SQL Server Cluster: Clustered and Non-marketed Indexes http://www.devmedia.com.br/indices-clusterizados-e-nao-clusterizados-no-sql-server/30288#ixzz39XFTkuzg

Answer 3

They are elements that we use to index our data, that is, define a "position" it within a context. Much like a vector or array. With indexed data, searches are faster since you know their position. The main problem of the indexes is the insertion, because the indexed data need to be ordered so that there is a better performance during the queries, and to remain ordered need to be inserted in the correct position!

Look a little bit about data structures, can help!