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

How will you decide the active and passive nodes?

641

---

You have a table with close to 100 million records recently, a huge amount of this data was updated now, various queries against this table have slowed down considerably what is the quickest option to remedy the situation?

486

---

What the different topologies in which replication can be configured?

558

---

What are the different types of backups that exist?

682

---

How do I port a number to sql server?

503

---

---

When does a workload on SQL Azure get throttled?

113

---

How adventureworkslt tables are related?

532

---

How the authentication mode can be changed?

608

---

What are the different authentication modes in sql server? How can it be changed?

568

---

Write the queries for commands like Create Table, Delete table, Drop Table etc.

611

---

Which are the third-party tools used in sql server and why would you use them?

493

---

Where is trigger in sql server?

543

---

Which rendering formats are affected by the pagesize properties?

106

---

What is a dataset and what are the different types of datasets?

93

---

Are there any preferred steps that need to be taken care of before starting the installation of sql server 2000?

576

---