12 July 2018

1 Comment

12 July 2018

1 Comment

SQL Prompt code analysis: avoiding the old-style TOP clause

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

    Finding code smells using SQL Prompt: procedures that lack schema-qualification

    SQL Prompt implements a static code analysis rule, PE001, which will check code automatically, during development and testing work, for occurrences of a stored procedure being called, via the EXECUTE command, without the schema being specified. PE001 – Schema name for procedure is not specified Even where you don’t have to qualify the name of

  • Article

    When to use the SELECT…INTO statement

    We can use SELECT…INTO in SQL Server to create a new table from a table source. SQL Server uses the attributes of the expressions in the SELECT list to define the structure of the new table. Prior to SQL Server 2005, use of SELECT…INTO in production code was a performance ‘code smell’ because it acquired

  • Article

    Quick SQL Prompt tip – using the CDB snippet

    SQL Prompt includes a number of snippets by default that can help you quickly write T-SQL code. These are templates of code that users use regularly. One of the more popular snippets is the ‘cdb’ snippet that helps with quickly creating a new database for development work. This can also be used to ensure production

  • Article

    SQL Prompt Code Analysis: Avoid using the ISNUMERIC Function (E1029)

    Avoid using the IsNumeric() function, because it can often lead to data type conversion errors, when importing data. SQL Prompt Code Analysis rules include an Execution rule, E1029, which will alert you to use of this function, in your T-SQL. If you’re working on SQL Server 2012 or later, it’s much better to use the Try_Convert()or

  • Article

    How to implement a SQL formatting standard using SQL Prompt v8

    With ten programmers working on the same project, how do you agree on a standard style for formatting SQL code, and then implement it consistently? One way is through draconian rules, meeting after meeting, and the occasional sacrifice of a programmer to put the fear in those who remain. If that doesn’t sound like fun,

  • Article

    Misuse of the scalar user-defined function as a constant (PE017)

    Do not use a scalar user-defined function (UDF) in a JOIN condition, WHERE search condition, or in a SELECT list, unless the function is schema-bound. Scalar UDFs are often used without a parameter to return a global constant: Unless you need to use these as constants in computed columns or constraints, it is generally safer

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