When should you create an index and when not?
Use the following guidelines for determining when to create an index:
- Create an index if you frequently want to retrieve less than about 15% of the rows in a large table.
- Index columns that are used for joins to improve join performance.
What are the disadvantages in creating an index?
The disadvantages of indexes are as follows:
- They decrease performance on inserts, updates, and deletes.
- They take up space (this increases with the number of fields used and the length of the fields).
- Some databases will monocase values in fields that are indexed.
What’s the difference between create index and create statistics?
What are the differences between CREATE INDEX and CREATE STATISTICS and when should I use each? Indexes store actual data (data pages or index pages depending on the type of index we are talking about), and Statistics store data distribution.
Do you need to gather global statistics when creating an index?
As you can see 1) the number of distinct keys at the global level is wrong; it should be 20! 2) the GLOBAL_STATS column at the index level is set to NO. As a result, when you create a partitioned index, you should manually gather the global index statistics straight after.
When to use column level statistics instead of index?
When particular queries do not run very often, you can select to create column-level statistics rather than an index. Column-level statistics help Query Optimizer find better execution plans, even though those execution plans are suboptimal due to the index scans involved.
When to use index selectivity instead of index?
Remember that index selectivity is a metric which is inversely proportional to density i.e the more unique values a column has, the higher its selectivity. When particular queries do not run very often, you can select to create column-level statistics rather than an index.