SQL Query writing: Best practices

Writing effective queries on a RDBMS is something we develop from regular practice of query writing. We discuss here about the SQL RDBMS and some best practices in writing DML statements.

It’s always recommended to have the DML statements inside a transaction. This will avoid unnoticed loss of records and occurrences of orphan records in tables. There is a practice of using multiple transactions so as more control over each statement is possible. There is also possibility in placing the db operation queries in transaction with front end technologies too. So such practices can also be followed while using ORM techniques for db access.

Identifying and normalizing database table structure is important before starting coding or implementation of system. Separate the tables as needed based on the frequency/volume of 1: n relationships

Always set proper relationship between tables, so that we get reliable data always in quicker fashion. Setting proper relationship between tables prevent loss of data to an extent, while playing with db directly.

Theuse of the NOLOCK query hint (equivalent to using the read uncommitted isolation level) is one of the most common practices in T-SQL yet one of the worst. The practice is so common because it makes queries run faster. When using the NOLOCK option, the storage engine will assume your main focus is speed and will therefore prefer the faster option. This can result in double-reading or skipping rows in cases that would typically not be allowed to happen. In short, you get data consistency problems with NOLOCK well beyond getting uncommitted reads.

  Some tips and practices that can be easily followed for better output:

• Do query for only the number of columns you need. This will make sure you are querying the db with only needed columns and any unwanted columns are not fetched, thus performance and requirement met to the best.
• Do NOT reuse queries unless it serves your full purpose.
• Always watch out for the execution plan and measure the time costs.
• Avoid sub queries. Perform Joins or write functions as needed.
• Use proper indexes (for faster search results).
• Always be mindful of NULL occurrences in your data.
• Always use table aliases when your SQL statement involves more than one source. Improves readability, maintainability and make sure you are fetching the right columns.
• Do not use column numbers in the ORDER BY clause. This is not for performance but for usability and scalability.When you develop the query it may not be issue but as time passes by and new columns are added to the SELECT statement or original table is re-ordered if you have used ColumnNumber it may possible that your query will start giving you unexpected results and incorrect ORDER BY.
• Always use a column list in your INSERT statements. This will help in avoiding table change conflicts and if any NOTNULL columns added, the IMPACT can easily be caught
• Don’t ever use double quotes in your T-SQL code.
• Do not prefix your stored procedure names with “sp_”. It’s syntax of the system stored procedures SQL provides us. We should follow a unique naming pattern for user written procedures to distinguish easily.

The formatting of SQL code may not seem that important,but consistent formatting makes it easier for others to scan and understand your code. SQL statements have a structure, and having that structure be visually evident makes it much easier to locate and verify various parts of the statements. Uniform formatting also makes it much easier to add sections to and remove them from complex T-SQL statements for debugging purposes.