CODEWITHSUNDEEP
sql-servert-sqlviewmaterialized-views
Ammar Ameerdeen
Ammar Ameerdeen

Reputation: 969

SQL Server Materialized View (Indexed View) error

I'm trying to create a Materialized View using Microsoft SQL Server Management Studio 14.0.17285.0. Following is the script I ended up with to achieve the same.

My plan is to create a view and create an index on top of it.

use data_warehouse;

--Set the options to support indexed views.  
    SET NUMERIC_ROUNDABORT OFF;  
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,  
        QUOTED_IDENTIFIER, ANSI_NULLS ON;
    GO

CREATE VIEW products_yearly_v2
WITH SCHEMABINDING 
AS
    SELECT  
        p.product_id AS product_id, 
        p.product_description AS product_name,
        d.order_year AS order_year,  
        SUM(s.order_total) AS sal_by_dept
    FROM   
        [data_warehouse].[dbo].orders_fact AS s
    INNER JOIN 
        [data_warehouse].[dbo].time_dimension AS d ON s.time_id = d.order_date
    INNER JOIN 
        [data_warehouse].[dbo].product_dimension AS p ON s.product_id = p.product_id
    GROUP BY 
        d.order_year, p.product_id, p.product_description;

CREATE UNIQUE CLUSTERED INDEX IDX_V1   
    ON products_yearly_v1 (order_year, product_id);

I'm getting this error:

Msg 156, Level 15, State 1, Procedure products_yearly_v2, Line 12 [Batch Start Line 7]
Incorrect syntax near the keyword 'CREATE'

Update Added COUNT_BIG(*) in order to create the index.

SELECT  COUNT_BIG(*) as count_big, p.product_id as product_id, d.order_year as order_year,  sum(s.order_total) AS sal_by_dept ....

Removed product_description from GROUP BY

GROUP BY d.order_year,p.product_id;

Readded,

USE data_warehouse

Upvotes: 1

Views: 5341

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175686

You need to use 2-part names:

SCHEMABINDING

Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

USE data_warehouse
GO

CREATE VIEW dbo.products_yearly_v2
WITH SCHEMABINDING 
AS 
SELECT  
    p.product_id AS product_id, 
    p.product_description AS product_name,
    d.order_year AS order_year,  
    SUM(s.order_total) AS sal_by_dept
FROM [dbo].orders_fact AS s
JOIN [dbo].time_dimension AS d ON s.time_id = d.order_date
JOIN [dbo].product_dimension AS p ON s.product_id = p.product_id
GROUP BY d.order_year, p.product_id, p.product_description;
GO

-- index on products_yearly_v2 not products_yearly_v1
CREATE UNIQUE CLUSTERED INDEX IDX_V2
   ON dbo.products_yearly_v2 (order_year, product_id);

Upvotes: 1

Related Questions