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. Either all the work completes as a whole unit or none of it does. For SQL Server, all changes to database 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). Technically, 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, meaning that all database changes in the 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 - Every transaction looks as though it occurs in isolation from other transactions in regard to database changes 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 have transaction logs?

Answer 6

Every database including 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 and (2) User transactions. System transactions are not under user control. They are used by SQL Server to maintain its internal persistent system tables. User transactions are created by users in the process of changing and even 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 user transactions is user_transaction if one is not assigned by the user.

Question 11

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

Answer 11

BEGIN TRANSACTION, BEGIN TRAN. A name can be assigned to the transaction.

Question 12

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

Answer 12

To commit a transaction, issue the COMMIT TRANSACTION command, which you can also write as COMMIT TRAN, COMMIT WORK, or just COMMIT. To roll back a transaction, issue the ROLL BACK TRANSACTION command or alternatively, ROLLBACK TRAN, ROLLBACK WORK, or just 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, at this point, the code is not within a transaction.

– @@TRANCOUNT > 0 indicates that there is an active transaction and a number > 1 indicates the level of a nested transaction.

Question 14

What does the XACT_STATE() function indicate?

Answer 14

– A state of 0 indicates that there is no active transaction.

– A state of 1 indicates that there is an uncommitted transaction and it can be committed but the nesting level is not reported.

– A state of -1 indicates that there is an uncommitted transaction but 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. Further, @@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 you issue one or more DML or DDL statements or a SELECT statement, 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 you explicitly issue the BEGIN TRANSACTION or BEGIN TRAN command to start a transaction. As soon as you enter the BEGIN TRAN command, the value of @@TRANCOUNT is incremented by 1. Then you issue your DML or DDL commands, and when ready, issue COMMIT or ROLLBACK.

Note that you can use explicit transactions in implicit transaction mode, but if you start an explicit transaction when running your session in implicit transaction mode, the value of @@TRANCOUNT will jump to 2 immediately after the BEGIN TRAN command - effectively becoming a nested transaction.

Question 19

What are some advantages of using implicit transactions?

Answer 19

(1) You can roll back an implicit transaction after the command has been completed.
(2) Because you must issue the COMMIT statement, you may be able to catch mistakes after the command finished.

Question 20

What are some disadvantages of using implicit transactions?

Answer 20

(1) Any locks taken out by your command are held until you complete the transaction. Therefore, you could end up blocking other users from doing their work. (2) Because this is not the standard method of using SQL Server, you must constantly remember to set it for your session. (3) The implicit transaction mode does not work well with explicit transactions because it causes the @@TRANCOUNT value to increment by 2 unexpectedly. (4) If you forget to commit an implicit transaction, you may leave locks open.

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. Just the outermost COMMIT statement, actually commits the transaction.

Also, note that it doesn’t matter at what level you issue a ROLLBACK command. 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

You can name an explicit transaction by putting the name 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.

e.g.

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 to which one or more databases can be restored. When the transaction is recorded in the transaction log, the transaction mark is also recorded.

e.g.

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

• • You must use the transaction name with STOPMARK
• • You can place a description after the clause WITH MARK, but SQL Server ignores it
• • You can restore to just before the transaction with STOPBEFOREMARK.
• • You can recover the dataset by restoring with either WITH STOPMARK or STOPBEFOREMARK.
• • You can add RECOVERY to the WITH list but it has no effect.

Question 25

What are Savepoints?

Answer 25

Savepoints are locations within a transaction that you can use to roll back a selective subset of work. (1) You can define a savepoint by using the SAVE TRANSACTION command. (2) 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. (1) SQL Server preserves the ACID properties of cross-database transactions without any additional considerations. (2) 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.

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 - Used for sessions that read data - readers. (2) Exclusive locks - Used for changes to data - writers.

Note that 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 to not being able to write to the resource.

The other session must wait until the first 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 may need to place lock on an entire page or an entire table.

Question 34

How long are exclusive locks held?

Answer 34

In a transaction, 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, even if only momentarily, because any exclusive lock request has to wait until the 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 UNCOMMITTED, (3) READ COMMITTED SNAPSHOT, (4) REPEATABLE READ, (5) SNAPSHOT, (6) SERIALIZABLE

Question 40

What is the READ UNCOMMITTED isolation level?

Answer 40

This isolation level allows readers to read uncommitted data. This setting removes the shared locks taken by SELECT statements so that readers are no longer blocked by writers. No shared locks are requested. Still makes exclusive locks. However, the results of a SELECT statement could read uncommitted data that was changed during a transaction and the later was rolled back to it’s initial state. This is called reading dirty data.

• -All of the bad data issues
• -No shared locks requested
• -Sets exclusive locks
• -(No Lock)
• -Most concurrency (parallel queries).

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

You can also apply the READ UNCOMMITTED statement to a single command instead of applying the isolation level to the entire session by using the READCOMMITTED table hint.

SELECT * FROM HR.Employees WITH (READUNCOMMITTED).

Question 41

What is the READ COMMITTED isolation level?

Answer 41

This isolation level is the default isolation level. All readers in that session will only read data changes that have been committed. So all the SELECT statements will attempt to acquire shared locks and any underlying data resources that are being changed by a different session (and therefore have exclusive locks) will block the READ COMMITTED session.

• -Prevents dirty reads
• -Shared locks held for the duration of the statement - data access statement.
• -Exclusive locks held for duration of the transaction.

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

Question 42

What is the REPEATABLE READ isolation level?

Answer 42

This isolation level, also set per session, guarantees that whatever data is read in a transaction can be re-read later in the transaction. Updates and deletes of rows already selected 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.

Question 43

What is the SERIALIZABLE isolation level?

Answer 43

This isolation level is the strongest 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.

• -Shared locks a range. Held for the duration of the transaction. Blocks INSERTs into range.
• -No phantom reads.

–Most contention.

Question 44

What is the SNAPSHOT isolation level?

Answer 44

This 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 may be added to the table, but the transaction will not see them. Because it uses row versioning, the SNAPSHOT isolation level does not require shared locks on the underlying data.

The SELECT will retrieve all committed data as of the point in time in which the SELECT command was issued.

• • No read locks are taken.
• -uses

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.

Question 47

How are isolation levels set?

Answer 47

Isolation levels are set per session. If you do not set a different isolation level in your session, all your transactions will execute using the default isolation level, READ COMMITTED.

Question 48

Does that PRINT statement 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. It actually holds shared locks until the end of a transaction, and therefore can actually increase blocking and deadlocks.

Question 50

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

Answer 50

Yes.READ UNCOMMITTED causes no shared locks to be used. READ COMMITTED SNAPSHOT and SNAPSHOT reduce shared locks by reading committed data from committed versions and not be using shared locks.

Question 51

What is the @@ERROR function?

Answer 51

When SQL Server generates an error condition, 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, but is not inside 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

The error number part is an integer value. 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 security levels numbered from 0 through 25. 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 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. Errors with severity level 0 through 10 are informational only.

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

Note that the message (a message id, string, string variable) along with the severity and state are required at a minimum:

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

You can also use printf style formatting in the string:

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

You can use a variable for the message:
DECLARE @message AS VARCHAR(1000) = ‘Error in % stored procedure’;
RAISERROR(@message, 16, 0, ‘sp_name’);

And you can 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);

Question 60

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

Answer 60

(1) You can issue purely informational messages (similar to PRINT) by using a severity level of 0 through 9. (2) You can issue RAISERROR with a severity level > 20 if you use the WITH LOG option and if you have the SQL Server sysadmin role. SQL Server will then terminate the connection when the error is raised. (3) You can use RAISERROR with NOWAIT 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;

Because THROW does not allow formatting of the message parameter, you can 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 parenthesis to delimit parameters
• • THROW can be used without parameters, but only in the CATCH block of a TRY/CATCH construct
• • When parameters are supplied, error_number, message, and state are all required.
• • The error_number does not require a match defined in sys,messages,
• • The message parameter does not allow formatting, but you can use FORMATMESSAGE() with a variable to get the same effect.
• • The state parameter must be an integer that ranges from 0 to 255.
• • Any parameter can be a variable.
• • There is no severity parameter, the severity is always set to 16.
• • THROW always terminates the batch except for when it’s used in a TRY block.
• -You cannot issue THROW with a NOWAIT command in order to cause immediate buffer output.
• -You cannot issue THROW using 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.

Question 68

What is severity level 0 used for?

Answer 68

When you issue RAISERROR with 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.

Question 69

What are the two error handling methods available?

Answer 69

Unstructured and structured. With unstructured error handling, you must handle each error as it happens by accessing the @@ERROR function. With structured error handling, you can designate a central location (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

You can make an entire batch fail if any error occurs by beginning it with SET XACT_ABORT ON. You set XACT_ABORT per session. After it is set to ON, all remaining transactions in that setting 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:

• • You cannot trap for the error or capture the error number.
• • Any error with severity level > 10 causes the transaction to roll back.
• • None of the remaining code in the transaction is executed. Even the final PRINT statements of the transaction are not executed.
• • 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

– You wrap the code that you want to test for errors in a TRY block. Every TRY block must be followed by a CATCH block where you handle the errors.

– 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.

– Compile errors and some runtime errors involving statement level compilation abort the batch immediately and do not pass control to CATCH.

– 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.

– A TRY/CATCH block does not trap errors that cause the connection to be terminated such as fatal errors or a sysadmin executing the KILL command.

– You can also not trap errors that occur due to compilation errors, syntax errors, or non-existent objects.

– 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.

You can encapsulate calls to these functions 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

To re-throw an error in the CATCH block, you have 3 options: RAISERROR, THROW with parameters, and THROW without parameters.

You can use RAISERROR in the CATCH block to report the original error back to the client or to raise an additional error that you want to report. 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, you could add it to the @error_message_string.

Execution of the CATCH block continues after the RAISERROR statement.

You can use a THROW statement with parameters, like RAISERROR to re-raise the error in the CATCH block. However, THROW with parameters always raises errors with a custom error number and a severity level of 16, so you don’t get the exact information. THROW with parameters terminates the batch, so commands following it are not executed.

A 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. Instead of terminating the transaction as it does in unstructured error handling. XACT_ABORT transfers control to the CATCH block and as expected any error is fatal. The transaction is left in an uncommittable state (XACT_STATE() returns -1). Therefore you cannot commit a transaction inside a CATCH block if XACT_ABORT is turned on; you must roll it 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 in 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.

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.

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, you delimit string literals by using the single quotation marks, and use double quotation marks only to delimit T-SQL identifiers.

When SET QUOTED_IDENTIFIER is OFF, then along with single quotations marks, you can also use double quotations marks to delimit strings. But then you must use square brackets 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.

Question 86

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

Answer 86

In order to handle embedded single quotation marks, you must change the intended single quotation mark into two single embedded quotation marks in order to get the output of one single quotation mark, e.g.

WHERE address = N’5678 rue de l’’ Abbaye’;

Unfortunately, embedding two single quotation marks for every intended output single quotation mark makes it difficult to read and understand dynamic SQL when dealing with delimited strings. To PRINT the previous command, you would have to double up you single quotation marks again.

An alternative is to use the QUOTENAME function, which can hide the complexity of the embedded quotation marks. You can use QUOTENAME to automatically double up the number of quotation marks, e.g.

PRINT QUOTENAME(N’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, because 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, e.g. “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 which terminates the data input string and a final comment which disables the internal terminating sting 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.

e.g.

SET @SqlString = N’SELECT * FROM [Sales].[Customers] WHERE address = @address’;
SET @Address = ‘5678 rue de l’‘Abbaye’;

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.