0

In SQL Server, when to create statistics only on a table column instead of index and when create to index (which creates statistics as well automatically)? I mean both improve query performance then how can one decide which is suitable?

2
  • 1
    Please take a look at earlier a question.
    – vonPryz
    Jan 30, 2019 at 10:01
  • Yes thanks, that is relevant but no suitable/satisfactory answer there which clarify when only statistics is enough. The question itself is very informative there. Jan 30, 2019 at 14:35

1 Answer 1

0

Let us try to understand what is index and statistics first:
An index is an on-disk structure associated with a table or view that speeds retrieval of rows from the table or view. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.
Statistics are exactly what the name implies. They are statistical information about the distribution and/or correlation of data in the first specified column of indexes or tables in the form of a histogram. Histograms measure the frequency of which a value occurs within a data set.

When we should use index:
Profile your queries and see if the index improves your queries. The difficulty in answering this is that it is nearly impossible to generalize the behavior of the query optimizer (when you have a query that you think performance can be inhanced they index can be usful).

Statistics when you want to check query plan and performance plan.

for more information plesae refer to:
https://learn.microsoft.com/en-us/sql/relational-databases/statistics/statistics?view=sql-server-2017
https://stackoverflow.com/questions/250289/when-to-create-a-new-sql-server-index
https://www.virtual-dba.com/sql-server-statistics/
https://learn.microsoft.com/en-us/sql/relational-databases/indexes/clustered-and-nonclustered-indexes-described?view=sql-server-2017
SQL Server Index vs Statistic
When is it better to create STATISTICS instead of creating an Index?

1

Not the answer you're looking for? Browse other questions tagged or ask your own question.