SQL Server

SQL Server Interview Questions and answers

1. What is SQL?

 Ans: This stands for Structured Query Language. SQL is a standard query language used for maintaining the relational database and perform many different operations of data manipulation on the data.

2. How to optimize SQL queries?

Ans: 

• Create indexes on proper database column
• Avoid using functions in clause, join, where condition, group by and order by.
• Avoid using wild card(%) at the beginning of the search term.
• Only necessary columns should be projected in the select clause.
• Avoid distinct and union, use only if it is necessary
• Use union all instead of union, as union performs sorting operation on the result set.
• Avoid correlated sub queries and replace these with joins.
• Analyze the execution plan produced by the optimizer.  

3. How to get second highest salary of employee?

Ans: select max(salary) from employee where salary < (select max(salary) from employee.

4. How to get n-th highest salary of employee?

Ans: select top 1 salary from 

(

  select distinct top n salary from employee order by salary desc

) result

order by salary;

5.  How to get n-th highest salary of employee(by using CTE)?

Ans: 

;with EmpCTE as

(

    select salary, DENSE_RANK() over(order by salary desc) as DRank from employee

)

select top 1 salary from EmpCTE where EmpCTE.DRank = n;

6. How to get n-th highest salary of employee(without using TOP)?

Ans: 

;with EmpCTE as

(

    select salary, Row_Number() over(order by salary desc) as RN from employee

)

select salary from EmpCTE where EmpCTE.RN= n;

7. Write SQL query to delete all duplicate records except one?

Here id is the primary key of employee table.

;with EmpCTE as

(

    select salary, Row_Number() over(partition by id order by id) as 

    Row_Number from employee

)

delete from employee where EmpCTE.Row_Number> 1;

8. Diffrence between delete and truncate in SQL ?

Delete : 

• DML command
• Filter can be applied in the query
• Operation logged and trigger is called while executed
• Maintain logs so slower
• Can be rolled back
• Syntax: Delete from tableName where condition

Truncate :  

• DDL command
• Filter can not be applied in the query
• No trigger is called while executed
• Does not maintain logs so faster
• Can not be rolled back, can be rolled back only if it is inside a transaction.
• Syntax: Truncate table tableName