Answer Posted / inder kumar singh

1) If all coumns are duplicate
create tablet tbl_deleteDuplicate
(
emp_id numeric,emp_name varchar(100)
)

insert into tbl_deleteDuplicate values(1,'a')
insert into tbl_deleteDuplicate values(1,'a')
insert into tbl_deleteDuplicate values(2,'b')
insert into tbl_deleteDuplicate values(3,'c')
insert into tbl_deleteDuplicate values(4,'d')
insert into tbl_deleteDuplicate values(4,'d')
insert into tbl_deleteDuplicate values(5,'e')
insert into tbl_deleteDuplicate values(5,'e')
insert into tbl_deleteDuplicate values(6,'f')

set rowcount 1
while @@rowcount > 0
delete a from tbl_deleteDuplicate a where
(select count(*) from tbl_deleteDuplicate b
where a.emp_id = b.emp_id)>1
set rowcount 0
select * from tbl_deleteDuplicate
set nocount off

2) if value column is duplicate

truncate table tbl_deleteDuplicate
insert into tbl_deleteDuplicate values(1,'a')
insert into tbl_deleteDuplicate values(2,'a')
insert into tbl_deleteDuplicate values(3,'b')
insert into tbl_deleteDuplicate values(4,'c')
insert into tbl_deleteDuplicate values(5,'d')
insert into tbl_deleteDuplicate values(6,'d')
insert into tbl_deleteDuplicate values(7,'e')
insert into tbl_deleteDuplicate values(8,'e')
insert into tbl_deleteDuplicate values(9,'f')
insert into tbl_deleteDuplicate values(10,'f')
insert into tbl_deleteDuplicate values(11,'f')

delete tbl_deleteDuplicate where emp_id not in
(
select max(emp_id) from tbl_deleteDuplicate group by
emp_name having count(emp_name)>1
)
and emp_id not in
(
select max(emp_id) from tbl_deleteDuplicate group by
emp_name having count(emp_name)=1
)

select * from tbl_deleteDuplicate

What are the approximate numeric data types?

946

---

Tell me what is difference between clustered and non clustered index?

946

---

How to check table values in sql server?

959

---

What is a View ? Can we insert, Update and delete a view?

997

---

How many types of local tables are there in sql server?

934

---

What are diverse clauses that form a part of sql?

1072

---

How would you use user_constraints table in DB?

1009

---

Tell me about builtinadministrator?

1002

---

What are the rendering extensions of ssrs?

157

---

What are orphan records?

935

---

What have included columns when we talk about sql server indexing?

1039

---

Which are the important points to note when multilanguage data is stored in a table?

1004

---

What are the types of joins in sql?

1131

---

How to read data in a table with "select" statements?

976

---

What is “asynchronous” communication in sql server service broker?

1039

---