Don't Do This in SQL

Posted March 19, 2007

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.

1.    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.

2.    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.

3.    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 * 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.

4.    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.

5.    Do not embed T-SQL DML code in your application code.  Use stored procedures instead.

6.    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.

7.    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.

8.    Do not embed business logic code within your stored procedures.

9.    Do not use BLOBs in ADO.NET simply because it's easier to do than in classic ADO.

10.     Do not store your database connection string in the web.config file unencrypted.

11.     Do not over-normalize your database.

12.     Do not create lookup tables for data that has only a few possibilities (e.g. gender).

13.     Do not add the MachineASPNET account to the Administrators role in SQL Server.

14.     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 sp's in future releases can have the same name as something you've named with an sp_ and it will cause major confusion.

15.     Do not create varchar(1) columns, varbinary for true/false columns or other cases of improper datatype usage.

16.     Always implement error handling in your stored procedures.

17.     Do not use select distinct instead of fine-tuning your joins and where clauses to get the desired results.

18.     Take advantage of referential integrity and set up relationships between tables when there clearly should be.

19.     Take advantage of output parameters in stored procedures (i.e. to prevent returning an entire row of data when you only need one value).

20.     Do not make multiple calls to SQL Server to get the necessary data when one trip would have been sufficient.

21.     Learn how to use Profiler and Index Tuning to get the most out of your design.

22.     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.

24.     Index your foreign key columns.

25.     Do not use a select statement inside of an if statement.

26.     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.