T-SQL Best Practices - PART III - Do's and Don'ts

[green]Do's[/green]
[ul][li][green]Do use [/green]joins rather than WHERE IN ( ) clause. [/li]
[li][green]Do use [/green]templates to expedite the creation of standardized stored procedures.[/li]
Comments:
Program Name:
Programmer:
Date Created:
Purpose:
Dependency:
History of Changes:
[li][green]Do use [/green]a prefix like EraseMe or DeleteThis for objects that you want to delete, but would like time to "wait and see".[/li]
[li][green]Do code[/green] your assignment statements within the Set clause of an Update statement as follows:[/li][ol]
[li]All variable assignments first, because that is the order in which they are processed regardless of the order listed.[/li]
[li]Followed by all column assignments.[/li]
[li]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.[/li][/ol]
[li][green]Do use[/green] the "FROM Table1 INNER JOIN Table2 ON" construct rather then "FROM Table1, Table2" construct. See faq183-5168 for a detailed discussion.[/li][/ul]


[red]Don'ts[/red]
[ul][li][red]Do not[/red] use varchar fields for dates. SmallDateTime is sufficient for most purposes.[/li]
[li][red]Do not[/red] use @@Identity. Use Scope_Identity( ) instead.[/li]
[li][red]Do not[/red] enter multiple pieces of information in one column, e.g. full name. Use FirstName, LastName.[/li]
[li][red]Do not[/red] 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.[/li]
[li][red]Avoid[/red] the use of cursors. They are slow and very rarely necessary.[/li]
[li][red]Avoid[/red] the use of triggers. When you must use them, be sure that you have allowed for multiple inserts, deletes or updates.[/li]
[li][red]Avoid[/red] the use of user definded functions. They are slow, esentially eliminating the advantage of a set-based solution.[/li]
[li][red]Avoid[/red] 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.[/li][/ul]

Thanks again to all those who contributed to this FAQ. If you have further suggestions please forward them to me.[blue]

ESquared MDXer SQLSister
JayKusch nigelrivett TJRTech
john76 SQLBill vongrunt[/blue]


-Karl