Sql Server Interview questions part 1

http://www.allinterview.com/Interview-Questions/SQL-PLSQL.html
1. wat new abt truncate in sql server ?

Ans: 

While using TRUNCATE stmt, WHERE clause can not be used.
When we use TRUNCATE stmt, Trigger does not get fire. After
using TRUNCATE stmt, all the deleted rows are not entered in
the transaction log file(.ldf)

2.

how to find the second salary?

Answer # 10

Here we can find out 2nd highest salary in many ways, according to the situation we can select anyone… 1st Type: select min(esal) from emp where esal in (select top 2 esal from emp order by esal desc) 2nd Type: select max(esal) from emp where esal not in(select max(esal) from emp) 3rd Type: select max(esal) from emp where esal <(select max(esal) from emp ) 4th Type: select Max(esal) from EMP a where 2=(select COUNT(distinct(esal)) from EMP b where a.eSAL<=b.eSAL);

3.

Re: Advantages and disadvantages of stored procedures.
Answer # 1	The only disadvantage I see with Stored-procs is that we cannot use them with select statements.

4.

how many instance use in sql server 2005
Answer # 1	normally sql sever 2000 16 instance possible in 2005 50 instance possible

5.

Re: In join, which clause in not used?
Answer # 1	Order Clause is not used in joining two tables.

6.

What is Peer to peer Replication?

Answer # 1

Peer-to-peer replication (also known as multimaster replication) is a configuration that has the following characteristics: Replication occurs between tables on two or more servers. Updates on any one server are replicated to all other associated servers. Applications on any of the servers can update the same rows and columns in those tables at the same time. All servers are equal peers with equal ownership of the data; no server is the "master" or source owner of the data.

7.

I have to display ten columns values from diffrent ten tables. how many joins are require?
Answer # 7	there are 3 answeres 1. No joins are required if do not want to display related data 2. 9 joins are requred if all tables are related 3. no joins reuquired we can use union if condition is like following select cola from a union select colb from b union select colc from c . . . like wise ten statements

8 .

i have table students with fields classname,studname
select * from students
classname  studname
1           xxxxx
1           yyyy
1           zzzz
2            qqqq
2             tttt
3             dsds
3             www
i want the output should be

No of students in class 1 : 3
No of students in class 2 : 2
No of students in class 3 : 2 

ANS: 
  select 'No of students in class '+ CONVERT(VARCHAR,ClassName)+' : '+ convert(varchar,Count(studname))  from students 
group by Classname order by classname  

9..

how to update a null value field in sql server

eg
a table contains 3 fields id,name,salary
and 3 records 
salary of 1 record is null 
i want update the nullfield

111 arun 300
112 ddd  200
113 ttt null
i want to update table with add 100 to every record include null
after updation 
the recrds should be
111 arun 400
112 ddd  300
113 ttt 100 

ANS:  create table #temp (eid int, names varchar(10),sal int)

insert into #temp values (111, 'arun', 300)
insert into #temp values(112, 'ddd', 200)

insert into #temp values(113,'ttt',null)

select * from #temp 

update #temp 
set sal = isnull(sal,0)+ 100

select * from #temp

ANS2:  

  update tablename set salary=100 where salary is null

10.

What is cursor ? And what is difference between Trigger ?

Answer # 2

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. Trigger is a database object invoked automatically when any event occured. These aevents are Delete, Insert, Update etc.... SQL server store the intermediate data into to dummy table INSERTED and DELETED... At the time of insert operation SQL Server STores inserted rows into the INSERTED table and at the time of delete or update SQL Server stores the information into DELETED dummy table

11. 

how to delete duplicate rows from table in sql server

ANS 1  
delete  tblname where columname  in (select columnname 
FROM  tblname  GROUP BY  columnname having count(columnname)
>=2)

ANS 2

CREATE TABLE dbo.duplicateTest      ------Deleting
Duplicates with same id 
( 
[ID] [int] , 
[FirstName] [varchar](25), 
[LastName] [varchar](25)  
) ON [PRIMARY] 

INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith') 
INSERT INTO dbo.duplicateTest VALUES(2, 'Dave','Jones') 
INSERT INTO dbo.duplicateTest VALUES(3, 'Karen','White') 
INSERT INTO dbo.duplicateTest VALUES(1, 'Bob','Smith') 



SELECT * FROM dbo.duplicateTest 

SET ROWCOUNT 1 
DELETE FROM dbo.duplicateTest WHERE ID = 1 
SET ROWCOUNT 0 

SELECT * FROM dbo.duplicateTest 

Drop table dbo.duplicatetest