What are the most common SQL anti-patterns? [close

Question

All of us who work with relational databases have learned (or are learning) that SQL is different. Eliciting the desired results, and doing so efficiently, involves a tedious process partly characterized by learning unfamiliar paradigms, and finding out that some of our most familiar programming patterns don't work here. What are the common antipatterns you've seen (or yourself committed)?

Answer 1

Using primary keys as a surrogate for record addresses and using foreign keys as a surrogate for pointers embedded in records.

Answer 2

Stored Procedures or Functions without any comments...

Answer 3

Developers who write queries without having a good idea about what makes SQL applications (both individual queries and multi-user systems) fast or slow. This includes ignorance about:

• physical I/O minimization strategies, given that most queries' bottleneck is I/O not CPU
• perf impact of different kinds of physical storage access (e.g. lots of sequential I/O will be faster than lots of small random I/O, although less so if your physical storage is an SSD!)
• how to hand-tune a query if the DBMS produces a poor query plan
• how to diagnose poor database performance, how to "debug" a slow query, and how to read a query plan (or EXPLAIN, depending on your DBMS of choice)
• locking strategies to optimize throughput and avoid deadlocks in multi-user applications
• importance of batching and other tricks to handle processing of data sets
• table and index design to best balance space and performance (e.g. covering indexes, keeping indexes small where possible, reducing data types to minimum size needed, etc.)

Answer 4

• Human readable password fields, egad. Self explanatory.

• Using LIKE against indexed columns, and I'm almost tempted to just say LIKE in general.

• Recycling SQL-generated PK values.

• Surprise nobody mentioned the god-table yet. Nothing says "organic" like 100 columns of bit flags, large strings and integers.

• Then there's the "I miss .ini files" pattern: storing CSVs, pipe delimited strings or other parse required data in large text fields.

• And for MS SQL server the use of cursors at all. There's a better way to do any given cursor task.

Edited because there's so many!

Answer 5

The ones that I dislike the most are

1. Using spaces when creating tables, sprocs etc. I'm fine with CamelCase or under_scores and singular or plurals and UPPERCASE or lowercase but having to refer to a table or column [with spaces], especially if [ it is oddly spaced] (yes, I've run into this) really irritates me.

2. Denormalized data. A table doesn't have to be perfectly normalized, but when I run into a table of employees that has information about their current evaluation score or their primary anything, it tells me that I will probably need to make a separate table at some point and then try to keep them synced. I will normalize the data first and then if I see a place where denormalization helps, I'll consider it.

3. Overuse of either views or cursors. Views have a purpose, but when each table is wrapped in a view it's too much. I've had to use cursors a few times, but generally you can use other mechanisms for this.

4. Access. Can a program be an anti-pattern? We have SQL Server at my work, but a number of people use access due to it's availabilty, "ease of use" and "friendliness" to non-technical users. There is too much here to go into, but if you've been in a similar environment, you know.

Answer 6

For storing time values, only UTC timezone should be used. Local time should not be used.