Sql Server DBA Interview Question more than 1000

Question 1. What Is The Difference Between Lock, Block And Deadlock?

Solution -

Lock: DB engine locks the rows / page / table to access 

the data which is worked upon according to the query.

Block: When one process blocks the resources of another

process then the situation occured is called blocking. Blocking can be identified by using the following query

SELECT * FROM sys.dm_exec_requests 

where blocked <> 0
SELECT * FROM master..sysprocesses 

where blocked <> 0

Deadlock: When something happens as follows:

 Error 1205 is reported by SQL Server for deadlock.

Question 2. what is the sequence of SQL statement are processed in SQL Server?

Solution - The clauses of the select are processed in 

the following sequence

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause
7. TOP clause

Question 3. on which TCP/IP port does SQL Server run?

Solution - By default SQL Server runs on port 1433. 

But according to requirement is can be changed such as we have more than one instance of SQL Server, 

Then  differenet port can be assigned to different Instances