TOP Clause in SELECT - Sybase SQL Anywhere to SQL Server Migration

Both Sybase SQL Anywhere and SQL Server support TOP clause to limit the number of rows returned by SELECT statement after ORDER BY, but there are some differences that require conversion.

Consider the following sample table:

  -- Sample table
  CREATE TABLE colors (name VARCHAR(30));
 
  -- Sample data
  INSERT INTO colors VALUES ('Red');
  INSERT INTO colors VALUES ('Orange');
  INSERT INTO colors VALUES ('Blue');

Sybase SQL Anywhere:

  -- Specify an integer constant
  SELECT TOP 2 * FROM colors
  # Red
  # Orange
 
  DECLARE @cnt INT = 2
 
  -- Use a variable 
  SELECT TOP @cnt * FROM colors
  # Red
  # Orange
 
  -- Use an expression, parentheses are required now!
  SELECT TOP (@cnt - 1) * FROM colors
  # Red

SQL Server:

 -- Specify an integer constant
  SELECT TOP 2 * FROM colors
  # Red
  # Orange
 
  DECLARE @cnt INT = 2
 
  -- Use a variable, parentheses are required now! 
  SELECT TOP (@cnt) * FROM colors
  # Red
  # Orange
 
  -- Use an expression, parentheses are also required!
  SELECT TOP (@cnt - 1) * FROM colors
  # Red

You can see that Sybase SQL Anywhere allows you to skip parentheses for integers and variables, while SQL Server for integers only.

For more information, see Sybase SQL Anywhere to SQL Server Migration.