20 Ways To Abuse T-SQL

In just over one week from now, I will be making my debut as a SQL Server trainer/presenter/speaker person.  On January 31st, I'll be presenting the first of several sessions to an audience of 85 developers, QA staff, business analysts, and other technical users.  The goal of these sessions is to improve the overall SQL skills of the technical teams, with a secondary goal of improving the relationship between these teams and the DBA team.  There is traditionally a good deal of friction between developers and DBA's, and our environment is no exception.

The first presentation, likely to extend over three or four lunch sessions, is entitled "20 Ways To Abuse T-SQL".  This presentation introduces the twenty most common mistakes, best-practice violations, and general "no-no's" that I've seen as a DBA, within this company and others prior.

As a spin-off of that presentation, I'm starting a series of blog postings, sharing the same title.  I'll try to post, at minimum, one item a week.  The full list of twenty items is listed below, I'll update this list with links to the actual posts as they're published.

20 Ways To Abuse T-SQL

1. Procedural thinking vs set-based
2. SELECT * (performance implications, dangers of using in a view)
3. COUNT() vs EXISTS()
4. Non-SARGable filtering (functions around columns, exclusionary filters <>, NOT IN)
5. Improper sort ordering (using ORDER BY when row order is irrelevant, not using ORDER BY and assuming a default sort order)
6. Failing to review the query plan
7. Incorrect or mismatched data types (implicit conversions, comparing numeric to character, casting dates to strings)
8. Reassigning a variable from a query (without first setting it to NULL)
9. Failing to qualify object names
10. Subquery gotchas (unqualified column names, performance, NULLs, multiple rows)
11. Lack of error handling (transactions, TRY/CATCH)
12. Assumptions about predicate processing order (key/value tables, cast/convert plus check of data type)
13. Incorrect placement of predicates (WHERE vs ON in a LEFT JOIN)
14. Misuse of user-defined functions
15. Designing triggers for single-row operations
16. Using @@IDENTITY instead of SCOPE_IDENTITY()
17. Underestimating production data volume and workload (ran fine in Development database, runs for hours in Production)
18. Relying on old techniques, not utilizing new language features (CTE’s, INTERSECT/EXCEPT, OUTPUT)
19. Overlooking stored procedures for complex queries (when/why to use, advantages/disadvantages)
20. Ignoring industry best practices and DBA recommendations, lack of or misunderstanding of database concepts