sql - Optimal way of determining number of table entries that are duplicate on particular columns -

i need determine whether particular table rows unique on particular columns. i'm doing using subquery so:

select     t1.id,     (select count(*)         mytable t2         (t2.firstname = t1.firstname) , (t2.surname = t1.surname)     ) cnt mytable t1 t1.id in (100, 101, 102); 

which works fine. however, i'd know if knows of more efficient way of achieving same result using subquery.

i'm doing on azure sql server, way.

you use group this:

select     t1.firstname,     t1.surname,     count(t1.id) cnt mytable t1 t1.id in (100, 101, 102) group t1.firstname, t1.surname order cnt desc 

you can add having cnt > 1 after group clause if want filter dupplicates.

however, depends if need id column well, if do, might have use subquery.

here can find more information on subject: http://technet.microsoft.com/en-us/library/ms177673.aspx