TOP

Important Notice: Migration of Documentation Website

Please be advised that our documentation website is currently undergoing a migration to a new platform. To ensure you have access to the most up-to-date information, we kindly request that you visit our new documentation website located at:

Official Snowflake Snowconvert Documentation

For any immediate assistance or if you encounter any issues, please contact our support team at [email protected].

Thank you for your understanding.

Applies to

• SQL Server

• Azure Synapse Analytics

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. (Transact-SQL TOP documentation)

Syntax in Transact-SQL

TOP (expression) [PERCENT] [ WITH TIES ]

To get more information about the TOP arguments please check the Transact-SQL 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:

Transact-SQL

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

Transact-SQL

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

Transact-SQL

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 Transact-SQL.

TOP WITH TIES

Transact-SQL

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 Transact-SQL.

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-0040: Statement Not Supported.