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:

SQL Server

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');

Snowflake

Common Case

SQL Server

Query

IN -> SqlServer_02.sql

SELECT TOP(1) Model, Color, Price
FROM Cars
WHERE Color = 'red'

Result

Snowflake

Query

OUT -> SqlServer_02.sql

SELECT
TOP 1
Model,
Color,
Price
FROM
Cars
WHERE
Color = 'red';

Result

TOP using PERCENT

SQL Server

Query

IN -> SqlServer_03.sql

SELECT TOP(50)PERCENT Model, Color, Price FROM Cars

Result

Snowflake

Query

OUT -> SqlServer_03.sql

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

Result

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

Query

IN -> SqlServer_04.sql

SELECT TOP(50)PERCENT WITH TIES Model, Color, Price FROM Cars ORDER BY Price;

Result

Snowflake

Query

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;

Result

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.

Related EWIs

1. SSC-EWI-00040: THE STATEMENT IS NOT SUPPORTED IN SNOWFLAKE