Chapter 12 Implementing Transactions, Error Handling, and Dynamic SQL

Question 1

What is a transaction?

Answer 1

A transaction is a logical unit of work. Work completes as a whole unit or none of it does. For SQL Server, all changes to data take place in the context of a transaction. In other words, all operations that in any way write to the database are treated by SQL Server as transactions. This includes DML (INSERT, UPDATE) and DDL (CREATE TABLE, CREATE INDEX). Even single SELECT statements are a type of transaction (read-only transactions).

Question 2

What do the terms commit and rollback mean?

Answer 2

The terms commit and rollback refer to the act of controlling the result of transactions in SQL Server. When the work of a transaction has been approved by the user, SQL Server completes the transaction’s changes by committing them. If an unrecoverable error occurs or the user decides not to commit, then the transaction is rolled back.

Question 3

What does the ACID acronym stand for?

Answer 3

• (A) Atomicity - Every transaction is an atomic unit of work. All database changes in a transaction succeed or none of them succeed,
• (C) Consistency - Every transaction whether successful or not leaves the database in a consistent state as defined by all objects and database constraints,
• (I) Isolation - In regards to database changes, every transaction looks as though it occurs in isolation from other transactions. The degree of isolation can vary based on isolation level.
• (D) Durability - Every transaction endures through an interruption of service. When service is restored, all committed transactions are committed and all uncommitted transactions are rolled back.

Question 4

How does SQL Server enforce transactional isolation?

Answer 4

SQL Server ensures that when a transaction makes multiple changes to the database, none of the objects being changed by that transaction are allowed to be changed by any other transaction.

In other word’s, one transaction’s changes are isolated from any other transaction’s activities. If two transactions want to change the same data, one of them must wait until the other transaction is finished.

SQL Server accomplishes transactional isolation by means of locking, as well as, row versioning. SQL Server locks objects (rows and tables) to prevent other transactions from interfering.

Question 5

How does SQL Server maintain transactional durability?

Answer 5

SQL Server maintains transactional Durability by using the database transaction log. Every database change (DML or DDL) is first written to the transaction log with the original version of the data (in the case of updates and deletes).

For example, if the database server shuts down unexpectedly just after the fact of a successful commit has been written to the transaction log, when SQL Server starts back up, the transaction will be rolled forward and finished.

On the other hand, if the database server shuts down unexpectedly before a successful commit could be written to the log, when the database server starts back up, the transaction will be rolled back and any database changes undone.

Question 6

Which database has transaction logs?

Answer 6

Every database and every system database has a transaction log to enforce transaction durability.

Question 7

Can you turn off the transaction log?

Answer 7

No.

You cannot turn off a database’s transaction log or remove it. Some operations can somewhat reduce transaction logging. But all database changes are always written first to the transaction log.

Question 8

Why is it important for SQL Server to maintain the ACID properties of transactions?

Answer 8

To ensure that the integrity of the database data will not be compromised.

Question 9

What are the two types of transactions?

Answer 9

1. System transactions
   
   1. System transactions are not under user control. They maintain internal persistent system tables.
2. User transactions
   
   1. User transactions are created by users in the process of changing and/or reading data whether automatically, implicitly, or explicitly.

Question 10

What DMV can you query to observe the names of the transactions?

Answer 10

sys.dm_tran_active_transactions

The default name for a user transactions is user_transaction if one is not assigned by the user.

SELECT *

FROM sys.dm_tran_active_transactions

WHERE Name = ‘user_transaction’

DMV = Data Management Views

Question 11

What command(s) do you use to mark the start of a transaction - explicitly?

Answer 11

BEGIN TRANSACTION or BEGIN TRAN.

A name can be assigned to the transaction.

DECLARE @TranName VARCHAR(20);

SELECT @TranName = ‘MyTransaction’;

BEGIN TRANSACTION @TranName;

USE AdventureWorks2012;

DELETE FROM AdventureWorks2012.HumanResources.JobCandidate WHERE JobCandidateID = 13;

COMMIT TRANSACTION @TranName;

GO

Question 12

What command(s) do you use to mark the end of a transaction by committing it or rolling it back?

Answer 12

COMMIT TRANSACTION, COMMIT TRAN, COMMIT WORK, or COMMIT.

To roll back a transaction use ROLL BACK TRANSACTION, ROLLBACK TRAN, ROLLBACK WORK, or ROLLBACK.

Transactions can be nested.

Question 13

What does the @@TRANCOUNT system function indicate?

Answer 13

@@TRANCOUNT can be queried to find the level of the transaction.

@@TRANCOUNT = 0 indicates that the code is not within a transaction.

@@TRANCOUNT > 0 indicates that there is an active transaction

@@TRANCOUNT > 1 indicates the level of a nested transaction.

Question 14

What does the XACT_STATE() function indicate?

Answer 14

XACT_STATE() = 0 Indicates that there is no active transaction.

XACT_STATE() = 1 Indicates that there is an uncommitted transaction and it can be committed but the nesting level is not reported.

XACT_STATE() = -1 Indicates that there is an uncommitted transaction and it cannot be committed due to a prior fatal error.

Question 15

What are the three modes of user transactions in SQL Server?

Answer 15

1. Autocommit
2. Implicit transaction
3. Explicit transaction

Question 16

How does the autocommit mode of transactions behave?

Answer 16

Autocommit is the default transaction management mode. In autocommit mode, single DML and DDL statements are executed in the context of a transaction that will automatically be committed when the statement succeeds or automatically rolled back if the statement fails.

In the autocommit mode, you do not issue any surrounding transactional commands such as BEGIN TRAN, ROLLBACK TRAN, or COMMIT TRAN.

Also, @@TRANCOUNT is not normally detectable. Whatever changes you make to the database are automatically handled, statement by statement, as transactions.

Question 17

How does the implicit mode of transactions behave?

Answer 17

In the implicit transaction mode, when one or more DML or DDL statements or a SELECT statement is issued, SQL Server starts a transaction, increments @@TRANCOUNT, but does not automatically commit or roll back the statement.

You must issue a COMMIT or ROLLBACK to finish the transaction.

Implicit transaction mode is not the default, so you must enter the following command:

SET IMPLICIT_TRANSACTIONS ON; or SET ANSI_DEFAULTS ON;

Note that @@TRANCOUNT is incremented as soon as you enter any command to change data.

Also, implicit transactions can span batches.

Question 18

How does the explicit mode of transactions behave?

Answer 18

An Explicit Transaction occurs when the Begin Transaction command is issued and committed when the COMMIT TRANSACTION command is issued. When the Begin Transaction command is issued the @@TRANCOUNT is incremented by 1.

Note that when the Explicit Transaction mode is used within the Implicit Transaction mode, the value of @@TRANCOUNT will jump to 2 immediately after the BEGIN TRAN command - effectively becoming a nested transaction.

SET IMPLICIT_TRANSACTIONS ON; or SET ANSI_DEFAULTS ON;

AND

Begin Transaction

@@TRANCOUNT = 2

….

Commit Transaction

Question 19

What are some advantages of using implicit transactions?

Answer 19

1. After a command has been completed, the transaction can still be rolled back
2. Because the COMMIT statement must be issued, mistakes can be found after the execution of a command.

Question 20

What are some disadvantages of using implicit transactions?

Answer 20

1. Any locks are held until the transaction receives the COMMIT command. Therefore, the locks can block other users from doing their work.
2. It is not the standard method of using SQL Server, you must remember to set it for your session.
   
   1. If it is forgotten to commit an implicit transaction, the locks remain open.
3. The implicit transaction mode does not work well with explicit transactions because it causes the @@TRANCOUNT value to increment to 2

Question 21

Can transactions span batches?

Answer 21

Yes.

Both implicit and explicit transactions can span batches - that is, GO statements.

However, it is a best practice to ensure that each transaction takes place in one batch.

Question 22

What are nested transactions?

Answer 22

When explicit transactions are nested, they are called nested transactions. In this scenario, the behavior of COMMIT and ROLLBACK change when you nest transactions.

An inner COMMIT statement has no real effect on the transaction - only decrementing @@TRANCOUNT by 1.

Only the outermost COMMIT statement will commits the transaction.

Also, note that it doesn’t matter at what level a ROLLBACK command is issued. A transaction can only contain one ROLLBACK command and it will roll back the entire transaction and reset the @@TRANCOUNT to 0.

Question 23

How do you mark a transaction?

Answer 23

An explicit transaction can be named by putting the identifier after the BEGIN TRAN statement. Transaction names must follow the rules for SQL identifiers; however, only the first 32 characters are acknowledged. The transaction name is displayed in the name column of the sys.dm_tran_active_transactions DMV.

BEGIN TRANSACTION Tran1;

Note that SQL Server only records transaction names for the outermost transaction. If you have nested transactions, any names for the nested transactions are ignored.

Named transactions are used for placing a mark in the transaction log in order to specify a point at which one or more databases can be restored. When the transaction is recorded in the transaction log, the transaction mark is also recorded.

BEGIN TRAN Tran1 WITH MARK; COMMIT TRAN;

Question 24

How do you restore the database to a specific transaction mark?

Answer 24

RESTORE DATABASE TSQL2012 FROM DISK

=

‘C:\SQLBackups\TSQL2012.bak’

WITH NORECOVERY;

GO

RESTORE LOG TSQL2012 FROM DISK

=

‘C:\SQLBackups\TSQL2012.trn’

WITH STOPMARK

=

‘Tran1’;

GO

The transaction name, WITH STOPMARK = ‘Tran1’, must be used

A description after the clause WITH MARK, but SQL ignores it.

To restore just before the transaction, use WITH STOPBEFOREMARK

You can recover the dataset by restoring with either WITH STOPMARK or WITH STOPBEFOREMARK.

RECOVERY can be added to the WITH list but it has no effect.

Question 25

What are Savepoints?

Answer 25

Savepoints are locations within a transaction that can be used to roll back a selective subset of work.

A savepoint can be defined by using the SAVE TRANSACTION command.

The ROLLBACK statement must reference the savepoint.

Otherwise, if the statement is unqualified, it will rollback the entire transaction.

Question 26

What are Cross-database transactions?

Answer 26

Cross-database transactions span two or more databases on a single instance of SQL Server without any additional work on the user’s part.

SQL Server preserves the ACID properties of cross-database transactions without any additional considerations.

There are limitations on database mirroring when using cross-database transactions. A cross database transaction may not be preserved after a failover of one of the databases.

Question 27

What are Distributed transactions?

Answer 27

Distributed transactions span more than one server by using a linked server connection.

Question 28

How many COMMITs must be executed in a nested transaction to ensure that the entire transaction is committed?

Answer 28

One COMMIT must be issued for each level of the nested transaction. However, only the last COMMIT actually commits the entire transaction.

Question 29

What are the two general modes of locking in SQL Server?

Answer 29

1. Shared locks -
   
   1. Used for sessions that read ‘data - readers’.
2. Exclusive locks -
   
   1. Used for changes to ‘data - writers’.

Note: There are more advanced modes called update, intent, and schema locks used for special purposes.

Question 30

What happens when a session sets out to change data?

Answer 30

SQL Server will attempt to secure an exclusive lock on the objects in question. These exclusive locks always occur in the context of a transaction, even if only in the autocommit mode. When a session has an exclusive lock on an object (row, table), no other transaction can change that data until this transaction either commits or rolls back.

Except in special isolation levels, other sessions cannot even read exclusively locked objects.

Question 31

What happens when a session sets out to read data?

Answer 31

By default, SQL Server will issue very brief shared locks on the resource (row and table). Two or more sessions can read the same objects because shared locks are compatible with other shared locks.

Question 32

What happens when a session has a resource locked exclusively?

Answer 32

No other session can read the resource.

in addition, no other session can to write to the resource.

Other sessions must wait until the first session releases it’s exclusive lock.

Only shared locks are compatible with each other.

An exclusive lock is not compatible with any other kind of lock.

Question 33

What is the most granular type of resource?

Answer 33

The most granular type of resource for SQL Server is the row of a table. However, SQL Server can place a lock on an entire page or an entire table.

Question 34

How long are exclusive locks held?

Answer 34

Exclusive locks are held to the end of the transaction (commit or rollback).

Question 35

How long are shared locks held?

Answer 35

Shared locks are released as soon as the data is read, and they are not held to the end of the transaction except in higher isolation levels.

Question 36

What is a deadlock?

Answer 36

A deadlock results from mutual blocking between two or more sessions. This occurs due to a cyclical relationship between two or more commands. SQL Server detects this cycle as a deadlock between the two sessions, aborts one of the transactions, and returns an error message 1205 to the client.

Question 37

Can readers block writers?

Answer 37

Yes.

Though only momentarily, because any exclusive lock request has to wait until a shared lock is released.

Question 38

Can a SELECT statement be involved in a deadlock?

Answer 38

Yes.

If the SELECT statement locks some resource that keeps a second transaction from finishing, and the SELECT cannot finish because it is blocked by the same transaction, the deadlock cycle results.

Question 39

What are the 6 different isolation levels?

Answer 39

1. READ COMMITTED
2. READ COMMITTED SNAPSHOT
3. SNAPSHOT
4. READ UNCOMMITTED
5. REPEATABLE READ
6. SERIALIZABLE

Question 40

What is the READ UNCOMMITTED isolation level?

Answer 40

READ UNCOMMITTED isolation level allows the reading of uncommitted data. This removes shared locks normally applied by SELECT statements and will return data normally blocked by data writers.

The results of a READ UNCOMMITTED SELECT statement is the possible reading of uncommitted data. Uncommitted data can be changed during a transaction and/or later be rolled back to its initial state. This is called reading dirty data.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

The READ UNCOMMITTED statement can be applied to a single command instead of an entire session by using the WITH (READUNCOMMITTED) table hint.

SELECT *

FROM HR.Employees

WITH (READUNCOMMITTED) –> No Space READUNCOMMITTED.

Question 41

What is the READ COMMITTED isolation level?

Answer 41

The READ COMMITTED isolation level allows reading only committed data.

The READ COMMITTED isolation level is the default isolation level.

The READ COMMITTED SELECT statements will apply shared locks on any underlying data resources that are being changed by a different session (and therefore have exclusive locks) and that data will be blocked during the READ COMMITTED session.

Prevents dirty reads

Shared locks are held for the duration of the statement

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Question 42

What is the REPEATABLE READ isolation level?

Answer 42

The REPEATABLE READ isolation level guarantees that whatever data is read in a transaction can be re-read later in the transaction.

Updates and deletes on row level data belonging to the transaction are prevented. As a result, shared locks are kept until the end of a transaction. However, the transaction may see new rows added after it’s first read. This is called a phantom read. –Locking specific rows/pages. Does not block INSERTS.

This blocks existing data from being modified but allows new data to be inserted. Prohibits reading modified data that has not been committed. Also, prohibits other processes from modifying data that its’ transaction is modifying. This is suited for tables that are frequently updated (and maybe a lot of rows).

Question 43

What is the SERIALIZABLE isolation level?

Answer 43

The SERIALIZABLE isolation level is the strongest isolation level and is set per session. At this level, all reads are repeatable and new rows are not allowed in the underlying table that would satisfy the conditions of the SELECT statements in the transaction. It (Shared) locks a range. Held for the duration of the transaction. Blocks INSERTs into range. –No phantom reads. –Most contention.

This prevents users from modifying data until the transaction is complete. Prevents user from adding records to the transactions tables, even if the records are not related. This locks a range of records and is suited for tables that are infrequently used (and maybe few rows).

Question 44

What is the SNAPSHOT isolation level?

Answer 44

The SNAPSHOT isolation level uses row versioning in tempdb. It is enabled as a persistent database property and then set per transaction. A transaction using SNAPSHOT isolation level will be able to repeat any reads, and it will not see any phantom reads. New rows can be added to the table, but the transaction will not see them. The SNAPSHOT isolation level does not require shared locks on the underlying data because it is using row versioning in the tempdb. The SELECT will retrieve all committed data at the point in time in which the SELECT command was issued. – No read locks are taken.

Question 45

What is the READ COMMITTED SNAPSHOT isolation level?

Answer 45

This is an optional way of using the default READ COMMITTED isolation level.

Instead of requesting shared locks for data access, it will use the version store by default. SELECT statements no longer need shared locks on the underlying resource while only reading (originally) committed data. Non repeatable reads are still possible.

ALTER DATABASE TSQL2012 SET READ_COMMITTED_SNAPSHOT ON;

Note that this is the default isolation level for a Windows Azure SQL Database.

Question 46

What is the difference between SNAPSHOT and READ COMMITTED SNAPSHOT isolation levels?

Answer 46

READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes.

In contrast, SNAPSHOT does optimistic reads and optimistic writes.

Optimistic assumes that nothing’s going to change while you’re reading it.

Pessimistic assumes that something will and locks it.

Question 47

How are isolation levels set?

Answer 47

Isolation levels are set per session.

Unless a different isolation level is set per session, all your transactions will execute using the default isolation level, READ COMMITTED.

Question 48

Does the PRINT command automatically occur in the context of a transaction?

Answer 48

No.

The PRINT command does not change data and therefore does not execute by itself in a transaction.

Question 49

Does the REPEATABLE READ isolation level reduce blocking or deadlocks?

Answer 49

No.

REPEATABLE READS hold shared locks until the end of a transaction, and therefore can increase blocking and deadlocks.

Question 50

Do the READ UNCOMMITTED, READ COMMITTED SNAPSHOT, and SNAPSHOT isolation levels reduce blocking deadlocks?

Answer 50

Yes.

1. READ UNCOMMITTED causes no shared locks to be used.
2. READ COMMITTED SNAPSHOT, as well as, SNAPSHOT reduce shared locks by reading committed data from committed versions and not by using shared locks.

Question 51

What is the @@ERROR function?

Answer 51

When an error condition occurs, the system function @@ERROR will have a positive integer value indicating the error number. It returns 0 if the previous statement encountered no errors.

Question 52

For T-SQL code that generates an error outside a TRY/CATCH, will the error message be passed to the client?

Answer 52

Yes.

The error message will be passed to the client and cannot be intercepted in T-SQL code.

Question 53

What are the two commands to raise your own errors?

Answer 53

1. The older RAISERROR command
2. The SQL Server 2012 THROW command

Question 54

What are the 4 parts of an error message in SQL Server?

Answer 54

1. Error number
2. Severity level
3. State
4. Error message

Question 55

What is the Error number part of a SQL Server error message?

Answer 55

SQL Server error messages are numbered 1 through 49999.

Custom error messages are numbered 50001 and higher.

The error number 50000 is reserved for a custom message that does not have a custom error number.

Question 56

What is the Severity level part of a SQL Server error message?

Answer 56

• SQL Server defines 26 severity levels numbered from 0 through 25.
• Errors with severity level 0 through 09 are informational only.
• Error 10 Informational messages that return status information or report errors that are not severe. For compatibility reasons, the Database Engine converts severity 10 to severity 0 before returning the error information to the calling application.
• Errors 11 - 16 Indicate errors that can be corrected by the user.
• As a general rule, errors with a severity level of 16 or higher are logged automatically to the SQL Server log and the Windows Application log.
• Errors 17 - 19 Indicate software errors that cannot be corrected by the user. Inform your system administrator of the problem.
• Errors with a severity level from 19 through 25 can be specified only be members of the sysadmin fixed server role,
• Errors with a severity level from 20 through 25 are considered fatal and cause the connection to be terminated and any open transactions to be rolled back.

Question 57

What is the State part of a SQL Server error message?

Answer 57

This is an integer with a maximum value of 127, used by Microsoft for internal purposes.

Question 58

What is the Error message part of a SQL Server error message?

Answer 58

The error message can be up to 255 unicode characters long.

SQL Server error messages are listed in sys.messages.

You can add your own custom messages by using sp_addmessage.

Question 59

What are some examples of how RAISERROR can be invoked?

Answer 59

Minimum Requirements for RAISERROR

1. A Message (a message id, string, string variable)
2. The severity
3. The state

RAISERROR( Message, Severity, State);

RAISERROR(‘Error in sp_name stored procedure’, 16, 0);

To use printf style formatting in the string:

RAISERROR(‘Error in % stored procedure’, 16, 0, ‘sp_name’);

To use a variable for the message:

DECLARE @message AS VARCHAR(1000) = ‘Error in % stored procedure’;

RAISERROR(@message, 16, 0, ‘sp_name’);

To add formatting outside RAISERROR by using the FORMATMESSAGE function

DECLARE @message AS VARCHAR(1000) = ‘Error in % stored procedure’;

SELECT @message = FORMATMESSAGE(@message, ‘sp_name’);

RAISERROR (@message, 16, 0);

Note the ‘sp_name’ is incorporated into the message returned to the client.

Question 60

What are some of the more advanced features that were available in RAISERROR?

Answer 60

1. Purely informational messages (similar to PRINT) can be issued by using a severity level of 0 through 9.
2. A RAISERROR with a severity level > 20 can be issued when the WITH LOG option together with the SQL Server sysadmin role. SQL Server will then terminate the connection when the error is raised.
3. The RAISERROR with NOWAIT can be used to send messages immediately to the client. The message does not wait in the output buffer before being sent.

Question 61

What are some examples of how THROW can be invoked?

Answer 61

THROW 50000, ‘Error in sp_name stored procedure’, 0;

THROW does not allow formatting the message parameter.

A work around is to use FORMATMESSAGE():

DECLARE @message AS VARCHAR(1000) = ‘Error in % stored procedure’;

SELECT @message = FORMATMESSAGE(@message, ‘sp_name’);

THROW 50000, @message, 0;

Question 62

What are the difference between THROW and RAISERROR?

Answer 62

• THROW does not use parentheses to delimit parameters
• THROW can be used without parameters, but only in the CATCH block of a TRY/CATCH construct
  
  • ;THROW
• When parameters are used with the THROW command these are required:
  
  • error_number
    
    • error_number does not require a match defined in sys,messages
  • message, and
    
    • message parameter does not allow formatting, but you can use FORMATMESSAGE() with a variable to get the same effect.
  • state.
    
    • state parameter must be an integer that ranges from 0 to 255.
• Any parameter can be a variable.
• Except when used in the TRY block, THROW always terminates the batch.
• THROW with a NOWAIT command cannot be used to cause an immediate buffer output.
• There is no severity parameter, the severity is always set to 16.
  
  • THROW cannot be used with a severity level higher than 16 by using the WITH LOG clause as you can with RAISERROR.

Question 63

Does the statement before the THROW statement have to be terminated by a semicolon?

Answer 63

Yes.

The statement before the THROW statement must be terminated by a semicolon.

This reinforces the best practice to terminate all T-SQL statements with a semicolon.

Question 64

How do RAISERROR and THROW compare as far as batch termination?

Answer 64

RAISERROR(‘Hi there’, 16, 0);

PRINT ‘RAISERROR error’; –prints…

GO

THROW 50000, ‘Hi There’, 0;

PRINT ‘Throw error’; – does not print…

GO

Question 65

What is the TRY_CONVERT function?

Answer 65

TRY_CONVERT attempts to cast a value as a target data type, and if it succeeds, returns the value, otherwise, returning NULL if the test fails.

SELECT TRY_CONVERT(DATETIME, ‘1752-12-31’); – returns NULL

SELECT TRY_CONVERT(DATETIME, ‘1753-01-01’); – returns the converted DATETIME.

Question 66

What is the TRY_PARSE function?

Answer 66

TRY_PARSE allows you to take an input string containing data of an indeterminate data type and convert it to a specific data type if possible, and return NULL if not possible.

SELECT TRY_PARSE(‘1’ AS INT); – converts 1 to an integer and returns it

SELECT TRY_PARSE(‘B’ AS INT); – returns NULL since ‘B’ is not an integer.

Question 67

How can you add custom error messages?

Answer 67

You can use the stored procedure sp_addmessage to add your own custom messages.

EXEC sp_addmessage

50001, –> Error Number

16, –> Severity Number

N’Percentage expects a value between 20 and 100. Please reexecute with a more appropriate value.’

–> Message

; GO

Question 68

What is severity level 0 used for?

Answer 68

When RAISERROR is issued with a severity level 0, only an information message is sent.

If you add WITH NOWAIT, the message will be sent without waiting in the output buffer.

This does not apply to the THROW command.

Question 69

What are the two error handling methods available?

Answer 69

1. Unstructured
   
   1. With unstructured error handling, Each error must be handled as it happens by accessing the @@ERROR function.
2. Structured.
   
   1. A central location is designated (The Catch Block) to handle errors.

Question 70

How do you perform unstructured error handling using @@ERROR?

Answer 70

Unstructured error handling consists of testing individual statements for their error status immediately after they execute by querying the @@ERROR system function.

When SQL Server executes any T-SQL statement, it records an error status of the command result in @@ERROR.

You can query @@ERROR to find the error number.

If the statement succeeded, @@ERROR will be 0, and if the statement fails @@ERROR will contain the error number.

Just by querying the @@ERROR function, even if it’s in an IF clause, causes it to be reset to a new number.

Therefore it is not possible to test the value of @@ERROR inside the error handling code.

Instead it is better to add code that captures @@ERROR in a variable and then test the variable.

Question 71

What is XACT_ABORT?

Answer 71

XACT_ABORT is used to make an entire batch roll back if any error with a severity > 10 occurs.

SET XACT_ABORT ON; This is set per session.

After it is set to ON, all remaining transactions in that session are subject to it until it is set to OFF.

SET XACT_ABORT causes a transaction to be rolled back based on any error with severity > 10.

However, XACT_ABORT has many limitations:

1. The error can’t be trapped and the error number can’t be caught.
2. Any error with severity level > 10 causes the transaction to roll back.
3. None of the remaining code in the transaction is executed. Even the final PRINT statements of the transaction are not executed.
4. After the transaction is aborted, you can only infer what statements failed by inspecting the error message returned to the client by SQL Server.

Question 72

When was the TRY/CATCH construct added?

Answer 72

SQL Server 2005 added the TRY/CATCH construct to provide structured error handling to SQL Server.

Question 73

How does TRY/CATCH work?

Answer 73

• Wrap the code to test for errors in a TRY block. Every TRY block must be followed by a CATCH block where the errors are handled.
• If an error condition is detected inside the TRY block, control is passed to its corresponding CATCH block for error handling. The remaining statements in the TRY block are not executed.
• When SQL Server encounters an error in the TRY block, no message is sent to the client. This contrasts sharply with unstructured error handling where an error message is always sent to the client and cannot be intercepted. Even a RAISERROR in the TRY block (severity level 11-19) will not generate a message to the client, but instead transfers control to the CATCH block.

Question 74

What are the rules for TRY/CATCH?

Answer 74

• Errors with severity greater than 10 and less than 20 within the TRY block result in transferring control to the CATCH block.
• Errors with severity of 20 and greater that do not close connections are also handled by the CATCH block.
  
  • Errors that do cause the connection to be terminated, such as fatal errors or the sysadmin executed KILL command, will not be trapped with a Try/Catch Block.
• Compile errors and some runtime errors involving statement level compilation abort the batch immediately and do not pass control to CATCH.
  
  • Errors that occur due to compilation errors, syntax errors, or non-existent objects cannot be trapped.
• If an error is encountered in the CATCH block, the transaction is aborted and the error is returned to the calling application unless the CATCH block is nested within another TRY block.
• Within a CATCH block, you can commit or rollback the current transaction unless the transaction cannot be committed and must be rolled back. To test for the state of a transaction, you can query the XACT_STATE function.
• TRY/CATCH blocks can be nested.

Question 75

What functions can be used within the CATCH block to report on errors?

Answer 75

1. ERROR_NUMBER - Returns the error number,
2. ERROR_MESSAGE - Returns the error message,
3. ERROR_SEVERITY - Returns the severity of the error,
4. ERROR_LINE - Returns the line number of the batch where the error occurred,
5. ERROR_PROCEDURE - The function, trigger, or procedure name that was executing when the error occurred,
6. ERROR_STATE - The state of the error.

These functions can be encapsulated in a stored procedure along with additional information such as the database and server name, and then call the stored procedure from the various CATCH blocks.

Question 76

How can you “re-throw” an error in the CATCH block?

Answer 76

There are 3 options to re-throw an error in a Catch Block

1. RAISERROR,
   
   1. Use RAISERROR in the CATCH block to report the original error back to the client or to raise an additional error. The original error number cannot be re-raised. It must be a custom error message number or the default error number 50000. To return the error number, add it to the @error_message_string. Execution of the CATCH block continues after the RAISERROR statement.
2. THROW with parameters, and
   
   1. THROW with parameters always raises errors with a custom error number and a severity level of 16, so the exact information is lost. THROW with parameters terminates the batch, so commands following it are not executed.
3. THROW without parameters.
   
   1. THROW without parameters can be used to re-raise the original error message and send it back to the client. This is by far the best method for reporting the error back to the caller.

Question 77

How does XACT_ABORT behave when used in a TRY/CATCH?

Answer 77

XACT_ABORT behaves different when used in a TRY block. Normally, XACT_ABORT terminates the transaction and rolls back any changes. Instead, it’ll transfer control to the CATCH Block. And, the transaction is left in an uncommittable state (XACT_STATE() returns -1). Therefore, a transaction inside a CATCH block cannot be committed if XACT_ABORT is turned on; it must be rolled back. Within the CATCH block, you can determine the current transaction nesting level with the @@TRANCOUNT system function. If you have nested transactions, you can retrieve the state of the innermost transaction with the XACT_STATE function.

Question 78

What are the main advantages of using TRY/CATCH block over the traditional trapping for @@ERROR?

Answer 78

The main advantage is that you have one place in your code that errors will be trapped, so you only need to put error handling in one place.

Question 79

Can a TRY/CATCH block span batches?

Answer 79

No.

You must have one set of TRY/CATCH blocks for each batch of code.

Question 80

What is the advantage of using THROW in a CATCH block?

Answer 80

The THROW statement in a CATCH block can re-throw an error and thereby allow you to report on an error from the TRY block without having to store any prior information.

This makes it possible to do all error handling in the CATCH block.

Question 81

What functions can be used in a CATCH block to return information about the error?

Answer 81

1. @@ERROR changes with each successful statement, so if it’s accessed in the very first statement of the CATCH block, you can get the original error message.
2. ERROR_NUMBER returns the error number from the original error that led to control being passed to the CATCH block
3. ERROR_MESSAGE returns the text of the original error.
4. XACT_STATE() tells you the state of the transaction in a CATCH block, in particular whether the transaction is committable.
   
   1. XACT_STATE() = 0 Indicates that there is no active transaction.
   2. XACT_STATE() = 1 Indicates that there is an uncommitted transaction and it can be committed but the nesting level is not reported.
   3. XACT_STATE() = -1 Indicates that there is an uncommitted transaction and it cannot be committed due to a prior fatal error.

Question 82

How does SET XACT_ABORT ON affect a transaction?

Answer 82

If a T-SQL error with a severity level > 10 occurs, the transaction will be aborted and rolled back in its entirety.

Question 83

What is Dynamic SQL?

Answer 83

Dynamic SQL refers to the technique of using T-SQL code to generate and execute other T-SQL. This can come in handy in numerous cases where variables cannot be substituted for literals in T-SQL code.

Examples include

• The database name in the USE statement
• Table names in the FROM clause
• Column names in the SELECT, WHERE, GROUP BY, and HAVING clauses in addition to the ORDER BY clause
• Contents of lists such as in the IN and PIVOT clauses.

Question 84

What is the QUOTED_IDENTIFIER setting?

Answer 84

When SET QUOTED_IDENTIFIER is ON which is the default, string literals are delimited using the single quote character, and T-SQL identifiers are delimited using either the double quote character or the square bracket character.

When SET QUOTED_IDENTIFIER is OFF, then string literals can be delimited with either the single or double quote character. However, square brackets must be used to delimit T-SQL identifiers.

Question 85

Why should you leave QUOTED_IDENTIFIER set to ON?

Answer 85

Because that is the ANSI standard and the SQL Server default.

It is more portable code.

Question 86

When using only single quotation marks as string delimiters, what do you do about embedded single quotation marks?

Answer 86

To handle embedded single quote marks, change them to double quote marks.

This can be problematic, as it is difficult to read and can be difficult to do when there are many embedded single quote marks.

And alternative is to use the QUOTENAME function to automatically double up on the single quote marks.

It returns a Unicode string with parenthesis delimiters:

SELECT QUOTENAME(‘abcdef’, ‘()’);

The ‘()’ is Optional. A one-character string to use as the delimiter. Can be a single quotation mark ( ‘ ), a left or right bracket ( [] ), a double quotation mark ( “ ), a left or right parenthesis ( () ), a greater than or less than sign ( >< ), a left or right brace ( {} ) or a backtick ( ` ). If quote_char is not specified, brackets are used.

PRINT QUOTENAME(N’5678 rue de l’’ Abbey’, ‘’’’);

This will output ‘5678 rue de l’’’’ Abbey’

Question 87

What are the two methods available to execute the dynamic SQL?

Answer 87

1. the EXECUTE statement and
2. the sp_executesql stored procedure

Question 88

What is the EXECUTE statement?

Answer 88

EXECUTE is the simplest method provided by SQL Server for executing dynamic SQL.It can be abbreviated as EXEC. The EXEC statement has several uses of which only one is for dynamic SQL:

• Executing stored procedures
• Impersonating users or logins
• Querying a linked server
• Executing dynamic SQL strings To generate dynamic SQL

EXEC accepts a character string as input in parenthesis.

• The input string must be a single T-SQL batch. The string can contain many T-SQL commands, but the string cannot contain GO delimiters.
• You can provide the dynamic SQL as a string literal, string variables, or a concatenation of the two.
• The string variables can have any string data type (regular or unicode).
• The string variables can have MAX length definitions. EXEC (@SqlString); EXEC (@SqlString + @tableName);

Question 89

Can you generate and execute dynamic SQL in a different database than the one you code is in?

Answer 89

Yes.

The USE command can be inserted into a dynamic SQL batch.

Question 90

What are some objects that cannot be referenced in T-SQL by using variables?

Answer 90

• Database names in the USE statement,
• table names in the FROM clause
• column names in the SELECT and WHERE clauses, and
• lists of literal values in the IN and PIVOT functions.

Question 91

What is a SQL Injection attack?

Answer 91

When you use dynamic SQL that sends user input to the database, your application could be subject to a SQL injection attack. This is where a user enters something that was not intended to be executed… Hackers have learned that by inserting just a single quotation mark, they can sometimes cause an application to report back an error message to the user indicating the command that has been assembled using dynamic SQL and may be hackable,

Example

“unclosed quotation mark after the character string ‘’’ “ This message can tell a hacker that they have terminated a string early so that a “dangling” string delimiter was detected. Now, all the hacker has to type is a single line comment after the single quotation mark to make SQL Server ignore the trailing single quotation mark. The hacker types ‘–. If this removes the error message, then the hacker knows that another T-SQL command can be injected into the string as in the following:

’ SELECT TOP 1 name FROM sys.tables –

Question 92

How do you prevent SQL injection attacks?

Answer 92

One of the most important methods is to parameterize the dynamic SQL generation and execution by using sp_executesql.

Question 93

For a SQL injection attack, where is the injected code executed?

Answer 93

Between the initial single quotation mark that terminates the data input string and the final comment which disables the internal terminating string quotation mark.

Question 94

What is the sp_executesql stored procedure?

Answer 94

The sp_executesql stored procedure was introduced as an alternative to using the EXEC command for executing dynamic SQL. It supports both generating and executing dynamic SQL. sp_executesql supports parameters including output parameters. The parameters must be passed in as unicode. Because of this, sp_executesql is more secure and can help prevent some types of SQL injection.

Note that the sp_executesql parameters cannot be used to replace required string literals such as table and column names. The @statement input parameter is basically an NVARCHAR(MAX). You submit a statement in the form of a unicode string in the @statement parameter and embed parameters in that statement you would like to have substituted in the final string. You list those parameter names with their data types in the @params string, and then put the values in the @param1 list, @param 2 list ,etc.

SET @Address = ‘5678 rue de l’‘Abbaye’;

SET @SqlString = N’SELECT * FROM [Sales].[Customers] WHERE address = @address’;

EXEC sp_executesql @statement = @SQLString, @params = N’@address NVARCHAR(60)’, @address = @address;

Question 95

Does the sp_executesql stored procedure provide better query performance than the EXEC command?

Answer 95

The sp_executesql stored procedure can sometimes provide better query performance than the EXEC command because it’s parameterization aids in reusing cached execution plans. Because sp_executesql forces you to parameterize, often the actual query string will be the same and only the parameter values will change. Then SQL Server can keep the overall string constant and reuse the query plan created for the original call of that distinct string to sp_executesql.

Question 96

How can you pass information from sp_executesql to the caller?

Answer 96

Use one or more output parameters. You can also persist the data in a permanent or temporary table, but the most direct method is through the OUTPUT parameter.

Question 97

How does sp_executesql help to stop SQL injection?

Answer 97

You can use sp_executesql to parametertize user input which can prevent any injected code from being execute.

Question 98

What are the advantages of sp_executesql over the EXECUTE command?

Answer 98

1. parameterization is the key advantage of sp_executesql over the EXEC command because it ensures that any injected code will only be seen as a string parameter value and not as executable code.
2. The use of output parameters solves a serious limitation of EXECUTE command. EXECUTE cannot return information to the calling session directly.