I created a new table with composite primary key, Lets say PrmID and Type, therefor a new composite clustered index has created (PrmID is first).I add another non clustered index for Type.
My question is - when I generate a queries that perform any statement on Type (such as GROUP BY), is the SQL engine using the non clustered index table or the PK clustered index (which more expensive for that kind of queries) ?
It depends on what the query look like, what columns are accessed, is it covering etc
A simple GROUP BY on type will most likely use the NC index. If you use other columns you may get a bookmap/key lookup or the index will be ignored and you'll have an inefficient PK scan