How to know when to use indexes and what type?

Question

I searched a bit and did not see any such questions, so here it goes.

How do you know when to specify an index in a table? How do you determine which columns to include in the index? When should I use a clustered index?

Can an index slow down the performance of select statements? How many indexes are too many and how many tables do you need to benefit from the index?

EDIT:

What about column data types? Is it good to have an index on varchar or datetime ?

Answer 1

Well, the first question is simple:

When should a clustered index be used?

Always. Period. Except in very rare, rare cases. A clustered index makes the table faster for each operation. YES! It does. See Kim Tripp excellent. Cluster index discussion continues for more information. She also mentions her basic criteria for a cluster index:

• narrow
• static (never changes)
• unique
• if possible: ever increasing

INT IDENTITY does this perfectly - the GUID does not. See the GUID as the primary key for detailed reference information.

Why narrow? . Since the cluster key is added to each index page of each non-clustered index in the same table (to be able to really look for a data row if necessary). You do not want to have VARCHAR (200) in the clustering key ....

Why unique? See above - the clustering key is an element and mechanism that SQL Server uses to uniquely find a row of data. It must be unique. If you choose a custom clustering key, SQL Server itself will add a 4-byte identifier to your keys. Be careful!

Next: non-clustered indexes. Basically, there is one rule: any foreign key in a child table that refers to another table must be indexed, this will speed up JOIN and other operations.

In addition, any queries containing WHERE clauses are a good candidate - choose those that run a lot. Place indexes on the columns that appear in WHERE clauses in ORDER BY statements.

Next: measure your system, check DMV (Dynamic Management Views) for tips on unused or missing indexes, and set up your system again and again. This is an ongoing process, you will never become! See here for information on these two DMVs (missing and unused indexes).

Another warning: with a load of indexes, you can make any SELECT query really very fast. But at the same time, INSERT, UPDATE, and DELETE may suffer, which should update all indexes involved. If you only CHOOSE - go! Otherwise, it is a wonderful and delicate balance. You can always tune one query beyond belief, but the rest of your system may suffer in doing so. Do not reindex your database! Put some good indexes in place, check and observe how the system works, and then maybe add one or two more, and again: see how this affects the overall performance of the system.

Answer 2

The thumb rule is the primary key (implied and used by default for clustering), and each column of the foreign key

There is more, but you can do worse than using a SQL Server missing DMVs index

An index can slow down SELECT if the optimizer makes a poor choice and there are probably too many. Slows down the record too much, but it is also possible to overlap indices

Answer 3

Answering my questions, I can say that every table, no matter how small, will always benefit from at least one index, because there must be at least one way that you are interested in finding data; otherwise why keep it?

A general rule for adding indexes would be if you need to find data in a table using a specific field or set of fields. This leads to the fact that the number of indexes is too large, as a rule, the more indexes you have slower inserts and updates, it is that they also have to change the indexes, but it all depends on how you use your data. If you need quick inserts, do not use too much. When presenting read-only data, you may have several of them to speed up the execution of all your queries.

Unfortunately, there is no rule to indicate the number or type of indexes used, although the query optimizer of the database you choose can give hints based on the queries being performed.

As for clustered indexes, this is an Ace map that you can use only once, so choose carefully. It is worth calculating the selectivity of the field that you are going to overlay it, since it can be wasted to put it in something like a logical field (a far-fetched example), since the selectivity of the data is very low.

Answer 4

This is indeed a very tricky question, although indexing any column that you will filter the results on would be a good starting point. i.e. If you often group products by sales price, index the sale_price column of the product table to improve crawl time for this query, etc.

Answer 5

If you are querying based on the value in a column, you probably want to index that column.

i.e.

 SELECT a,b,c FROM MyTable WHERE x = 1 

You need an index on X.

Typically, I add indexes for columns that are often requested, and add compound indexes when I query more than one column.

Indexes will not hurt SELECT performance, but they can slow down INSERTS (or UPDATES) if you have too many indexes for each table.

Typically, start by adding indexes when you find that WHERE a = 123 (in this case, the index for "a").

Answer 6

You should use the index for the columns that you use to select and organize — for example, the WHERE and ORDER BY clauses.

Indexes can slow down select statements if there are many, and you use WHERE and ORDER BY for columns that have not been indexed.

As for the size of the table - a few thousand rows and up will begin to show the real benefits of using the index.

Having said that, there are automated tools for this, and on the SQL server there is a Database Advisor to help you with this.