SQL server index

If we have a composite non-cluster index, the order of columns is important. For example, if I create an index on columns A,B,C and I query database filtering based on A,B,C columns, this index will help my query to run faster but if I want to query on B, C columns and not A, this index will not be used by SQL because the index has been created first on column A and if column A is not in the filtering criteria then SQL cannot use this index. If I query on A, B columns and not C because the starting column exists in the where clause then SQL can partially use the index.

If instead of a composite index I create 3 separate indexes on columns A, B and C, then these indexes will help my queries to run faster only if I query single columns like A or B or C and if I have a select statement which filters based on all three columns then those indexes would not be used at all.

If in the select statement we want to return other columns, then we should include all those columns in the Include part of the index, otherwise after finding the row by index SQL needs to issue another query to find those additional columns.