SA0165 : TOP (100) PERCENT found

Introduction

Using TOP (100) PERCENT can lead to inefficient SQL queries and unexpected results in certain contexts.

Description

The use of TOP (100) PERCENT in T-SQL queries is misleading because it suggests a limit on the result set that does not actually restrict the results. This is particularly relevant in SQL Server where this issue often originates from views created using the SQL Server View Designer.

For example:

-- Example of problematic query using TOP (100) PERCENT
SELECT TOP (100) PERCENT * FROM LargeTable ORDER BY ColumnName;

This query appears to apply an ordering constraint, but in reality, the ORDER BY clause may be ignored in a larger context because TOP (100) PERCENT does not limit the number of rows. This can lead to inefficient execution and confusion about query results.

Misleading query intent: The use of TOP (100) PERCENT implies a restriction that does not exist.
Potential performance impact: Unnecessary logical operations and sorting that provide no benefit to the query execution.

How to fix

Remove the TOP (100) PERCENT clause to avoid inefficient SQL queries and unexpected results.

Follow these steps to address the issue:

1.Identify queries that include the TOP (100) PERCENT clause, especially in views or stored procedures.

2.Evaluate whether the ORDER BY clause is necessary for the result context. Move ORDER BY to the main query if needed for correct sorting, rather than relying on TOP (100) PERCENT .

3.Remove the TOP (100) PERCENT clause, as it provides no actual restriction on the result set and can cause inefficiencies.

For example:

-- Example of corrected query
SELECT * FROM LargeTable ORDER BY ColumnName;

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

3 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

SELECT TOP 99 PERCENT
           LastName, FirstName, JobTitle, Department
FROM       HumanResources.vEmployeeDepartment
ORDER BY LastName ASC

SELECT TOP 100 PERCENT
           LastName, FirstName, JobTitle, Department
FROM       HumanResources.vEmployeeDepartment
ORDER BY LastName ASC

SELECT TOP 100 PERCENT
           LastName, FirstName, JobTitle, Department
FROM       HumanResources.vEmployeeDepartment

SELECT TOP (100) PERCENT  /*IGNORE:SA0165(LINE)*/
           LastName, FirstName, JobTitle, Department
FROM       HumanResources.vEmployeeDepartment

SELECT
  F.Code,
  F.CustNum,
  SupplierCode = ((SELECT TOP 100 PERCENT S.SupplierCode FROM Supplier S WHERE S.FacilityCode = F.FacilityCode))
FROM Facility F;

Analysis Results

	Message	Line	Column
1	SA0165 : TOP (100) PERCENT found.	6	7
2	SA0165 : TOP (100) PERCENT found.	11	7
3	SA0165 : TOP (100) PERCENT found.	22	25

Analysis Rules