Views

In this section, we will check the transformation for the create view.

Source

CREATE VIEW VIEWNAME
AS
SELECT AValue from ATable;

Expected

CREATE VIEW PUBLIC.VIEWNAME
AS
SELECT AValue from PUBLIC.ATable;

Not much of a difference between the examples right? As you can see, the name of the create view is being transformed.

Now, let's see a few create view with more specific parts that come from SqlServer.

CREATE OR ALTER VIEW

Source

CREATE OR ALTER VIEW VIEWNAME
AS
SELECT AValue from ATable;

Expected

CREATE OR REPLACE VIEW PUBLIC.VIEWNAME
AS
SELECT AValue from PUBLIC.ATable;

The CREATE OR ALTER definition used in SqlServer is transformed to CREATE OR REPLACE in Snowflake.

CREATE VIEW WITH

In this type of View, after the name of the View, the following clauses can come

• WITH ENCRYPTION

• WITH SCHEMABINDING

• WITH VIEW_METADATA

In the transformation, we are going to comment out these clauses, and add a warning.

Source

CREATE OR ALTER VIEW VIEWNAME
WITH ENCRYPTION  
AS
SELECT AValue from ATable;

Expected

CREATE OR REPLACE VIEW PUBLIC.VIEWNAME
-- ** MSC-WARNING - MSCEWI1042 - Commented VIEW WITH CLAUSE - THIS IS NON-RELEVANT **
--WITH ENCRYPTION
AS
SELECT AValue from PUBLIC.ATable;

CREATE VIEW AS SELECT WITH CHECK OPTION

In this type of View, the clause WITH CHECK OPTION comes after the end of the Select statement used in the Create View.

Like the case from above, we are going to comment this clause.

Souce

CREATE OR ALTER VIEW VIEWNAME
AS
SELECT AValue from ATable
WITH CHECK OPTION;

Expected

CREATE OR REPLACE VIEW PUBLIC.VIEWNAME
AS
SELECT AValue from PUBLIC.ATable
-- ** MSC-WARNING - MSCEWI1042 - Commented WITH CHECK OPTION - THIS IS NON-RELEVANT **
--WITH CHECK OPTION
                 ;

CREATE VIEW AS COMMON TABLE EXPRESSION

Common Table Expressions must be used to retrieve the data:

Source

CREATE VIEW EMPLOYEEIDVIEW
AS
WITH CTE AS ( SELECT NationalIDNumber from [HumanResources].[Employee] 
UNION ALL 
SELECT BusinessEntityID FROM [HumanResources].[EmployeeDepartmentHistory] )
SELECT * FROM MyCTE;

Expected

CREATE OR REPLACE VIEW AdventureWorks2019.HumanResources.EmployeeVew
AS
/*** MSC-INFORMATION - MSCINF0017 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD ***/
WITH MyCTE AS ( SELECT
NationalIDNumber
from
AdventureWorks2019.HumanResources.Employee
)
SELECT
*
FROM
PUBLIC.MyCTE;

UNSUPPORTED SCENARIOS

Common table expressions with Update, Insert or Delete statements will be commented out because they are not supported in Snowflake and SQLServer.

In the case where an invalid CTE is added to the view, this will be completely commented out.

-- ** MSC-ERROR - MSCEWI1021 - COMMON TABLE EXPRESSION IN VIEW NOT SUPPORTED **
--CREATE OR REPLACE VIEW PUBLIC.EmployeeInsertVew
--AS
--WITH MyCTE AS ( SELECT
--NationalIDNumber
--from
--HumanResources.Employee
--UNION ALL
--SELECT
--BusinessEntityID
--FROM
--HumanResources.EmployeeDepartmentHistory
--)
--INSERT INTO PUBLIC.Dummy

FINAL SAMPLE

Let's see a final sample, let's put together all the cases that we have seen so far and see how the transformation would be

Source

CREATE OR ALTER VIEW VIEWNAME
WITH ENCRYPTION  
AS  
Select AValue from ATable
WITH CHECK OPTION;  

Expected

CREATE OR REPLACE VIEW PUBLIC.VIEWNAME
-- ** MSC-WARNING - MSCEWI1042 - Commented VIEW WITH CLAUSE - THIS IS NON-RELEVANT **
--WITH ENCRYPTION
AS
Select AValue from PUBLIC.ATable
-- ** MSC-WARNING - MSCEWI1042 - Commented WITH CHECK OPTION - THIS IS NON-RELEVANT **
--WITH CHECK OPTION
                 ;

As you can see, we changed the OR ALTER with OR REPLACE and we commented the clause WITH ENCRYPTION that comes after the view name and the WITH CHECK OPTION that comes after the Select.