T sql bad habits best practices
Download
1 / 25

T-SQL : Bad Habits & Best Practices - PowerPoint PPT Presentation


  • 84 Views
  • Uploaded on

T-SQL : Bad Habits & Best Practices. Aaron Bertrand SQL Sentry. Who Am I?. Senior consultant at SQL Sentry Microsoft MVP since 1997 Blog: sqlperformance.com / sqlblog.com Twitter: @AaronBertrand. 2. Before we start: Don’t take offense

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'T-SQL : Bad Habits & Best Practices' - hova


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
T sql bad habits best practices

T-SQL : Bad Habits&Best Practices

Aaron Bertrand

SQL Sentry


Who am i
Who Am I?

  • Senior consultant at SQL Sentry

  • Microsoft MVP since 1997

  • Blog: sqlperformance.com / sqlblog.com

  • Twitter: @AaronBertrand

2


T sql bad habits best practices

Before we start:

  • Don’t take offense

    • I’ve learned many of these things the hard way

  • I want everyone to take away at least one thing

  • Most slides have links to blog posts in the notes

3


Use select omit columns
Use SELECT * / omit columns

  • Metadata overhead is NOT the problem here

  • Can cause needless lookups, network, I/O

  • Change management:

    • Views do not magically update

    • INSERT dbo.table SELECT * FROM

  • Also, don’t just create an index because the plan, DMV or Tuning Advisor tells you to…

  • 4


    Specify length for n var char
    Specify length for (n)(var)char

    • Pop Quiz : Do these yield the same answer?

      DECLARE @x VARCHAR = 'xyz';

      SELECT @x,

      CAST('xyz' AS VARCHAR),

      CONVERT(VARCHAR, 'xyz');

    5


    Choose the wrong data type
    Choose the wrong data type

    • All kinds of violations here:

      • String/numeric types for date/time data

      • Datetimewhen date/smalldatetime will do

      • Time in place of an interval

      • MONEY/FLOAT because they sound appropriate

      • NVARCHAR for postal code

      • MAX types for URL & e-mail address

      • VARCHAR for proper names

    6


    Always use the schema prefix
    Always use the schema prefix

    • When creating, altering, referencing objects

      • Being explicit prevents confusion or worse

        • Object resolution can work harder without it

        • Can yield multiple cached plans for same query

    • Even if all objects belong to dbo, specify

      • Eventually, you or 3rd parties will use schemas

    7


    Abuse order by
    Abuse ORDER BY

    • ORDER BY [ordinal]

      • OK for ad hoc, not for production

      • Query or underlying structure can change

    • Popular myth: table has “natural order”

      • Without ORDER BY, no guaranteed order

      • TOP + ORDER BY in a view does not do this

        • TOP here is which rows to include, not how to order

    8


    Use set nocount on
    Use SET NOCOUNT ON

    • Eliminates DONE_IN_PROC messages

      • Chatter can interpreted as resultsets by app code

      • Even in SSMS, this chatter can slow processing

    • BUT : Test your applications!

      • Some older providers may rely on this info

    9


    Abuse date range queries
    Abuse date / range queries

    • Avoid non-sargable expressions

      • YEAR(), CONVERT(), DATEADD() against columns

    • Avoid date/time shorthand

      • GETDATE() + 1

      • Spell it out! n, ns, m, mi, mm, mcs, ms, w, wk, ww, y, yyyy

    • Avoid BETWEEN / calculating “end” of period

      • Open-ended date range is safer

      • Don’t “Chop off time” with a serrated edge

    10


    Use safe date formats
    Use safe date formats

    • This is not safe at all:

      mm/dd/yyyy

    • Always use:

      yyyymmddoryyyy-mm-ddThh:mm:ss.nnn

    11


    Use old style joins
    Use old-style joins

    • Old-style inner joins (FROM x, y)

      • Easy to muddle join and filter criteria

      • Easy to accidentally derive Cartesian product

      • Not deprecated, but not recommended either

  • Old-style outer joins (*= / =*)

    • Deprecated syntax with unpredictable results

  • 12


    Use sensible naming conventions
    Use sensible naming conventions

    • Procedures from a real customer system:

      dbo.GetCustomerDetails

      dbo.Customer_Update

      dbo.Create_Customer

      dbo.sp_updatecust

    • Styles vary; even your own changes over time

      • Convention you choose isn’t the point; consistency is

      • Just don’t use the sp_ prefix (link in notes)

    13


    Default to cursors
    Default to cursors

    • Can be difficult to think set-based

      • For maintenance tasks, maybe not worth it

      • Not always possible to go set-based

  • Cursors are often “okay” but rarely optimal

    • Most common exception : running totals

  • 14


    Use efficient cursor options
    Use efficient cursor options

    • Avoid heavy locking / resource usage

    • My syntax is always:

      DECLARE c CURSOR

      LOCAL FAST_FORWARD

      FOR …

    15


    Default to dynamic sql
    Default to dynamic SQL

    • Like cursors, not always evil – can be best

    • However, be aware of:

      • Potential cache bloat

        • Use “optimize for ad hoc workloads” setting

      • “Sea of red” – maintenance is tough

      • SQL injection

    16


    Use sp executesql vs exec
    Use sp_executesqlvs. EXEC()

    • Helps thwart SQL injection

      • Allows use of strongly-typed parameters

      • Only partial protection, but better than zero

  • Promotes better plan re-use

  • 17


    Use subqueries in case coalesce
    Use subqueries in CASE / COALESCE

    • SELECT is evaluated twice:

      SELECT CASE

      WHEN (SELECT …) > 0

      THEN (SELECT …) ELSE -1 END;

      SELECT COALESCE((SELECT …), -1) …;

    • One case where ISNULL() is better

    18


    Use consistent case formatting
    Use consistent case / formatting

    • For readability, be liberal with:

      • BEGIN / END

      • Carriage returns

      • Indenting

  • Use semi-colons to future-proof code

  • Case/spacing differences yield different plans

    • A concern if devswrite ad hoc queries

  • 19


    Abuse count
    Abuse COUNT

    • Use EXISTS instead of this common pattern:

      IF (SELECT COUNT(*) FROM dbo.table WHERE …) > 0

      IF EXISTS (SELECT 1 FROM dbo.table WHERE …)

    • And for total count, use sys.partitions rather than:

      SELECT COUNT(*) FROM dbo.table;

      SELECT SUM(rows) FROM sys.partitions

      WHERE index_id IN (0,1) AND [object_id] = …

    20


    Stay employed
    Stay Employed

    • Always use BEGIN TRAN on ad hoc updates

      • SQL Server doesn’t have Ctrl + Z

    • Otherwise, keep resume in open transaction

    • Grab MladenPrajdic’s SSMS Tools Pack

      • Lets you modify the “New Query” template

    21


    Overuse nolock
    Overuse NOLOCK

    • The magic, pixie-dust “turbo button”

      …if you like inaccuracy

    • There are times it is perfectly valid

      • Ballpark row counts

    • Usually, though, better to use RCSI

      • Test under heavy load – can hammer tempdb

      • Use scope-level setting, not table hint

    22


    T sql bad habits best practices

    Plenty more…Search for bad habitsat sqlblog.comPlease check the slide notes for additional info and links to blog posts and articles

    23


    Coming up

    Coming up…

    Followed by…

    • #SQLBITS


    Coming up1

    Coming up…

    • #SQLBITS