Skip to content

SA0234 : It is recommended to use the new TOP(expression) clause syntax

Ensure that parentheses are used with the TOP clause in SQL queries to maintain consistency and avoid potential issues.

In T-SQL code, not using parentheses for the TOP clause in SELECT statements can lead to inconsistencies, as other SQL commands like INSERT , UPDATE , MERGE , and DELETE require parentheses around the expression. While older versions of SQL Server allow this omission for compatibility reasons, it’s considered a best practice to include them.

For example:

-- Incorrect usage
SELECT TOP 10 * FROM Employees;
-- Correct usage
SELECT TOP (10) * FROM Employees;

In the incorrect usage example, omitting parentheses is allowed, but it can lead to confusion or errors when incorporated into other SQL operations that mandate parentheses. Moreover, adopting a consistent style prevents errors when moving between different types of SQL statements.

  • Inconsistencies in query format when transitioning between different SQL commands.

  • Potential confusion or errors in script maintenance and readability.

Ensure the use of parentheses with the TOP clause to maintain consistency and avoid potential issues in SQL queries.

Follow these steps to address the issue:

1.Identify all SQL statements using the TOP clause without parentheses. For instance, look for statements like SELECT TOP 10 .

2.Modify the statements by adding parentheses around the expression in the TOP clause. Change TOP 10 to TOP (10) .

3.Validate the updated queries to ensure correctness and maintain a consistent coding style across different SQL commands.

For example:

-- Incorrect usage
SELECT TOP 10 * FROM Employees;
-- Correct usage
SELECT TOP (10) * FROM Employees;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

2 minutes per issue.

Design Rules, Code Smells

There is no additional info for this rule.

SELECT TOP 9
LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment
ORDER BY LastName ASC
SELECT TOP 12
LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment
ORDER BY LastName ASC
SELECT TOP (5)
LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment
SELECT TOP 11 /*IGNORE:SA0234(LINE)*/
LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment
SELECT
F.Code,
F.CustNum,
SupplierCode = ((SELECT TOP 1 S.SupplierCode FROM Supplier S WHERE S.FacilityCode = F.FacilityCode))
FROM Facility F;
SELECT TOP (9)
LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment
ORDER BY LastName ASC
SELECT TOP (12)
LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment
ORDER BY LastName ASC
SELECT TOP (5)
LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment
SELECT TOP 11 /*IGNORE:SA0234(LINE)*/
LastName, FirstName, JobTitle, Department
FROM HumanResources.vEmployeeDepartment
SELECT
F.Code,
F.CustNum,
SupplierCode = ((SELECT TOP (1) S.SupplierCode FROM Supplier S WHERE S.FacilityCode = F.FacilityCode))
FROM Facility F;
  Message Line Column
1 SA0234 : It is recommended to use the new TOP(expression) clause syntax. 1 7
2 SA0234 : It is recommended to use the new TOP(expression) clause syntax. 6 7
3 SA0234 : It is recommended to use the new TOP(expression) clause syntax. 22 25

Analysis Rules