Geek Answers Handbook

The smell of SQL code

Could you list some of the bad SQL practices that newbies do?

I found using a WHILE loop in scripts that can be resolved using set operations.

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

Any other thoughts?

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

thanks

Lijo

+7
sql
source share
5 answers

Here are some of them that I have seen:

  • Using cursors instead of equivalent (and faster) dialing operations (joins, etc.).
  • Dynamic SQL for everyone.
  • Code open for SQL injection attacks.
  • Complete external connection, even if it is not required.
  • Huge stored procedures (hundreds / thousands of lines).
  • No comments yet.
+8
source share

Placing ODBC or dynamic SQL calls throughout the code.

It is often better to define a level of data abstraction that provides access to databases. All SQL code can be hidden in this layer. This often avoids the repetition of such queries and data models are easier to do.

+4
source share

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

I don't like the logic in SQL.

+2
source share

My biggest beef here is unconditionally repetitive SQL. For example, several stored procedures that perform the same connections but different filters.

Using views in such cases can make your database BIG easier to look at and work with.

+1
source share

  • Creating vendor-specific SQL when generic SQL will be used.

  • Dynamically creating tables at runtime (except for TEMPORARY tables).

  • Providing your application with code to create a table or superusers.

0
source share

More articles:


All Articles