SQL Server Best Practices

1
SQL ServerBest Practices
By Vern Rabe
2
What are Best Practices?

• General rules or guidelines for software
  development.
• Intended to improve quality, performance, and
  maintenance
• Not always possible to follow, but always try

3
Best Practices GeneralComment code

• (duh) Since stored procedures and triggers are
  compiled, not interpreted, there is no
  performance loss by adding many lengthy comments.

4
Best Practices GeneralDont Hardcode

• Consider replacing ANY reference to a number,
  character string, or datetime with data driven

5
Best Practices GeneralFormat Code

• Formatting eases reading, debugging
• No right method, but
• Select U.VIN,
• U.Instance,
• U.AcquisitionDate
• from dbo.UsedTruck U
• join dbo.TradeID T
• on U.TradeID T.TradeID
• left outer join dbo.UsedTruckSale S
• on U.VIN S.VIN
• and U.Instance S.Instance
• where dbo.fxIsInInventory(U.StatusCode) 1
• and (T.TradeType Repo
• or T.TradeType NT Repo)
• and COALESCE(S.SaleType, X) X

6
Best Practices GeneralUse Windows
Authentication

• Easier administration
• Centralized administration
• More secure
• More flexible
• Better auditing

7
Best Practices DB DesignNormalize

• Normalize to at least 3rd normal form, then
  denormalize only when performance requires it. In
  most cases, a normalized database will perform
  faster.
• Denormalized sometimes OK
• Un-normalized never OK

8
Best Practices DB DesignData Integrity

• Data integrity should be maintained in the
  database, using CONSTRAINTS when possible,
  otherwise triggers.
• Never rely on code outside of the table to assure
  the tables integrity

9
Best Practices DB DesignPrimary Key

• Every table should have a declared PK, and if the
  PK is a surrogate key, there should be a declared
  Alternate Key on the candidate key(s).

10
Best Practices DB DesignReferential
Integrity

• Declared Referential Integrity (DRI) is the first
  choice. FAST Absolute
• DRI Implemented with FOREIGN KEY CONSTRAINTs
• If DRI cant work (e.g., dbo.Expense.VIN), use
  trigger code. Still absolute, but not as fast
  (see TRUNCATE)

11
Best Practices DB DesignDomain Integrity

• Limit what can be stored in a column, e.g.,
  ActiveFlag can only be 0, 1, 2, 3 or 9
• Similar to Referential Integrity, but where
  theres no table holding possible values
• Implemented with CHECK CONSTRAINTs

12
Best Practices DB DesignClustered Index

• Every table should have a clustered index
• The Primary Key is usually NOT the best choice
  for the clustered index, even though by default
  it will be
• The best choice is determined by how the table
  will be used (queried)

13
Best Practices DB DesignData Types to Avoid

• TEXT and NTEXT - Many string functions cannot be
  used on these datatypes, and indexing is nearly
  useless.
• FLOAT or REAL - These are approximate numeric
  datatypes, and are usually only appropriate in
  scientific applications.

14
Best Practices DB DesignData Types to Avoid

• Use CHAR instead of VARCHAR only when character
  length is very consistent. Good example for CHAR
  is State Code or Fleet Code.

15
Best Practices T-SQLThink SET

• SQL is a set based language, and the optimizer is
  designed to take advantage of that. Avoid cursors
  or looping (row level processing) whenever you
  can.

16
Best Practices T-SQLUDF

• User Defined Functions can make queries easier to
  read and write, but if not deterministic, they
  can severely impact performance.

17
Best Practices T-SQLSelect

• Do not use select or insert into dbo.Table
  select (without explicit column list).
• Explicit column lists reduce disk I/O (no need to
  read system tables to identify columns), and are
  more maintainable when columns are added or
  re-positioned.

18
Best Practices T-SQLAvoid ltgt andlike ...

• An index cannot be used on a where clause using
  wild cards at the beginning of a search string
  using LIKE, and using ltgt (or NOT).
• To increase the chances of the optimizer
  identifying an efficient query plan, include any
  and all qualifiers that apply.

19
Best Practices T-SQLAlways Use BeginEnd

• Even if a block only contains one statement,
  delimit with beginend
• Good
• If _at_somevar gt 25
• begin
• Select _at_somevar 25
• End
• Bad
• If _at_somevar gt 25
• select _at_somevar 25

20
Best Practices T-SQLUse Schema Name

• When querying a table, prefix it with the owner
  (schema), typically dbo. There is a slight
  performance improvement, schema binding requires
  it, and its more readable.
• SELECT a.au_id,
• a.au_lname
• FROM dbo.authors a

21
Best Practices T-SQLUse Table Alias

• Use table aliasing, even if you are not joining.
  Aliasing positively eliminates ambiguity, eases
  subsequent modification, and further reduces the
  chance of a typo not being recognized, as in a
  subquery.
• SELECT a.au_id,
• a.au_lname
• FROM dbo.authors a

22
Best Practices T-SQLAvoid Table Hints

• It is rare that the SQL Server optimizer doesnt
  choose the best plan.
• If you think you need to add a Table Hint, triple
  check your query before doing so.

23
Best Practices T-SQLUse WITH with Table
Hints

• Good
• from dbo.Num N WITH (UPDLOCK, PAGLOCK)
• Bad
• from dbo.Num N (UPDLOCK, PAGLOCK)
• Yukon will not support without WITH

24
Best Practices T-SQLSET NOCOUNT ON

• All stored procedures and triggers should begin
  with SET NOCOUNT ON.
• See _ProcedureTemplate.sql and _FunctionTemplate.s
  ql in SourceSafe

25
Best Practices T-SQLUse ANSI Join Syntax

• Support for T-SQL join syntax will be eliminated
  in upcoming SQL Server release
• T-SQL syntax can return incorrect results with
  outer joins
• Difficult to miss a join criterion w/ ANSI syntax
• Easier to read

26
Best Practices T-SQLAvoid Dynamic SQL

• Performs slower because the optimizer must create
  an execution plan with every execution
• Increased security risks because it does not take
  part in ownership chaining.

27
Best Practices T-SQLUse of _at__at_IDENTITY

• The _at__at_IDENTITY function can return incorrect
  results if there is a trigger on the destination
  table. Use SCOPE_IDENTITY(), or in rare cases
  IDENT_CURRENT() instead.

28
Best Practices T-SQLStored Procedure Debug

• Add a _at_DebugFlag bit parameter to stored
  procedures, defaulting to 0. When passed in as 1,
  conditional code throughout the procedure can
  return variable values, interim result sets, and
  execution times.
• See _ProcedureTemplate.sql in SourceSafe

29
Best Practices T-SQLStored Procedure Return

• Make sure stored procedures return a value
  indicating success/failure. Use the RETURN
  statement for this, and do not use the RETURN
  statement for anything else.
• See _ProcedureTemplate.sql in SourceSafe

30
Best Practices T-SQLCall Procedures with
Parameter Names

• Exec pnl_GetSomething _at_what name
• Easier to read
• Allows error free changes to procedure

31
Best Practices T-SQLError Trap in ALL
Procedures w/DML

• In Stored Procedures and Triggers, always check
  _at__at_error after every data manipulation statement
• Valid transaction control requires it
• Examplepe_valuegroups Instead of aborting, it
  continued, returning NULLs
• See _ProcedureTemplate.sql in SourceSafe

32
Best Practices T-SQLTemp Tables vs.Table
Variables

• As a general rule, temp tables perform better
  than table variables on large tables, and table
  variables perform better on small tables. There
  are so many factors, however, that testing each
  case is the only sure way to know.
• Also consider using derived tables

33
Best Practices T-SQLAvoid stringexpression

• Bad
• Select SerNo SerialNo,
• Good
• Select SerialNo AS SerNo,
• Deprecated, support will be dropped

34
Best Practices T-SQLDont use NULLor ltgt
NULL

• ANSI says any comparison to NULL returns NULL,
  e.g.,
• If _at_i NULL will never return true
• Use IS (NOT) NULL Instead

35
Best Practices T-SQLFollow ANSI Standards

• Use ANSI terms instead of T-SQL
• COALESCE instead of ISNULL
• CURRENT_TIMESTAMP instead of GET_DATE()
• ROWVERSION instead of TIMESTAMP
• Format dates YYYYMMDD

36
Best Practices T-SQLFollow ANSI Standards

• These should be ON
• ANSI_NULLS
• ANSI_PADDING
• ANSI_WARNINGS
• ARITHABORT
• CONCAT_NULL_YIELDS_NULL
• QUOTED_IDENTIFIERS
• These should be OFF
• NUMERIC_ROUNDABORT

37
Best Practices T-SQLAvoid Truncate

• Does not fire triggers if theres a delete
  trigger, you probably want it to fire
• Cannot use SCHEMA BINDING
• Cannot create FK constraints
• Cannot create indexed views
• Cannot index computed columns

38
Best PracticesThis Presentation

• Can be found at
• www.rabe.net
• Click on SQL tab

39
SQL Review

• Structured
• Query
• Language