Index

It is Database Object. 

created on table or view.
Fast Searching Data.
It Takes space in Database.
It is organized in a B-Tree Structure.

Syntax

Clustered
Create Clustered Index Ix_IndexName TableName(Column Name)

Non-Clustered
Create NonClustered Index Ix_IndexName TableName(Column Name)
Create Index Ix_IndexName TableName(Column Name)

Disable Index
Alter Index Ix_IndexName On TableName Disable
Alter Index ALL ON TableName Disable ---All index disable on table

Enable Index
Alter Index  Ix_IndexName ON TableName REBUILD

Heap table- No Index on Table.

You will get complete information about this topic in below video,

Clustered Index

1) A Table can have only 1 Clustered Index.

2) A Clustered Index always has Index Id of 0.

3) A Primary Key constraint creates a Clustered Index by default.

4) The leaf node of a Clustered Index contains data pages of the table.

5) Why is it called a clustered index?

In our example, we seen all 'ajit' name entries are one by one, this is the “clustering”, or grouping of similar values, which is referred to in the term “clustered” index.

6) What is a disadvantage to using a clustered index?

A disadvantage to using a clustered index is the fact that if a given row has a value updated in one of it’s (clustered) indexed columns what typically happens is that the database will have to move the entire row so that the table will continue to be sorted in the same order as the clustered index column. 

7) simple UPDATE has turned into a DELETE and then an INSERT – just to maintain the order of the clustered index. For this exact reason, clustered indexes are usually created on primary keys or foreign keys, because of the fact that those values are less likely to change once they are already a part of a table.

8) A comparison of a non-clustered index with a clustered index with an example

As an example of a non-clustered index, let’s say that we have a non-clustered index on the empid column. A non-clustered index will store both the value of the empid and a pointer to the row in the Employee table where that value is actually stored. But a clustered index, will actually store the row data for a particular empid– so if you are running a query that looks for an empid of 5, the data from other columns in the table like name, salary, etc. will all actually be stored in the leaf node of the clustered index itself.

9)A table can have multiple non-clustered indexes because they don’t affect the order in which the rows are stored on disk like clustered indexes.

10) Why can a table have only one clustered index?

Because a clustered index determines the order in which the rows will be stored on disk, having more than one clustered index on one table is impossible. Imagine if we have two clustered indexes on a single table – which index would determine the order in which the rows will be stored? Since the rows of a table can only be sorted to follow just one index, having more than one clustered index is not allowed.

Nonclustered Index

1) 999 Nonclustered Indexes can be created per table.

2) Nonclustered Indexes have Index Id > 0.

3) A Unique Key constraint created a Nonclustered Index by default.

4) A Unique Key constraint can also be enforced by Clustered Index, You can specify the index type while creating Unique Key - we have to use word as unique clustered.

5) Non Clustered Index Contain key value, row Locator contain 

   If table have Clustered Index then it contain Clustering Key.

   If table not having Clustered Index then it contain pointer to the row.(points to Physical Location of the row this is referred to as RID)

Unique Index- 

index key contains no duplicate values as well as searching is fast for that column.

Best Candidate for Unique Index

Aadhar card Number / Social Security Number/ Passport Number / Pan Card Number/

National Insurance Number

it can be bigint / varchar 

Drop Table IF Exists Employee 

Create Table Employee

(

EmployeeId BigInt Identity(1,1),

EmpName varchar(50),

aadharcardNumber Bigint Not NULL 

)

Declare @i INT=1

While (@i<1000)

BEGIN

Insert into Employee(EmpName,aadharcardNumber) values ('Ename'+Convert(varchar(10),@i),125845861452+@i)

Set @i=@i+1

END

1) Using Unique constraint

Drop Table IF Exists Employee 

Create Table Employee

(

EmployeeId BigInt Identity(1,1),

EmpName varchar(50),

aadharcardNumber Bigint Not NULL unique

)

OR

Alter Table Employee

Add Constraint UK_Employee_aadharcardNumber Unique(aadharcardNumber)

2) 

create unique clustered Index IX_emp_aadharcardNumber On Employee(aadharcardNumber)

create unique nonclustered Index IX_emp_EmployeeId On Employee(EmployeeId)

Select *,FORMAT(aadharcardNumber, '####-####-####') Display_aadharcardonUI from Employee where aadharcardNumber=125845861544

Filtered Index 

Person is the Table, Email is the column,

Create Table Person

(

Id Int Identity(1,1) Primary Key,

FirstName Varchar(50) NOT NULL,

LastName Varchar(50) NOT NULL,

Email Varchar(50)

)

Question :

you have make Email column as Unique 

(using unique key or unique constraint its your choice) and 

Email is not mandatory from application side so its allow NULL value.

How to achieve this?

1) Answer

Create Unique Nonclustered Index IX_Person_Email On dbo.person(Email)

--Msg 1505, Level 16, State 1, Line 21

--The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Person' and 

the index name 'IX_Person_Email'. The duplicate key value is (<NULL>).

--The statement has been terminated.

2) Answer

Alter Table dbo.person

Add Constraint Unique_constraint_email Unique(Email)

--Msg 1505, Level 16, State 1, Line 26

--The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.Person' and the index name 'Unique_constraint_email'. The duplicate key value is (<NULL>).

Actual Answer:

Create Unique Nonclustered Index IX_Person_Email On dbo.person(Email) where (Email IS NOT NULL)

it's filtered index.

it maintain uniqueness and NULL value can also allow.

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

Drop Table if exists Person

Create Table Person

(

Id Int Identity(1,1) Primary Key,

FirstName Varchar(50) NOT NULL,

LastName Varchar(50) NOT NULL,

Email Varchar(50) NOT NULL

)

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

Declare @i INT=1

 

While (@i<=500)

BEGIN

Insert into Person (FirstName,LastName,Email) 

Values (

'FirstName_'+Convert(varchar(10),@i),

'LastName_'+Convert(varchar(10),@i),

'FirstName_LastName_'+Convert(varchar(10),@i)+'_gmail.com')

Set @i=@i+1

END

GO

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

--Declare @i INT=501

 

--While (@i<=500000)

--BEGIN

-- Insert into Person (FirstName,LastName,Email) 

-- Values (

-- 'FirstName_'+Convert(varchar(10),@i),

-- 'LastName_'+Convert(varchar(10),@i),

-- NULL)

--Set @i=@i+1

--END

--GO

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

Select * from Person where Email='FirstName_LastName_33_gmail.com'

--Create Unique Nonclustered Index IX_Person_Email On dbo.person(Email) 

Create Table Person

(

Id Int Identity(1,1),

FirstName Varchar(50) NOT NULL,

LastName Varchar(50) NOT NULL,

Email Varchar(50) NOT NULL unique clustered

)