SQL server best practices and code review check list

Quite frankly let me share with you that i learned T-SQL programming from one of the worst database design which was designed by me. I never realized my database design is going to work for me or not/...what i did was started with development and created bunch of stored procedures, functions, views.... Every sproc having huge data processing logic, table joins, function calls, so i almost used all the syntax of T-SQL programming (including Transaction handling, temp Tables, Cursors, derived table, sub queries.....etc. ).
I never forget one of my sproc in which i used cursor inside cursor and two temp table for each cursors, and processing huge records in the cursor now you can imagine performance of my sproc. Anyhow first phase of my application was developed and deployed successfully.

Now Time to start with next phase....in the beginning i realized that the current database design is not going to support implementation of a small functionality, so what should i do..create new tables...maintain same data in multiple table..oh no..how bad design!!!!!!

Thank God it was not a customer project it was internal application for training purpose.

Then i started looking at my existing database design and started tweaking..... it took me around two weeks to stabilize my existing database ..changing the sprocs..code and making sure that existing functionality does not breaks.

At the end DB was stabilized...

Now let me make sure you do not make such mistakes....so here are some tips which you can take a look before you finalize your database design.


So guys SQL Server code review is very important...review you database design carefully..below are the steps which will help to ensure a good database design...

Database Design Considerations

• Database Model - Build tables that balance the number of joins (normalization) versus a reasonable amount of denormalization. If you want the application to be highly normalized for the opportunity to extend it for more generic needs, be sure you understand the trade-off of having to join a potentially large number of tables. On the other side of the coin, if your database only has a handful of tables that are 200+ columns with a great deal of duplicate data, you may want to strike a balance.
  • SQL Server 2005 Exposed = Data Modeling Tools
• Primary Keys - Ensure a column in each table is designated as the primary key and select an appropriate data type such as an integer.
  • Finding primary keys and missing primary keys
• Foreign Keys - Validate all of the foreign keys are established to maintain referential integrity among your tables to ensure the proper relationships are maintained.
  • The Importance of Foreign Keys
  • Identify all of your foreign keys in a database
• Auto Increment - If a column needs an incremental value, use an identity for that column as opposed to a custom solution.
  • Managing and maintaining identity values
  • Retrieving identity column properties for all tables in a database
• Correct Data Types - Be sure to select the correct data types for each column. For example, if a varchar column can be used as opposed to a BLOB data type, in general the performance implications will be far less. So choose the data type wisely.
  • Identify All Tables with Columns of a BLOB Data Type
• Indexing - As new queries are written, columns are added or removed, it is a good time to validate that your indexes will support your performance needs. Validate indexes are not unnecessarily duplicated and validate as indexes are added that the intended queries take advantage of the query. If not, think twice about building a new index that SQL Server will need to use, but your new query is not using.
  • Clustered Tables vs Heap Tables
  • Index Analysis Script - All Indexes for All Tables
• Statistics - Validate sufficient statistics are available on a per column basis or validate based on the database configurations that the indexes will be created.
  • The NORECOMPUTE option of UPDATE STATISTICS
• Lookup Tables - As opposed to being forced to hard code values in T-SQL code, lookup the values in a table with the proper referential integrity. Over time the code will be much easier to understand and simpler to maintain.
• Defaults - As applicable, create defaults for the columns to have a meaningful value for the column.
• NULL Usage - Review the NULL usage on a per-column basis and determine if NULL is an applicable value or if a business specific value will be used instead which can be enforced via a lookup table.

General T-SQL Considerations

• Testing Results - Validate the functional testing was completed successfully to ensure you will not face functional issues once the code is deployed and be in a situation where you are asked to change the code on the fly.
  • Testing Options with SQL Server 2000 and 2005
• Response Time Requirements - Validate the performance testing yielded acceptable performance for the new code that is going to be released as well as not negatively impact the existing platform. To me, the only situation worse than having to change code on the fly is the need to firefight a performance issue.
  • Testing Options with SQL Server 2000 and 2005
• Scope of Data - Only process (SELECT, INSERT, UPDATE or DELETE) the needed data. I have seen in a few different applications where SQL Server is flooded with SELECT statements that return unneeded data that is unnecessarily filtered by the front end application. A much more efficient process is to just select the data needed. On another occasion, I have observed duplicate data being unnecessarily inserted into the table. The resolution in this specific scenario is duplicate data that needs to be filtered on the front end.

Stored Procedure Considerations

• General Review - As you review the code, make sure you do not see any of the following:
  • Unnecessary JOINs
  • Inaccurate calculations or functions
  • Unnecessary data
  • Cartesian product
• General Technique - Make sure the general coding practice is reasonable. If possible, use SET based logic as opposed to a cursor or a while statement. In addition, avoid logic that processes data in a temp table as opposed to processing the data directly. For example, there is no reason to load data into a temporary table, update the data in the temporary table, delete the data from the base table and then load the data from the temporary table. Just update the needed data in the base table.
• Query Plan - Review the query plan to validate it is efficient. Validate the following items:
  • Table scanning is not occurring
  • Bookmark lookups are not occurring
  • Code or indexes can be changed to convert index scans to index seeks
• Optimizer Overrides - Review the code to determine if index hints or NOLOCK clauses are really necessary or if a portion of the code can be changed to not try to out smart the optimizer. These hints could be beneficial in the short term, but as the database or code changes as well as when new versions of SQL Server are released, these overrides can be an unexpected exercise when it comes time to upgrade.

  • Notes from the field - Post SQL Server 2000 Service Pack 4 Performance Issues

• Comments - Be sure valuable comments are included with the code; if not, it is rare that comments are added once the code is deployed.
  • Commenting SQL Server T-SQL Code

Read this full article on SQL Server Code Review Checklist on

http://www.mssqltips.com/tip.asp?tip=1303

How to remove an object with specific property value from the List of Objects using LINQ

Suppose you have an object call Employee (having properties EmployeeID and EmployeeName) and you have List of Employee object (List lstEmployee, now if you want to remove a particular Employee object which has EmployeeID lets say 10 from the list lstEmployee, below is the source code which will give some feel of LINQ.

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Collections.Generic;

public partial class _Default : System.Web.UI.Page

{

class Employee

{

public int EmployeeId { get; set; }

public string EmployeeName { get; set; }

}

protected void Page_Load(object sender, EventArgs e)

{

List<Employee> lstEmployee = new List<Employee>();

Employee emp = new Employee();

emp.EmployeeId = 1;

emp.EmployeeName = "Name1";

lstEmployee.Add(emp);

emp = new Employee();

emp.EmployeeId = 2;

emp.EmployeeName = "Name2";

lstEmployee.Add(emp);

emp = new Employee();

emp.EmployeeId = 10;

emp.EmployeeName = "Name10";

lstEmployee.Add(emp);

//Below LINQ is used to get all the employee which is not having employee id 10

lstEmployee = lstEmployee.Where(x => x.EmployeeId != 10).ToList();

}

}

Happy Coding !!!!!!!