Relational Database Systems

SQL Server Programming Guideline and Best Practices

As we all know that it is very important to write good program but few do not write due to lack of proper guideline or they overlook database programming. Intend of this article is provide some of the programming best practices considering performance, quality and maintainability.  Here is top 20 lists:

  1. Do not use any system table name in your program. As you might have observed, every new release of SQL Server come up with list of deprecated table name or discontinued table name. If not deprecated or discontinued then change in structure.

  2. Normalize your data to at least 3rd Form. By saying this does not mean you should compromise on your performance part if you see performance will be much better if you can go one step back in normalization then go ahead. But analyze it before you do so. Technically, query performance gets better if you take a step back in normalization but that can be thumb rule for all queries.

  3. Do not use SELECT *. There are multiple benefit of not doing this. 1) if table structure change and your resultset is not designed to hold new column then your resultset will fail and 2) if you write select statement with column name then it results in less disk IO and less network traffic so ultimately it will boost your query performance.

  4. Use Column Name in Insert Statement. Using column name in insert statement helps avoiding issue when table structure changes.

  5. Do not use Constant or number in Order By clause. If you are new to SQL Server 2008 and you use constant in order by clause then it is very important for you to know that Constant in Order By clause is no longer supported. And using column number in Order By clause impairs the readability of SQL Statement.

  6. Stick to "set based approach". Whenever you are accessing data avoid using "procedural approach". You can easily avoid cursor by writing while loop. I know it is difficult to identity each row uniquely if there is no primary key or unique key defined but I also believe if database designer has done their job properly then every table will have primary or unique key.

  7. Use Temporary Table for Complex Query. I do understand that creating temporary table while processing data contributes in more disk IO but if your query is complex that is joining multiple huge tables then it is better to use temporary tables to stage your data and then get whatever result desired by complex query.

  8. Do not use any functionality that is undocumented. You will come across on website with many undocumented commands but do not use them.Because 1) Microsoft will not provide you support on such command in case of any issue and 2) Microsoft may stop supporting that command in future release.

  9. Prefix Owner name in table name. Qualifying table name with owner name helps in execution plan reuse so it improves readability.

  10. Always use SET NOCOUNT ON. It is very important to use SET NOCOUNT ON in every procedure, trigger, batch etc you write as it improves performance by reducing network traffic.

  11. Always use ANSI Standard Join. *= and =* are no longer supported starting SQL Server 2008. Hence it is important for you to start writing ANSI Standard join if you are still a SQL Server 2000 programmer and used to of using Old style joins.

  12. Do not name your stored procedure starting with sp_. Whenever stored procedure that has prefix sp_ executed database engine first try to locate that stored procedure in master database then it looks for qualifiers supplied. Though practically it is not required to write general purpose stored procedure but if there is a requirement for you write general purpose stored procedure that you would like to execute for any database then prefix stored procedure name with sp_ but store that in master database.

  13. Always write Comment in Stored Procedure. One of your this practice will help other programmer to understand code without any issue. If you are wondering if large length of comment will impact performance then you do not need to be worried about it as it does not impact performance.

  14. Use UserDefined DataType. Analyze your database design and see there is any column that repeats in multiple tables then use UserDefined data type to be consistent. Check Allow Null if that column can have NULL value other wise you shall set not null. You can also think binding rule or default value if needed.

  15. Do not use CHAR data type for Nulllable column. If column can contain null value or multi length value then DO NOT use CHAR data type.

  16. Avoid Dynamic SQL Statement. Whenever you need feel need of dynamic sql statement consider IF or CASE statement to avoid dynamic sql statement. Dynamic SQL Statement is costly than Static sql statement. Dynamic sql statement maximize possibility of SQL Injection attach. If you do not have alternative then writing dynamic sql then refer http://msdn.microsoft.com/en-us/library/bb669091.aspx for Writing Secure Dynamic SQL in SQL Server.

  17. Use unicode for multilingual character. If your database can contain character other than plain english then it is essential for you to use Unicode data type like nvarchar, ntext, nchar etc.

  18. Perform data validation using constraints like foreign key and check constraints. Foreign key and check constraints are much faster than Trigger. You should perform all your referential integrity checks and data validation using these constraints. This will not only provide you faster result but also save your time that you need to spend in writing code for Trigger.

  19. Data type casting and conversion should be offloaded to Front End. Whenever you need to manipulate string, or concatenate string, or type conversion or case conversion

  20. Use Exception/Error Handling.Always handle error properly especially when performing INSERT, DELETE or UPDATE. It will help you in rolling back transactions due to error and will help you in maintaining data consistency. SQL Server does not rollback previous transaction by default if any transaction fails.

Leave a Reply

Your email address will not be published. Required fields are marked *

To Top