SQL with Manoj

 
While working on an official Database or DW/BI project we want to make sure that the Code written by team members should be correct, as per standards, with comments and beautifully indented. But everyone has a different coding style, some are good, but some are not so good, I won’t say bad :). So, to make sure everyone’s code is correct we/you introduce a process of Code Reviews.
 

Here in this post I’ll share some best coding practices and good to have stuff that will help others to better understand your code. The below checklist features some of them:
 

1. Before sending any item for Code Review please make sure to do following actions:
       – Compile the Stored Procedures, Views, T-SQL Scripts.
       – For a SSRS report check the Stored Procedure by running from the Report, and check if you are getting desired output and it meets the requirement.
       – If it’s an Integration SP run it for Full Load, Delta Load, and see if your are getting expected functionality.

2. Before doing the check-ins ensure you do a get latest and resolve all the conflicts.

3. Use TRY-CATCH to handle exceptions, and THROW clause to raise the error in CATCH clause.

4. Use “;THROW” clause instead of RAISERROR() syntax. This is aligned with SQL Server future versions (> 2012) as RAISERROR is deprecated & this will reduce the code from multiple lines to just 1 line.

5. Do not use TRANSACTIONs while creating Reporting or read-only Stored Procedures. Use them only in Integration SPs if the DMLs exceeds by one Query.

6. Do not include #Table processing within TRANSACTIONs, and try to keep them as small as possible.

7. Do not JOIN/APPLY UDFs with other tables, first extract and store records in #Tables then JOIN the #Table with other tables, otherwise it may kill performance.

8. Use “WHERE EXISTS (SELECT * FROM <table>)” while evaluating Sub-Queries, and use “IN()” only while dealing with constant values.

9. Use COALESCE() instead of ISNULL(), its faster & fail proof.

10. Never use ISNULL() on the BIT data type, as it only accepts 1/0/NULL as the possible values.

11. While comparing data against a NULLABLE column, ensure COALESCE is always used to handle records which in fact have NULLs.

12. Don’t use ISNUMERIC(), use TRY_PARSE() instead.

13. Do not mix DDLs & DMLs in a Stored Procedure, try to use DDLs first at the top section of SP, than use DMLs in below section. Otherwise this leads to recompilation of SP on every execution and not use the optimal cached plan.

14. For temporary storage use @Table Variables for small set of records, and use #Tables for larger sets of data. Like to store Parameter values use @Table variables.

15. Always use SET NOCOUNT ON statement in the beginning of SP.

16. Use CTEs instead of Sub-Queries for better code manageability and readability.

17. Handle Divide-by-zero errors the columns/variables occurring in denominator.

18. With WHERE clause put OR conditions within brackets, otherwise they will conflict with other AND conditions and may behave differently.

19. In WHERE clause use columns from a LEFT JOIN table very carefully as it may lead to convert the LEFT JOIN to an INNER JOIN or behave differently.

20. Do not include DROP & CREATE statements in the CREATE table script; they should be added only In the deployment scripts.

21. While designing new tables, ensure most of the columns are created as NOT NULLs.

22. Please do not use “SELECT *” while creating Views, in SPs, instead get only required columns. Even if all Columns are required, list out all the columns instead of “SELECT *”.

23. Do not use USE <db_name> and GO keywords in DB Object (SPs, Views, Table, etc) scripts, should be only used with Custom Pre/Post SQL Scripts.

24. While CREATING or ALTERING any DB object try checking its existence by using IF-ELSE condition like:

  IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ‘TABLE_NAME’ AND TABLE_SCHEMA = ‘SCHEMA_NAME’)
  BEGIN
    CREATE TABLE dbo.TABLE_NAME (…)
  END
 

25. While sending Code Reviews for SSRS RDLs, also mention the URL where the reports are deployed and the changes done, with steps to populate the required params with expected O/P.

I would also like to hear from you what all things you make sure for Code Review process.