Multi-column statistics

 dans Sites Microsoft par

In post SQL Server CE: Multiple single-column statistics, I discussed how SQL Server calculates Multiple single-column statistics. I’m going to talk about the ‘multiple-columns statistics’ today.

 

 

Here are examples , adventure 2019 OLTP  database is used in this example

 

——- Data manipulation——————————————-

alter database [AdventureWorks2019] set compatibility_level=150

go

use [AdventureWorks2019]

go

if exists(select 1 from sys.tables where name=’SalesOrderDetail’)

drop table SalesOrderDetail

go

select * into SalesOrderDetail from Sales.SalesOrderDetail—- import all the data into new table SalesOrderDetail.

Go

–Create two statistics explicitly. The first statistics has two columns

create statistics I_ProductID_UnitPrice on SalesOrderDetail(ProductID,UnitPrice) with fullscan

create statistics I_UnitPrice  on SalesOrderDetail(UnitPrice)