Home / Questions / 34214
Accepted
Juan Manuel Palacios
Asked: 2020-11-17 09:54:32 +0800 CST 2020-11-17 09:54:32 +0800 CST

What is the best practice to create an Index in SQL Server?

  • 772

I am having problems with the response time of the queries that I am executing with SQL Server.

Researching I found that a good practice is to create indexes associated with the fields within a table or a view.

What is the best way to create these indexes? Is it convenient to make an index for each field or to make an index for several fields?

sql

3 Answers

  1. Best Answer

    The number of indexes you need to create depends a lot on your application and the queries you make.

    Adding indexes will make queries (SELECT) execute faster, but updates to the database (INSERT, UPDATE or DELETE) will be slower.

    You definitely shouldn't create an index for every field. What you should do is understand how indexes work to know which ones you should create.

    The best thing would be to identify the queries that take the longest, and for each of them decide if you have to create an index or not, and what index to create.

    Some rules that should be pretty generic about what index to create (after the query is identified):

    • if the query traverses the entire table and has an ORDER, the fields that are in the ORDER could be in an index.
    • if a table has many columns, and your query only operates on a few, putting all the columns in the index can make the SELECT not have to go to the table, it goes directly to the index
    • in a table with many records, if the query has a WHERE that filters many of these records, then creating an index on the WHERE fields may be quite reasonable.

    In any case, SQL Server has a tool, SQL Server Profiler, that can help you decide which indexes can be used in your application.

    • 4

  2. To which @Marcos said very well, I wanted to explain the subject of indices in a very simple way.

    SQL Server by default creates indexes on tables using Primary Keys.

    Many times it is what is needed, but it is not always so.

    Think of a paper phone book: It is arranged by City, Last Name and First Name . We can say that its index is those fields. Finding a number is easy this way, because that's what you're usually looking for. If I want all the numbers of the city "A", it is easy to get them.

    Now , what would happen if I wanted all the numbers of the people whose name is “Juan Manuel”? Now the index would be of no use to me. I would have to go cover to cover to get them all out.

    The same thing happens with databases. It is important that the index is what is most searched for.

    And a detail, in SQL Server you can have two types of indexes.

    • Cluster (It is only one, and it should be the field/fields through which the table is accessed the most)
    • Logical (can be several)

    I encourage you to investigate them and see what is best for your application.

    In a nutshell, how wrong could I say it would be to not have indexes or create indexes that are not used for anything. As @Marcos explained to you, that overloads the database with things that are not used.

    What we usually do in my company is to have a logical index by the primary key (for joins). The index is usually clustered by a date field and a primary key.

    • 1

  3. 1.- In addition to what you have been told, I recommend you use the Database Tuning Advisor (DETA) with the query that gives you problems. DETA will offer you suggestions for improving the database structure to make the query more efficient (sometimes it suggests the creation of indexes, statistics, etc.).

    2.- Another thing you can do is to analyze the execution plan and detect something interesting like sequential scans (table scan) instead of searches with indexes (index seek) for example.

    3.- If you want some very general advice, then maybe seeing these tips on using indexes in SQL Server will be useful to you.

    • 0