03 how to create clustered and non clustered indexes in sql server and delete indexes

https://youtu.be/Xil6BKgdbUA

There are two basic types of indexes:

Clustered Index:

 Data is physically stored in the order of index key.

 There can be only one clustered index on table.

 Leaf level of a clustered index is the actual data.

 It can include multiple columns.

 Creating primary key on particular column creates clustered index on that column.

 A table that has a clustered index is referred to as a clustered table. A table that has no clustered

index is referred to as a heap

Non Clustered Index:

 It does not affect the order of data being stored physically.

 Leaf nodes of a non clustered index contain only the values from the indexed columns and reference

to actual row ( row locators)

 If there is clustered index on table then the row locator points to the clustered index key otherwise

points to actual data row

Composite index:

 It is an index that contains more than one column.

 Both clustered and non-clustered indexes can be composite indexes.

Unique Index:

 It is an index that ensures the uniqueness of each value in the indexed column.

 If the index is a composite, the uniqueness is enforced across the columns as a whole, not on the

individual columns.

 For example, if you were to create an index on the FirstName and LastName columns in a table, the

names together must be unique, but the individual names can be duplicated.

-------------------

Types of Indexes:

1. Clustered Index 2. Non-Clustered Index

Syntax to Create an Index

Create index <Index_Name> on

<table_Name> (Column_Name (ASC | DESC]);

Clustered Index:

A clustered index determines the physical order of data in a table. For this reason a table can have only one clustered index.

However the index can contain multiple columns(a composite index)

Whenever we apply Primary key constraint on a column in a table, then automatically clustered index will applied on primary key column and

clustered Index will arrange the data in Ascending order.

To verify the Index:

Execute sp_helpindex Tbl_Employee;

With the above query it will display the all Index's information which are assigned on that particular Table.

Creating Clustered Index Explicitly: - Syntax:

Create clusteredindex<Index_Name>on <Table_Name> (Colum_Name ASC | Desc, Column-name ASC| Desc);

Non- Clustered Index:

A Non clustered index is analogous to an index in a textbook. The data

is stored in one place, the index in another place. The index will have

pointers to the storage location of data.

Whenever we are applying Unique key constraint on a column in a table

then it automatically creates Non clustered index will be applied on

Unique column.

We can apply more than one non clustered index on a single table.

Non clustered index will not arrange the data in Ascending order.

Syntax:

Create Non Clustered Index <Index_Name> on <Table_Name> (Column-Name);

Create Non Clustered Index IX_Tbl_Employee_Name on Tbl_Employee(Name);

---------------------

NOTE:

Only one clustered index per table, where as you can have more than

one Non clustered index.

Clustered index is faster than a non-clustered index , because the

clustered index has to refer back to the table , if the selected column is

not present in the index.

Clustered index determines the storage order of rows in the table, and

hence doesn't require additional disk space, but whereas a nonclustered index is stored separately from the table, additional storage space is required.

Unique Index:

A unique index does not allow any duplicate values to be inserted into the table.

Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there's an index.

Unique Index is not an separate Index type itself. It's a property.

Syntax:

Create Unique Index <Index_Name> on <Table_Name> (<Column-Name>);

Drop Index:

Drop index<Tble Name> <Index_Name>;

-----------------

Go to Object Explorer (Left side) select the database Name expand the Table folder and Expand the Index Folder click on Index Name and select the Particular Index Name Right click on it select Delete.

Useful Point: -

By default Primary constraints creates a unique clustered index where

as a Unique constraint creates a unique non clustered index. These

defaults can be changed if you wish to.

2. A unique index can't be created on an existing table, if the table

contains duplicate values. To solve this remove the key columns from

the index definition or delete or update the duplicate values.

3. In non-clustered indexes it will occupy more disk space.

When should indexes be avoided?

Although indexes are intended to enhance a database's performance, there are times when they should be avoided.

• Indexes should not be used on small tables.

• Tables that have frequent, large batch update or insert operations,