Query for searching duplicates rows on multiple columns

The query below is much more repetitive than @TimSchmelter's solution, but yields a measurable performance advantage for large tables with relatively few duplicates if you happen to have an index on sid, lid, aid, did, shid, soid.

SELECT t.* FROM t INNER JOIN (
  SELECT sid, lid, aid, did, shid, soid, COUNT(1) AS count FROM t
  GROUP BY sid, lid, aid, did, shid, soid HAVING COUNT(1) > 1
) tt ON t.sid=tt.sid AND t.lid=tt.lid AND t.aid=tt.aid AND t.did=tt.did AND t.shid=tt.shid AND t.soid=tt.soid;

You can GROUP BY multiple columns. But since you want to find all duplicates you can use EXISTS:

SELECT
   hID, sid, lid, aid, did, shid, soid, date
FROM TableName t1
WHERE EXISTS
(
   SELECT 1 FROM TableName t2
   WHERE t2.hid <> t1.hid
   AND  t2.sid  = t1.sid   
   AND  t2.lid  = t1.lid    
   AND  t2.aid  = t1.aid   
   AND  t2.did  = t2.did
   AND  t2.shid = t2.shid   
   AND  t2.soid = t2.soid
)

This works in all SQL servers I tried (SQLFiddle):

SELECT * FROM t
WHERE (sid,lid,aid,did,shid,soid) IN (
    SELECT sid,lid,aid,did,shid,soid
    FROM t
    Group By 1,2,3,4,5,6
    HAVING count(1) > 1
)

;with tempTable as
(
select *, row_number() over(partition by sid, lid, aid, did, shid, soid order by hid asc )  as rowid
from #table)
select *
from tempTable
where rowid>1