How to choose appropriate data types to create indexes on sql-server tables

How to choose appropriate data types to create indexes on sql-server tables

As we know Indexes are very useful in tables and increases the overall searching performance, But developer also aware about that indexes are like a “Sweet poison” because creating the indexes are not only the solution to increase the performance and sometimes indexes decrease the performance.

So this is a big challenge and confusing task to take decisions that what data type is appropriate to create index on column.Sql-Server provide various data types like “Int, Varchar, Nvarchar,DateTime” etc, but question arise which data type should I choose? What implications might the choice of a data type have for storage and performance in SQL Server?

Now i am going to demonstrate how to select data type for creating the indexes on columns.

Step 1: first we have to create a sample database table having name “Sales” and have to insert approx 200000 records(prefer a large amount of data to test the performance issue).

CREATE TABLE [dbo].[Sales]( [BatchNo] [varchar](50) NOT NULL, [Price] [float] NULL, [DateTransaction] [datetime] NULL, [ProductId] [nvarchar](50) NULL )

after adding the details looks like as per below:

data

Step 2: Create clustered index on column “Product Id” by creating primary key and execute sql command then see the message tab.

table structure

execute below sql command:

set statistics IO on select ProductId,BatchNo,DateTransaction from sales where ProductId= 55005

You can see in below message  by clicking on Message tab from the query output window and it shows that Sql database engine takes 1309 logical reads to find the desired result.

(200000 row(s) affected) Table ‘Sales’. Scan count 1, logical reads 1082, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. (1 row(s) affected) you can also see the below image

table structure

Step 3: initially data type of the column “productId” was nvarchar now change data type from “Nvarchar” to integer and save the table. Again run the same query and see the result.

table structure

execute below sql command:

set statistics IO on select ProductId,BatchNo,DateTransaction from sales where ProductId= 55005

but this you can see the difference in output as compare to previous one.this time Sql-server database engine trace only 3 logical reads and below message comes:

(1 row(s) affected) Table ‘Sales’. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

You can clearly see in above result that Sql database engine takes only 3 logical reads while for nvarchar it takes 1309 logical reads.This is a big difference and assume if this searching performs on very large table like table having Lacs of data with complex data type columns then you can really feel better performance and difference. I have recorded this figure in excel and you can see that there are big difference between the performance with two data types. Capture

The main reason behind this, sql engine manipulate faster on numeric values rather than string values and so be careful whenever you have to take decision to create index then always prefer integer type column. So always try to reduce the logically reads by database engine by applying correct indexes and select correct data type.

Advertisements