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,
• Indexes should not be used on columns that contain a high number of
nULL values.
· Columns that are frequently manipulated should not be indexed.
interview qustions and ansers
https://dotnettutorials.net/lesson/sql-server-indexes-interview-questions-answers/
Indexes
01 What is index in SQL server and advantages of creating indexes in table 01 -Telugu - VLR Training
https://youtu.be/yvs-zVayRj8
Notes
https://www.vlrtrain.com/2022/07/01-what-is-index-in-sql-server-and.html
02 index and heap how data stored in sql server database in telugu
https://youtu.be/OO6s8oSHsUk
https://www.vlrtrain.com/2022/07/02-index-and-heap-how-data-stored-in.html
03 how to create clustered and non clustered indexes in sql server and delete indexes
https://youtu.be/Xil6BKgdbUA
How to Import or restore database in ms SQL server in Telugu - VLR Training
https://youtu.be/5spJcvUnWho
hai all
Please watch below videos and ask if you have doubts
I will arrange one online session