RDBMS and SQL – Basic Rules-2

This is a continuation post that describes the basic rules of RDBMSs and SQL, for the previous post click here. In this post, we will be discussing SQL basic rules.

Before performing the query in the database server, it must be created according to best practices. If a test or staging environment is available, the query should be run there to see how much resources it consumes. It’s far better to optimize queries ahead of time than to discover them during a performance troubleshooting.

Once all of the tests have been completed, they can be run in the production environment. However, because most SQL engines generate a query plan, the initial execution takes a long time.

The best practices listed here aren’t just about optimization; they also include knowing how to write code in a legible manner.

Here are the best practices:

All reserved words in the database, such as sub-language commands, system built-in functions, operators, clauses, and so on, should be capitalized.
To define functionality in SQL, comments should be utilized. The comments must be formatted as /* text> */.
Every nested statement must start with a new indentation level.
The WHERE clause must be used to guarantee that only the relevant data is retrieved from the database.
To prevent wasting resources, one must retrieve the appropriate column data from the database, similar to the example above. Specify the column names instead of the asterisk (*) in the SELECT statement.
Wherever possible, avoid SORTING. The ORDER BY clause frequently depletes system resources.
Use UNION ALL instead of UNION because UNION ALL is much faster. Note that, UNION will remove any duplicate rows, but UNION ALL will not.
When the subquery results are huge, the EXISTS clause is much faster than IN. When the number of subquery results is modest, the IN clause is faster than EXISTS. Also, the IN clause cannot compare anything with NULL values, whereas the EXISTS clause can.
Dots, spaces, and dashes should not be used in the database, schema, table, or column names.
When generating aliases, utilize the AS keyword to make the code easier to read.
Give tables and column names that are illustrative. Better, if the names are self-explanatory.
Use the data types that are appropriate for the data. Convert Unix timestamps and strings into “date-time” rather than storing them as dates.
Steer clear of over-normalization. There is no need for separate tables for dates, zip codes, locations, cities, or nations. Split them only when it is mandatory.
If the name of the table or column must include more than one word, use CamelCase or an underscore to separate the words.
Avoid naming a table and a column the same thing.