TOP

Description

Some parts in the output code are omitted for clarity reasons.

Limits the rows returned in a query result set to a specified number of rows or percentage of rows. When you use TOP with the ORDER BY clause, the result set is limited to the first N number of ordered rows. Otherwise, TOP returns the first N number of rows in an undefined order. Use this clause to specify the number of rows returned from a SELECT statement. Or, use TOP to specify the rows affected by an INSERT, UPDATE, MERGE, or DELETE statement. (SQLServer TOP documentation)

Syntax in SQL Server

TOP (expression) [PERCENT] [ WITH TIES ]

To get more information about the TOP arguments please check the SQL Server TOP documentation.

Syntax in Snowflake

TOP <n> 

To get more information about TOP arguments please check the Snowflake TOP documentation.

Sample Source Patterns

To execute correctly the following samples it is required run the next CREATE TABLE statement:

IN -> SqlServer_01.sql
CREATE TABLE Cars(
    Model VARCHAR(15), 
    Price MONEY, 
    Color VARCHAR(10)
);

INSERT Cars VALUES ('sedan', 10000, 'red'), 
('convertible', 15000, 'blue'),
('coupe', 20000, 'red'), 
('van', 8000, 'blue'),
('sub', 8000, 'green');

Common Case

SQL Server

IN -> SqlServer_02.sql
SELECT TOP(1) Model, Color, Price
FROM Cars
WHERE Color = 'red'

Snowflake

OUT -> SqlServer_02.sql
SELECT
TOP 1
Model,
Color,
Price
FROM
Cars
WHERE
Color = 'red';

TOP using PERCENT

SQL Server

IN -> SqlServer_03.sql
SELECT TOP(50)PERCENT Model, Color, Price FROM Cars

Snowflake

OUT -> SqlServer_03.sql
SELECT
TOP 50 !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
Model,
Color,
Price
FROM
Cars;

Since PERCENT argument is not supported by Snowflake it is being removed from the TOP clause, that's why the result of executing the query in Snowflake is not equivalent to SQL Server.

TOP WITH TIES

SQL Server

IN -> SqlServer_04.sql
SELECT TOP(50)PERCENT WITH TIES Model, Color, Price FROM Cars ORDER BY Price;

Snowflake

Out -> SqlServer_04.sql
SELECT
TOP 50 !!!RESOLVE EWI!!! /*** SSC-EWI-0040 - THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE ***/!!!
Model,
Color,
Price
FROM
Cars
ORDER BY Price;

Since WITH TIES argument is not supported by Snowflake it is being removed from the TOP clause, that's why the result of executing the query in Snowflake is not equivalent to SQL Server.

Known Issues

1. PERCENT argument is not supported by Snowflake

Since the PERCENT argument is not supported by Snowflake it is being removed from the TOP clause and a warning is being added. Functional equivalence mismatches in the results could happen.

2. WITH TIES argument is not supported by Snowflake

Since the WITH TIES argument is not supported by Snowflake it is being removed from the TOP clause and a warning is being added. Functional equivalence mismatches in the results could happen.

Last updated