About creating a single column index on SQL Server for various queries

Question

Suppose such a table

[KEY] [int] NOT NULL, [INT1] [int] NULL, [INT2] [int] NULL, [INT3] [int] NULL, [STR1] [varchar](20) NULL, [STR2] [varchar](20) NULL, [STR3] [varchar](20) NULL, 

The query is very bent, but always like this format: SELECT KEY FROM [TABLE] WHERE...

The search condition is several times in the same column and in most cases for several columns for type [int] , quesy as BETWEEN or >= or <= , for varchar always requested as = or IN [] . All conditions are combined with AND

Since the query is not always fixed on one column (s), so I wonder if I will create an INDEX for each individual column, improve performance or even lose resources.

Answer 1

Do not just create an index for each individual column - it is a complete waste of time and resources!

Basically, my approach is always:

• defining a good primary and clustered key in any "normal" table (with the exception, for example, of intermediate tables, etc.) is already a big step

• place non-clustered indexes on any columns of a foreign key - this really helps a lot, especially with

  JOIN <

What is it!

Then:

• Watch your system - watch when everything is slow.
• System performance measurement
• capture server side trace to get typical workload
• analyze this workload and see what additional indexes might be useful.
• make tweaks - one at a time
• measure over and over again whether you have improved system performance (or not)

You need a full, representative workload to find out which queries are really common and used a lot, and see which indexes can be useful for these frequent queries. Otherwise, you can provide index help for all invalid queries, and you can slow down ...

You would be surprised how rarely non-clustered indexes really help!

Not over-index - it's just as bad - if not worse - than there are no indexes at all! It could be worse, because every index you have must also be kept for its entire life ... and there is no free lunch - even here ...

See Kimberly Tripp for an excellent blog post on Indexes: just because you can not mean you should! on this subject is very useful, a lot of understanding. Or, in principle, I just read everything that Kim wrote on index blogs - she is the Queen of Indexing and everything she posted on her blog is usually extremely useful and useful!

In addition, SQL Server 2005 and newer offers DMV (Dynamic Management Views) that let you know which indexes are not used (they can be deleted) or which are missing, according to the SQL Server query optimizer. For more information, see SQL Server - Finding Missing and Unused Indexes . But keep in mind: these are dynamic views - they reset every time you start the system and may not be completely accurate - do not just do everything they tell you - take everything with salt and carefully study what you are doing - write it down so that you could cancel it if the situation worsened, but not better!

Answer 2

Creating an index for each column can affect performance, as described in General Guidelines for Pointer Design :

A large number of indexes in a table affects the performance of the INSERT, UPDATE, DELETE, and MERGE statements, because all indexes must be adjusted accordingly as the data in the table changes.

Also, if you want to always extract the KEY column in your queries, consider adding it as an included column to the index so that it can only be accessed by accessing the index, avoiding access to the table. But keep in mind when creating an index with included columns, available with SQL Server 2005 and later.

You can examine the most commonly used filter combinations and add only a few multi-column indexes, keeping in mind:

Consider the order of the columns if the index contains several columns. A column that is used in the WHERE clause with equal (=), greater than (>), less (<) or BETWEEN search conditions, or participates in a join, must be placed first. Additional columns should be ordered based on their level of distinctness, that is, from the most distinct to the least different.

Answer 3

Entering indexes inside will partially help queries if it leads directly to the correct result, but it can also bring great benefits if it improves the locality of links and reduces the amount of data read.

In the question that is asked, the answer is "it depends." It depends on your requests. If you specify one main column that always appears in the search, for example. INT1 , create an index:

  unique (INT1, INT2, INT3, REF) 

Then any query that references INT1 and any combination of other fields will be fast.

In addition, any query that refers to INT2, but not to INt1, will also be useful, because the entire table does not need to be read - only the index. Despite the fact that INT2 is not at the head of the index, the query still wins: DB will skip INT1 and just look at INT2, but it can get a view of the values ​​of the INT2 table without having to read the whole table.

So, you need to better understand the requests that will be executed. If one ALLWAYS column appears, put it at the beginning of the index. If another OFTEN column appears, it must be the number 2.

If there are two columns that appear frequently, you can do this:

 unique (INT1, INT2, INT3, REF), unique (INT2, INT1, INT3, REF) 

Then we hope that if INT1 is not specified, but INT2 is specified, the second index will be used.

Do not use too many indexes, although they can take up a lot of disk space.

Bottom line: Check queries with and without indexes. You need to collect 10-20 minimum sample requests and check their I / O time and hours. This is the only way to get a true answer.