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.

Description

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:

SQL
Copy
1-- Incorrect usage
2SELECT TOP 10 * FROM Employees;
3
4-- Correct usage
5SELECT 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.

How to fix

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:

SQL
Copy
1-- Incorrect usage
2SELECT TOP 10 * FROM Employees;
3
4-- Correct usage
5SELECT TOP (10) * FROM Employees;

Scope

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

Parameters

Rule has no parameters.

Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
2 minutes per issue.
Categories

Design Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1SELECT TOP 9  
 2           LastName, FirstName, JobTitle, Department
 3FROM       HumanResources.vEmployeeDepartment
 4ORDER BY LastName ASC
 5
 6SELECT TOP 12  
 7           LastName, FirstName, JobTitle, Department
 8FROM       HumanResources.vEmployeeDepartment
 9ORDER BY LastName ASC
10
11SELECT TOP (5)  
12           LastName, FirstName, JobTitle, Department
13FROM       HumanResources.vEmployeeDepartment
14
15SELECT TOP 11    /*IGNORE:SA0234(LINE)*/
16           LastName, FirstName, JobTitle, Department
17FROM       HumanResources.vEmployeeDepartment
18
19SELECT
20        F.Code, 
21        F.CustNum, 
22        SupplierCode = ((SELECT TOP 1 S.SupplierCode FROM Supplier S WHERE S.FacilityCode = F.FacilityCode))
23FROM Facility F;
Example Test SQL with Automatic Fix
SQL
Copy
 1SELECT TOP (9)  
 2           LastName, FirstName, JobTitle, Department
 3FROM       HumanResources.vEmployeeDepartment
 4ORDER BY LastName ASC
 5
 6SELECT TOP (12)  
 7           LastName, FirstName, JobTitle, Department
 8FROM       HumanResources.vEmployeeDepartment
 9ORDER BY LastName ASC
10
11SELECT TOP (5)  
12           LastName, FirstName, JobTitle, Department
13FROM       HumanResources.vEmployeeDepartment
14
15SELECT TOP 11    /*IGNORE:SA0234(LINE)*/
16           LastName, FirstName, JobTitle, Department
17FROM       HumanResources.vEmployeeDepartment
18
19SELECT
20        F.Code, 
21        F.CustNum, 
22        SupplierCode = ((SELECT TOP (1) S.SupplierCode FROM Supplier S WHERE S.FacilityCode = F.FacilityCode))
23FROM Facility F;
Analysis Results
  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
See Also

Other Resources

Analysis Rules