A table with a nonclustered index but without primary key or clustered index

Question

A table with a nonclustered index but without primary key or clustered index

peterzeke

SSCrazy Eights

Points: 9003
More actions
January 31, 2011 at 9:21 pm

#234227

I've discovered in one of our databases there is a large table (million rows) that has a non-clustered index but no primary key nor clustered index.
Does a non-clustered index provide any performance benefit without a clustered index or primary key? This question isn't something I've seen addressed in various articles on indexes and performance tuning.
Thanks in advance for some enlightenment
--pete

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

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

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

Maybe. The question isn't is there a clustered index (pk is irrelevant for the discussion), but rather is the NC index useful for the query. If it is, there will be a benefit, if it isn't, there won't.

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

tertiusdp SSCommitted Points: 1569 More actions · Answer 2

I prefer to have clustered indexes on all my tables. I am not saying you should never have a clustered index.

Look at this post for more info.

http://www.sqlskills.com/BLOGS/KIMBERLY/post/The-Clustered-Index-Debate-Continues.aspx

peterzeke SSCrazy Eights Points: 9003 More actions · Answer 3

Thank you for the replies. I was concerned that the nonclustered indexes might merely create overhead without any expected querying benefit. Good to know that it can be ok to not have a clustered index, although rare to see a table without one.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 4

1) check the DMV for index usage to see if the nonclustered index is actually being used

2) you may also want to run a DETAILED index physical stats DMV check on the table (BEWARE hard IO hit here) to check for forwarded records

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

shaikh.kurshid Old Hand Points: 301 More actions · Answer 5

what is dmv ?

can a non- clustered index provides better performance than clustered index.

Pls let me know

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

1) DMV - Dynamic management view

2) Yes

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

peterzeke SSCrazy Eights Points: 9003 More actions · Answer 7

shaikh.kurshid (2/8/2011)
what is dmv ?
can a non- clustered index provides better performance than clustered index.
Pls let me know

Gail Shaw (aka GilaMonster) wrote a very nice three part series of articles on indexes:

http://www.sqlservercentral.com/articles/Indexing/68439/[/url]

What I didn't quite find in her articles (and most articles on indexes in general), though, was whether non-clustered indexes depend on a clustered index for optimized data access. Her response on 2/1/2011 indicates that a non-clustered index can be beneficial regardless of the existence of a clustered index (or at least that's my interpretation of her response.)

So, take a look at here articles on indexes -- they are very informative.

--Pete