SQL Server TSQL Coding Conventions, Best Practices, and Programming Guidelines

  • Though we survived the Y2K, always store 4 digit years in dates (especially, when using cCHAR or INT datatype columns), instead of 2 digit years to avoid any confusion and problems. This is not a problem with DATETIME columns, as the century is stored even if you specify a 2 digit year. But it’s always a good practice to specify 4 digit years even with DATETIME datatype columns. 

  • As is true with any other programming language, do not use GOTO, or use it sparingly. Excessive usage of GOTO can lead to hard-to-read-and-understand code.

  • Do not forget to enforce unique constraints on your alternate keys.

  • Always be consistent with the usage of case in your code. On a case insensitive server, your code might work fine, but it will fail on a case sensitive SQL Server if your code is not consistent in case. For example, if you create a table in SQL Server or a database that has a case-sensitive or binary sort order, all references to the table must use the same case that was specified in the CREATE TABLE statement. If you name the table as ‘MyTable’ in the CREATE TABLE statement and use ‘mytable’ in the SELECT statement, you get an ‘object not found’ error.

  • Though T-SQL has no concept of constants (like the ones in the C language), variables can serve the same purpose. Using variables instead of constant values within your queries improves readability and maintainability of your code. Consider the following example: 

    SELECT OrderID, OrderDate
    FROM Orders
    WHERE OrderStatus IN (5,6)

    The same query can be re-written in a mode readable form as shown below:

    DECLARE @ORDER_DELIVERED, @ORDER_PENDING
    SELECT @ORDER_DELIVERED = 5, @ORDER_PENDING = 6

    SELECT OrderID, OrderDate
    FROM Orders
    WHERE OrderStatus IN (@ORDER_DELIVERED, @ORDER_PENDING)

  • Do not use column numbers in the ORDER BY clause. Consider the following example in which the second query is more readable than the first one:

    SELECT OrderID, OrderDate
    FROM Orders
    ORDER BY 2

    SELECT OrderID, OrderDate
    FROM Orders
    ORDER BY OrderDate

    • Well, this is all for now folks. I’ll keep updating this page as and when I have something new to add. I welcome your feedback on this, so feel free to email me. Happy database programming!

    Published with the explicit written permission of the author. Copyright 2001.]]>

    Leave a comment

    Your email address will not be published.