How do statistics work for views vs base tables

Normal views are expanded and rely entirely on base table statistics, while indexed views are physically stored and maintain their own statistics. The optimizer always estimates cardinality based on where the data actually lives.

Statistics describe data distribution, not objects.

Each statistics object contains:

Ø  Histogram (up to 200 steps)

Ø  Density vector

Ø  Row count

Ø  Modification counter

Stored metadata:

Ø  sys.stats

Ø  sys.stats_columns

Ø  DBCC SHOW_STATISTICS

Statistics are tied to storage structures, not SELECT statements.

A normal view is stores no data and it Is just a stored SELECT definition also it Is expanded inline during optimization.

Indexed views ARE different Once we are creating a indexed view Internally:

Ø  View becomes a physical storage object

Ø  Rows are materialized

Ø  Index structures exist

Ø  Statistics are auto-created

So Indexed views behave exactly like tables for statistics.

For the demo we are creating a table and inserting records.

CREATE TABLE dbo.Sales_stats_demo (     SaleID INT IDENTITY PRIMARY KEY,     Amount INT NOT NULL );  INSERT INTO dbo.Sales_stats_demo (Amount) SELECT TOP (10000) ABS(CHECKSUM(NEWID())) % 1000 FROM sys.objects a, sys.objects b; GO

Now creating normal view

CREATE VIEW dbo.vw_Sales_stats_demo_Normal AS SELECT SaleID, Amount FROM dbo.Sales_stats_demo WHERE Amount > 900; GO

 View created successfully. See the data into this view

Now see the statistics on the system table.

SELECT * FROM sys.stats WHERE object_id = OBJECT_ID('dbo.vw_Sales_stats_demo_Normal');

To check need to run this.

Now let’s create indexed view and see

CREATE VIEW dbo.vw_Sales_stats_demo_Indexed WITH SCHEMABINDING AS SELECT     Amount,     COUNT_BIG(*) AS Cnt FROM dbo.Sales_stats_demo WHERE Amount > 900 GROUP BY Amount; GO   CREATE UNIQUE CLUSTERED INDEX IX_vSales_Indexed ON dbo.vw_Sales_stats_demo_Indexed(Amount); GO

Indexed view is created successfully.

See the data

Select * from dbo.vw_Sales_stats_demo_Indexed

 

Now see the status.

Statistics are created automatically.