What is a transaction?
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).
What do the terms commit and rollback mean?
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.
What does the ACID acronym stand for?
(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.
How does SQL Server enforce transactional isolation?
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.
How does SQL Server maintain transactional durability?
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.
Which database have transaction logs?
Every database including every system database has a transaction log to enforce transaction durability.
Can you turn off the transaction log?
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.
Why is it important for SQL Server to maintain the ACID properties of transactions?
To ensure that the integrity of the database data will not be compromised.
What are the two types of transactions?
(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.
What DMV can you query to observe the names of the transactions?
sys.dm_tran_active_transactions. The default name for user transactions is user_transaction if one is not assigned by the user.
What command(s) do you use to mark the start of a transaction - explicitly?
BEGIN TRANSACTION, BEGIN TRAN. A name can be assigned to the transaction.
What command(s) do you use to mark the end of a transaction by committing it or rolling it back?
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.
What does the @@TRANCOUNT system function indicate?
@@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.
What does the XACT_STATE() function indicate?
– 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.
What are the three modes of user transactions in SQL Server?
(1) Autocommit, (2) Implicit transaction, (3) Explicit transaction
How does the autocommit mode of transactions behave?
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.
How does the implicit mode of transactions behave?
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;
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.
How does the explicit mode of transactions behave?
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.
What are some advantages of using implicit transactions?
(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.
What are some disadvantages of using implicit transactions?
(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.
Can transactions span batches?
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.
What are nested transactions?
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.
How do you mark a transaction?
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.
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.
BEGIN TRAN Tran1 WITH MARK;
How do you restore the database to a specific transaction mark?
RESTORE DATABASE TSQL2012 FROM DISK = ‘C:\SQLBackups\TSQL2012.bak’
RESTORE LOG TSQL2012 FROM DISK = ‘C:\SQLBackups\TSQL2012.trn’
WITH STOPMARK = ‘Tran1’;
- 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.
What are Savepoints?
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.
What are Cross-database transactions?
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.
What are Distributed transactions?
Distributed transactions span more than one server by using a linked server.
How many COMMITs must be executed in a nested transaction to ensure that the entire transaction is committed?
One COMMIT must be issued for each level of the nested transaction. However, only the last COMMIT actually commits the entire transaction.
What are the two general modes of locking in SQL Server?
(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.
What happens when a session sets out to change data?
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.
What happens when a session sets out to read data?
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.
What happens when a session has a resource locked exclusively?
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.
What is the most granular type of resource?
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.
How long are exclusive locks held?
In a transaction, exclusive locks are held to the end of the transaction (commit or rollback).
How long are shared locks held?
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.
What is a deadlock?
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.
Can readers block writers?
Yes, even if only momentarily, because any exclusive lock request has to wait until the shared lock is released.
Can a SELECT statement be involved in a deadlock?
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.
What are the 6 different isolation levels?
(1) READ COMMITTED, (2) READ UNCOMMITTED, (3) READ COMMITTED SNAPSHOT, (4) REPEATABLE READ, (5) SNAPSHOT, (6) SERIALIZABLE
What is the READ UNCOMMITTED isolation level?
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).
What is the READ COMMITTED isolation level?
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;
What is the REPEATABLE READ isolation level?
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.
What is the SERIALIZABLE isolation level?
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.
What is the SNAPSHOT isolation level?
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.
What is the READ COMMITTED SNAPSHOT isolation level?
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.
What is the difference between SNAPSHOT and READ COMMITTED SNAPSHOT isolation levels?
READ COMMITTED SNAPSHOT does optimistic reads and pessimistic writes. In contrast, SNAPSHOT does optimistic reads and optimistic writes.
How are isolation levels set?
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.
Does that PRINT statement automatically occur in the context of a transaction?
No. The PRINT command does not change data and therefore does not execute by itself in a transaction.
Does the REPEATABLE READ isolation level reduce blocking or deadlocks?
No. It actually holds shared locks until the end of a transaction, and therefore can actually increase blocking and deadlocks.
Do the READ UNCOMMITTED, READ COMMITTED SNAPSHOT, and SNAPSHOT isolation levels reduce blocking a deadlocks?
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.
What is the @@ERROR function?
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.
For T-SQL code that generates an error, but is not inside a TRY/CATCH will the error message be passed to the client?
Yes. The error message will be passed to the client and cannot be intercepted in T-SQL code.
What are the two commands to raise your own errors?
(1) The older RAISERROR command, (2) The SQL Server 2012 THROW command
What are the 4 parts of an error message in SQL Server?
(1) Error number, (2) Severity level, (3) State, (4) Error message
What is the Error number part of a SQL Server error message?
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.
What is the Severity level part of a SQL Server error message?
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.
What is the State part of a SQL Server error message?
This is an integer with a maximum value of 127, used by Microsoft for internal purposes.
What is the Error message part of a SQL Server error message?
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.
What are some examples of how RAISERROR can be invoked?
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);
What are some of the more advanced features that were available in RAISERROR?
(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.
What are some examples of how THROW can be invoked?
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;
What are the difference between THROW and RAISERROR?
- 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.
Does the statement before the THROW statement have to be terminated by a semicolon?
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.
How do RAISERROR and THROW compare as far as batch termination?
RAISERROR(‘Hi there’, 16, 0);
PRINT ‘RAISERROR error’; –prints…
THROW 50000, ‘Hi There’, 0;
PRINT ‘Throw error’; – does not print…
What is the TRY_CONVERT function?
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.
What is the TRY_PARSE function?
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.
How can you add custom error messages?
You can use the stored procedure sp_addmessage to add your own custom messages.
What is severity level 0 used for?
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.
What are the two error handling methods available?
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.
How do you perform unstructured error handling using @@ERROR?
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.
What is XACT_ABORT?
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.
When was the TRY/CATCH construct added?
SQL Server 2005 added the TRY/CATCH construct to provide structured error handling to SQL Server.
How does TRY/CATCH work?
– 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.
What are the rules for TRY/CATCH?
– 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.
What functions can be used within the CATCH block to report on errors?
(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.
How can you “re-throw” an error in the CATCH block?
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.
How does XACT_ABORT behave when used in a TRY/CATCH?
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.
What are the main advantages of using TRY/CATCH block over the traditional trapping for @@ERROR?
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.
Can a TRY/CATCH block span batches?
No. You must have one set of TRY/CATCH blocks for each batch of code.
What is the advantage of using THROW in a CATCH block?
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.
What functions can be used in a CATCH block to return information about the error?
(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.
How does SET XACT_ABORT ON affect a transaction?
If a T-SQL error with a severity level > 10 occurs, the transaction will be aborted.
What is Dynamic SQL?
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.
What is the QUOTED_IDENTIFIER setting?
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.
Why should you leave QUOTED_IDENTIFIER set to ON?
Because that is the ANSI standard and the SQL Server default.
When using only single quotation marks as string delimiters, what do you do about embedded single quotation marks?
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’, ‘’’’);
What are the two methods available to execute the dynamic SQL?
(1) the EXECUTE statement and (2) the sp_executesql stored procedure
What is the EXECUTE statement?
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 + @tableName);
Can you generate and execute dynamic SQL in a different database than the one you code is in?
Yes, because the USE command can be inserted into a dynamic SQL batch.
What are some objects that cannot be referenced in T-SQL by using variables?
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.
What is a SQL Injection attack?
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 –
How do you prevent SQL injection attacks?
One of the most important methods is to parameterize the dynamic SQL generation and execution by using sp_executesql.
For a SQL injection attack, where is the injected code executed?
Between the initial single quotation mark which terminates the data input string and a final comment which disables the internal terminating sting quotation mark.
What is the sp_executesql stored procedure?
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 @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;
Does the sp_executesql stored procedure provide better query performance than the EXEC command?
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.
How can you pass information from sp_executesql to the caller?
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.
How does sp_executesql help to stop SQL injection?
You can use sp_executesql to parametertize user input which can prevent any injected code from being execute.
What are the advantages of sp_executesql over the EXECUTE command?
(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.