How do Database Indexes Work? – Master Software Solutions

SQL index is something related to speedup the search in SQL Database. Index allows programmer to retrieve data from database very fast. Suppose you are a student or some book reader. Your book contains 50,000 pages. First day you read some topic “ABC” next day you want to read some another topic “xyz”. you will never manually go through page by page. What you will do in this situation is to use Book index to look the some specific topic and then Jump directly to your topic. Index saved your lots of time to search topic. Same in SQL index, Index allows to search millions of records very quickly from database.

Index speed up the DDL (Data definition Language) but it slow down the speed of DML (Data manipulation Language).

DDL: DDL stands for Data definition Language.It contains following quiries:

CREATE – To create objects in the database.

ALTER – modify the structure of the database.

DROP – delete objects from the database.

TRUNCATE – remove all records from a database table, and also clear all the memory allocation.

COMMENT – add comments to the data dictionary

RENAME – rename an object

DML : DML stands for data manipulation language. It is used to select,insert, update and delete etc.

SELECT – retrieve data from the a database.

INSERT – insert data into a table.

UPDATE – updates existing data within a table.

DELETE – deletes all records from a table, but the memory allocation remain.

MERGE – INSERT operation (insert or update).

EXPLAIN PLAN – explain access path to data.

LOCK TABLE – control concurrency.

How To Create Index??

You need to write following query in SQL database:

CREATE INDEX TableDataIndex ON BLK_TABLE_DATA

1. Single Column Index: Single column index is created only on one column of a table.

   CREATE INDEX index_name ON table_name (column_name);

2. Unique Index: Unique index is not only used to speed up the performance of search but also it does not allow the duplicate values to inserted in the table.

   CREATE UNIQUE INDEX index_name on table_name (column_name);

Lets understand with example:

1. Create Table

   USE TestDB
   GO
   CREATE TABLE SalesTest(
   ID INT IDENTITY(1,1) ,
   ProductCode VARCHAR(20) ,
   Price FLOAT(53) ,
   DateTransaction DATETIME);

2. Sales Test table is created ,now create a stored procedure.

   CREATE PROCEDURE InsertIntoSalesTest
   AS SET NOCOUNT ON
   BEGIN
   DECLARE
   @PC VARCHAR(20)='A12CB'
   DECLARE @Price INT = 50
   DECLARE @COUNT INT = 0
   WHILE @COUNT<200000
   BEGIN
   SET @PC=@PC+CAST(@COUNT AS VARCHAR(20))
   SET @Price=@Price+@COUNT
   INSERT INTO Sales VALUES (@PC,@Price,GETDATE())
   SET @PC='A12CB' SET
   @Price=50 SET
   @COUNT+=1
   END
   END
   EXEC InsertIntoSalesTest

   After creating stored procedure suppose we have inserted 20,000 records.

3. Make a select query, select * from SalesTestAfter making query enable “Actual Execution Plan” by pressing Ctrl+M(SQL Management studio). It shows that logical reads is 1129.
4. Now create an Index on table and make same query again and press Ctrl+M. It shows the logical reads is only 2.