NonClustred Index on Primary key vs Clustred Index on Primary key

Question

NonClustred Index on Primary key vs Clustred Index on Primary key

krish.sett

SSC Eights!

Points: 974
More actions
July 1, 2011 at 8:40 am

#243102

Hi All,
In my current Project most of the tables have NonClustred index on Primary Key.
please let me know what is the advantage of having NonClustred Index on Primary Key
And also what is the need of NonClustred Unique Index..?

Viewing 10 posts - 1 through 9 (of 9 total)

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

HBK_4700 SSCrazy Eights Points: 8932 More actions · Answer 1

Hi Krish,

Indexes are stored separately from tables, thus overall database size increases. A properly selected index can increase performance. A poorly selected index can actually slowdown your database.

When to use clustered index

Large tables with more than 100 rows

Frequently searched for columns

Columns used in aggregate functions

Columns used by group by and order by

Columns used by JOIN queries

When to use non clustered index

Small tables

Columns rarely used

Columns with long string of data

Columns in which values are updated frequently

http://msdn.microsoft.com/en-us/library/aa933130%28v=sql.80%29.aspx

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

laddu4700 (7/1/2011)
When to use clustered index
Large tables with more than 100 rows
Frequently searched for columns
Columns used in aggregate functions
Columns used by group by and order by
Columns used by JOIN queries
When to use non clustered index
Small tables
Columns rarely used
Columns with long string of data
Columns in which values are updated frequently

The reasons you've listed for a clustered index are actually good uses for a nonclustered index, the places you've listed for nonclustered are actually places you don't want an index at all. Makes no sense to index columns that are rarely used but frequently updated.

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

GSquared SSC Guru Points: 260824 More actions · Answer 3

krish.sett (7/1/2011)
Hi All,
In my current Project most of the tables have NonClustred index on Primary Key.
please let me know what is the advantage of having NonClustred Index on Primary Key
And also what is the need of NonClustred Unique Index..?

There can be an advantage if there's a better clustered index option.

If, for example, you have an Orders table, and the primary key is Order Number, which you use a GUID for in order to expedite Merge Replication, then you probably don't want your clustered index on that column. You might want it on an Order Date column, to make inserts faster, or you might want it on a Customer ID column, to make queries faster on that.

It will really depend on what the usage patterns are for the table, and the data structure.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

krish.sett (7/1/2011)
please let me know what is the advantage of having NonClustred Index on Primary Key

The main reason would be so that you can put the clustered index on a more appropriate column.

And also what is the need of NonClustred Unique Index..?

Take a read through this series (3 parts)

http://www.sqlservercentral.com/articles/Indexing/68439/

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

krish.sett SSC Eights! Points: 974 More actions · Answer 5

Thanks to everyone for the quick response.

However there is one more clarification i need...I.e ..what is the need of NonClustred Unique Index..?

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

For when you want to enforce uniqueness on a column or set of columns that's not the clustered index.

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

krish.sett SSC Eights! Points: 974 More actions · Answer 7

Hi Gail Shaw,

So you mean to say its another way of appling unique constrain ...?

GSquared SSC Guru Points: 260824 More actions · Answer 8

krish.sett (7/1/2011)
Hi Gail Shaw,
So you mean to say its another way of appling unique constrain ...?

It's actually how unique constraints are done by the database. Same thing.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

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

krish.sett (7/1/2011)
Hi Gail Shaw,
So you mean to say its another way of appling unique constrain ...?

Exactly.

The only differences between unique index and unique constraint is the way they appear in the catalog views. Constraints appear in sys.objects (forst class objects), indexes appear only in sys.indexes.

Also you can filter a unique index (SQL 2008+), you can't filter a unique constraint.

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