Clustered and Non Clustered index

Question

Post reply

Clustered and Non Clustered index

RGP

SSCarpal Tunnel

Points: 4381
More actions
July 21, 2008 at 12:47 am

#210185

Hi,
Any one please tell me how to use indexes and when to use clustered or non clustered index?
How do you identify that an index is required??
thanks:)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 1

rinu,

go through below links.

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

http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx"> http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx

http://www.odetocode.com/Articles/70.aspx"> http://www.odetocode.com/Articles/70.aspx

Cheers!

Sandy.

--

ChiragNS One Orange Chip Points: 26137 More actions · Answer 2

ChiragNS

One Orange Chip

Points: 26137

July 21, 2008 at 1:06 am

#845569

you have a lot of reading to do 🙂

"Keep Trying"

RGP SSCarpal Tunnel Points: 4381 More actions · Answer 3

Sandy (7/21/2008)
rinu,
go through below links.
http://msdn.microsoft.com/en-us/library/ms188783.aspx"> http://msdn.microsoft.com/en-us/library/ms188783.aspx
http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx"> http://www.sql-server-performance.com/articles/per/index_data_structures_p1.aspx
http://www.odetocode.com/Articles/70.aspx"> http://www.odetocode.com/Articles/70.aspx
Cheers!
Sandy.

http://www.odetocode.com/Articles/70.aspx--good one..thanks

IN_Sandeep SSCertifiable Points: 6259 More actions · Answer 4

rinnuuuuu,

you can see "MSDN" for more details...

and update yourself also "Full text indexing and Xml Indexing"

both indexing are hot topic now in sql....

I personally feel the advantage of fulltext indexing on OLAP side where

Xml Indexing for Xml data...for webservices data.

"all the best..renuu:)"

Cheers!

Sandy.

--

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

That's a big topic. Worthy of a book or two.

When use an index - Anytime you want a query to run without having to scan the entire table to find the rows that it needs. Indexes are created based on the workload. Look at what queries are running, look at what conditions they do the filters on and create indexes to satisfy that.

The clustered index is the index that defines the physical order that the rows are stored in. You only get one clustered index, so it's important to choose carefully. Moving the cluster later can be expensive.

Considerations for the cluster are that it should be narrow (becuase the clusterng key is presenta in all nonclustered indexes), unique (means that SQL won't have to add another column to make it unique), unchanging (because changing the value of the clustering key means moving the row) and ever-increasing (to avoid page splits)

Nonclustered indexes can be wider and often are better wider tha narrow. A wide NC index can better support queries. That said, don't make it wide just because you can. Make sure that some query needs the extra index columns before adding.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

RGP SSCarpal Tunnel Points: 4381 More actions · Answer 6

GilaMonster (7/21/2008)
That's a big topic. Worthy of a book or two.
When use an index - Anytime you want a query to run without having to scan the entire table to find the rows that it needs. Indexes are created based on the workload. Look at what queries are running, look at what conditions they do the filters on and create indexes to satisfy that.
The clustered index is the index that defines the physical order that the rows are stored in. You only get one clustered index, so it's important to choose carefully. Moving the cluster later can be expensive.
Considerations for the cluster are that it should be narrow (becuase the clusterng key is presenta in all nonclustered indexes), unique (means that SQL won't have to add another column to make it unique), unchanging (because changing the value of the clustering key means moving the row) and ever-increasing (to avoid page splits)
Nonclustered indexes can be wider and often are better wider tha narrow. A wide NC index can better support queries. That said, don't make it wide just because you can. Make sure that some query needs the extra index columns before adding.

Thanks for the wonderful explanation 😉 appreciate it