Tell Me about Index??????????????????????

Posted by Sabarimahesh under Sql Server on 3/27/2012 | Points: 10 | Views : 1176 | Status : [Member] | Replies : 13
I need Practical Code Explanation

index in Sql??????

What is index????

What is the use of index??????

Life is a Race
Thanks & Regards
By
Sabari Mahesh P M



Responses

Posted by: Rajni.Shekhar on: 3/27/2012 [Member] Bronze | Points: 25

Up
0
Down
when you create a primary key in sql, it will create a cluster index by default on that table. and indexing is used for fast searching.

Thanks,
Rajni Shekhar

Sabarimahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 3/27/2012 [Member] Silver | Points: 25

Up
0
Down
Hi,Sabari Mahesh,
Index is used to make data retrieval process faster .In a table there can be two types of indices . a)Clustered and b)Non-Clustered.
There can be one and only one Clustered index possible where as it can have many non-clustered indices.
Ginerally Clustered index is set on Primary key.Clusterindex physically arranges all the records.
Non-clustered index is generally set on the fields which are used in where clause.It logically arranges record.To user it seems as if records are arranged physically.

Thanks,
Sanjay

Sabarimahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sabarimahesh on: 3/28/2012 [Member] Bronze | Points: 25

Up
0
Down
Rajni.Shekhar

Can You GIve a Code EXample

Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

Sabarimahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sabarimahesh on: 3/28/2012 [Member] Bronze | Points: 25

Up
0
Down
Sksamantaray

Do YOu Have Any Practical Example

Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

Sabarimahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Patel28rajendra on: 3/28/2012 [Member] Starter | Points: 25

Up
0
Down
Hi

for example please visit this link

http://www.componentace.com/sql/create-index.htm

R D Patel

Sabarimahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sabarimahesh on: 3/28/2012 [Member] Bronze | Points: 25

Up
0
Down
Patel28rajendra
Ok

Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

Sabarimahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sksamantaray on: 3/28/2012 [Member] Silver | Points: 25

Up
0
Down

Yes Sabar Mahesh
http://www.mssqltips.com/sqlservertip/1015/index-builds-in-sql-server-2000-vs-sql-server-2005/


Thanks,
Sanjay

Sabarimahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Rajni.Shekhar on: 3/28/2012 [Member] Bronze | Points: 25

Up
0
Down
check example here:

http://msdn.microsoft.com/en-us/library/ms188783.aspx

Thanks,
Rajni Shekhar

Sabarimahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sabarimahesh on: 3/28/2012 [Member] Bronze | Points: 25

Up
0
Down
Sksamantaray

i'll Check

Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

Sabarimahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sabarimahesh on: 3/28/2012 [Member] Bronze | Points: 25

Up
0
Down
Rajni.Shekhar

thanks Man i'll Check And Say You

Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

Sabarimahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/26/2012 [Member] Bronze | Points: 25

Up
0
Down
Indexes are a common way to enhance database performance. An index allows the database server to find and retrieve specific rows much faster than it could do without an index. But indexes also add overhead to the database system as a whole, so they should be used sensibly.
2 types of Indexes
1.Clustered index
2.Nonclustured index

Clustered index
A clustered index sorts and stores the data rows of the table or view in
order based on the clustered index key. The clustered index is
implemented as a B-tree index structure that supports fast retrieval of
the rows, based on their clustered index key values.

CREATE CLUSTERED INDEX MyIndex1 ON MyTable(Column1);

Nonclustered index
A nonclustered index can be defined on a table or view with a clustered
index or on a heap. Each index row in the nonclustered index contains the nonclustered key value and a row locator. This locator points to the data row in the clustered index or heap having the key value. The rows in the index are stored in the order of the index key values, but the data rows are not guaranteed to be in any particular order unless a clustered index is created on the table.

CREATE NONCLUSTERED INDEX MyIndex ON MyTable(Column1,Column2);

Sabarimahesh, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Muhsinathk on: 6/26/2012 [Member] Bronze | Points: 25
Posted by: Muhsinathk on: 6/26/2012 [Member] Bronze | Points: 25

Up
0
Down
Hope you understand it.
Mark as Answer if its helpful to you...

Sabarimahesh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response