How Index impacts DML Operations

How Index impacts DML Operations in SQL Server

In this article, I am going to discuss how Index impacts DML Operations such as Insert, Update and Delete with examples. Please read our previous article, where we discussed the Non-Clustered Index and the difference between clustered and non-clustered index in detail.

In our previous three articles, we saw how indexes make our search operations faster. As we know whenever a product comes into the market, there should be some advantages as well as disadvantages that exist of that product. This is also the same in the case of Indexes. In this article, we will discuss in detail, how indexes impact the insert, update and delete operations in SQL Server.

Example to understand how Index impacts DML Operations:

In order to understand this, we are going to use the following Employee table. As you can see, the following Employee table has three columns i.e. EmployeeId, and AddressLine. Here, EmployeeId is of type integer and as it is marked as Primary Key, so by default it will create a clustered index. The column AddressLine1 is of type varchar with size 2000.

-- Create Employee table with EmployeeId as Primary Key
CREATE TABLE Employee
(
   EmployeeId INT Primary Key,
   AddressLine1 Varchar(2000)
);

Once you created the Employee table, now insert four records into the table by executing the below query.

INSERT INTO Employee VALUES (101, '1111111111', 'Address1')
INSERT INTO Employee VALUES (102, '2222222222', 'Address2')
INSERT INTO Employee VALUES (103, '3333333333', 'Address3')
INSERT INTO Employee VALUES (104, '4444444444', 'Address4')

Now let us understand, for this type of data type and this type of table, how will internal index structure is formed. To make the things simple and easy to understand, I have put the entire things together (table, data and index structure) in a single image as shown below. As we already discussed index is created based on the nodes i.e. we have a root node, intermediate nodes, and leaf nodes. And in the case of a clustered index, the leaf node actually contains the data which you can see in the below image.

How Index impacts DML Operations in SQL Server

As you can see in the above image, currently the employee table is having two columns. The EmployeeId column is of type integer and hence it size is 8 Bytes and the AddressLine2 column size is 2000 Bytes as declared. That means one record will take 2008 bytes memory space and currently we have four records, so a total of 8032 bytes memory space will be consumed.

The leaf nodes actually store the data in 8KB (approximately 8192 Bytes) page. So, from the 8192 Bytes, the four records actually consume 8032 Bytes i.e. 160 Bytes are free from the above data page.

Now, if you insert the fifth records into the table, then what will happen?

In order to insert a record, it requires 2008 Bytes. But currently, on the data page, only 160 Bytes are available. So, what it will do is, it will create another data page with size 8KB. That means page split happens. To understand this better please have a look at the following image.

Page Split in SQL Server

Page split is nothing but when the current data page is filled with space or space is not enough for the record to insert it will create a new data page and insert that record into the new data page. This is called as Page Split.

So, when your table having too many indexes, then you have too many page splits which affect the performance of your application.

Another Drawback of Index in SQL Server:

Suppose you modify any data using Insert, Update, or Delete statement, then the updated information needs to be updated in the leaf node of all the indexes (i.e. in the index table) where the data has been changed.

Indexes can help us to search and locate the data faster. However, too many indexes in a table can actually hurt the performance of DML operations. So, you need to use the indexes properly to balance the performance.

In the next article, I am going to discuss the Unique and Non-Unique Index in SQL Server with examples. Here, in this article, I try to explain how Index Impacts the DML (Insert, Update, and Delete) operations in SQL Server step by step with one example. I hope you enjoy this article.

Leave a Reply

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