If you have to use a non-clustered index (because your single clustered index can be used better elsewhere in a table), and if you know that your application will be performing the same query over and over on the same table, consider creating a covering index on the table. http://www.sql-server-performance.com/covering_indexes.asp ---------------------------------------- Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com
A composite index is an index that is made up of more than one column. In some cases, a composite index is also a covering index. (as referred by Dinesh's link). IMHO, composite indexes (with the exception of covering indexes) should be avoided. This is because composite indexes tend to be wide, which means that the index will be larger, requiring more disk I/O to read it, hurting performance. Satya SKJ Microsoft SQL Server MVP Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com This posting is provided AS IS with no rights for the sake of knowledge sharing.
CREATE INDEX ... ON Table(all involved column go here) -- Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:http://www.insidesql.de
Lookup at Books online too for code examples. quote:Originally posted by sramesh ok tell me how to create a covering index? Thanks S. Ramesh Satya SKJ Microsoft SQL Server MVP Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com This posting is provided AS IS with no rights for the sake of knowledge sharing.
