Select

Translation reference to convert SQL Server Select statement to Snowflake

Description

Allows the selection of one or more rows or columns of one or more tables in SQL Server.

For more information regarding SQL Server Select, check here.

<SELECT statement> ::=    
    [ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ]  
    <query_expression>   
    [ ORDER BY <order_by_expression> ] 
    [ <FOR Clause>]   
    [ OPTION ( <query_hint> [ ,...n ] ) ]   
<query_expression> ::=   
    { <query_specification> | ( <query_expression> ) }   
    [  { UNION [ ALL ] | EXCEPT | INTERSECT }  
        <query_specification> | ( <query_expression> ) [...n ] ]   
<query_specification> ::=   
SELECT [ ALL | DISTINCT ]   
    [TOP ( expression ) [PERCENT] [ WITH TIES ] ]   
    < select_list >   
    [ INTO new_table ]   
    [ FROM { <table_source> } [ ,...n ] ]   
    [ WHERE <search_condition> ]   
    [ <GROUP BY> ]   
    [ HAVING < search_condition > ]   

Sample Source Patterns

SELECT WITH COLUMN ALIASES

The following example demonstrates how to use column aliases in Snowflake. The first two columns, from the SQL Server code, are expected to be transformed from an assignment form into a normalized form using the AS keyword. The third and fourth columns are using valid Snowflake formats.

SQL Server

SELECT
    MyCol1Alias = COL1,
    MyCol2Alias = COL2,
    COL3 AS MyCol3Alias,
    COL4 MyCol4Alias
FROM TABLE1;

Snowflake

SELECT
	COL1 AS MyCol1Alias,	-- Translate to us keyword AS
	COL2 AS MyCol2Alias,	-- Translate to us keyword AS
	COL3 AS MyCol3Alias,	-- Already using a valid format
	COL4 MyCol4Alias	-- Already using a valid format
FROM TABLE1;

SELECT TOP

The basic case of SQL Server Select Top is supported by Snowflake. However, three more cases exist that are not supported, you can check them in the Known Issues section.

SQL Server

SELECT TOP 1 * from ATable;

Snowflake

SELECT TOP 1 * from PUBLIC.ATable;

SELECT INTO

The following example shows the SELECT INTO is transformed to a CREATE TABLE AS, this is because in Snowflake there is not an equivalent for SELECT INTO and to create a table based on a query has to be with the CREATE TABLE AS.

SQL Server

SELECT * INTO NEWTABLE FROM TABLE1;

Snowflake

CREATE TABLE PUBLIC.NEWTABLE AS SELECT * FROM PUBLIC.TABLE1 ;

Another case is when including set operators such as EXCEPT and INTERSECT. The transformation is basically the same as the previous one.

SQL Server

SELECT * INTO NEWTABLE FROM TABLE1
EXCEPT
SELECT * FROM TABLE2
INTERSECT
SELECT * FROM TABLE3;

Snowflake

CREATE TABLE PUBLIC.NEWTABLE AS SELECT * FROM PUBLIC.TABLE1
EXCEPT
SELECT * FROM PUBLIC.TABLE2
INTERSECT
SELECT * FROM PUBLIC.TABLE3 ;

Known Issues

SELECT TOP Aditional Arguments

Since PERCENT and WITH TIES keywords affect the result, and they are not supported by Snowflake, they will be commented out and added as an error.

SQL Server

SELECT TOP 1 PERCENT * from ATable;
SELECT TOP 1 WITH TIES * from ATable;
SELECT TOP 1 PERCENT WITH TIES * from ATable;

Snowflake

SELECT TOP 1 /*** MSC-ERROR - MSCEWI1021 - PERCENT NOT SUPPORTED ***/ * from PUBLIC.ATable;
SELECT TOP 1 /*** MSC-ERROR - MSCEWI1021 - WITH TIES NOT SUPPORTED ***/ * from PUBLIC.ATable;
SELECT TOP 1 /*** MSC-ERROR - MSCEWI1021 - PERCENT AND WITH TIES NOT SUPPORTED ***/ * from PUBLIC.ATable;

SELECT FOR

Since the FOR clause is not supported in Snowflake, it is commented out and added as an error during the transformation.

SQL Server

SELECT column1, column2 FROM my_table FOR XML PATH('');

Snowflake

SELECT column1,
column2 FROM PUBLIC.my_table
-- ** MSC-ERROR - MSCEWI1021 - FOR CLAUSE NOT SUPPORTED **
--                             FOR XML PATH('')
                                             ;

SELECT OPTION

The OPTION clause is not supported by Snowflake. It will be commented out and added as a warning during the transformation.

SQL Server

SELECT column1, column2 FROM my_table OPTION (HASH GROUP, FAST 10);

Snowflake

SELECT column1,
column2 FROM PUBLIC.my_table
-- ** MSC-WARNING - MSCEWI1042 - Commented OPTION CLAUSE - THIS IS NON-RELEVANT **
--                             OPTION (HASH GROUP, FAST 10)
                                                         ;

SELECT WITH

The WITH clause is not supported by Snowflake. It will be commented out and added as a warning during the transformation.

SQL Server

SELECT AValue from ATable WITH(NOLOCK, NOWAIT);

Snowflake

SELECT AValue from PUBLIC.ATable
-- ** MSC-WARNING - MSCEWI1042 - Commented WITH TABLE HINT - THIS IS NON-RELEVANT **
--                                 WITH(NOLOCK, NOWAIT)
                                                     ;

Related EWIs

1. MSCEWI1021: PERCENT NOT SUPPORTED

2. MSCEWI1021: WITH TIES NOT SUPPORTED

3. MSCEWI1021: PERCENT AND WITH TIES NOT SUPPORTED

4. MSCEWI1021: FOR CLAUSE NOT SUPPORTED

5. MSCEWI1042: Commented OPTION CLAUSE - THIS IS NON-RELEVANT

6. MSCEWI1042: Commented WITH TABLE HINT - THIS IS NON-RELEVANT