why cant i create a materialized view in 2017

Question

hi i'm attempting to run the following in 2017 and getting the error "unknown object type materialized...". in what version of sql did materialized views first become available? i'd also like to do this in a 2014 instance we have.

USE [mydb]  
GO  
  
SET ANSI_NULLS ON  
GO  
  
SET QUOTED_IDENTIFIER ON  
GO  
  
create materialized view [dbo].[vw_test] as  
SELECT * from mytable  
GO  

Answer 1

unknown object type materialized...

Because that's PL/SQL = Oracle or PostGreSQL syntax, not available in Microsoft SQL Server.

Answer 2

thx olafhelper, i'm torn between adding what amounts to a rank() on the base table and then building a filtered index (probably with one "included" column called city name) over the acct number and that rank() on the base table or going the route of a view (indexed) that only has rank() =1, account and the city name.

But from what i'm seeing indexed views cant be based on windowing or aggregate functions even in sub selects.

so i think i have to take the riskier/costlier path of adding that rank() (which may just become a current flag of Y or N) to the core table and probably building a filtered index on the core table's account where current flag is Y and "including" city name.

do you have any thoughts before i take the wrong path?

Answer 3

You can create a trigger to update a field in the table with the rank value you want, or "current".

However, that is a very poor database design. If you use "current' 99% of the time, you are better off having a "current" table and a "history" table.

Answer 4

thx Tom, in non oltp environments i'm not a big fan of triggers. even in oltp environments i'm not a big fan.

i don't have a problem running an update script. The ETL is incremental.

about 3.5 million records of the 50 million + records are current (rank 1 or current flag Y). The design of the table is bizarre in that the "planned redundancy" is over the top. The current flag is an afterthought. The table is used in a large number of places so i talk about risk. But more importantly, i look for a low impact way of accomplishing the same thing without changing the core table. To top things off , temporal tables arent available in this version (2014) of sql. Some time in the next year or so we will migrate to i believe 2019 though.

Answer 5

Hi @db042190
The SELECT list in the materialized view definition needs to meet at least one of these two criteria:

1. The SELECT list contains an aggregate function.
2. GROUP BY is used in the Materialized view definition and all columns in GROUP BY are included in the SELECT list. Up to 32 columns can be used in the GROUP BY clause.

Aggregate functions are required in the SELECT list of the materialized view definition. Supported aggregations include MAX, MIN, AVG, COUNT, COUNT_BIG, SUM, VAR, STDEV.

For example:

CREATE MATERIALIZED VIEW mv_test2    
WITH (distribution = hash(i_category_id), FOR_APPEND)    
AS  
SELECT MAX(i.i_rec_start_date) as max_i_rec_start_date, MIN(i.i_rec_end_date) as min_i_rec_end_date, i.i_item_sk, i.i_item_id, i.i_category_id  
FROM syntheticworkload.item i    
GROUP BY i.i_item_sk, i.i_item_id, i.i_category_id  

Refer to this document: CREATE MATERIALIZED VIEW AS SELECT (Transact-SQL)

Best regards,
LiHong