In March, I ran a Question of the Month that asked, “What is the biggest mistake/problem you ever found on a SQL Server instance, and how did you fix it.” There were so many great responses that I thought I would summarize many of them here, as this list provides many remarkable examples of real mistakes made by developers and DBAs. To see all of the responses (and who contributed them), visit this webpage. Of course, this list is not a comprehensive list of every potential mistake that can be made, but it offers you a glimpse of the kind of mistakes (some very amusing) that some people make when working with SQL Server. Keep in mind that this list applies to the specific examples provided by those who responded to the question, and that there are always exceptions to every rule. Thanks to everyone who contributed to this list.

1. Don’t use a GUID as the clustered primary key of a table, especially when the table has 600+ million rows.
2. Don’t put all your data in a single database table; consider the benefits of normalization. As with the above example, when a non-normalized table has 600+ million rows, this can create a bit of a problem.
3. Don’t add an index to every column in a table, just to cover all your bases.
4. Don’t use a case sensitive collation, as developers will have to modify virtually every query in order to render each query case insensitive, so matches can be found, as you never know what case the data will be in.
5. Don’t set your database to the full recovery model, then forget to make transaction log backups. Some people just can’t figure out why their transactions log keeps growing and growing and growing. And don’t add injury to insult by resolving the “problem” of large transaction logs by switching the database from the full recover mode to the simple recovery mode, perform a full database backup, shrink the log file, and then change the database back to the full recovery mode (and repeat the process every time the transaction log gets big again).
6. Don’t give all users DBO or SA rights to all of your databases and/or servers. Along the same line of thinking, don’t tell all the users the SA password. While this may save a little time setting up security, the downside is much greater.
7. Don’t forget that indexes need maintenance, especially if the tables are subject to huge numbers of updates every day, and the clustered index is not monotonically increasing.
8. Don’t forget to add a clustered index to each table, unless you have a really good reason not to.
9. If your database needs lookup tables, ensure they are properly normalized. Don’t try to save a little time by creating a single, massive lookup table where all lookup data is stored, especially if most of the lookup table data is unrelated.
10. Don’t forget to use foreign key constraints to ensure data consistency (as appropriate).
11. Don’t forget to add indexes to foreign keys.
12. Don’t use SELECT *.
13. Don’t create Transact-SQL code that causes a full table scan on an orders table for each customer, especially if there are 4.5 million customers.
14. Don’t have a backup retention policy that causes your backups to be deleted too soon. For example, if you have just discovered that 5% of the historical data of a customer database has been deleted by accident six weeks ago (a problem in itself), don’t delete your backups after four weeks, otherwise the data will be gone for good.
15. Don’t set the Cost Threshold for Parallelism to 0 (the default is 5), as this can cause the query optimizer to consider (and often use) parallel queries for most every query, other than non-trivial queries. Doing so can contribute to massive CPU utilization.
16. Don’t forget to screw (or somehow secure) the physical drives into the frame of an array. If you don’t, and you need to replace one of the drives, all the other drives could fall out, crashing to the ground.
17. Don’t delete local backups until you verify that they have first been moved to safely to another secure location.If the local backups aren’t moved successfully, and the local backups are already deleted, then you no longer have any backups.
18. Don’t use any third-party application, or write your own application, that hard codes the SA password into the application. This is especially troublesome if the hard-coded password is a blank password, and if the database contains private patient data that is subject to HIPPA regulations.
19. Don’t use cursors, remember, SQL Server is designed to be set-based.
20. Don’t install the SQL Server Developer Edition on a production server. While this may work great in development and testing, the limitation (not to mention the licensing issues) of 10 concurrent connections prevents your application to scale.
21. Don’t use NTFS compression on a SQL Server database.
22. Don’t forget that physical file fragmentation can be a problem for some databases, especially if the database is fragmented into over 100,000 fragments.
23. Don’t create a script that runs in an infinite loop, querying your SQL Server 4000 times a second, just to test if a particular condition has been met.
24. Don’t use cross joins for every query (that joins two or more tables) written for use in a production application, especially when every query is lacking a WHERE clause, resulting in a Cartesian product for every table involved in the join.
25. Don’t forget to always backup your production databases, verify the success of the backups, and then move the backups to a safe location, before any modification are made to the production databases, or before performing any upgrade. If you don’t, and the modification or upgrade fails, you may have to go to your last backup, which may not include the most recent data modifications.
26. Don’t upgrade a production server without first performing a test upgrade in a test environment. Once you are convinced that the upgrade is 100% successful, then perform the upgrade in production. And even then, be sure to have a “back out” plan in place in case the upgrade fails.
27. Don’t forget that on 32-bit editions of SQL Server, extended stored procedures, if not written correctly, can cause memory leaks that can use up all of the available MemToLeave virtual address space, potentially causing system crashes.
28. Don’t run an update script against a production database until you have personally reviewed it, and tested it in a test environment. For example, if you neglect to notice that the way a script modifies an existing table is by first dropping it (with all of its production data still in in), and then creates a newly modified table (with no production data), then you will end up having a very bad day.
29. Don’t use the same SQL Server instance for development, test, and production, especially if the developers have SA access. Just imagine what could happen.
30. Don’t have your SQL Server connected to a light switch, than when it is turned off, also turns off the power to the server. This makes it very difficult for any nightly scheduled jobs to run if the light switch is turned off at 5:00PM every night.

If you know of any other potential SQL Server “mistakes,” please share them here.