12 July 2018

1 Comment

12 July 2018

1 Comment

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.

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

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.

SQL Prompt code analysis style rule ST006

TOP examples

Imagine we want the top ten best performing customers at AdventureWorks.

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).

results from a top 10 with ties query

However, if you are using a constant scalar subquery, the top 0.1% of all customers in this example, then the parentheses are required.

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.

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.

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!

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    Refactoring a Batch or Object with SQL Prompt

    Louis Davidson reveals some useful Prompt features for refactoring individual code blocks or modules during development, which will improve code quality, reduce tedium, make testing simpler, or sometimes all three.

  • Article

    The Sins of SELECT * (BP005)

    If Prompt warns you of use of the asterisk, or 'star' (*), in SELECT statements, consider replacing it with an explicit column list. It will prevent unnecessary network load and query performance problems, and avoid problems if the column order changes, when inserting into a table.

  • Article

    Enhancing your database development using coding standards

    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 the implementation of

  • Article

    Cleaning up common T-SQL coding issues with SQL Prompt

    Some SQL coding habits are just annoying. Commas in front of column names? No way! Others are actively harmful; they’ll make your code error prone, harder for others to read and understand, and even harder to edit without making mistakes. This article covers five harmful problems that I see regularly in T-SQL code, and shows

  • University

    Take the SQL Prompt course

    This Redgate University course takes you from installation all the way up to getting the most out of the advanced operations in SQL Prompt. As well as autocompleting your code, you’ll learn how SQL Prompt can help you with code formatting, object renaming, code analysis, and other useful tip and tricks.

  • Forums

    SQL Prompt Forum

    Write, format, and refactor SQL effortlessly

  • Another great post Phil! Even learned some new things myself. 🙂