Anti Patterns are common in Sql, where programmers used to procedural languages build models that would work well in a procedural environment, but in the Sql environment result in very large numbers of queries or avoidable replication of data.
The AntiPatterns:
- Modeling a tree in SQL using the 'obvious' equivalent of pointers. Down the road you will find you need to perform many many queries, each retrieving just a few rows of data to work with the tree. Solution: TreeInSql. Although BreadthFirstTraversalInSql can make the 'obvious' representation perform well for certain operations.
- Putting all business logic into triggers. (You will hit implementation limits). See WritingUnmaintainableSql for some more anti-patterns. Solution: Just say no.
- ScatterSqlEverywhere. If you're writing server side ASP web pages, your SQL is mixed in with the html formatting. Solution: (1) refactor so that the html formatting is in 'template' files which are read by the asp. (2) Move the text of the SQL queries to the top of the asp file so it is all in one place. {Some of us don't feel that keeping the logic close to where it is used is an overall bad thing. There are some existing topics on this long debate already.}
- Nested queries, where subcategories in a category are retrieved by repeated queries. someone please describe this anti-pattern better!. Solution: (for example) DocQueryInSql.
- Modeling version information in SQL. One 'obvious' solution replicates the information for each 'version'. Another 'obvious' solution keeps a start date and end date for each version. The first wastes storage. The second doesn't accommodate changes in versions well. Solution: TimeSeriesInSql.
- Naive composition of queries from strings. Systems that accept all or part of SQL commands and clauses without validation are a security risk (because one can supply malicious substrings) and a source of dynamic bugs (because a malformed string may only be detected at runtime when the composed query fails). An extreme example are RDBMS or API's that allow multiple SQL commands per API call, since then a malicious substring could actually insert one or more entire SQL commands that are completely unrelated to the base query being composed. Solution: SqlStringsAndSecurity
- Sloppy use of nulls (e.g. if an empty string is needed instead, or if two-valued logic is assumed, etc). Solution: NullsInSql
Related to SIMD parallel processing?
These anti-patterns may be of interest too on SIMD parallel architectures. Just as inefficient SQL queries typically retrieve very small numbers of records for each query, inefficient SIMD procedural code results in large numbers of processors staying inactive.
EditHint: Perhaps merge with MisuseOfSql
CategoryAntiPattern, SqlFlaws
CategorySqlProgramming
Sql Anti Patterns