Don’t Do This in SQL: twenty-six SQL Server practices to avoid, kept from 2007

Archive note: originally published March 19, 2007, and kept for the record rather than revised. Three items have not aged well. Item 21 points at Profiler and the Index Tuning Wizard; both are deprecated, and the current path is Extended Events plus the missing-index and query-store DMVs. Item 12 argues against small lookup tables and uses gender as its example, which is a poor one for reasons that have nothing to do with SQL - the underlying point is about cardinality, not that column. Item 26 claims a select into inside an open transaction blocks sysobjects; that was a SQL Server 2000-era catalog-locking behavior and it is obsolete. Keeping transactions short is still right, but not for that reason. For how I do this now, see Eleven SQL Server Checks, Ordered by Return on the Hour Spent.

Seriously. At least have a very good reason (if one exists) if you’re going to do something on this list. And yes, I’m well aware that you may disagree with some points. If so, please let me (and others) know why.

Do not use the system administration (sa) account to access your database in a production application. Nor should you be using a single account for accessing everything. Every user, including developers and applications, should have their own account with access to objects based on their role. The sa account has too many privileges and a malicious user can take advantage of this. For example, using a SQL injection attack a malicious user can access all the databases, or even worse, the dreaded xp_cmdshell extended stored procedure to do just about anything he pleases.
Do not use a blank password. This is especially bad for the sysadmin (sa) account, for obvious reasons. Later version of SQL Server do not allow this, thankfully.
Do not use select * to populate a simple control, such as a text box. You should never use select * when all you need is a couple of or one specific field from a table. For example, code shouldn’t select all fields from a table with more than two fields to load a drop-down list, especially when large data types are used in the table, such as BLOBs and text.
Use set nocount on; and set nocount off; when creating stored procedures where you don’t need to know the number of rows affected (such as a select * from myTable). By using set nocount on; and set nocount off; you will streamline your procedure and reduce the amount of unnecessary data moved between the database and your application.
Do not embed T-SQL DML code in your application code. Use stored procedures instead.
If your data only changes n-times per day, make every effort in your applications to cache that data to prevent hitting your SQL Server for every page request.
Do not accept user input to SQL Server without validating it or checking for potentially malicious code and SQL injection attacks. Saying that’ll never happen isn’t a defense. (Still true, and still finding things: a security audit of one of my own admin panels in 2026 turned up injection through unvalidated column names, which parameterized queries don’t protect you from.)
Do not embed business logic code within your stored procedures.
Do not use BLOBs in ADO.NET simply because it’s easier to do than in classic ADO.
Do not store your database connection string in the web.config file unencrypted.
Do not over-normalize your database.
Do not create lookup tables for data that has only a few possibilities (e.g. gender).
Do not add the MachineASPNET account to the Administrators role in SQL Server.
Do NOT prefix all of your stored procedures with sp_. When the prefix is sp_, SQL Server checks the master database first before the current database. Also, existing and new system stored procedures in future releases can have the same name as something you’ve named with an sp_ and it will cause major confusion.
Do not create varchar(1) columns, varbinary for true/false columns or other cases of improper datatype usage.
Always implement error handling in your stored procedures.
Do not use select distinct instead of fine-tuning your joins and where clauses to get the desired results.
Take advantage of referential integrity and set up relationships between tables when there clearly should be.
Take advantage of output parameters in stored procedures (i.e. to prevent returning an entire row of data when you only need one value).
Do not make multiple calls to SQL Server to get the necessary data when one trip would have been sufficient.
Learn how to use Profiler and Index Tuning to get the most out of your design.
Do not use @@identity to return newly created field values from stored procedures. You should use ident_current() or scope_identity() to obtain values created on a specific table or any table in the current scope.
- ident_current returns the last identity value generated for a specific table in any session and any scope.
- @@identity returns the last identity value generated for any table in the current session, across all scopes.
- scope_identity returns the last identity value generated for any table in the current session and the current scope.
Do not use exec (@stmt). You should instead use exec sp_executesql @stmt = @stmt;.
Index your foreign key columns. (Eleven years later I wrote a detection query for the ones that aren’t, along with the ones that have quietly become untrusted.)
Do not use a select statement inside of an if statement.
Do not use a select into clause within a long-running transaction. While within an uncommitted transaction, a select into statement will block access to the sysobjects table.

The parts of this list I still run, updated with what has changed since, live in Eleven SQL Server Checks, Ordered by Return on the Hour Spent.