Check it out!
- If a table doesn't have CLUSTERED Index on a table called "HEAP"
- "HEAP" will be converted into "CLUSTERED" once Clustered Index created on that table!
1. Creating a new Table without any Indexe(s)
Create Table IndexSample
(
Id Int Identity(1,1),
Column1 Int,
Column2 Varchar(100)
)
Go
2.Query to findout the Index on a table
Select OBJECT_NAME([Object_id]) [Table],Name [Index Name], Type_desc [Index Type] from sys.indexes Where [Object_id] = Object_Id('IndexSample')
GoResult
Table Index Name Index Type
------ ---------- ----------
IndexSample NULL HEAP
3.Creating Clustered/Nonclustered Index
Create Clustered Index CI_Id On IndexSample(Id)
Go
Create NonClustered Index NCI_Id On IndexSample(Column1)
Go
Query to findout the Index on a table
Select OBJECT_NAME([Object_id]) [Table],Name [Index Name], Type_desc [Index Type] from sys.indexes Where [Object_id] = Object_Id('IndexSample')
Go
Result
Table Index Name Index Type
------ ---------- ----------
IndexSample CI_Id CLUSTERED
IndexSample NCI_Id NONCLUSTERED
Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions
Oswaldlily, if this helps please login to Mark As Answer. | Alert Moderator