Practical use of Indexes.How to check it? [Resolved]

Posted by Oswaldlily under Sql Server on 7/16/2012 | Points: 10 | Views : 1229 | Status : [Member] | Replies : 1
I saw clustered Index automatically formed by using primary key and that can be visible under Indexes in server explorer..
1)Similarly How to view non clustered Index??
2)Is there any query present to view both?
3)How to uses is practically?




Responses

Posted by: Pandians on: 7/16/2012 [Member] [MVP] Silver | Points: 50

Up
0
Down

Resolved
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')

Go
Result
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

Login to post response