SQL Server interview question Part 4

1. Ques: What is Index ? how many possible index combinations in table ?
Answer: 
Index help  to retrieve the data quicker in SQL server. An index can be created in a table to increase the performance of application and we can getting the data more quickly and efficiently. Effective indexes are one of the best ways to improve performance in a database application.indexes are one of the best ways to improve performance in a database application.

Syntax to Create Index :
CREATE INDEX INDEX_NAME ON TableName (COLUMN_NAME)

There are two types of Indexes in SQL server .

• Clustered indexes
• non-clustered indexes.

A table can have one of the below indexes combinations

• No Indexes
• A clustered index
• A clustered index and many non-clustered indexes
• A non-clustered index
• Many non-clustered indexes

2. Ques: What is difference between Clustered Index and Non-Clustered Index ?
Answer: 
Clustered Index

• There is only one clustered index for a table.
• It is made on the primary key.
• The leaf nodes of a clustered index contain the data pages.
• Sort the records and store them physically according to the order.
• Do not need extra space to store logical structure.
• It doesn’t allow null values
• Assigned for primary key
• Reading from a clustered index is much faster than Non-Clustered Index.

Non-Clustered Index

• There can be only 249 non-clustered indexes for a table.
• It is made on the any key.
• The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
• Create a logical order for data rows and use pointers to physical data files.
• Use extra space to store logical structure.
• It allow one null values
• Assigned for unique key
• Reading from Non-Clustered Index is slower than Non-Clustered Index.

3. Ques: What is difference between Delete and Truncate command in SQL server ?
Answer:
Delete :

• The DELETE command is used to remove rows from a table based on WHERE condition.
• DELETE is a DML command.
• DELETE retain the identity column.
• DELETE can be rollback.
• DELETE command can the fired Trigger.
• DELETE is slower than TRUNCATE.
• we can use conditions(WHERE clause) in DELETE.
• Delete can be used with indexed views.
• DELETE using a row lock, each row in the table is locked for deletion.

Truncate :

• TRUNCATE removes all rows from a table.
• TRUNCATE is a DDL command.
• Identify column is reset to its seed value.
• TRUNCATE can't be rollback.
• TRUNCATE command doesn't get fired Trigger.
• TRUNCATE is much faster than DELETE.
• we can't use conditions(WHERE clause) in TRUNCATE.
• Truncate cannot be used with indexed views.
• TRUNCATE using a table lock and whole table is locked for remove all records.

4. Ques: What are the difference between Row_Number(), Rank() And Dense_Rank() functions in SQL Server ?
Answer:
Row_Number(): It give unique numbers to each row within the PARTITION given the ORDER BY clause.
ex:  Select Column1 , ROW_NUMBER(Column2) OVER() FROM tableName

RANK(): It give unique value to each distinct Row, but it leaves a gaps between the groups.
ex: Select EmployeeName, EmpSalary ,rank() over(order by EmpName) as rankID from tblemployee

Dense_Rank(): It give unique value to each distinct Row, but it will not leave gaps between groups.
ex: Select EmployeeName, EmpSalary , DENSE_RANK() over(order by EmpName) as DrankID from tblemployee


5. Ques: Explain the different types of isolation levels in SQL Server.
Answer:
SQL Server supports these transaction isolation levels (from lower level to higher level)

• Read Uncommitted
• Read Committed
• Repeatable Read 
• Serializable
• Snapshot

Read Uncommitted:

• In this level, One transaction can read the data which is modified by some other transactions but still not committed.
• It is lowest level and DIRTY READS got here.

Read Committed:

• In this level,One transaction prevents the reading data which are modified by some other transactions but still are not committed yet.
• It eliminates the problem of Dirty read.
• It is the default Isolation set by the SQL Server for any Database.

Repeatable Read:

• It does not allows any transaction to read a data that is being modified by some other transaction but not committed yet. 
• In Repeatable Read isolation levels Shared locks are acquired. 

Serializable:

• In this level,it  do not allow any transaction to read the data unless the other transactions completed their data modification operation. 
• Serializable isolation level use to prevented  PHANTOM reads.

Snapshot :

• In this level,transaction recognize only data which is committed before the start of the transaction. 

Isolation Level Dirty Read Lost Update Unrepeatable Read     Phantom       Records      Read uncommitted yes yes yes yes      Read committed No yes yes yes      Repeatable read No no no yes      Serializable No no no no

6. Ques: What is dirty read ?
Answer:
A dirty read happen when a transaction is allowed to read data from a row that has been modified by another transaction and not yet committed.

7. Ques: What is Phantom Read ?
Answer:
Phantom read occurs where in a transaction execute same query more than once, and the second transaction result set includes rows that were not visible in the first result set.

8. Ques: What are the several concurrency issues  in SQL Server ?
Answer:
Lost Update
Dirty Read
Non-Repeatable Read
Phantom Reads

9. Ques: What is lock in SQL server ? How many types of lock SQL server.
Answer:
Locking is preventing to acquire one transaction if other transaction already in use.
it main use for proper data modification by preventing other transactions to acquire a lock on the object higher in lock hierarchy.
What is lock in Sql server. how many types of lock.

Types of locks;
Shared (S) –It is use for Used for select operations.
            It is read-only operations.
            It Enable other sessions to perform select operations but prevent updates.
            SELECT statement Generally use in Shared mode.

Exclusive lock (X) :Only one Query can access the object.
                    It is Used for DML operations.
                    updates cannot be made to the same resource at the same time.

Update lock (U):It Prevents other update locks at row level.
               : It slove the problem of deadlock Cycle.

Intent shared (IS): Intent Locks are used for establish a lock Hierarchy.
                    It is three type,
                    Intent shared (IS),
                    intent exclusive (IX),
                    shared with intent exclusive (SIX).                  

10. Ques:  What are the different types of replication supported by SQL SERVER ?
Answer:
There are three types of replication  supported by SQL SERVER:

Snapshot Replication. Snapshot Replication takes snapshot of one database and moves it to the other database. After initial load data can be refreshed periodically. The only disadvantage of this type of replication is that all data has to be copied each time the table is refreshed.

Transactional Replication In transactional replication data is copied first time as in snapshot replication, but later only the transactions are synchronized rather than replicating the whole database.You can either specify to run continuously or on periodic basis.

Merge Replication. Merge replication combines data from multiple sources into a single central database.Again as usual the initial load is like snapshot but later it allows change of data both on subscriber and publisher, later when they come on-line it detects and combines them and updates accordingly.