SQL Server Interview questions

Hi ,

Here I am Posting some SQL Server Interview questions.Recently I changed my Job Location.

Here are the Interview questions and answers.

1)what is the difference between Constraint and trigger ?

2) what is difference between function and procedure?

3)what is cursor and how many types of cursors.?

4)what is view and how many type of views are there?

5)Can we use temp tables in Stored Procedures and what is the scope of that tables?

6) What is Dynamic SQL?

7)What is DMV.Explain brief?

DMV stands for Dynamic Management view.They are used for check the health of Database.

8)What is the Difference between Index seek and Index Scan?

Index scan Scans entire and index and index seek uses a part of the index where query satisifies.

9)What is Database Normalization and Denormalization?

10)What is Index and how many indexes for a table?

11)What are the Different types of joins and explain its usage with an example?

12)How can you ImProve stored Procedure Perfomance?

13)What is the use of system databases and Explain its in brief?

14)Explain about ACID Properties?

14)What is the difference between locking and Blocking?

15) What is Difference between Replace and Stuff?

16)What is Pagesplitting?how can you avoid page splitting?

17)What is CTE ?Explain breif about CTE usage when will we use CTE.



Questions about T-SQL Queries

I have table like this

I want to   Update the MgrId Column with Respective Manager EmpId.Write a query to update the

MgrI column with their Manager EmpIds.

Solutions is This:

UPDATE Test.dbo.EmpManager SET MgrId=(SELECT EmpId FROM


Test.dbo.EmpManager e1 WHERE e1.Empname=Test.dbo.EmpManager.MgrName)
 
You can write your own solution this is the best solution what i feel.
 
 
2) You have three tables  as shown below.

These are the above three tables for Employee called Empmaster,DeptMaster and EmpMapping.

From These I want to get the Result as shown below.

I want to get the above result .how do you aceive this.Following link Shows the exact result.

http://www.sqlservercentral.com/Forums/Topic922837-338-1.aspx

What is defualt isolation level in SQL server?

Read Committed

What is the new transaction isolation levels supported in SQL server 2005?

Sanpshot isolation

What is use of GO statement in SQL Server?

Go is a batch separator in sql server.

what is use of row_number() and other ranking functions?

what are the new features in SQL Server 2008?

What are the new data types introduced in SQL server 2008?

what is Merge statement will do and write Syntax for that?

Write Synatx for CTE?

Write Synatx for Cursor?

How many types of Cursors are the SQL server 2005?

Waht is use of @@rowcount?

Can i create a foreign key for a primary key in same table?

Yes.see the following link

http://technet.microsoft.com/en-us/library/ms175464.aspx

Different between primary key and foreign key ?

Difference between unique key and primary key?

.