Skip to main content

SQL Server: Coding Standards and best practices

Here I am writing some of coding standard and best practices one should follow while working with database. You can also check for Query Optimization and SQL Tunning

·         Use Pascal notation for SQL server Objects like Tables, Views, Stored Procedures. Also tables and views should have ending “s”. For example: UserDetails etc. Check naming convetion table given below for more details.
·         If “One Table” references “Another Table” than the column name used in reference should use the following rule: Column of another Table: <FirstTableName> Id
·         If you have big subset of table group than it makes sense to give prefix for this table group. Prefix should be separated by _. For example: meeting_Users, meeting_Attendee etc.

     SQL Object
     Pattern
     Example
     Stored Procedure
     sp<Application Name>_[<group name   >_]<action type><table name > Where action is: Get, Delete, Update, Write, Archive, Insert… i.e. verb

      Note: Do not prefix stored procedure names with “SP_”, as “SP_” is reserved for system stored procedures.
     spApplication_GetStudents
     Triggers
     TR_<TableName>_<action><description>
     TR_Student_UpdateFirstName
     Indexes
     IX_<tablename>_<columns separated by_>
     IX_Student_StudentId
     Primary Key
     PK_<tablename> 
     PK_Student
     Foreign Key
     FK_<tablename1>_<tablename2>
     FK_Student_Class
     Default
     DF_<table name>_<column name>
     DF_Student_FirstName
Naming Conventions

·         Do not use reserved words for naming database objects, as that can lead to some unpredictable situations.
·         Avoid the creation of temporary tables while processing data, as much as possible, as creating a temporary table means more disk IO. Consider advanced SQL or views or table variables or derived tables, instead of temporary tables. Keep in mind that, in some cases, using a temporary table performs better than a highly complicated query.
·         Use 'Derived tables' wherever possible, as they perform better. Consider the following query to find the second highest salary from Employees table:
    
SELECT MIN(Salary) FROM Employees
WHERE EmpID IN
(SELECT TOP 2 EmpID FROM Employees
ORDER BY Salary Desc)

The same query can be re-written using a derived table as shown below, and it performs twice as fast as the above query:
SELECT MIN(Salary) FROM
(SELECT TOP 2 Salary
FROM Employees ORDER BY Salary Desc) AS A

·         Practice writing Upper Case for all SQL keywords. For example: SELECT, UPDATE, INSERT, WHERE, INNER JOIN etc.
·         Write comments in your stored procedures, triggers and SQL batches generously, whenever something is not very obvious. This helps other programmers understand your code clearly. Don't worry about the length of the comments, as it won't impact the performance Always create stored procedure in same database where its relevant table exists otherwise it will reduce network performance.
·         Do not query / manipulate the data directly in your front end application, instead create stored procedures, and let your applications to access stored procedure.
·         If you need to write query, try to use parameterized queries.
·         Do not call functions repeatedly within your stored procedures, triggers, functions and batches. For example, you might need the length of a string variable in many places of your procedure, but don't call the LEN function whenever it's needed, instead, call the LEN function once, and store the result in a variable, for later use.
·         Do not use wild card characters at the beginning of word while search using LIKE keyword as it results in Index scan.
·         Avoid using ntext, text, and image data types in new development work. Use nvarchar (max), varchar (max), and varbinary (max) instead.
·         Use Unicode datatypes, like NCHAR, NVARCHAR or NTEXT if it needed, as they use twice as much space as non-Unicode datatypes.
·         Columns with Default value constraint should not allow NULLs.
·         Minimize the use of Nulls. Because they incur more complexity in queries and updates. ISNULL and COALESCE functions are helpful in dealing with NULL values
·         Perform all referential integrity checks and data validations using constraints instead of triggers, as they are faster. Limit the use of triggers only for auditing, custom tasks, and validations that cannot be performed using constraints.
·         Use SET NOCOUNT ON at the beginning of SQL Batches, Stored Procedures and Triggers. This improves the performance of Stored Procedure.
·         Default constraint must be defined at the column level. All other constraints must be defined at the table level.
·         Use graphical execution plan in Query Analyzer or SHOWPLAN_TEXT or SHOWPLAN_ALL commands to analyze SQL queries. Your queries should do an “Index Seek” instead of an “Index Scan” or a “Table Scan”.
·         With Begin and End Transaction always use global variable @@ERROR, immediately after data manipulation statements (INSERT/UPDATE/DELETE), so that if there is an Error the transaction can be rollback.
·         The RETURN statement is meant for returning the execution status only, but not data. If you need to return data, use OUTPUT parameters.
·         Effective indexes are one of the best ways to improve performance in a database application.
·         Views are generally used to show specific data to specific users based on their interest. Views are also used to restrict access to the base tables by granting permission on only views. Yet another significant use of views is that, they simplify your queries. Incorporate your frequently required complicated joins and calculations into a view, so that you don't have to repeat those joins/calculations in all your queries, instead just select from the view.
·         If you have a choice, do not store binary files, image files (Binary large objects or BLOBs) etc. inside the database. Instead store the path to the binary/image file in the database and use that as a pointer to the actual binary file. Retrieving, manipulating these large binary files is better performed outside the database and after all, database is not meant for storing files. However SQL Server 2012 comes with new feature, i.e. FileTable which can help you out in such situations.
·         Avoid dynamic SQL statements as much as possible. Dynamic SQL tends to be slower than static SQL, as SQL Server must generate an execution plan every time at runtime. IF and CASE statements come in handy to avoid dynamic SQL. Another major disadvantage of using dynamic SQL is that, it requires the users to have direct access permissions on all accessed objects like tables and views. Generally, users are given access to the stored procedures which reference the tables, but not directly on the tables. In this case, dynamic SQL will not work.
·         Offload tasks like string manipulations, concatenations, row numbering, case conversions, type conversions etc. to the front-end applications, if these operations are going to consume more CPU cycles on the database server.
·          As is true with any other programming language, do not use GOTO or use it sparingly. Excessive usage of GOTO can lead to hard-to-read-and-understand code.
·         Make sure you normalize your data at least till 3rd normal form. At the same time, do not compromise on query performance. A little bit of denormalization helps queries perform faster.

Popular posts from this blog

C#: Merging Excel cells with NPOI HSSFWorkbook

In this post we’ll see how to merge the two or more cell with each other while creating the excel sheet using NPOI.
Mentioned below is code to merge multiple cells, in this example we are merging first cell to fifth cell of first row (you can adjust row or cell range by passing particular parameters in CellRangeAddress).
//Created new Workbook var hwb = new NPOI.HSSF.UserModel.HSSFWorkbook(); //Create worksheet with name. var sheet = hwb.CreateSheet("new sheet"); //Create row and cell. var row = sheet.CreateRow(0); var cell = row.CreateCell(0); ; //Set text inside cell cell.SetCellValue("This is Merged cell"); cell.CellStyle.WrapText = true; //define cell range address // parameters: -> first row to last and first cell to last cell var cra = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 4); //Add merged region to sheet. sheet.AddMergedRegion(cra);
Hope this solution helps you J

Difference between Web API, WCF and Web Service

So now we have got the basic idea about Web API, now let’s do some comparison of Web API with WCF and web services.
Web Service WCF Web API Web services created as file with .asmx extension. WCF create with .svc extension Web API are simple class file with .cs(for C#) extension. Web API is inherited from “ApiController” and class name must end with “Controller”. It is SOAP based service and return data in XML form. It is also based on SOAP and return data in XML form. Web API is HTTP based service and by default it return data in JSON or XML form. It supports only HTTP protocol. It supports various protocols like TCP, HTTP, HTTPS, Named Pipes, and MSMQ. It supports HTTP protocol. It can be hosted only on IIS. It can be hosted with in the application or on IIS or using window service. It can be hosted with in the application or on IIS. It is not open source but can be consumed by any client that understands xml. It is not open source but can be consumed by any client t…

Calculate Sum and average of two or more Textbox values using JQuery

We can calculate sum and average of values entered in textboxes with the help of small piece of jQuery code.
First of all let’s create a HTML form where we’ll ask user to enter marks in each subject, under body section add following HTML:
<tableclass="demo-table"> <trclass="label"> <tdcolspan="2">Please enter marks(0-99):</td> </tr> <tr> <td>English</td> <td> <inputtype="text"class="txtMarks"name="txtMarks"