INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

T-SQL Hints and Tips

T-SQL Best Practices - PART III - Do's and Don'ts by donutman
Posted: 5 Jul 04 (Edited 19 Aug 04)

Do's
  • Do use joins rather than WHERE IN ( ) clause.
  • Do use templates to expedite the creation of standardized stored procedures.

  •     Comments:
        Program Name:
        Programmer:
        Date Created:
        Purpose:
        Dependency:
        History of Changes:
  • Do use a prefix like EraseMe or DeleteThis for objects that you want to delete, but would like time to "wait and see".
  • Do code your assignment statements within the Set clause of an Update statement as follows:
    1. All variable assignments first, because that is the order in which they are processed regardless of the order listed.
    2. Followed by all column assignments.
    3. NOTE: Variable assignments are executed in the order listed and their new values become available to subsequent assignment statements.  Column assignments do not become available to subsequent assignment statements.  This makes it possible to swap the value of two columns without a temporary holding variable.
  • Do use the "FROM Table1 INNER JOIN Table2 ON" construct rather then "FROM Table1, Table2" construct.  See FAQ183-5168 for a detailed discussion.
Don'ts
  • Do not use varchar fields for dates.  SmallDateTime is sufficient for most purposes.
  • Do not use @@Identity. Use Scope_Identity( ) instead.
  • Do not enter multiple pieces of information in one column, e.g. full name.  Use FirstName, LastName.
  • Do not use the double = construct in the Set clause of an Update statement, i.e. ColName=@Variable=@Variable+1.  This construct does fail to operate consistently when used with additional assignment statements.
  • Avoid the use of cursors.  They are slow and very rarely necessary.
  • Avoid the use of triggers.  When you must use them, be sure that you have allowed for multiple inserts, deletes or updates.
  • Avoid the use of user definded functions.  They are slow, esentially eliminating the advantage of a set-based solution.
  • Avoid the use of dynamic SQL.  There may be times when it is appropriate, but dynamic SQL doesn't allow the optimizer to pre-compile the procedure.  If the user provides input to the dynamic SQL, then there is the very real possibility of SQL injection attacks.
   Thanks again to all those who contributed to this FAQ.  If you have further suggestions please forward them to me.

                 ESquared              MDXer              SQLSister
                 JayKusch               nigelrivett         TJRTech
                 john76                  SQLBill              vongrunt



-Karl

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close