CODEWITHSUNDEEP
sqlsql-servert-sqlindexed-view
Deepak
Deepak

Reputation: 6802

Error adding an index to a view

I have created a view using the following code

CREATE VIEW dbo.two_weeks_performance WITH SCHEMABINDING
AS 
  SELECT dbo.day_dim.date_time AS Date, 
         dbo.order_dim.quantity AS Target_Acheived
    FROM dbo.day_dim 
    JOIN dbo.order_fact ON dbo.day_dim.day_id = dbo.order_fact.day_id 
    JOIN dbo.branch_dim ON dbo.order_fact.branch_id = dbo.branch_dim.branch_id 
    JOIN dbo.order_dim ON dbo.order_fact.order_id = dbo.order_dim.order_id   
GROUP BY dbo.order_dim.quantity, dbo.day_dim.date_time`

Now when I use: 

CREATE UNIQUE CLUSTERED INDEX two_weeks_performance_I ON two_weeks_performance (Date)

I am getting an error:

Cannot create index because its select list does not use the correct usage of COUNT_BIG(). Consider adding COUNT_BIG(*) to the select.

Please help me solve this issue.

Upvotes: 3

Views: 1706

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

The error tells you exactly what you have to do - add COUNT_BIG(*) to your select list.

From Creating Indexed Views:

If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, ROLLUP, CUBE, or GROUPING SETS.

CREATE VIEW dbo.two_weeks_performance WITH SCHEMABINDING
AS 
  SELECT dbo.day_dim.date_time AS Date, 
         dbo.order_dim.quantity AS Target_Acheived,
         COUNT_BIG(*) as Cnt 
    FROM dbo.day_dim 
    JOIN dbo.order_fact ON dbo.day_dim.day_id = dbo.order_fact.day_id 
    JOIN dbo.branch_dim ON dbo.order_fact.branch_id = dbo.branch_dim.branch_id 
    JOIN dbo.order_dim ON dbo.order_fact.order_id = dbo.order_dim.order_id   
GROUP BY dbo.order_dim.quantity, dbo.day_dim.date_time
GO
CREATE UNIQUE CLUSTERED INDEX two_weeks_performance_I ON two_weeks_performance (Date)

Upvotes: 6

Related Questions