SQL SERVER – Guidelines and Coding Standards Part – 2

  • To express apostrophe within a string, nest single quotes (two single quotes).

Example:

SET @sExample = 'SQL''s Authority'

  • When working with branch conditions or complicated expressions, use parenthesis to increase readability.

IF ((SELECT 1
FROM TableName
WHERE 1=2) ISNULL)

  • To mark single line as comment use (–) before statement. To mark section of code as comment use (/*…*/).
  • If there is no need of resultset then use syntax that doesn’t return a resultset.

IF EXISTS   (SELECT 1
FROM UserDetails
WHERE UserID = 50)

    Rather than,

IF EXISTS  (SELECT COUNT (UserID)
FROM UserDetails
WHERE UserID = 50)

  • Use graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze SQL queries. Your queries should do an “Index Seek” instead of an “Index Scan” or a “Table Scan”. (Read More Here)
  • Do not prefix stored procedure names with “SP_”, as “SP_” is reserved for system stored procedures.
    Example:
    SP<App Name>_ [<Group Name >_] <Action><table/logical instance>
  • Incorporate your frequently required, complicated joins and calculations into a view so that you don’t have to repeat those joins/calculations in all your queries. Instead, just select from the view. (Read More Here)
  • Do not query / manipulate the data directly in your front end application, instead create stored procedures, and let your applications to access stored procedure.
  • Do not store binary or image files (Binary Large Objects or BLOBs) inside the database. Instead, store the path to the binary or image file in the database and use that as a pointer to the actual file stored on a server.
  • Use the CHAR datatype for a non-nullable column, as it will be the fixed length column, NULL value will also block the defined bytes.
  • Avoid using dynamic SQL statements if you can write T-SQL code without using them.
  • Minimize the use of Nulls. Because they incur more complexity in queries and updates. ISNULL and COALESCE functions are helpful in dealing with NULL values
  • Use Unicode datatypes, like NCHAR, NVARCHAR or NTEXT if it needed, as they use twice as much space as non-Unicode datatypes.
  • Always use column list in INSERT statements of SQL queries. This will avoid problem when table structure changes.
  • Perform all referential integrity checks and data validations using constraints instead of triggers, as they are faster. Limit the use of triggers only for auditing, custom tasks, and validations that cannot be performed using constraints.
  • Always access tables in the same order in all stored procedure and triggers consistently. This will avoid deadlocks. (Read More Here)
  • Do not call functions repeatedly in stored procedures, triggers, functions and batches, instead call the function once and store the result in a variable, for later use.
  • With Begin and End Transaction always use global variable @@ERROR, immediately after data manipulation statements (INSERT/UPDATE/DELETE), so that if there is an Error the transaction can be rollback.
  • Excessive usage of GOTO can lead to hard-to-read and understand code.
  • Do not use column numbers in the ORDER BY clause; it will reduce the readability of SQL query.
    Example: Wrong Statement
    SELECT UserID, UserName, Password
    FROM UserDetails
    ORDER BY 2

Example: Correct Statement
SELECT UserID, UserName, Password
FROM UserDetails
ORDER BY UserName

  • The RETURN statement is meant for returning the execution status only, but not data. If you need to return data, use OUTPUT parameters.
  • If stored procedure always returns single row resultset, then consider returning the resultset using OUTPUT parameters instead of SELECT statement, as ADO handles OUTPUT parameters faster than resultsets returned by SELECT statements.
  • Effective indexes are one of the best ways to improve performance in a database application.
  • BULK INSERT command helps to import a data file into a database table or view in a user‐specified format.
  • Use Policy Management to make or define and enforce your own policies fro configuring and managing SQL Server across the enterprise, eg. Policy that Prefixes for stored procedures should be sp.
  • Use sparse columns to reduce the space requirements for null values. (Read More Here)
  • Use MERGE Statement to implement multiple DML operations instead of writing separate INSERT, UPDATE, DELETE statements.
  • When some particular records are retrieved frequently, apply Filtered Index to improve query performace, faster retrieval and reduce index maintenance costs.
  • EXCEPT or NOT EXIST clause can be used in place of LEFT JOIN or NOT IN for better peformance.

Example:
SELECT EmpNo, EmpName
FROM EmployeeRecord
WHERE Salary > 1000 AND Salary
NOT IN (SELECT Salary
FROM EmployeeRecord
WHERE Salary > 2000);

    (Recomended)

SELECT EmpNo, EmpName
FROM EmployeeRecord
WHERE Salery > 1000
EXCEPT
SELECT
EmpNo, EmpName
FROM EmployeeRecord
WHERE Salery > 2000
ORDER BY EmpName;

© Copyright 2000-2008 Pinal Dave. All Rights Reserved. SQLAuthority.com

Reference : Pinal Dave (http://blog.SQLAuthority.com)

18 thoughts on “SQL SERVER – Guidelines and Coding Standards Part – 2

  1. I’m just starting with T-SQL, so this series is the right guide at the right time. Thanks a lot!

    I have one question. Do you really write all the SQL language keywords like (SELECT, FROM, JOIN, ON,…) with capital letters? From my newbie perspective this is 1) quite tiring to write; and 2) quite tiring to read. My eyes and fingers don’t like it.

    Like

  2. @Iliad: I don’t think the author of this blog ever reads his comments so I would expect a response.

    I agree with what your saying though, even then typing in caps is the correct way to do it, I perfer to type my Sql in PascalCase so that it matches the style of my other languages.

    Like

  3. @iliad,

    Yes, It is quite common to use Keywords in UPPER CASE.

    It may be difficult to write and read in beggining but after a while it becomes second nature.

    @Stephen,

    I usually answer questions through email if I think it will be not useful to others.

    If I think it is useful to everyone I post on blog.

    Kind Regards,
    Pinal Dave ( http://www.SQLAuthority.com )

    Like

  4. Pingback: SQL SERVER - Guidelines and Coding Standards Complete List Download Journey to SQL Authority with Pinal Dave

  5. i want to create a function which returns the following.
    yyyyddmm#####
    can you please help me out….

    CREATE FUNCTION dbo.AutoGenerateID
    (

    @UserId char(15)

    )
    RETURNS char(15)
    AS
    begin
    declare @date_extract char(8)
    declare @id_extract char(10)
    declare @max_id char(20)
    declare @userid1 char(15)
    declare @id_size char(10)
    declare @temp1 char(15)
    declare @data char(10)

    set @max_id = @userid

    set @date_extract = left(@max_id,8)
    set @id_size = LEN(@max_id) – LEN(@date_extract)
    set @id_extract = right(@max_id,@id_size)

    set @data = convert(varchar,getdate(),112)

    if ( @data = @date_extract)
    set @id_extract = id_extract + 1
    else
    set @id_extract = concat((repeat(‘0′,@id_size-1)),’1′)
    set @date_extract = date
    end if
    set @userid1 = concat(@date_extract, @id_extract)
    set @temp1 = LEN(@max_id)-(LEN(@userid1))

    set @userid1 = concat(@date_extract,concat(repeat(‘0′,@temp1),@id_extract))

    return @userid1

    GO

    Like

  6. Dear Pinal,

    Recently, i came to know about your blog. Lot of information gathered by me going through your site. Awsome!!!!.

    I need to know more about cluster services and replication services offered by SQL 2005. Effective use of both, advantages and disadvantages.

    Thanks and regards

    Rajgopal

    Like

  7. Sir,

    I have a question about the LIKE keyword..

    I have 1 table named tbl1 and a column in it is Discount.

    in the discount column may contain a ‘%’ symbol (not necessary. Eg: 10%,100Rs)

    How can I find all values that contain the character ‘%’ using LIKE operator?

    Like

  8. Pinal,

    I’ve been following your blog for quite a while now and have great respect for you but I’d like to ask why you recommend the use of @@ERROR after transaction blocks instead of using a BEGIN/END TRY; BEGIN/END CATCH; and checking the errors from within there.

    I notice that this thread is very old and I guess you just haven’t updated it or am I missing a trick?

    Cheers

    Chris

    Like

  9. Hi Pinal and thanks for sharing.
    One thing: I found the example on “If there is no need of resultset then use syntax that doesn’t return a resultset.” misleading.
    The two query aren’t equivalent because the second “if exists (select count …” if alsways true.
    You probably should change it to “if (select count…) > 0″, it makes more sense to me.
    Anyway I agree with the suggestion – I usually use “if exists (select * …) ” because I found that sql server ignores the list of fields.
    thanks

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s