Tips to improve SQL Server database performance

During database designing and data manipulation we should consider the following key points:

1.           Choose Appropriate Data Type

Choose appropriate SQL Data Type to store your data since it also helps in to improve the query performance. Example: To store strings use varchar in place of text data type since varchar performs better than text. Use text data type, whenever you required storing of large text data (more than 8000 characters). Up to 8000 characters data you can store in varchar.

2.           Avoid nchar and nvarchar

Does practice to avoid nchar and nvarchar data type since both the data types takes just double memory as char and varchar. Use nchar and nvarchar when you required to store Unicode (16-bit characters) data like as Hindi, Chinese characters etc.

3.           Avoid NULL in fixed-length field

Does practice to avoid the insertion of NULL values in the fixed-length (char) field. Since, NULL takes the same space as desired input value for that field. In case of requirement of NULL, use variable-length (varchar) field that takes less space for NULL.

4.           Avoid * in SELECT statement

Does practice to avoid * in Select statement since SQL Server converts the * to columns name before query execution. One more thing, instead of querying all columns by using * in select statement, give the name of columns which you required.
            -- Avoid
               SELECT * FROM tblName
               --Best practice 
               SELECT col1,col2,col3 FROM tblName 

5.           Use EXISTS instead of IN

Does practice to use EXISTS to check existence instead of IN since EXISTS is faster than IN.
          -- Avoid 
         SELECT Name,Price FROM tblProduct 
       where ProductID IN (Select distinct ProductID from tblOrder)
        --Best practice 
       SELECT Name,Price FROM tblProduct 
        where ProductID EXISTS (Select distinct ProductID from tblOrder)

6.           Avoid Having Clause

Does practice to avoid Having Clause since it acts as filter over selected rows. Having clause is required if you further wish to filter the result of an aggregations. Don't use HAVING clause for any other purpose.

7.           Create Clustered and Non-Clustered Indexes

Does practice to create clustered and non clustered index since indexes helps in to access data fastly. But be careful, more indexes on a tables will slow the INSERT,UPDATE,DELETE operations. Hence try to keep small no of indexes on a table.

8.           Keep clustered index small

Does practice to keep clustered index as much as possible since the fields used in clustered index may also used in nonclustered index and data in the database is also stored in the order of clustered index. Hence a large clustered index on a table with a large number of rows increase the size significantly.

9.           Avoid Cursors

Does practice to avoid cursor since cursor are very slow in performance. Always try to use SQL Server cursor alternative.

10.        Use Table variable inplace of Temp table

Does practice to use Table varible in place of Temp table since Temp table resides in the TempDb database. Hence use of Temp tables required interaction with TempDb database that is a little bit time taking task.

11.        Use UNION ALL inplace of UNION

Does practice to use UNION ALL in place of UNION since it is faster than UNION as it doesn't sort the result set for distinguished values.

12.        Use Schema name before SQL objects name

Does practice to use schema name before SQL object name followed by "." since it helps the SQL Server for finding that object in a specific schema. As a result performance is best.

1.      --Here dbo is schema name

2.     SELECT col1,col2 from dbo.tblName

3.     -- Avoid

4.     SELECT col1,col2 from tblName 

13.        Keep Transaction small

Does practice to keep transaction as small as possible since transaction lock the processing tables data during its life. Some times long transaction may results into deadlocks.

14.        SET NOCOUNT ON

Does practice to set NOCOUNT ON since SQL Server returns number of rows effected by SELECT,INSERT,UPDATE and DELETE statement. We can stop this by setting NOCOUNT ON like as:

1.      CREATE PROCEDURE dbo.MyTestProc

2.     AS

3.     SET NOCOUNT ON

4.     BEGIN

5.     .

6.     .

7.     END 

15.        Use TRY-Catch

Does practice to use TRY-CATCH for handling errors in T-SQL statements. Sometimes an error in a running transaction may cause deadlock if you have no handle error by using TRY-CATCH.

16.        Use Stored Procedure for frequently used data and more complex queries

Does practice to create stored procedure for quaery that is required to access data frequently. We also created stored procedure for resolving more complex task.

17.        Avoid prefix "sp_" with user defined stored procedure name

Does practice to avoid prefix "sp_" with user defined stored procedure name since system defined stored procedure name starts with prefix "sp_". Hence SQL server first search the user defined procedure in the master database and after that in the current session database. This is time consuming and may give unexcepted result if system defined stored procedure have the same name as your defined procedure.

Boxing and unboxing in C#.net

                 C# Type System contains three Types , they are Value Types , Reference Types and Pointer Types. C# allows us to convert a Value Type to a Reference Type, and back again to Value Types . The operation of Converting a Value Type to a Reference Type is called Boxing and the reverse operation is called Unboxing.

Boxing

  1:           int Val = 1;

  2:           Object Obj = Val; //Boxing

                  The first line we created a Value Type Val and assigned a value to Val. The second line , we created an instance of Object Obj and assign the value of Val to Obj. From the above operation (Object Obj = i ) we saw converting a value of a Value Type into a value of a corresponding Reference Type . These types of operation is called Boxing.

UnBoxing

  1:           int Val = 1;

  2:           Object Obj = Val; //Boxing

  3:           int i = (int)Obj; //Unboxing

               The first two line shows how to Box a Value Type . The next line (int i = (int) Obj) shows extracts the Value Type from the Object . That is converting a value of a Reference Type into a value of a Value Type. This operation is called UnBoxing.

Boxing and UnBoxing are computationally expensive processes. When a value type is boxed, an entirely new object must be allocated and constructed , also the cast required for UnBoxing is also expensive computationally.

using System;

using System.Windows.Forms;

namespace WindowsApplication1

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void button1_Click(object sender, EventArgs e)

        {

            int Val = 1;

            Object Obj = Val;            //Boxing

            int i = (int)Obj;            //Unboxing

            MessageBox.Show("The value is   : " + i);

        }

    }

}

Remove duplicate records from a table in SQL Server

Create Employee table

create table tbl_employee(Id int identity(100,1) primary key,Name varchar(200),Sal decimal,address varchar(200))

insert data

insert into tbl_employee(Name,Sal,Address)values('adi',20000,'bangalore')

insert into  tbl_employee values('pavan',30000,'bangalore')

insert into tbl_employee values('Ekanath',40000,'bangalore')

insert into tbl_employee values('ravi',13000,'bangalore')

insert into tbl_employee values('sinu',50000,'bangalore')

insert into tbl_employee values('Ekanath',40000,'bangalore')

insert into tbl_employee values('ravi',13000,'bangalore')

insert into tbl_employee values('sinu',50000,'bangalore')

select  data from table

select * from tbl_employee

Output

Remove Duplicate Records

WITH TempEmp (Name,duplicateRecCount)

AS

(

SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Sal ORDER BY Name)

AS duplicateRecCount

FROM dbo.tbl_employee

)

--Now Delete Duplicate Records

DELETE FROM TempEmp

WHERE duplicateRecCount > 1

Output

(3 row(s) affected)

select  data from table

select * from tbl_employee

Output

Get nth highest and lowest salary of an employee table

Create Employee table

create table tbl_employee(Id int identity(100,1) primary key,Name varchar(200),Sal decimal,address varchar(200))

insert data

insert into tbl_employee(Name,Sal,Address)values('adi',20000,'bangalore')

insert into  tbl_employee values('pavan',30000,'bangalore')

insert into tbl_employee values('Ekanath',40000,'bangalore')

insert into tbl_employee values('ravi',13000,'bangalore')

insert into tbl_employee values('sinu',50000,'bangalore')

 select  data from table

select * from tbl_employee

Output

1^st Highest Salary

Select TOP 1 Sal as '1st Highest Salary'

from (SELECT DISTINCT TOP 1 Sal from tbl_employee ORDER BY Sal DESC)

a ORDER BY Sal ASC

Output

1^st Lowest  Salary

Select TOP 1 Sal as '1st Lowest Salary'

from (SELECT DISTINCT TOP 1 Sal from tbl_employee ORDER BY Sal ASC)

a ORDER BY Sal DESC

Output

3^rd Highest Salary (or) N^th Highest Salary

Select TOP 1 Sal as '3rd Highest Salary'

from (SELECT DISTINCT TOP 3 Sal from tbl_employee ORDER BY Sal DESC)

a ORDER BY Sal ASC

Output

3rd Lowest  Salary (or) N^th Lowest  Salary

Select TOP 1 Sal as '3rd Lowest Salary'

from (SELECT DISTINCT TOP 3 Sal from tbl_employee ORDER BY Sal ASC)

a ORDER BY Sal DESC

Output

Get field name, data type and size of database table in sql server

Query

SELECT column_name as 'Column Name', data_type as 'Data Type',

character_maximum_length as 'Length'

FROM information_schema.columns

WHERE table_name = 'tbl_employee'

Output

Difference between int, Int16, Int32 and Int64 in c#

 FCL (framework class library), reference, and value types. This cause bugs and performance issues into the code. In this article, I would like to expose the different behavior of integer type.

int

1.     It is a primitive data type defined in C#.
2.     It is mapped to Int32 of FCL type.
3.     It is a value type and represent System.Int32 struct.
4.     It is signed and takes 32 bits.
5.     It has minimum -2147483648 and maximum +2147483647 capacity.

Int16

1.     It is a FCL type.
2.     In C#, short is mapped to Int16.
3.     It is a value type and represent System.Int16 struct.
4.     It is signed and takes 16 bits.
5.     It has minimum -32768 and maximum +32767 capacity.

Int32

1.     It is a FCL type.
2.     In C#, int is mapped to Int32.
3.     It is a value type and represent System.Int32 struct.
4.     It is signed and takes 32 bits.
5.     It has minimum -2147483648 and maximum +2147483647 capacity.

Int64

1.     It is a FCL type.
2.     In C#, long is mapped to Int64.
3.     It is a value type and represent System.Int64 struct.
4.     It is signed and takes 64 bits.
5.     It has minimum –9,223,372,036,854,775,808 and maximum 9,223,372,036,854,775,807 capacity.

Difference between disabled and read only attributes

Disabled attribute

1.     Disabled form fields or elements values don’t post to the server for processing.

2.     Disabled form fields or elements don’t get focus.

3.     Disabled form fields or elements are skipped while tab navigation.

4.     Some browsers (Like IE) provide default style (Gray out or emboss text) for disabled form fields or elements.

Read Only Attribute

1.     Read Only form fields or elements values post to the server for processing.

2.     Read Only form fields or elements get focus.

3.     Read Only form fields or elements are included while tab navigation.

4.     Some browsers do not provide default style for Read-Only form fields or elements.

Asp.net Enter Key to submit form

Suppose, you want to press/click submit button on Enter key press or you are trying to post the form on Enter key press. In asp.net, to achieve this functionality we need to set "Defaultbutton" property either in Form or in panel.

Form DefaultButton Property

.      <form id="form1" runat="server" defaultbutton="btnSubmit">

    <div>

        <asp:TextBox ID="txtUserID" runat="server" />

        <asp:TextBox ID="txtUserpwd" runat="server" />

        <asp:Button ID="btnSubmit" runat="server" OnClick="btnSubmit _Click" Text="Submit" />

    </div>

    </form>

Panel DefaultButton Property

  <asp:Panel ID="Panel1" runat="server" DefaultButton="btnSubmit">

        <div>

            <asp:TextBox ID="txtUserID" runat="server" />

            <asp:TextBox ID="txtUserpwd" runat="server" />

            <asp:Button ID="btnSubmit" runat="server" OnClick="btnSubmit _Click" Text="Submit" />

        </div>

    </asp:Panel>

 

Note

    1. We specify the defaultbutton property at the Form level in the form tag when there is only one Submit Button for post back.

    2.     We specify the defaultbutton property at the Panel level in the Panel tag when there are multiple Submit Button for post back.