Transaction interview questions answers  

In this article I will share some frequently asked transaction interview questions and answers in SQL server.
1. What is transaction?

Transactions provide a mechanism for grouping a series of database changes into one logical operation. After changes are made to the database, these changes can be committed or canceled as a single unit.

2. What is save transaction and save point?

Creates points within groups of transactions in which to ROLLBACK.Save points offer a mechanism to roll back portions of transactions. A user can decide and set a savepoint, within a transaction. 

The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled.

In Detail… http://codechef4u.com/post/2015/04/13/save-transaction-and-savepoint

3. Explain Transaction Isolation levels in SQL Server?

To ensure quality of your data in RDBMS transaction isolation level is important.
There are five isolation levels - READ UNCOMMITTED, READ COMMITTED (locking), READ COMMITTED (snapshot), REPEATABLE READ and SERIALIZATION.

4. What is read committed?

This is the default isolation level and means selects will only return committed data. This prevents dirty reads.

For more info ….http://www.codechef4u.com/post/2015/04/05/read-committed

5. What is read UNCMMITED?

Specifies that statements can read rows that have been modified by other transactions but not yet committed.

 For more info … http://www.codechef4u.com/post/2015/04/05/read-uncommitted

6. What is snapshot?

Except when a database is being recovered, SNAPSHOT transactions do not request locks when reading data. SNAPSHOT transactions reading data do not block other transactions from writing data. Transactions writing data do not block SNAPSHOT transactions from reading data.

7. What is REPEATABLE READ?

This is the default isolation level and means selects will only return committed data. This prevents dirty reads.

For more info ….http://www.codechef4u.com/post/2015/04/05/read-committed

8. What is SERIALIZABLE?

 This isolation level takes Repeatable Read and adds the guarantee that no new data will be added eradicating the chance of getting Phantom Reads.

For more info ….http://www.codechef4u.com/post/2015/04/07/serializable

9. What are Different Types of Locks?

Following are the list of locks used in sql server:

SHARED : 

SQL Server uses shared locks for all read operations. For example a select statement.

UPDATE :

This lock applied on those resources that can be updated. This lock prevents the common form of dead lock that occurs when multiple sessions are locking the data so that they can update it later.

EXCLUSIVE :

 Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time.

INTENT: 

Used to establish a lock hierarchy. The different types of intent locks are: intent shared, intent exclusive, and shared with intent exclusive.

SCHEMA: 

Used when an operation dependent on the schema of a table is executing. The different types of schema locks are: schema modification and schema stability.

BULK UPDATE :

This lock is applied when there is a bulk copying of data and the TABLOCK is applied.

KEY RANGE : 

Protects the range of rows read by a query when using the serializable transaction isolation level. Ensures that other transactions cannot insert rows that would qualify for the queries of the serializable transaction if the queries were run again.

10. What are Pessimistic Lock and Optimistic Lock?

Pessimistic:

In pessimistic locking a record or page is locked immediately when the lock is requested.

The disadvantage of pessimistic locking is that a resource is locked from the time it is first accessed in a transaction until the transaction is finished, making it inaccessible to other transactions during that time

Optimistic:

In an optimistic lock the record or page is only locked when the changes made to that record are updated. 

11. What is Dirty Read?

Reading uncommitted modifications are call Dirty Reads. Values in the data can be changed and data is changing in the data set before the end of the transaction, thus getting you incorrect or wrong data.

12. What is Difference between Commit and Rollback when Used in Transactions?

Commit:

Commit marks the end of a successful implicit or explicit transaction. When we use Commit in any query then the change made by that query will be permanent and visible. We can't Rollback after the Commit.

Rollback (MSDN details):

Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside the transaction. You can use ROLLBACK TRANSACTION to erase all data modifications made from the start of the transaction or to a savepoint. It also frees resources held by the transaction.

13. What is NOLOCK?

When user accessing data, data get locks so that other user cannot modify or delete data that someone is reading.

WITH (NOLOCK)

Do not issue shared locks and do not honor exclusive locks. When this option is in effect, it is possible to read an uncommitted transaction or a set of pages that are rolled back in the middle of a read. Dirty reads are possible. Only applies to the SELECT statement. 

14. What is the Difference between Update Lock and Exclusive Lock?

Exclusive lock ensures that multiple updates cannot be made to the same resource at the same time.

Update lock prevents the common form of dead lock that occurs when multiple sessions are locking the data so that they can update it later.

15. What is explicit mode in SQL Server?

An explicit transaction is one in which you explicitly define both the start and end of the transaction.

16. What is implicit mode in SQL Server?

When a connection is operating in implicit transaction mode, the instance of the SQL Server Database Engine automatically starts a new transaction after the current transaction is committed or rolled back.

17. What is autocommit mode in SQL Server?

Autocommit mode is the default transaction management mode of the SQL Server Database Engine. Every Transact-SQL statement is committed or rolled back when it completes. If a statement completes successfully, it is committed; if it encounters any error, it is rolled back.

18. What are “Unrepeatable reads”?

Non repeatable read is a situation where a session finds itself in when it perform multiple read.

It is possible to perform the query in the same transaction more than one and show different result.

19. What are “Phantom rows”?

Phantom row is rows that douse not appear in the initial read, but appear when same data is read again during the same transaction.

20. What are “Lost Updates”?

Lost updates occur when two or more transactions select the same row and then update the row based on the value originally selected. Each transaction is unaware of other transactions. The last update overwrites updates made by the other transactions, which results in lost data.

21. What are “Lock” hints?

Lock hints are used to customize the locking behavor of SQL Server from 3 main perspectives, granularity, mode, and duration. Available hints are HoldLock, NoLock, NoWait, PagLock, ReadCommtted, ReadCommittedLock, ReadPast, ReadUncommitted, RepeatableRead, RowLock, Serializable, TabLock, TabLockX, UpdLock, and XLock. A full understanding of them is important before using them.

Reference … http://www.sqlnotes.info/tag/lock-hints/

22. What is a “Deadlock”?

This is situation where both transactions in a deadlock will wait forever unless the deadlock is broken by an external process.

A deadlock occurs when two or more processes are waiting on the same resource and each process is waiting on the other process to complete before moving forward.

23. What are the steps you can take to avoid “Deadlocks”?

Some Suggestions:

1. Implementing an error handler that traps error message 1205 allows an application to handle the deadlock situation.

2. Ensure the database is normalized properly because bad database design can increase the number of deadlocks to occur inside database.

3. Avoid cursors if possible .

4. Implement proper locking or avoid it.

5. Consider using the NOLOCK hint where possible

6. Determine whether a transaction can run at a lower isolation level.

24. What is Concurrency?

25. How can we solve concurrency problems?

27. What kind of problems occurs if we do not implement proper locking strategy?

1. Deadlock issue.
2. Concurrency problems.

SQL Server Error Handling Interview Question and Answers

In this article I will share some frequently asked Error Handling interview questions and answers in SQL server.

1. How will you Handle Error in SQL SERVER ?

Error handling for T-SQL similar to C#, Java, C++. Group of SQL statement enclosed
Inside try block, if error occurs in try block control is passed to another Sql.

Before SQL server 2012 release tray…catch block used to handle error with RAISERROR.

SQL Server 2012 introduced the THROW statement. You now have a replacement for RAISERROR, the THROW statement, which makes it easier than ever to capture the error-related data.

More.. http://codechef4u.com/post/2015/04/13/throw-t-sql

http://codechef4u.com/post/2015/04/06/raiserror

2. What is RAISEERROR?

Used to return error message back to application, system warning or error message generated by Sql server database engine.

Syntax:

RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]

 More detail… http://codechef4u.com/post/2015/04/06/raiserror

3. Explain Try...Catch with sql server?

Error handling for T-SQL similar to C#, Java, C++. Group of SQL statement enclosed
Inside try block, if error occurs in try block control is passed to another Sql. Statement enclosed in catch block.

Syntax:

BEGIN TRY
     { T-Sql_ statement | statement_block }
END TRY
BEGIN CATCH
     [ { T-Sql _statement | statement_block } ]
END CATCH
[ ; ]

More info… http://codechef4u.com/post/2015/04/13/try-catch

4. Explain THROW statement in sql server 2008?

THROW statement raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server (SQL Server 2012+)

You don’t have to specify any parameters and the results are more accurate. You simply include the statement as is in the CATCH block.

 more info...http://www.codechef4u.com/post/2015/04/13/throw-t-sql.aspx

5. Explain following error properties?

ERROR_NUMBER():

The number assigned to the error.

ERROR_LINE():

The line number inside the routine that caused the error.

ERROR_MESSAGE():

The error message text, which includes the values supplied for any substitutable parameters, such as times or object names.

ERROR_SEVERITY():

The error’s severity.

ERROR_STATE():

The error’s state number.

ERROR_PROCEDURE():

The name of the stored procedure or trigger that generated the error.

6. Explain Error and Transaction Handling in SQL Server?

You can use try-catch block for Error and Transaction Handling, inside try block write your sql statements or queries check "@@TRANCOUNT > 0"  if that’s is greater than "0" rollback transaction in catch block else commit transaction.

More… http://www.codechef4u.com/post/2015/04/06/raiserror

http://codechef4u.com/post/2015/04/13/try-catch

7. Can we use two or more catch block with associated one try block?

No that is not allowed.

8. Can we use TRY..CATCH block with transaction?

Yes ,TRY..CATCH blocks can be used with transactions.

9. Can we use try catch block inside user defined function?

No, TRY...CATCH construct cannot be used in user defined function.

interview questions and answers on sql server Views

In this article I will share some frequently asked views interview questions and answers in SQL server.  

1. What is a View?

View is a virtual table based on the result-set of an SQL statement and that is Stored in the database with some name.

Example:

CREATE VIEW StudentProducts AS

SELECT  * FROM Products WHERE CategoryId in (Select CategoryID from Categories where CategoryName='Books')

GO  

2. What are Different Types of views?

Basically there are two types of views

1. System views
2. User defined views.

You can create following views:

a. Standard Views
b. Indexed Views
c. Partitioned Views

3. Explain System views?

Microsoft SQL Server provides the ready collections of system views that expose metadata.
System Views that already exist in the Master database of Sql Server.
Example: Catalog views

4. Explain User defined views?

As name suggests these view are defined by users.

Example:

CREATE VIEW StudentProducts AS

SELECT  * FROM Products WHERE CategoryId in (Select CategoryID from Categorieswhere CategoryName='Books')

GO  

 5. What is indexed view?

Creating a unique clustered index on a view is an indexed view.
If the view is indexed then any queries that can be answered using the index only will never need to refer to the underlying tables. This can lead to an enormous improvement in performance.

More info…http://www.codechef4u.com/post/2015/09/05/indexed-views

6. What is Catalog Views?

Catalog views retrieves information used by the SQL Server. They are the most general interface to the catalog metadata and provide efficient way to obtain, transform and present custom forms of this information.

Note: Catalog view do not contain information about replication, backup or maintenance plans

7. What are Various Limitations or Restriction of the Views?

Following are some limitations with sql server views

1. SELF JOIN is not possible with Indexed View

2. ORDER BY now allowed with views.

3. Outer Join Not Allowed in Indexed Views.

4. Count(*) not allowed in views , you can use COUNT_BIG(*) instead.

5. With Index View union not allowed

6. You can't create a view with a parameter or parameterized view.

7. Cross Database Queries Not Allowed in Indexed View

8. You can’t use keyword View Definition with Index View.

9. Views are not based on temporary tables, when you try you will get error message like

“Views or functions are not allowed on temporary tables”

8. What are partitioned views and distributed partitioned views?

A partitioned view:

A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers, making the data appear as if from one table

Local partitioned view:

In a local partitioned view, all participating tables and the view reside on the same instance of SQL Server.

Distributed partitioned view:

In a distributed partitioned view, at least one of the participating tables resides on a different (remote) server.

Distributed partitioned views can be used to implement a federation of database servers. A federation is a group of servers administered independently, but which cooperate to share the processing load of a system.

9. Describe the functionalities that views support? Or Views features

View Builder allows you to build views visually. It allows you to create and edit views without knowledge of SQL

Simple to use:

A view can draw data from several different tables and present it as a single table, turning multi-table queries into single-table queries against the view.

Table joins:

Views can subset data in a table
They can join multiple tables into one virtual table

Security:

Views can provide more security and decrease complexity.

Performance:

Better performance compared with complex queries.
They save space because only their definition is stored.

Abstraction:

They can also be used to create abstraction
Materialized views are commonly used in data warehousing. They represent a snapshot of the data from remote sources.

Data Integrity

If data is accessed and entered through a view, the DBMS can automatically check the data to ensure that it meets the specified integrity constraints.

10. Explain Indexed views and partitioned view with their syntax?

Tasks required to create partitioned view:

a. Adding linked server definitions on each member server that contains the connection information required to run distributed queries on the other member servers.
b. Setting the lazy schema validation option, by using sp_serveroption, for each linked server definition that is used in distributed partitioned views.
c. Creating a distributed partitioned view on each member server.

Example:

CREATE VIEW ProductsData AS
   SELECT * FROM CompanyDatabase.TableOwner.Products_33
UNION ALL
   SELECT * FROM Server2.CompanyDatabase.TableOwner.Products_66
UNION ALL
   SELECT * FROM Server3.CompanyDatabase.TableOwner.Products_99

Tasks required to create Indexed view:

The following steps are required to create an indexed view and are critical to the successful implementation of the indexed view:

1. Verify the SET options are correct for all existing tables that will be referenced in the view.
2. Verify that the SET options for the session are set correctly before creating any new tables and the view.
3. Verify that the view definition is deterministic.
4. Create the view by using the WITH SCHEMABINDING option.
5. Create the unique clustered index on the view.