suhasini.m (4/8/2009)
When i am creating a new table how to identify whether i should create a Clustered or Nonclustered Index on this table? In terms of performance which one is better?
It's not a case of which is best. Both are usually needed. There can be only one clustered index on a table and it is recommended practive to have a clustered index on every table.
As for nonclustered indexes, create as necessary to support the queries that run against the table.
For some insight into choosing indexes, take a look at these:
http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/
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