SQL SERVER – Database Coding Standards and Guidelines – Part 2

SQL Server Database Coding Standards and Guidelines – Part 2

Coding Standards

  • Optimize queries using the tools provided by SQL Server5
  • Do not use SELECT *
  • Return multiple result sets from one stored procedure to avoid trips from the application server to SQL server
  • Avoid unnecessary use of temporary tables
    • Use ‘Derived tables’ or CTE (Common Table Expressions) wherever possible, as they perform better6
  • Avoid using <> as a comparison operator
    • Use ID IN(1,3,4,5) instead of ID <> 2
  • Use SET NOCOUNT ON at the beginning of stored procedures7
  • Do not use cursors or application loops to do inserts8
    • Instead, use INSERT INTO
  • Fully qualify tables and column names in JOINs
  • Fully qualify all stored procedure and table references in stored procedures.
  • Do not define default values for parameters.
    • If a default is needed, the front end will supply the value.
  • Do not use the RECOMPILE option for stored procedures.
  • Place all DECLARE statements before any other code in the procedure.
  • Do not use column numbers in the ORDER BY clause.
  • Do not use GOTO.
  • Check the global variable @@ERROR immediately after executing a data manipulation statement (like INSERT/UPDATE/DELETE), so that you can rollback the transaction if an error occurs
    • Or use TRY/CATCH
  • Do basic validations in the front-end itself during data entry
  • Off-load tasks, like string manipulations, concatenations, row numbering, case conversions, type conversions etc., to the front-end applications if these operations are going to consume more CPU cycles on the database server
  • Always use a column list in your INSERT statements.
    • This helps avoid problems when the table structure changes (like adding or dropping a column).
  • Minimize the use of NULLs, as they often confuse front-end applications, unless the applications are coded intelligently to eliminate NULLs or convert the NULLs into some other form.
    • Any expression that deals with NULL results in a NULL output.
    • The ISNULL and COALESCE functions are helpful in dealing with NULL values.
  • Do not use the identitycol or rowguidcol.
  • Avoid the use of cross joins, if possible.
  • When executing an UPDATE or DELETE statement, use the primary key in the WHERE condition, if possible. This reduces error possibilities.
  • Avoid using TEXT or NTEXT datatypes for storing large textual data.9
    • Use the maximum allowed characters of VARCHAR instead
  • Avoid dynamic SQL statements as much as possible.10
  • Access tables in the same order in your stored procedures and triggers consistently.11
  • Do not call functions repeatedly within your stored procedures, triggers, functions and batches.12
  • Default constraints must be defined at the column level.
  • Avoid wild-card characters at the beginning of a word while searching using the LIKE keyword, as these results in an index scan, which defeats the purpose of an index.
  • Define all constraints, other than defaults, at the table level.
  • When a result set is not needed, use syntax that does not return a result set.13
  • Avoid rules, database level defaults that must be bound or user-defined data types. While these are legitimate database constructs, opt for constraints and column defaults to hold the database consistent for development and conversion coding.
  • Constraints that apply to more than one column must be defined at the table level.
  • Use the CHAR data type for a column only when the column is non-nullable.14
  • Do not use white space in identifiers.
  • The RETURN statement is meant for returning the execution status only, but not data.

Reference:

SQL SERVER - Database Coding Standards and Guidelines - Part 2 codingstandard

5) Use the graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze your queries. Make sure your queries do an “Index seek” instead of an “Index scan” or a “Table scan.” A table scan or an index scan is a highly undesirable and should be avoided where possible.

6) Consider the following query to find the second highest offer price from the Items table:

SELECT MAX(Price)
FROM Products
WHERE ID IN
(
SELECT TOP 2 ID
FROM Products
ORDER BY Price DESC
)

The same query can be re-written using a derived table, as shown below, and it performs generally twice as fast as the above query:

SELECT MAX(Price)
FROM
(
SELECT TOP 2 Price
FROM Products
ORDER BY Price DESC
)

7) This suppresses messages like ‘(1 row(s) affected)’ after executing INSERT, UPDATE, DELETE and SELECT statements. Performance is improved due to the reduction of network traffic.

8) Try to avoid server side cursors as much as possible. Always stick to a ‘set-based approach’ instead of a ‘procedural approach’ for accessing and manipulating data. Cursors can often be avoided by using SELECT statements instead. If a cursor is unavoidable, use a WHILE loop instead. For a WHILE loop to replace a cursor, however, you need a column (primary key or unique key) to identify each row uniquely.

9) You cannot directly write or update text data using the INSERT or UPDATE statements. Instead, you have to use special statements like READTEXT, WRITETEXT and UPDATETEXT. So, if you don’t have to store more than 8KB of text, use the CHAR(8000) or VARCHAR(8000) datatype instead.

10) Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan at runtime. IF and CASE statements come in handy to avoid dynamic SQL.

11) This helps to avoid deadlocks. Other things to keep in mind to avoid deadlocks are:

  • Keep transactions as short as possible.
  • Touch the minimum amount of data possible during a transaction.
  • Never wait for user input in the middle of a transaction.
  • Do not use higher level locking hints or restrictive isolation levels unless they are absolutely needed.

12) You might need the length of a string variable in many places of your procedure, but don’t call the LEN function whenever it’s needed. Instead, call the LEN function once and store the result in a variable for later use.

13)

IF EXISTS (
 SELECT 1
 FROM Products
 WHERE ID = 50)

Instead Of:

IF EXISTS (
 SELECT COUNT(ID)
 FROM Products
 WHERE ID = 50)

14) CHAR(100), when NULL, will consume 100 bytes, resulting in space wastage. Preferably, use VARCHAR(100) in this situation. Variable-length columns have very little processing overhead compared with fixed-length columns.

Complete Series of Database Coding Standards and Guidelines
SQL SERVER Database Coding Standards and Guidelines – Introduction
SQL SERVER – Database Coding Standards and Guidelines – Part 1
SQL SERVER – Database Coding Standards and Guidelines – Part 2
SQL SERVER Database Coding Standards and Guidelines Complete List Download

Click here to get free chapters (PDF) in the mailbox

Reference: Pinal Dave (https://blog.sqlauthority.com)

Quest

Database, DBA, Software Development, SQL Coding Standards, SQL Download, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Good, Better and Best Programming Techniques
Next Post
SQL SERVER – Definition, Comparison and Difference between HAVING and WHERE Clause

Related Posts

16 Comments. Leave new

  • hello sir

    Thank you sir.. it is very usefull .. it helps me very much ..
    keep updating .. once again thanks

    Salam

    Reply
  • Very good site and great standards and guidelines.

    My question is regarding cursors. Everywhere I read do not use cursors instead use while loops. I did a test with 2.6 million records and the cursor took 4 minutes and the while loop took 12 minutes.

    I am using SQL Server 2005

    Create PROCEDURE [Company].[uspVendorKeyIDGets_IMC_whileloop]
    @Vendor VARCHAR(50),
    –@KeyIDServer VARCHAR(50),
    @KeyIDBlockSize INT = 100

    — exec [Company].[uspVendorKeyIDGets_IMC] ‘iusacredit’,100
    WITH EXECUTE AS CALLER
    AS
    SET NOCOUNT ON

    DECLARE @N INT, @Index INT, @Loop INT, @KeyID INT, @VendorFeedID INT
    DECLARE @KeyIDList VARCHAR(8000)
    DECLARE @StartTime DATETIME
    DECLARE @iCustLoopControl INT

    select @VendorFeedID=VendorFeedID FROM dbo.VendorFeed WHERE Name=@Vendor
    IF @@ROWCOUNT 1
    BEGIN
    return
    END

    SET @N = 200 — for testing
    SET @Loop = @KeyIDBlockSize

    SET @StartTime = GETDATE()
    PRINT Convert(varchar(20),@StartTime) + ‘ StartTime’

    — replace CURSOR with WHILE LOOP
    SELECT @iCustLoopControl = 1

    — retrieve the first row
    SELECT @Keyid = MIN(keyid)
    FROM company.companykeyid with(nolock)
    WHERE VendorFeedID = 40

    — start the main processing
    WHILE @iCustLoopControl = 1
    BEGIN
    SET @Index = 1
    SET @KeyIDList = CONVERT(VARCHAR(10), @KeyID)

    — start the nested loop for 100 records
    WHILE @Index @Keyid

    SET @Index = @Index + 1

    IF ISNULL(@KeyID,0) = 0
    BEGIN
    SET @iCustLoopControl = 0
    BREAK
    END
    END
    SET @StartTime = GETDATE()
    END

    PRINT Convert(varchar(20),GETDATE()) + ‘ EndTime’

    CREATE PROCEDURE [Company].[uspVendorKeyIDGets_IMC_cursor]
    @Vendor VARCHAR(50),
    –@KeyIDServer VARCHAR(50),
    @KeyIDBlockSize INT = 100

    — exec [Company].[uspVendorKeyIDGets_IMC_cursor] ‘iusacredit’,100
    WITH EXECUTE AS CALLER
    AS
    SET NOCOUNT ON

    DECLARE @N INT, @Index INT, @Loop INT, @KeyID INT, @VendorFeedID INT
    DECLARE @KeyIDList VARCHAR(8000)
    DECLARE @StartTime DATETIME
    DECLARE @iCustLoopControl INT

    select @VendorFeedID=VendorFeedID FROM dbo.VendorFeed WHERE Name=@Vendor
    IF @@ROWCOUNT 1
    BEGIN
    return
    END

    SET @N = 200 — for testing
    SET @Loop = @KeyIDBlockSize

    SET @StartTime = GETDATE()
    PRINT Convert(varchar(20),@StartTime) + ‘ StartTime’

    — Make it forward_only static so that a temp company is made and used otherwise we get tripped up by other loaders
    DECLARE KeyID_Cursor CURSOR FORWARD_ONLY STATIC –FAST_FORWARD
    FOR SELECT DISTINCT KeyID FROM Company.CompanyKeyID with(nolock) WHERE VendorFeedID = 40 ;

    OPEN KeyID_Cursor
    FETCH NEXT FROM KeyID_Cursor INTO @KeyID
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @Index = 1
    SET @KeyIDList = CONVERT(VARCHAR(10), @KeyID)
    WHILE @Index

    Reply
  • my table structure was like

    cond 1: row1 datetime1 <= datetime2

    cond 2: row2 datedtime1<= row1 datetime2

    here i need to find leasure time using row1 datetime2 – row 2 datetime1

    row1 2007-09-20 15:30:29.000 2007-09-20 16:35:29.130
    row2 2007-09-20 15:31:29.000 2007-09-20 16:35:34.130
    row3 2007-09-20 15:32:34.000 2007-09-20 16:35:49.130
    row4 2007-09-20 15:33:49.000 2007-09-20 16:35:54.130
    row5 2007-09-20 15:34:34.000 2007-09-20 17:06:34.130
    row6 2007-09-20 15:35:34.000 2007-09-20 17:06:39.130
    row7 2007-09-20 15:36:34.000 2007-09-20 17:36:34.130
    row8 2007-09-20 15:37:34.000 2007-09-20 17:36:39.130
    row9 2007-09-20 15:38:34.000 2007-09-20 18:06:34.130
    row10 2007-09-20 15:39:34.000 2007-09-20 18:06:39.130
    row11 2007-09-20 15:40:34.000 2007-09-20 18:35:49.130
    row12 2007-09-20 15:41:49.000 2007-09-20 18:35:54.130

    Please give me select query for this

    Reply
  • thank you for this tutorial, it’s very usefull for me

    Reply
  • Hi There, I wondered if there is a handy way within SQL server to select say, the rows numbered 500-1000.

    The basis for this request is that I have a small website that was working fine for displaying results, but now the result set is soo huge that pages are performing badly (on occasion timing out).

    What I would like to do it select say, the top 500 rows for page one, second 500 rows for page 2 etc etc.

    Whilst I am aware of offset commands in things like MySQL, I have not come accross anything like this in SQL Server.

    Does anyone know of any nice ways to do this?

    Thanks in advance, Doug

    Reply
  • Doug,

    Scott Mitchell has a good article on paging large result sets:

    If you’re using SQL Server 2000, also check out this post: https://forums.asp.net/t/1050460.aspx

    Reply
  • Hi Pinal,

    Why is using “” as a comparison operator less efficient than using a set operator?

    thanks,
    Chris

    Reply
  • hello sir
    plz send projects on ms sql server 2000&2005 with coding
    iam struggling hard to get code on stored proc
    thank u sir

    Reply
  • You didn’t mention much on VIEW nomenclature.

    I use views to represent data for consumption by Application software. The Application user (say “AppUser”) is NOT the dbo. So, access is only granted to views and columns allowed by the dbo. This is a powerful layer of abstraction.

    Also, for .Net developers using LINQ. Using a plural on entity names is nice. A LIST has a single row object of Customer. I went with singular entity names for many years and have since changed my thinking.

    Reply
  • Dear Mr. sir thanks a lot…..

    Reply
  • Omprakash yadav
    January 27, 2009 1:18 pm

    Sir, I m facing problem while accessing the date column inside the while loop . can u give the alternate solution..

    WHILE (SELECT DISTINCT DATE FROM HOLIDAYS H WHERE DATE >= @FROM_DATE AND DATE <= @TO_DATE) IS NOT NULL
    BEGIN
    SET @H_DAY = @H_DAY + DATEPART(d,H.DATE)
    END

    Regards,

    Reply
  • why not to use the identitycol or rowguidcol.

    its urgent .
    Thanks and Regards

    Ashsih Gupta

    Reply
  • Just a suggestion.

    In the reference point number 6, we are missing the alias name for the derived table

    It should have been something like

    SELECT MAX(Price)
    FROM
    (
    SELECT TOP 2 Price
    FROM Products
    ORDER BY Price DESC
    )ProductDerivedTable

    Reply
    • try to this

      SELECT MAX(Price),name
      FROM
      (
      SELECT TOP 2 Price
      FROM Products
      ORDER BY Price DESC
      )ProductDerivedTable

      Reply
  • Hello sir,

    Is there any function to replace “space” in a table with null or some other values.
    Like I need to select a column(column1) which is having “space” in it and i have to replace that column(column1) with another column(column2) if any null or space is there in the 1st column(column1).
    I don’t want to use “if” and “case”, without this is there any other way.
    Kindly suggest me.

    Thanks and regards,
    Maria

    Reply

Leave a Reply