T-SQL Bad Habits and Best Practices

T-SQL: Bad Habits… and Best Practices Aaron Bertrand, Senior Consultant SQL Sentry, Inc. @AaronBertrand http://sqlsentry.com/ http://sqlperformance.com/

Agenda • I’m going to talk about ways you might currently be “doing it wrong.” • Don’t take offense • I learned many of these things the hard way • Most slides have links to blog posts in the notes • Many have more details, more background, more demos • I want everyone to take away at least one thing

DO NOT: SELECT * / Omit column list • The problems are not: • metadata overhead • the use of the * explicitly • Real problem is needless lookups, network, I/O • Also change management: • Views do not magically update • INSERT dbo.table SELECT * FROM ^ ^ problem problem

DO: Specify lengths for (n)(var)char • Do these yield the same answer? • DECLARE @x VARCHAR = 'aaron'; • SELECT • [variable] = @x, • [concat] = CONCAT(@x,'bertrand'), • [cast] = CAST('aaron' AS VARCHAR), • [convert] = CONVERT(VARCHAR, 'aaron');

DO NOT: 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 • VARCHAR for proper names • MAX types for URL & e-mail address • TIMESTAMP because people think it involves date/time

DO: Always use the schema prefix • When creating, altering, and 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

DO NOT: 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, subquery, CTE etc. does not do this • TOP here dictates the rows to include, not how to order

DO: Use SET NOCOUNT ON • Eliminates DONE_IN_PROC messages • Chatter can be interpreted as resultsets by app • Even in SSMS, this chatter can slow processing • Also can make finding errors and warnings tough • BUT : Test your applications! • Some older providers may rely on this info

DO NOT: Abuse date / range queries • Non-sargableexpressions • YEAR(), CONVERT(), DATEADD() against columns • Date/time shorthand • GETDATE() + 1 • Spell it out! n, ns, m, mi, mm, mcs, ms, w, wk, ww, y, yyyy • BETWEEN / calculating “end” of period • Open-ended date range is safer • Non-safe, regional date formats • m/d/y & d/m/y instead of yyyymmdd or yyyy-mm-ddThh:mm:ss

DO NOT: Compare to DATEDIFF • SQL Server gets DATEDIFF(DAY, 0, GETDATE()) wrong • Leads to really bad cardinality estimates • The bug was “fixed” under trace flag 4199 • Which most of you, hopefully, aren’t running everywhere • There are many ways to skin this cat, let’s use a different way

DO: Use MERGE wisely • Yes, it turns multiple statements into one • But it only prevents race conditions with explicit HOLDLOCK • There are many unresolved bugs and other issues: • http://bit.ly/merge-with-caution

DO NOT: Use old-style joins • Old-style outer joins (*= / =*) • Deprecated syntax • Unpredictable results • Old-style inner joins (FROM x, y) • Easy to mix up join and filter criteria • Easier to accidentally derive Cartesian product • Not deprecated, but not recommended either

DO: Use sensible naming conventions • Procedures from a real customer system: dbo.GetCustomerDetails dbo.Customer_Update dbo.Create_Customer dbo.usp_updatecust • Styles vary; even your own changes over time • Convention you choose is not the point; consistency is • Just don’t use the sp_ prefix (link in notes)

DO NOT: 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

DO: Use efficient cursor options • Defaults are slow and heavy-handed • Global, updateable, dynamic, scrollable • My syntax is always: DECLARE c CURSOR LOCAL FAST_FORWARD FOR …

DO NOT: Default to dynamic SQL • Like cursors, not always evil – can be best • Be aware of: • Potential cache bloat (turn on “optimize for ad hoc workloads”) • “Sea of red” • Concatenating different data types into a string • SQL injection

DO: Use sp_executesql, not EXEC() • sp_executesql helps thwart SQL injection • Allows use of strongly-typed parameters • But not for things like table/column names • Only partial protection, but better than zero

DO NOT: CASE/COALESCE in subquery • Inner SELECT is evaluated twice. This… SELECT COALESCE((SELECT …), 0) …; • …expands to this… SELECT CASE WHEN (SELECT …) > 0 THEN (SELECT …) ELSE 0 END;

DO: 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 devs write ad hoc queries, ORMs/vendors change tactics

DO NOT: Abuse COUNT For filtered count, use EXISTS: IF (SELECT COUNT(*) FROM dbo.table WHERE …) > 0 IF EXISTS (SELECT 1 FROM dbo.table WHERE …) For total count, use sys.partitions: SELECT COUNT(*) FROM dbo.table; SELECT SUM(rows) FROM sys.partitions WHERE index_id IN (0,1) AND [object_id] = …

DO: Stay Employed • Always use BEGIN TRANSACTION on ad hoc updates • SQL Server doesn’t have Ctrl + Z • Otherwise, keep your resume in an open transaction • Grab MladenPrajdic’s SSMS Tools Pack • Modify the “New Query” template • Use more reliable custom connection coloring • Not free for SSMS 2012, but worth every penny

DO NOT: Overuse NOLOCK • The magic, pixie-dust “turbo button” …if you’re okay with 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

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

T-SQL Bad Habits and Best Practices