SQL Prompt code analysis: avoiding the old-style TOP clause (ST006)
If you want to use TOP with an expression or subquery, or in INSERT, UPDATE, MERGE, and DELETE statements, then use of brackets is required, so it's a good habit to adopt everywhere.
The syntax of the TOP clause insists on a bracketed, or parenthesized, expression as its first argument, so that TOP 34 should, strictly speaking be TOP (34), although if you are just supplying a numeric constant, such as 34, as an argument then either style will work. Early incarnations of TOP could use only a constant value, so the parentheses weren’t necessary, and the old, bracket-less style is still supported so that code written in previous versions of SQL Server will still work.
However, TOP has come a long way since then, and now supports use of an expression, such as (@Rows*2), or a subquery. In these cases, the brackets are necessary. They are also required when TOP is used in INSERT, UPDATE, MERGE, and DELETE statements, so it is a habit worth adopting everywhere. SQL Prompt will raise one of its ‘code style violation’ warnings (ST006) when it spots use of the old-style syntax.
TOP examples
Imagine we want the top ten best performing customers at AdventureWorks.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT TOP 10 WITH TIES Sum(Ord.SubTotal) AS spend, Max(Coalesce(Per.Title + ' ', '') + Per.FirstName + ' ' + Coalesce(Per.MiddleName + ' ', '') + Per.LastName + Coalesce(Per.Suffix, '') ) FROM Sales.SalesOrderHeader AS Ord INNER JOIN Sales.Customer AS Cust ON Ord.CustomerID = Cust.CustomerID INNER JOIN Person.Person AS Per ON Cust.PersonID = Per.BusinessEntityID GROUP BY Ord.CustomerID ORDER BY spend DESC; |
Listing 1: TOP 10 WITH TIES
This old-style TOP 10 WITH TIES syntax will run quite happily, even though strictly we should be using (10).
However, if you are using a constant scalar subquery, the top 0.1% of all customers in this example, then the parentheses are required.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT TOP (SELECT Count(*)/1000 FROM Sales.Customer) Sum(Ord.SubTotal) AS spend, Max(Coalesce(Per.Title + ' ', '') + Per.FirstName + ' ' + Coalesce(Per.MiddleName + ' ', '') + Per.LastName + Coalesce(Per.Suffix, '') ) FROM Sales.SalesOrderHeader AS Ord INNER JOIN Sales.Customer AS Cust ON Ord.CustomerID = Cust.CustomerID INNER JOIN Person.Person AS Per ON Cust.PersonID = Per.BusinessEntityID GROUP BY Ord.CustomerID ORDER BY spend DESC |
Listing 2: TOP 0.1% of customers
Using OFFSET and FETCH NEXT as an alternative
If you don’t like being nagged about doing brackets you can happily use OFFSET and FETCH NEXT instead, which don’t require them, although it’s a bit more long-winded.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT Sum(Ord.SubTotal) AS spend, Max(Coalesce(Per.Title + ' ', '') + Per.FirstName + ' ' + Coalesce(Per.MiddleName + ' ', '') + Per.LastName + Coalesce(Per.Suffix, '') ) FROM Sales.SalesOrderHeader AS Ord INNER JOIN Sales.Customer AS Cust ON Ord.CustomerID = Cust.CustomerID INNER JOIN Person.Person AS Per ON Cust.PersonID = Per.BusinessEntityID GROUP BY Ord.CustomerID ORDER BY spend DESC OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY; |
Listing 3: Using OFFESET and FETCH NEXT
You can’t do ties, but you can specify a variable, parameter, or even constant scalar subquery as an argument to the OFFSET and FETCH NEXT, and you don’t need brackets unless you use a subquery.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT Sum(Ord.SubTotal) AS spend, Max(Coalesce(Per.Title + ' ', '') + Per.FirstName + ' ' + Coalesce(Per.MiddleName + ' ', '') + Per.LastName + Coalesce(Per.Suffix, '') ) FROM Sales.SalesOrderHeader AS Ord INNER JOIN Sales.Customer AS Cust ON Ord.CustomerID = Cust.CustomerID INNER JOIN Person.Person AS Per ON Cust.PersonID = Per.BusinessEntityID GROUP BY Ord.CustomerID ORDER BY spend DESC OFFSET 0 ROWS FETCH NEXT (SELECT Count(*)/1000 FROM Sales.Customer) ROWS ONLY; |
Listing 4: Using FETCH NEXT with a subquery
Conclusions
There are worse sins in SQL that leaving out brackets in the parameter to a TOP statement. Strangely, I’ve always felt a bit uncomfortable about leaving them out because it is useful to be reminded of the power of the TOP clause nowadays. It is a long way from the old days of having to temporarily set the ROWCOUNT to the number of rows you wanted returned!
Tools in this post
You may also like
-
Article
SQL Templates and Code Snippets in SSMS and SQL Prompt
Phil Factor explains the uses and limitations of native SSMS templates and snippets, and then demonstrates the extra versatility that SQL Prompt snippets provide.
-
Article
Enhancing your database development using coding standards
Updated June 2020 Development and IT departments face increasing demands to deliver changes faster, often with increasing complexity and less tolerance for downtime, and without a corresponding growth in team size to handle the extra work. Standardizing database development practices by establishing coding standards can help achieve this by removing blockers to understanding code, easing
-
Article
Building reusable table build scripts using SQL Prompt
You need a fast, general-purpose way to save the results of a query or batch or procedure into any sort of worktable, such as a temporary table or a table variable or table valued parameter. A simple SELECT…INTO isn't versatile enough for these requirements, and the alternative ways to handcraft the list of columns are slow and error prone. Phil Factor shows how to create a 'table-build generator' that will do all this, and save you a lot of time, especially if you use a lot of working tables in your code.
-
Article
How we used your feedback to improve parentheses formatting in SQL Prompt 8.1
After we released SQL Prompt 8, our priority was to continue to support the new formatting engine by fixing any bugs and reviewing feedback from users. One of the recurring patterns we noticed in your feedback regarded parentheses formatting. Some of you told us you couldn’t format the parentheses exactly the way you wanted to.
-
