SQL Server interview questions-Part 2


This article is my second phase for the Sql Interview Questions. It describes the fundamental SQL server concepts that is very commonly asked in the interviews.This article will be equally useful for freshers as well as experienced professional.

1. What are the different types of joins you know? And explain them.

The different types of join I know are:
Inner Join, Outer Join and Self Join.
Inner Join/Join:
The First table and second table are matched row by row. The result set contains only the matching records from both the tables, unmatched rows are ignored. If the 2 tables have no matching records, then it returns NULL.
Outer Join:
Outer join is of 3 types such as: Left outer Join, Right outer Join, Full outer Join.
Left outer Join:
This join returns all the rows from the left table along with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
Right outer Join:
This join returns all the rows from the right table along with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
Full outer Join:
It returns all the rows from both the table when the conditions are met and returns null value when there is no match.
Self Join:
Joining the table itself called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. Here we need to use aliases for the table to join 2 different column of the same table.
Cross Join:
The Cross join is otherwise known as Cartesian Join as it returns the Cartesian product of the two or more joined tables.
For Example, Suppose Table1 has 3 rows and Table2 has 5 rows. Then the cross join output will have 3*5=15 rows.

2. What is a database transaction?

Database transaction takes database from one consistent state to another. At the end of the transaction the system must be in the prior state if the transaction fails or the status of the system should reflect the successful completion if the transaction goes through.
There are three major statements included in a Transaction statement. They are:
Begin Transaction
Rollback Transaction
Commit Transaction

3. What is Nested Transaction?

Transact-SQL allows you to nest transaction operations i.e. To embed one transaction operation withing another transaction.

4. What happens to the outer transaction if the inner transaction is Rollbacked or committed ?

The Outer transaction behave differently for the Rollback and Commit of the Inner transaction. First, we should be aware that there is a @@TRANCOUNT system defined variable for the transactions. Whenever a transaction starts i.e.at the BEGIN TRAN command the @@TRANCOUNT value increases.
When the inner transaction is rolled back(ROLLBACK command), the whole transaction rolls back regardless of the nesting level and the @@TRANCOUNT variable becomes 0. But when the inner transaction is committed(COMMIT command) then nothing happens actually. It only decreases the value of @@TRANCOUNT variable from 2 to 1. The whole transaction is committed only after the outer transaction is committed.
Example,

/Code/
SELECT 'Before BEGIN TRAN', -- The value of @@TRANCOUNT is 0
BEGIN TRAN -- The value of @@TRANCOUNT becomes 1
----Your Code---
BEGIN TRAN(nested)-- The value of @@TRANCOUNT is 2
----Your Code---
ROLLBACK TRAN(nested) The value of @@TRANCOUNT becomes 0
COMMIT TRAN –- Nothing Happens

SELECT 'Before BEGIN TRAN', -- The value of @@TRANCOUNT is 0
BEGIN TRAN -- The value of @@TRANCOUNT becomes 1
----Your Code---
BEGIN TRAN(nested)-- The value of @@TRANCOUNT is 2
----Your Code---
COMMIT TRAN(nested) The value of @@TRANCOUNT becomes 1
COMMIT TRAN –- The transaction is committed and value of @@TRANCOUNT becomes 0
/Code/

5. What are Save Points in transaction?

Save Points is a point in a transaction where the transaction can return or rollback if the transaction fails or is set to be roll backed. SQL Server uses SAVE TRAN statement to use savepoints.

6. What is a cursor?

A Cursor is a database object used to manipulate data in a set on a row-by-row basis. It is used in scenarios where we want to update/insert/delete data row-by-row based on some conditions.

7. What is a User-defined Function (UDF)? How many types of UDFs are there?

An UDF in sql contains a set of codes that is reusable at various instances.
Function is of two types: Scalar valued Function and Table Valued Function
As the name suggests Scalar valued Function returns a single value whereas Table Valued Function returns a result set or record set as ouput.

8. What is a trigger?

A trigger is a special kind of stored procedure that automatically executes when an event/action occurs in a table like insert,delete,update. Triggers are mainly used to keep a track of the actions performed in the database. It is mainly used for audit purposes.There are 2 types of trigger:
After\For trigger.
Instead of trigger.

9. Difference between Temporary Table(#temptable) and Table Variable(@temptable) in Sql Server.

Temporary tables are real materialized tables that exist in tempdb whereas Table variable acts like a variable and exists for a particular scope of query execution. This is also created in the Tempdb database but memory is not allocated.
Temporary tables can have constraints and Can be indexed as well while Table variables can have a primary key, but indexes cannot be created on them.
Temp tables are transactional and do roll back.Table variables are not transactional and do not roll back.

10. What is Normalization? Explain the types of Normalization.

Normalization is the process of efficiently organizing data in a database to eliminate any kind of data redundancy.
The benefits of Normalization are as follows:
Eliminate data redundancy
Improve performance
Query optimization
Faster update due to less number of columns in one table
Index improvement


Comments

Author: sridhar thota19 Aug 2015 Member Level: Gold   Points : 8

Hi

Thanks for posting your interview experience. Let me provide answers to the following.

1. What are the different types of joins you know? And explain them?
A)Inner Join: Returns matching columns from both the tables.

Outer join: Returns all columns from both the tables.

Left join: Returns all columns from left table and matching columns from right table.

Right join: Returns all columns from right table and matching columns from left table.

2. What is a database transaction?
A)Executing set of statements at a time is called transaction.
If all statements of transaction is executed sucessfully then it should commit or else
it is rollbacked.

6. What is a cursor?
Cursor is a database object used by application to manipulate data in a row by row basis.
Cursors are used when we want to process each row and when we want to update many rows.

8. What is a trigger?
A)Trigger is a event fired automatically when the insert, update, delete statements are
executed.

10. What is Normalization? Explain the types of Normalization.
A)Splitting larger tables in to smaller tables to avoid redundency and inconsistency
of data is called as normalization.

1NF: First Normalization says that each column should contain one value and each
row should contain same number of columns.
Implement primary key to identify the records uniquely.

2NF: Second Normalization says move redundent data to separate table.
Use foreignkey contraint between two tables.

3NF: Third Normalization says it should be in 1NF and 2NF to be a 3NF.
Table should not contain columns which are not fully dependent on primary key.

Regards

Sridhar Thota.
If you learn't from defeat..
You haven't really lost..



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: