Here is the list of SQL server interview questions gathered from my end. You will be finding questions from various companies that I have attended so far. Let’s get started.
1. Apply all joins for the following table and write the number of rows?
Inner join -4 rows
Cross joins-4 rows
Left join-4 rows
Right join-4 rows
2. Explain view concepts with examples?
A View is a DB object used to select query.View can be also considered as a virtual table. It takes less space to store.
create view Vw_for_computers
select * from students where std_course =’computers’
select * from Vw_for_computers ( In o/p we can see only computers department data)
Advantages of view
— A View can be used to reduce the complexity of database schema.
Ex: When non-IT users want to access the database. It is not simple for them to combine 4-5 tables and get the data. So, we write a view which joins all the tables and give view access to non-IT users.
— A view can be used as the mechanism to implement row and column level security.
Ex: If we want IT department manager who only want IT employee list. If I grant access to the base table, he can see other department details also. So, create a view , within the view retrieve only IT employee. Then give access to view.
— A View can be used to present aggregated data and hide detailed data.
Ex1: Create view Vw_employee
Select ID, Name, Dept from tblemployee
On tblemployee.departmentID = tbldepartment.DeptId
Where tbldepartment.deptname = ‘IT’
Ex2: Create view VwSummarized data
Select DeptName, count(ID) as TotalEmployees
On tblEmployee.DeptID = tblDepatment.DeptID
3. Write the difference between Functions and Stored procedure?
|1||It must return a value||It is optional.It can return zero or any value.|
|2||It has only I/P parameters||It has both I/P and O/P parameters|
|3||Functions allow only select statement.||It allows select as well as DML statements|
|4||Functions can be used Where/Having/Select section||Procedures cant be used.|
|5||Exception cant be handled||Exception can be handled by using try-catch block|
|6||Transactions are not allowed within functions||Transactions are used in stored procedure.|
|7||Only table variables are used in functions. It will not allow temporary var.||Sp’s uses both table and temporary var.|
|8||Function cannot call SP||SP can call function|
|9||Functions can be used in join clause as result set||Procedures can’t be used in join clause.|