CODEWITHSUNDEEP
sql-serverdatabasestatistics
Kyle
Kyle

Reputation: 547

Does column order matter when creating statistics for Microsoft SQL Server?

The Database Engine Tuning Advisor has recommended the creation of some statistics for several of our queries. Turns out that some of these are the same just the column order is different in the CREATE STATISTICS command. For example:

CREATE STATISTICS [StatName1] ON [dbo].[table1]([column2], [column1])

CREATE STATISTICS [StatName2] ON [dbo].[table1]([column1], [column2])

are these the same or are they treated differently?

Along the same lines can I combine CREATE STATISTICS command for a given table? If the Advisor recommended 3 different stats on the same column for 3 different queries can I do a single create command for all 3 columns e.g.

CREATE STATISTICS [StatName1] ON [dbo].[table1]([column1], [column3])

CREATE STATISTICS [StatName2] ON [dbo].[table1]([column1], [column2])

into 

CREATE STATISTICS [StatName1] ON [dbo].[table1]([column1], [column2], [column3])

Thanks

Upvotes: 3

Views: 709

Answers (2)

Humayun Khan
Humayun Khan

Reputation: 63

I am not an expert but I am looking to implement something similar.

I am not sure about which version of MS SQL server is being referred in the selected answer; according to the latest documentation, things look different:

As pointed out by @Davos The order of the columns in the statistics is "very" relevant according to Microsoft documentation. Here is the link to the documentation: https://learn.microsoft.com/en-us/sql/relational-databases/statistics/create-statistics?f1url=%3FappId%3DDev15IDEF1%26l%3DEN-US%26k%3Dk(sql13.swb.statistics.propertis.f1)%26rd%3Dtrue&view=sql-server-ver15

Here is an extract from the documentation page:

Move Up: Move the selected column to an earlier location in the statistics grid. The location in the grid can substantially impact the usefulness of the statistics.

Upvotes: 0

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

multiple stats on the same column are useless. Only one is needed. order is irrelevant.

Upvotes: 4

Related Questions