SQL Server interview questions-Part 2

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