TSQL Bad Habits to Kick Aaron Bertrand SQL

T-SQL : Bad Habits to Kick Aaron Bertrand SQL Sentry, Inc.

Who is Aaron Bertrand? • Senior Consultant at SQL Sentry www. sqlsentry. net abertrand@sqlsentry. net • Microsoft MVP since 1997 -98 • Blog: www. sqlblog. com • Twitter: @Aaron. Bertrand AD-204 | T-SQL : Bad Habits to Kick 2

Agenda 12 simple slides with one common goal: Improving at least one bad habit. These are mostly just opinions; No right or wrong answer. AD-204 | T-SQL : Bad Habits to Kick 3

1. SELECT * / omitting column list • Needless lookups/scans, I/O, network load • Predictability / change management • Today’s tools negate carpal tunnel excuse AD-204 | T-SQL : Bad Habits to Kick 4

2. Declaring variables without length Guess the results: DECLARE @x VARCHAR = 'foo'; SELECT @x; SELECT CONVERT(VARCHAR, 'foo'); AD-204 | T-SQL : Bad Habits to Kick 5

3. Choosing the wrong data type Don’t choose: • String/numeric types for date/time data • TIME in place of an interval • DATETIME if DATE/SMALLDATETIME will do • NVARCHAR(MAX) for URL, zip, phone, e-mail • VARCHAR for proper names AD-204 | T-SQL : Bad Habits to Kick 6

4. Not using schema prefix Being explicit prevents confusion or worse • • Object resolution works harder without it Leads to multiple cached plans for same query Even if all objects belong to dbo, specify • Eventually, you or 3 rd party will use schemas AD-204 | T-SQL : Bad Habits to Kick 7

5. Using inconsistent naming conventions Examples I’ve seen in a single system: • • • Get. Customer. Details Customer_Update Create_Customer Styles vary – even your own changes over time • The convention you choose isn’t the point AD-204 | T-SQL : Bad Habits to Kick 8

6. Using loops to populate large tables WHILE…INSERT 1, 000 times is log intensive Much better constructs: • Numbers table • Recursive CTEs • Cross joins from catalog views If you must use a loop • Batch by committing every <n> rows AD-204 | T-SQL : Bad Habits to Kick 9

7. Mishandling date range queries Avoid non-sargeable clauses that come from: • YEAR() and other functions against columns • CONVERT() on both sides of clauses BETWEEN is ok for DATE but not DATETIME Do not try to calculate “end of today”: • Use >= today AND < tomorrow AD-204 | T-SQL : Bad Habits to Kick 10

8. Using SELECT/RETURN vs. OUTPUT In general… • • • SELECT is for multiple rows/columns OUTPUT is for limited number of scalar values RETURN is for status/error codes, NOT DATA! AD-204 | T-SQL : Bad Habits to Kick 11

9. Using old-style joins Do not use old-style inner joins (FROM x, y, z) • • Easy to accidentally derive Cartesian product Not deprecated, but not recommended either Do not use old-style outer joins (*= / =*) • • Deprecated syntax Unpredictable results AD-204 | T-SQL : Bad Habits to Kick 12

10. Using cursors with default options • Cursors are often bad, but not evil • Avoid heavy locking behavior - my syntax: DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY FOR … AD-204 | T-SQL : Bad Habits to Kick 13

11. Using ORDER BY [ordinal] • • Underlying structure/query changes OK habit for ad hoc stuff, not production code Keystrokes are only downside to being explicit Intelli. Sense / 3 rd party tools negate this anyway AD-204 | T-SQL : Bad Habits to Kick 14

12. Assuming ORDER without ORDER BY Popular myth: “table has natural order” • • Without ORDER BY, there is no guaranteed order TOP unfortunately has two meanings: 1. 2. • Which rows to include How to order them To separate, use a CTE or nested subquery AD-204 | T-SQL : Bad Habits to Kick 15

13 -> ∞ …plenty of others… Search for “bad habits to kick” at http: //sqlblog. com/ AD-204 | T-SQL : Bad Habits to Kick 16