Now I m Going To Explain the Topic Index in sql server , Which May be Help full To All If Any One Has Some Better Explaination Then Explain It
Index Is Used To Incease The Peformace Of Data Base As Every Organisation Has Its DataBase Which Stores Records As Data Increase In data Base Its Performance Reduce . Now To Mantain The Performance Of Data Base We Use The Indexes In dataBase Or On Our Table
Genral Syntex To create Index:
Create [IndexType] Index on (ColumnName)
Where IndexType Means Type Of Index Weather Unique,Clustered And Non Clustered
IndexName Is The Name Of Index
TableName Is The Name Of The Table On Which U Wann To Create Index
ColumnANme Is The Name Of Column Which Becomes Index For The Table
There Are Two Three Types Of Indexes:
1) Unique Index
2) clustered Index
3) Non Clustered Index
Unique Index :
We Can Create Unique Index Simply By Using Query
create Unique index indexName On TableName(ColumnName)
Unique Index Means , A Index Which Does Not Allow Same Index For More Then One Data Row Means No Two Data Row Can Have Same Index Beside It We Can Not Create Unique Index If Data Row Has Duplicate Value
Clustered Index:
We CAn Create The Clustered Index Simply By Using Query as
Create Clustered Index IndexName on TableName(ColumnName)
A Clustered Index Just create index In Which the logical order just points to the physical oreder of the data rows in data table and this index is created with in data table And The Physical Order Of The DataRows Is Dependent Of The Logical Ordering(Index).
Non Clustered Index:
we Can Create Non Clustered Index As
Create NonClustered Index IndexName On Table(Column)
Unlike Clustered Index, In Non Clustered Index The Physical Order of Table Is Independent of Index
Actually, Clustered Index Create a Logic Order Which Identyfy The Physical Order Of Data rows In Table And The LOgic Order Order Is Stored Independent Of The Table Means Any Where On The Disk
We Can Drop The Index If It Requered After Its Creation as
Drop Index Table.IndexName
Here Its Neccessary To Defined The Index Name With The Table Names
We Can Rename The Created Indexes By Using The Existing Stored Procedure as
EXEC sp_rename 'table.oldIndexName','NewIndexName'
Here sp_rename is inbuelt stoed procedure Which Takes Two Parameters First The Index Name With The Table Name Which Is To Be renamed and Second New Index NAme
We Can See The Currently Created Index On Any Table By Using The Inbuelt Stored Procedure as
Exec sp_helpindex table
This Stored Procedure Is Inbuelt Stored Pocedure Which Takes Only One Parameter Which Is Table NAme
And It Gives Coplete Information Of The Indexes Used On The Table
There Is Another Important InBuelt Stored Procedure Which Gives Complete Information About The Table sp_help it takes only one parameter which is table name
exec sp_help table
now it gives complete information about the table passed as parameter
Index Is Used To Incease The Peformace Of Data Base As Every Organisation Has Its DataBase Which Stores Records As Data Increase In data Base Its Performance Reduce . Now To Mantain The Performance Of Data Base We Use The Indexes In dataBase Or On Our Table
Genral Syntex To create Index:
Create [IndexType] Index on (ColumnName)
Where IndexType Means Type Of Index Weather Unique,Clustered And Non Clustered
IndexName Is The Name Of Index
TableName Is The Name Of The Table On Which U Wann To Create Index
ColumnANme Is The Name Of Column Which Becomes Index For The Table
There Are Two Three Types Of Indexes:
1) Unique Index
2) clustered Index
3) Non Clustered Index
Unique Index :
We Can Create Unique Index Simply By Using Query
create Unique index indexName On TableName(ColumnName)
Unique Index Means , A Index Which Does Not Allow Same Index For More Then One Data Row Means No Two Data Row Can Have Same Index Beside It We Can Not Create Unique Index If Data Row Has Duplicate Value
Clustered Index:
We CAn Create The Clustered Index Simply By Using Query as
Create Clustered Index IndexName on TableName(ColumnName)
A Clustered Index Just create index In Which the logical order just points to the physical oreder of the data rows in data table and this index is created with in data table And The Physical Order Of The DataRows Is Dependent Of The Logical Ordering(Index).
Non Clustered Index:
we Can Create Non Clustered Index As
Create NonClustered Index IndexName On Table(Column)
Unlike Clustered Index, In Non Clustered Index The Physical Order of Table Is Independent of Index
Actually, Clustered Index Create a Logic Order Which Identyfy The Physical Order Of Data rows In Table And The LOgic Order Order Is Stored Independent Of The Table Means Any Where On The Disk
We Can Drop The Index If It Requered After Its Creation as
Drop Index Table.IndexName
Here Its Neccessary To Defined The Index Name With The Table Names
We Can Rename The Created Indexes By Using The Existing Stored Procedure as
EXEC sp_rename 'table.oldIndexName','NewIndexName'
Here sp_rename is inbuelt stoed procedure Which Takes Two Parameters First The Index Name With The Table Name Which Is To Be renamed and Second New Index NAme
We Can See The Currently Created Index On Any Table By Using The Inbuelt Stored Procedure as
Exec sp_helpindex table
This Stored Procedure Is Inbuelt Stored Pocedure Which Takes Only One Parameter Which Is Table NAme
And It Gives Coplete Information Of The Indexes Used On The Table
There Is Another Important InBuelt Stored Procedure Which Gives Complete Information About The Table sp_help it takes only one parameter which is table name
exec sp_help table
now it gives complete information about the table passed as parameter
