1 / 16

T-SQL : Bad Habits to Kick

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: @AaronBertrand. Agenda. 12 simple slides with one common goal:

ethan
Download Presentation

T-SQL : Bad Habits to Kick

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

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

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

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

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

  6. 3. Choosing the wrong data type AD-204 | T-SQL : Bad Habits to Kick • 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

  7. 4. Not using schema prefix AD-204 | T-SQL : Bad Habits to Kick • 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 3rd party will use schemas

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

  9. 6. Using loops to populate large tables AD-204 | T-SQL : Bad Habits to Kick • WHILE…INSERT 1,000,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

  10. 7. Mishandling date range queries AD-204 | T-SQL : Bad Habits to Kick • 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

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

  12. 9. Using old-style joins AD-204 | T-SQL : Bad Habits to Kick • 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

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

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

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

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

More Related