Indexes in SQL Server

Indexes in SQL Server with Examples

Indexes in SQL Server with Examples

In this post I will explain all about Indexes in SQL Server with Examples, Although this part is too small. So let’s start –

Indexes in SQL Server with Examples

What are Indexes in SQL Server?

An Index is a special disk or data structure associated with a table or view that speeds up the query performance to the retrieval of rows from the table or views. An index contains keys built from one or more columns in the table or view. These keys are stored in a structure (B-Tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently. In other words we can say that Indexes are used to retrieve data from the database very fast, users can not see the indexes physically because indexes are just used to speed up searches or query performance. Microsoft SQL Server provides two types of indexes –

  1. Clustered Index (like Dictionary)
  2. Non Clustered Index (like Book Indexes)

If we use Primary key constraint in any table then it creates a default clustered index internally and if we don’t give the name of an index while creating index then it will be non-clustered index ideally as Non Clustered index is default index. In this part, we will learn everything that we need to know about indexes to come up with a good index strategy and optimize our queries.

Syntax (Indexes in SQL Server):

CREATE [NONCLUSTERED|CLUSTERED] INDEX index_name

ON schema_name.table_name (COL_LIST)

[WITH(SORT-IN_TEMPDB=ON/OFF)] DEFAULT

In the above syntax:

  1. First specify the name of the Index (Clustered / Non Clustered) after the CREATE CLUSTERED / NONCLUSTERED INDEX clause.
  2. Second specify the schema and table name on which we want to create the index.
  3. Third list one or more columns included in the index.

Clustered Index

Clustered indexes sort and store the data rows in the table or view based on their key values. These are the columns included in the index definition, there can be only one clustered index in each table because the data rows themselves can be stored in only one order. When a table has a clustered index then the table is called a clustered table. If a table doesn’t have a clustered index then its data rows are stored in a UN ordered structure called the heap. So the data rows in a table will be stored in sorted order when the table contains a clustered index.

The following statement creates a new table name Items that consists of two columns Item_ID and Item_Name

CREATE TABLE Items (
Item_ID INT NOT NULL,
Item_name VARCHAR(100))

Now we will insert some values into this table –

INSERT INTO Items (Item_ID, Item_name)
VALUES
(1,’Life’),
(2,’MS Excel’),
(3,’MS SQL’),
(4,’PostgreSQL’),
(5,’SurveyCTO’),
(6,’Technology’),
(7,’Visual Basic’),
(8, ‘WordPress’),
(9,’Videos’),
(10,’YouTube’)

In the above table Items don’t have a primary key therefore SQL Server stores its rows in an unordered structure called the heap.

When we will execute the query data from the Items table, the query optimizer needs to scan the whole table to locate the correct one. for example, we will write a statement to find the item whose id is 4 then we will run below query –

SELECT * FROM Items WHERE Item_Id = 4

If we want to display the estimated execution plan in SQL Server Management Studio then we have to press ctrl+L or click on Display Estimated Execution Plan (Ctrl+L) button the third button after Debug above query window. Now we can see how SQL Server came up with the following query plan –

Indexes in SQL Server with Examples - QO

Because the Items table has only 10 rows and the query will execute very fast. However if the table contains a large number of rows then it will take a lot of time and resources to search for data.

To resolve this issue, we can use the indexes

When we will create another table with a primary key then SQL Server automatically creates a corresponding clustered index based on columns included in the primary key like mentioned below –

CREATE TABLE Price (
Item_ID INT,
Price MONEY NOT NULL,
PRIMARY KEY(Item_ID))

Now we can easily see all the columns created along with the clustered index in below snapshot –

Indexes in SQL Server with Examples - PK

Clustered Indexes in SQL Server

The syntax for creating a clustered index is as follows –

CREATE CLUSTERED INDEX index_name

ON schema_name.table_name (column_list)

In above syntax:

  1. First specify the name of the clustered index after the CREATE INDEX clause.
  2. Second, specify the schema and table name on which we want to create the index
  3. Third, specify the list one or more columns included in the index.

CREATE CLUSTERED INDEX statement using SQL Server

In case a table does not have a primary key which is very rare but we can use the CREATE CLUSTERED INDEX statement to define a clustered index for that table.

CREATE CLUSTERED INDEX ix_items_id
ON Items (Item_ID)

Now if we open the indexes node under the table name then we will see the new index name ix_items_id with type Clustered is available there as shown in below snapshot –

Indexes in SQL Server with Examples - CI

When we execute the following query statement SQL Server will use Clustered Index seek to locate the row, which is faster than scanning the whole table.

SELECT * FROM Items WHERE Item_ID = 4

Indexes in SQL Server with Examples - QO

If we want to add a primary key constraint to an existing table that already has a clustered index then Microsoft SQL Server will enforce the primary key using Non Clustered Index, In my case, there is one table name – Items which we have created earlier and that have a clustered index on it but there is no primary key defined, so let’s implement this thing on that table with the help of below query –

ALTER TABLE Items
ADD PRIMARY KEY(Item_ID)

Indexes in SQL Server with Examples - PK

Non Clustered Indexes in SQL Server

Non Clustered Indexes have a structure separate from the data rows. Non clustered index contains the non clustered index key values and each key-value entry has a pointer to the data row that contains the key value. Pointer from an index row in a non clustered index to a data row is called a row locator. The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table. For a heap, a row locator is a pointer to the row. For a clustered table, the row locator is the clustered index key. We can add non-key columns to the leaf level of the non clustered index to by-pass existing index key limits and execute fully covered indexed queries. Similar to a clustered index, a non clustered index uses the B-Tree structure to organize its data. A table may have one or more non clustered index and each non clustered index may include one or more columns of the table.

If any table is a clustered table, the row pointer is the clustered index key then that table is a heap, the row pointer points to the row of the table.

CREATE NON CLUSTERED INDEX Statement:

CREATE NONCLUSTERED INDEX index_name

ON table_name (column_list)

In above syntax:

  1. First, specify the name of the index after the CREATE NONCLUSTERED INDEX clause, it should be noted here that the NON CLUSTERED keyword is optional here.
  2. Second, specify the table name on which we want to create the index.
  3. Third specify the list of columns of that table as the index key columns

To show the examples we will use the Price table here who has the two columns like – Item_ID and Price, as we remember this is a clustered table because it has a primary key Item_ID

CREATE NON CLUSTERED INDEX USING SQL SERVER FOR ONE COLUMN:

We will display the price which id is 4, in this if we want to display the estimated execution plan, then we will see the query optimizer scans the clustered index to find the row, this is because the Price table does not have an index for the price column.

Indexes in SQL Server with Examples - CI

To improve the speed of this query we can create a new index like ix_price_price for the price column

Syntax:

CREATE INDEX ix_price_price
ON dbo.price(price)

After execution of the above query, we can see that the NON-CLUSTERED INDEX name ‘ix_price_price’ has been created successfully as shown in below snapshot –

Indexes in SQL Server with Examples

Now if we display the estimated execution plan of the above query again, we will find that the query optimizer uses the Non-Clustered Index ‘ix_price_price’ as shown in below snapshot –

Indexes in SQL Server with Examples - NCI

When we create a NON CLUSTERED INDEX that consists of multiple columns, the order of the columns in the index is very important. We should place the columns that we often use to query data at the beginning of the column list

Therefore it is a good practice to place the columns that we often use to query data at the beginning of the column list of the index.

Both the clustered and non clustered indexes can be unique, means no two rows can have the same or duplicate values for the index key, otherwise the index is not unique and multiple rows can share the same key value. Indexes are automatically maintained for a table or view whenever the table data is modified.

Indexes and Constraints

Indexes are automatically created when PRIMARY KEY and UNIQUE KEY constraints are defined on table columns, means when we create a table with Unique key constraint, the database engine automatically creates a non clustered index internally. If we configure a Primary Key constraint then the database engine automatically creates a clustered index, unless a clustered index already exists. When we try to enforce a Primary Key constraint on an existing table and a clustered index is already exists on that table then the SQL server enforces the primary key using a non clustered index.

How Query Optimizer uses the Indexes

When any query is executed then the Query Optimizer evaluates each available method for retrieving the data and selects the most efficient method. The method may be a table scan or maybe scanning one or more indexes if they exist.

When QO(Query Optimizer) performing a table scan then it reads all the rows in the table and extracts the rows that meet the criteria of the query. A table scan generates many disk I/O(Input/Output) operations and can be resource-intensive. However a table scan could be the most efficient method if the result set of the query is the high percentage of rows from the table.

When QO(Query Optimizer) uses an index, it searches the index key columns finds the storage location of the rows needed by the query, and extracts the matching rows from that location. Generally searching the index is much faster than searching the table because unlike a table an index frequently contains very few columns per row and the rows are in sorted order.

As per the above examples we can observe that well-designed indexes can reduce disk I/O(Input/Output) operations and consume fewer system resources, therefore, improving query performance. Indexes can be helpful for a variety of queries that contain SELECT, UPDATE, DELETE or MERGE statements.

Renaming of an Index into Microsoft SQL Server using SQL Query

The sp_rename is a system stored procedure that allows you to rename any user create an object in the current database including table, index, and column.

Syntax:

EXEC sp_rename old_index_name, new_index_name, N’INDEX’

Renaming of an Index into Microsoft SQL Server using SQL Server Management Studio(SSMS)

To change the name of an index to the new one using the SQL Server Management Studio (SSMS), we should follow these steps –

  1. Navigate to the databases at the left side of SQL Server Management Studio (SSMS)
  2. Expand the databases object and expand your own named database
  3. Expand the Tables under the named database.
  4. Expand the exact table name and user table expand the Indexes then we can see all indexes which are created on that table as shown in below snapshot –
Indexes in SQL Server with Examples - Rename

As shown in the above snapshot we can right-click on the index which we want to change the name and choose the rename menu item and then give the right name which we want and we have press enter button, required name of the index will be changed successfully.

Disabling of an Index into Microsoft SQL Server using SQL Queries

To disable an Index we can use the ALTER INDEX statement as follows –

Syntax:

ALTER INDEX index_name

ON table_name

DISABLE

If we want to disable all indexes of a table then follow the below syntax –

ALTER INDEX ALL

ON table_name

DISABLE

Note: If we disable an Index then query optimizer will not consider that disabled index for creating query execution plans. When we disable an index on a table, SQL Server keeps the index definition in the metadata and the index statistics in non clustered indexes. However if we disable a non clustered or clustered index on a view, SQL Server will physically delete all the index data.

If we disable a clustered index of a table, we can not access the table data using DML (Data Manipulation Language) such as SELECT, INSERT, UPDATE and DELETE until we rebuild or DROP the Index.

Enabling of an Index into Microsoft SQL Server using SQL Queries

Sometimes we need to disable an index before doing a large update on a table. by disabling the index we can speed up the update process by avoiding the index writing overhead. After completing the update to the table we need to enable the index. Since the index was disabled we can rebuild the index but can not just simply enable it because after the update operation the index needs to be rebuild to reflect the new data in the table –

ALTER INDEX index_name

ON table_name

REBUILD

Statement to enable the disabled index and recreate it –

CREATE INDEX index_name

ON table_name(column_list)

WITH (DROP_EXISTING = ON)

Statement to enable all disabled indexes on a table –

ALTER INDEX ALL ON table_name

REBUILD

DBCC DBREINDEX Statement to enable an index on a table –

DBCC DBREINDEX (table_name,index_name)

DBCC DBREINDEX Statement to enable all indexes on a table –

DBCC DBREINDEX (table_name, ” “)

DROP AN INDEX USING SQL QUERY:

Syntax:

DROP INDEX (IF EXISTS] index_name
ON table_name

Assuming this tutorial post has well-acquainted aspiring to the complete requirement.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc as it may help some other needy peoples too.

Leave a Reply