SQL : Transaction

What are the advantages of using Stored Procedures?

• Stored procedure can reduced network traffic and latency, boosting application performance.
• Stored procedure execution plans can be reused, staying cached in SQL Server's memory, reducing server overhead.
• Stored procedures help promote code reuse.
• Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
• Stored procedures provide better security to your data.

Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?

• Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. 
• Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem.
•  A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps.
•  Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. 
• You can nest stored procedures and managed code references up to 32 levels.

How do you optimize stored procedures in SQL Server 2005

• Use as much as possible WHERE clause filters. Where Clause is the most important part for optimization 
• Select only those fields which really require. 
• Joins are expensive in terms of time. Make sure that use all the keys that relate the two tables together and don't join to unused tables, always try to join on indexed fields. The join type is important as well (INNER, OUTER).

What is the Maximum number of input and output parameters in Stored procedure in SQL Server 2000 ?

1024

What is user-defined functions in SQL Server?

• User-Defined Functions allows to define custom T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.
  
  
• In terms of functionality it is similar to C# or VB.NET functions except the fact that code is written in T-SQL :)

What is a difference between stored procedures and user-defined functions

Characteristics	Stored Procedure	Function
Parameters	Procedures can have input/output parameters	Functions can have only input parameters.
O/P	Procedure can return zero or n values	Function can return one value which is mandatory.
DMLs	Procedure allows select as well as DML statement	Function allows only select statement in it.
Calls	Functions can be called from procedure	Procedures cannot be called from function
Exception Handling	Exception can be handled by try-catch block in a procedure	try-catch block cannot be used in a function.
Transaction Support	Procedures support transaction Management.	Functions don’t support transaction management.
Embed in WHERE/HAVING/SELECT	Procedures cannot be utilized in these statement	Function can be embedded in a these statement.

What is a "trigger"?

• Microsoft SQL Server includes support for a special type of stored procedure called a trigger.
•  A trigger is a stored procedure that executes whenever an update, delete or insert statement is executed against a table or a view.
• Triggers are created in order to enforce integrity rules in a database. In other words, you can associate a trigger with a table in such a way that it fires whenever a change is made to the contents of the table.
• Basically, trigger is a set of SQL statements that execute in response to a data modification/retrieval event on a table.
• A trigger is a solution to the restrictions of a constraint. For instance :

Ø  A database column cannot carry PSEUDO columns as criteria where a trigger can.

Ø  A database constraint cannot refer old and new values for a row where a trigger can.

• There are

Ø   table triggers,

Ø  schema triggers.

Ø  and database triggers

These can be made to fire when new objects are created, when a user logs in, when the database shutdown etc.

Table level triggers can be classified into row and statement level triggers and those can be further broken down into

Ø  before triggers

Ø  after triggers.

• Triggers are generally used to implement

Ø   business rules,

Ø  auditing.

Ø  extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.

How many types of triggers are there?

There are four types of triggers.

1. Insert
2. Delete
3. Update
4. Instead of

What is a SQL View?

• A view can be thought of as either a virtual table or a stored query.
•  The data accessible through a view is not stored in the database as a distinct object.
• What is stored in the database is a SELECT statement. The result set of the SELECT statement forms the virtual table returned by the view.
•  A user can use this virtual table by referencing the view name in Transact-SQL statements the same way a table is referenced. A view is used to do any or all of these functions:

Ø  Restrict a user to specific rows in a table. For example, allow an employee to see only the rows recording his or her work in a labor-tracking table.

Ø  Restrict a user to specific columns. For example, allow employees who do not work in payroll to see the name, office, work phone, and department columns in an employee table, but do not allow them to see any columns with salary information or personal information.

Ø  Join columns from multiple tables so that they look like a single table.

Ø  Aggregate information instead of supplying details. For example, present the sum of a column, or the maximum or minimum value from a column.

• Views are created by defining the SELECT statement that retrieves the data to be presented by the view.
•  The data tables referenced by the SELECT statement are known as the base tables for the view.

 In this example, titleview in the pubsdatabase is a view that selects data from three base tables to present a virtual table of commonly needed data:

CREATE VIEW titleview

AS

SELECT title, au_ord, au_lname, price, ytd_sales, pub_id

FROM authors AS a

     JOIN titleauthor AS ta ON (a.au_id = ta.au_id)

     JOIN titles AS t ON (t.title_id = ta.title_id)

You can then reference titleview in statements in the same way you would reference a table:

SELECT *

FROM titleview

• A view can reference another view.
• Views in all versions of SQL Server are updatable (can be the target of UPDATE, DELETE, or INSERT statements), as long as the modification affects only one of the base tables referenced by the view, for example:

UPDATE titleview

SET price = price * 1.10

WHERE pub_id = '0736'

GO

• Views are called virtual tables because the result set of a view is not usually saved in the database. The result set for a view is dynamically incorporated into the logic of the statement and the result set is built dynamically at run time.

What is cursor in SQL Server?

• A cursor is a set of rows together with a pointer that identifies a current row.
  
  In other word, Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, its like recordset in the ASP and visual basic.
  
  Typical syntax of cursor is
  
  
  

DECLARE @fName varchar(50), @lName varchar(50)

DECLARE cursorName CURSOR -- Declare cursor

LOCAL SCROLL STATIC

FOR

Select firstName, lastName FROM myTable

OPEN cursorName -- open the cursor

FETCH NEXT FROM cursorName

   INTO @fName, @lName

   PRINT @fName + ' ' + @lName -- print the name

WHILE @@FETCH_STATUS = 0

BEGIN

   FETCH NEXT FROM cursorName

   INTO @fName, @lName

   PRINT @fName + ' ' + @lName -- print the name

END

CLOSE cursorName -- close the cursor

DEALLOCATE cursorName -- Deallocate the cursor

Is it possible to create Cluster Index on Unique Key Column?

• The answer is YES. Though unique key allows null value but it still maintains the uniqueness of the column.
• Unique key column by default creates a non-cluster index on the column.
• Primary key creates cluster index on the column. The only difference is Unique key column allows only one null value.

How We can Get List of Store Procedures?

select * from Sys.Objects where Type='p'

What is OLTP?

OLTP is abbreviation of On-Line Transaction Processing. This system is an application that modifies data At the very instant it is received and has a large number of concurrent users.

What is OLAP?

OLAP is abbreviation of Online Analytical Processing. This system is an application that collects, manages, processes and presents multidimensional data for analysis and management purposes.

What is ACID?

A transaction is a logical unit of work in which, all the steps must be performed or none. ACID stands for Atomicity, Consistency, Isolation, Durability. These are the properties of a transaction.

ACID (an acronymn for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved. 

Atomicity is an all-or-none proposition. 

Consistency guarantees that a transaction never leaves your database in a half-finished state. 

Isolation keeps transactions separated from each other until they’re finished. 

Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination. 

Above four rules are very important for any developers dealing with databases

What is Distributed transaction?

A distributed transaction is one in which it updates data present on two or more systems. They are useful in updating data that is distributed. They must be robust because they are subjected to failures very often. Failures like client server failure etc. interactions between these computers that are distributed is with the help of transaction managers.

What is nested transaction? Explain with an example.

A nested transaction is one in which a new transaction is started by an instruction that is already inside another transaction. This new transaction is said to be nested. The isolation property of transaction is obeyed here because the changes made by the nested transaction are not seen or interrupted by the host transaction.

BEGIN TRANSACTION trans_1

INSERT INTO TestTrans1 VALUES (1,’mark’)

COMMIT TRANSACTION trans_1;

GO
     /* Start a transaction and execute trans_1. */   

        BEGIN TRANSACTION trans_2;

GO
     EXEC trans_1 1, 'aaa'; //execute some procedure

GO
     ROLLBACK TRANSACTION trans_2;

GO
     EXECUTE TransProc 3,'bbb';

GO

How Can we Write the Transaction Block in SQL Server?

 

BEGIN TRANSACTION 

Statement 1 

Statement 2 

.................. 
............... 
IF(@ERROR>0) 
ROLLBACK TRANSACTION 

ELSE 
COMMIT TRANSACTION

Difference between Implicit Transaction And Explicit Transaction

Implicit Transaction is the auto commit. There is no beginning or ending of the transaction. 

Explicit Transaction has the beginning, ending and rollback of transactions with the command 
Begin Transaction 
Commit Transaction and 
Rollback Transation 

In the explicit transaction, if an error occurs in between we can rollback to the begining of the transaction which cannot be done in implicit transaction.

What are Checkpoints?

 Checkpoint creates a known good point from which the SQL Server Database Engine can start applying changes contained in the log during recovery after an unexpected shutdown or crash.

What is deferred transaction?

A transaction that is not committed when the roll forward phase of recovery finishes, and that cannot be rolled back during database startup because the data required by rollback is offline. This data can reside in either a page or a file.

Why use Locks in database?

Locks help to resolve the problems of concurrency.

Database locks serve to protect shared resources or objects. These protected resources could be:

• Tables
• Data Rows
• Data blocks
• Cached Items
• Connections
• Entire Systems

What are the general problems of concurrency?

Problems	Short description	Explanation
Dirty reads	"Dirty Read" occurs when one transaction is reading a record, which is part of a half, finished work of other transaction.	• User A and user B are seeing value as “5”. • User B changes the value “5” to “2”. • User A is still seeing the value as “5”….Dirty read has happened.
Unrepeatable read	Inconsistent analysis occurs when a second transaction accesses the same row several times and reads different data each time. Inconsistent analysis is similar to uncommitted dependency in that another transaction is changing the data that a second transaction is reading. However, in inconsistent analysis, the data read by the second transaction was committed by the transaction that made the change. Also, inconsistent analysis involves multiple reads (two or more) of the same row and each time the information is changed by another transaction; thus, the term nonrepeatable read.	An editor reads the same document twice, but between each reading, the writer rewrites the document. When the editor reads the document for the second time, it has changed. The original read was not repeatable. This problem could be avoided if the editor could read the document only after the writer has finished writing it
Phantom rows	Phantom reads occur when an insert or delete action is performed against a row that belongs to a range of rows being read by a transaction. The transaction's first read of the range of rows shows a row that no longer exists in the second or succeeding read, as a result of a deletion by a different transaction. Similarly, as the result of an insert by a different transaction, the transaction's second or succeeding read shows a row that did not exist in the original read.	For example, an editor makes changes to a document submitted by a writer, but when the changes are incorporated into the master copy of the document by the production department, they find that new unedited material has been added to the document by the author. This problem could be avoided if no one could add new material to the document until the editor and production department finish working with the original document.
Lost updates	"Lost Updates" are scenario where one updates which is successfully written to database is overwritten with other updates of other transaction.	• User A updates all value form “5” to “2”. • User B comes and updates all “2” values to “5”. • User A has lost all his updates.

What do you mean by pessimistic locking and what is optimistic locking?

Optimistic locking: As the name suggests “optimistic” it assumes that multiple transaction will work without affecting each other. In other words no locks are enforced while doing optimistic locking. The transaction just verifies that no other transaction has modified the data. In case of modification the transaction is rolled back.

Pessimistic locking assumes that concurrency / collision issues will happen so a lock is placed on the records and then data is updated.

Explain Optimistic Locking.

You can implement optimistic locking by numerous ways but the fundamental to implement optimistic locking remains same. It’s a 5 step process as shown below:-

• Record the current timestamp.

• Start changing the values.

• Before updating check whether anyone else has changed the values by checking the old time stamp and new time stamp.

• If it’s not equal rollbacks or else commit.
 

How can you implement optimistic locking?

Using

a.     Dataset : By default

b.    Timestamp: Compare the timestamps of the changed data and show.

c.     Comparing old values to new : Rollback if things have changed.

What are various types of locks in SQL?

·         Shared locks,

·         Exclusive locks,

·         Intent locks,

·         DML locks,

·         Backup-recovery locks

When to use?	Reads Allowed	Writes Allowed
Shared lock	When you want only to read and you do not want any other transactions to do update.	Yes	No
Exclusive	When you want to modify data and you do not want anyone to read the transaction, neither you want anyone to update.	No	No
Update lock	This is a hybrid lock. This lock is used when you want to do update operation which passes through multiple phases before the actual update happens. It first starts with shared lock in the read phase and then on the actual update it acquires an exclusive lock.
	Read phase	Yes	No
	Manipulating phase	Yes	No
	Update phase	No	No
Intent Lock ( Demand locks)	Intent lock is for lock hierarchy. This lock is used when you want to lock resources down in the hierarchy. For example a shared intent lock on a table means shared locks are placed on pages and rows with the table.	NA	NA
Schema locks	When you are changing table structure.	No	No
Bulk update locks	Used when you are doing bulk updates	Table level No	Table level No

Which kind of locks have you used extensively?

I have used optimistic locks.

In pessimistic locks have used exclusive lock.

Table Type	Examples	Suggested Locking Strategy
Live-High Volume	Account	Optimistic (first choice) Pessimistic (second choice)
Live-Low Volume	Customer Insurance Policy	Pessimistic (first choice) Optimistic (second choice)
Log (typically append only)	AccessLog AccountHistory TransactionRecord	Overly Optimistic
Lookup/Reference (typically read only)	State PaymentType	Overly Optimistic

What is Lock escalation?

The process of converting many fine-grain locks into fewer coarse-grain locks. 

ie: 
fine-grain = ROW 

Coarse-grain = TABL

What is blocking in SQL Server?

Blocking in SQL Server is a scenario where one connection to SQL Server locks one or more records, and a second connection to SQL Server requires a conflicting lock type on the record or records locked by the first connection. This causes the second connection to wait until the first connection releases its locks. By default, a connection will wait an unlimited amount of time for the blocking lock to go away. 

Blocking is not the same thing as a deadlock.

What are Dirty pages?

Buffer pages that contain modifications that have not been written to disk.

Explain DeadLock.

Deadlock can be explained as follows:

Time	User 1 Actions	User 2 Actions
1	Starts Transaction
2		Starts Transaction
3	Updates row 2 in table A
4		Updates row 10 in table B
5	Attempts to update row 10 in table B
	U1 Is Blocked by U2
6		Attempts to update row 2 in table A
	U1 Is Blocked by U2	U2 Is Blocked by U1
	DEADLOCK!!!