Chapter 19. T-SQL: bad habits to kick

 

Aaron Bertrand

As a longtime participant in the SQL Server community, I’ve seen my share of bad habits. I’ve even formed a few of my own, all of which I’m on the path to correcting. Whether they come from StackOverflow, the MSDN forums, or even from the #sqlhelp hash tag on Twitter, one thing is clear: bad habits can spread like poison ivy. The same is definitely true in Transact-SQL (T-SQL), where samples are borrowed, reused, and learned from on a daily basis.

Many people think SELECT * is okay because it’s easy, or that following coding standards for this one stored procedure is unimportant because it needs to be deployed in a rush, or that they shouldn’t have to type that ORDER BY statement because the rows “always” come back in the same order. This chapter is meant to take five of these habits and point out (a) why they’re problems, and (b) what you can do to avoid them.

Please note that several of the examples in this chapter will use tables and data from the AdventureWorks2008R2 sample database, available from CodePlex at http://sqlserversamples.codeplex.com/.

SELECT *

For as long as there has been T-SQL code, there has been prevalent use of SELECT *. Although typically this is the most straightforward way to determine what a table looks like in ad hoc and troubleshooting scenarios, my characterization as a “bad habit” in this case only refers to its use in production code.

Declaring VARCHAR without length

Not choosing the right data type

Mishandling date range queries

Making assumptions about ORDER BY

Summary

About the author