Some parts in the output code are omitted for clarity reasons.
In this section, we will check the transformation for the create view.
Sample Source Patterns
SIMPLE CREATE VIEW
The following example shows a transformation for a simple CREATE VIEW statement.
IN -> SqlServer_01.sql
CREATEVIEWVIEWNAMEASSELECT AValue from ATable;
Expected
OUT -> SqlServer_01.sql
CREATE OR REPLACEVIEWVIEWNAMECOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
ASSELECTAValuefromATable;
CREATE OR ALTER VIEW
The CREATE OR ALTER definition used in SqlServer is transformed to CREATE OR REPLACE in Snowflake.
IN -> SqlServer_02.sql
CREATEORALTER VIEW VIEWNAMEASSELECT AValue from ATable;
OUT -> SqlServer_02.sql
CREATE OR REPLACEVIEWVIEWNAMECOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
ASSELECTAValuefromATable;
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
Notice that the above clauses are removed from the translation. because are not relevant in Snowflake syntax.
IN -> SqlServer_03.sql
CREATEORALTER VIEW VIEWNAMEWITHENCRYPTIONASSELECT AValue from ATable;
OUT -> SqlServer_03.sql
CREATE OR REPLACEVIEWVIEWNAMECOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
ASSELECTAValuefromATable;
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.
Notice that WITH CHECK OPTIONis removed from the translation, because is not relevant in Snowflake syntax.
IN -> SqlServer_04.sql
CREATEORALTER VIEW VIEWNAMEASSELECT AValue from ATableWITHCHECKOPTION;
Expected
OUT -> SqlServer_04.sql
CREATE OR REPLACEVIEWVIEWNAMECOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
ASSELECTAValuefromATable;
CREATE VIEW AS COMMON TABLE EXPRESSION
Common Table Expressions must be used to retrieve the data:
IN -> SqlServer_05.sql
CREATEVIEWEMPLOYEEIDVIEWASWITH CTE AS ( SELECT NationalIDNumber from [HumanResources].[Employee] UNION ALLSELECT BusinessEntityID FROM [HumanResources].[EmployeeDepartmentHistory] )SELECT*FROM MyCTE;
OUT -> SqlServer_05.sql
CREATE OR REPLACEVIEWEMPLOYEEIDVIEWCOMMENT = '{"origin":"sf_sc","name":"snowconvert","version":{"major":1, "minor":0},{"attributes":{"component":"transact"}}'
AS--** SSC-PRF-TS0001 - PERFORMANCE WARNING - RECURSION FOR CTE NOT CHECKED. MIGHT REQUIRE RECURSIVE KEYWORD **WITH CTE AS ( SELECTNationalIDNumberfromHumanResources.EmployeeUNION ALLSELECTBusinessEntityIDFROMHumanResources.EmployeeDepartmentHistory)SELECT*FROMMyCTE;
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.
--!!!RESOLVE EWI!!! /*** SSC-EWI-0021 - 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
IN -> SqlServer_06.sql
CREATEORALTER VIEW VIEWNAMEWITHENCRYPTIONASSelect AValue from ATableWITH