Find duplicate records based on two columns

Try the Following Code:

      create table ##Employee
      (Fullname varchar(25),
       Address varchar(25),
       City varchar(25))

      insert into ##Employee values
     (    'AA',          'address1',    'City1')
    ,(    'AA',          'address3',    'City1')
    ,(    'AA',          'address8',    'City2')
    ,(    'BB',          'address5',    'City2')
    ,(    'BB',          'address2',    'City1')
    ,(    'CC',          'address6',    'City1')
    ,(    'CC',          'address7',    'City2')


      select E.* from ##Employee E
      cross apply(
      select Fullname,City,count(Fullname) cnt from ##Employee
      group by Fullname,City
      having Count(Fullname)>1)x
      where E.Fullname=x.Fullname
      and E.City=x.City

Instead of a grouped COUNT you can use it as a windowed aggregate to access the other columns

SELECT fullname,
       address,
       city
FROM   (SELECT *,
               COUNT(*) OVER (PARTITION BY fullname, city) AS cnt
        FROM   employee) e
WHERE  cnt > 1