SQL Code Smells

Question

Hi Team,

Could you please list some of the bad practices in SQL, that novice people do?

I have found the use of "WHILE loop" in scenarios which could be resolved using set operations.

Another example is inserting data only if it does not exist. This can be achieved using LEFT OUTER JOIN. Some people go for "IF"

Any other thoughts?

Edit: What I am looking for is specific scenarios (as mentioned in the question) that could be achieved using SQL without using procedural constructs

Thanks

Lijo

Answer 1

Here are some I have seen:

• Using cursors instead of equivalent (and faster) set operations (joins etc).
• Dynamic SQL for everything.
• Code that is open to SQL Injection attacks.
• Full outer joins even when they are not needed.
• Huge stored procedures (hundreds/thousands of lines).
• No comments.

Answer 2

Personally for me: anything that is not a plain INSERT, UPDATE, DELETE or SELECT statement

I don't like logic in SQL.

Answer 3

My biggest beef here is definitely repetitive SQL. As an example, multiple stored procedures that perform the exact same joins but different filters.

Using Views in such cases can make your database MUCH easier to look at and work with

Answer 4

Placing ODBC or dynamic SQL calls all over the code.

Often it is better to define a data abstraction layer that provides access to the databases. All the SQL code can hide in that layer. This often avoids replication of similar queries, and makes changing data models easier to do.

Answer 5

1) Creating vendor-specific SQL, when generic SQL would do. 2) Creating tables dynamically at runtime (other than TEMPORARY tables). Among other problems, letting your application code have table create privs is a security risk.