Altogether, these changes across the latest releases can cause a variation in SQL Server behavior that you weren’t expecting.

Note: This post is most applicable to SQL Server 2012 and higher, but some detail for prior releases is included for reference (and fun).

Here we also specify Statistics_Incremental option during index creation.

updating statistics sql server 2016-83updating statistics sql server 2016-38updating statistics sql server 2016-59

Let me show you this practically: --Create a database for Demo at default files location Create database Stats Demo2014 go Use Stats Demo2014 go --Create Partition Function create partition function xpfstatsdemo (int) as range right for values (10000,20000) go --Create Partition Scheme create partition scheme xpsstatsdemo as partition xpfstatsdemo ALL to ([Primary]) go --Create table by using Partition Scheme on id column create table xtstatsdemo ( id int not null, balance int not null, name varchar(25) ) on xpsstatsdemo (id) go --Create a clustered index on id column [This will cover complete table] --Here I have also specify statistics_Incremental option create clustered index CI_xtstatsdemo on xtstatsdemo (id) with (statistics_incremental=ON); go --You can check here about how many partitions belongs to the table select * from sys.partitions where object_id=OBJECT_ID('xtstatsdemo') go -- Now I am inserting 30000 rows in the table.

That means each partition will contain 10000 rows here -- Here id and balance both are having same values for demo purpose set nocount on declare @counter int =1 declare @balance int =1 while (@counter Now our environment has been setup.

Here I am going to show you a scenario with the help of which you can easily understand the importance of this new feature.

Environment: I have a table xtstatsdemo with columns id, name and balance. First partition is having rows with key range 1-10000; Second partition is having rows with key range 10001-20000; Third partition is having rows with key range 20001-30000. Here Statistics also created automatically due to index creation.

Now let me show you the status of statistics auto created by index. Prince started his career working on SQL Server since Yukon. Prince likes to explore technical things for Database World and Writing Blogs.

Prince is having almost 7 years of experience and worked in ERP Domain, Wealth Management Domain. In the starting of his career he was working on SQL Server, Internet Information Server and Visual Source Safe.

If a statistics object is defined on multiple columns, the object also stores details about the correlation of values across the columns.

These correlation statistics, referred to as densities, are based on the number of distinct rows of column values.

After all, statistics still get created the same way, they still tell you about the distribution of data, they’re still used by the Query Optimizer… The basic function of statistics remains the same – but how they’re used by the Query Optimizer does change depending on the Cardinality Estimator you’re using.