Which will be better composite index or single indexes?

Without seeing the queries that run against the table, absolutely no way to say.

http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/

http://sqlinthewild.co.za/index.php/2009/01/19/index-columns-selectivity-and-equality-predicates/

Thanks for reply.

I am just designing a new database. At the time of index creating, above mentioned points came into my mind that what should be deciding factor for this case. So what will you recommend at your end? Again Thanks 🙂

You can't create useful indexes based only on the table designs. You need to know the queries that will run against the table before you can create indexes to support the queries.

Reading the stuff I referenced to start.

If you're designing the database, focus first and foremost on the clustered index. Make sure that it is the right choice (and big hint, the primary key is not always the best choice for the clustered index). Since the data is stored with the clustered index, make darn sure that for each table the cluster represents the most common path to the data.

When designing a database, I don't generally put nonclustered indexes on until I see more about the queries that are going to be run against the system.

Just to give you a little bit of an answer to your question, whether a compound index or individual indexes, as Gail says, depends on the queries. Let's say you have a query that looks up stuff by LastName. Then an index on LastName is good. But, what if the query looks up stuff by FirstName. Then an index on FirstName is better. What if it looks up stuff based on LastName and Firstname. Then a compound index would be useful. What if it looks stuff up on LastName, but also returns the FirstName. Then, an index on LastName with a FirstName in the INCLUDE column would be useful. As you can see, just randomly picking an index without knowing the query that it satisfies is not the way to go.

The one thing I will say is, if you're filtering on multiple columns, except in very rare circumstances, you're best off using a compound key index. Using individual indexes on each column and then hoping for index intersection (the process of combining indexes) is usually a very poor design choice.