C2-Implement error handling and transactions

Question 1

What is TRY…CATCH and what are some of its remarks?

Answer 1

DEF-Implements error handling for Transact-SQL.

Remarks:

The CATCH Block is not always executed, if there is no error in the code it won’t be executed;

The TRY…CATCH must be contained within a single batch. You cannot span more than one BEGIN….END block.

TRY..CATCH blocks are not allowed within functions. Error handling needs to be placed in the code that calls the function, not within the function itself.

Question 2

What is @@ERROR and how would you use it?

Answer 2

The @@ERROR system function returns 0 if the last Transact-SQL statement executed successfully; if the statement generated an error, @@ERROR returns the error number.

Because @@ERROR gets a new value when every Transact-SQL statement completes, process @@ERROR in one of two ways:

• Test or use @@ERROR immediately after the Transact-SQL statement.
• Save @@ERROR in an integer variable immediately after the Transact-SQL statement completes. The value of the variable can be used later.

Question 3

What is BEGIN TRANSACTION and what is it’s syntax?

Answer 3

Marks the starting point of an explicit, local transaction. Explicit transactions start with the BEGIN TRANSACTION statement and end with the COMMIT or ROLLBACK statement.

Syntax:

BEGIN { TRAN | TRANSACTION } [;]

or

BEGIN { TRAN | TRANSACTION } [ { transaction_name | @tran_name_variable }

[WITH MARK [ ‘description’] ] ] [;]

Question 4

What is COMMIT TRANSACTION (Transact-SQL) and what is it’s syntax?

Answer 4

Marks the end of a successful implicit or explicit transaction. If @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications since the start of the transaction a permanent part of the database, frees the transaction’s resources, and decrements @@TRANCOUNT to 0. When @@TRANCOUNT is greater than 1, COMMIT TRANSACTION decrements @@TRANCOUNT only by 1 and the transaction stays active.

syntax:

COMMIT [{ TRAN | TRANSACTION } [ transaction_name | @tran_name_variable] ] [WITH ( DELAYED_DURABILITY = { OFF | ON } )] [;]

Question 5

SAVE TRANSACTION def, syntax, remarks

Answer 5

def: Sets a savepoint within a transaction.
syntax: SAVE { TRAN | TRANSACTION } { savepoint_name | @savepoint_variable } [;] ;

remarks MSFT:

• A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. If a transaction is rolled back to a savepoint, it must proceed to completion with more Transact-SQL statements if needed and a COMMIT TRANSACTION statement, or it must be canceled altogether by rolling the transaction back to its beginning. To cancel an entire transaction, use the form ROLLBACK TRANSACTION transaction_name. All the statements or procedures of the transaction are undone.
• Duplicate savepoint names are allowed in a transaction, but a ROLLBACK TRANSACTION statement that specifies the savepoint name will only roll the transaction back to the most recent SAVE TRANSACTION using that name.
• SAVE TRANSACTION is not supported in distributed transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or escalated from a local transaction.

​Remarks Mindhub:

• This saves a specific point in the transaction, and there is no corresponding ROLLBACK in the saved point.
• You cannot use SAVETRANSACTION outside of an active transaction.
• There is no value to using SAVE TRANSACTION within a CATCH block.

Question 6

What is SET NOCOUNT (TRANSACT SQL);

Answer 6

This statement tells SQL SERVER to omit counting the rows processed. The difference in performance would be extremely small, but it will tend to increase the processing time, not to decrease it.

Question 7

What is THROW(Transact-SQL), what is it’s syntax and remarks?

Answer 7

DEF: Raises an exception and transfers execution to a CATCH block of a TRY…CATCH construct in SQL Server 2019;

syntax: THROW
[ { error_number | @local_variable }, —(50000-2147483647)-user defined errors
{ message | @local_variable },
{ state | @local_variable } ] —(0-255)

Remarks:

• The statement before the THROW statement must be followed by the semicolon (;) statement terminator.
• If a TRY…CATCH construct is not available, the statement batch is terminated. The line number and procedure where the exception is raised are set. The severity is set to 16.
• For error handling any new code should use THROW;
• Does not transfer processing to the TRY block but to the CATCH block;
• If THROW does not have any parameters it must be used within a CATCH block.
• Without parameters, THROW re-raises the same error that caused control to be given to the catch block;

Question 8

RAISERROR-DEF, syntax, remarks

Answer 8

DEF:Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct. New applications should use THROW instead.

syntaxsql:

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

Remarks:

• Should not be used when writing new code;
• Cannot be used to raise system errors. System errors have error numbers less than 50000
• Always requires parameters

Question 9

SCOPE IDENTITY: DEF, syntax, remarks

Answer 9

DEF: Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, if two statements are in the same stored procedure, function, or batch, they are in the same scope.

Syntax: SCOPE_IDENTITY()

Remarks:

• SCOPE_IDENTITY, IDENT_CURRENT, and @@IDENTITY are similar functions because they return values that are inserted into identity columns.
• IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT (Transact-SQL).
• SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @@IDENTITY is not limited to a specific scope.

Question 10

IDENT_CURRENT-DEF, syntax, remarks

Answer 10

DEF: Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

IDENT_CURRENT( ‘table_or_view’ )

Remarks:

IDENT_CURRENT is similar to the SQL Server 2000 (8.x) identity functions SCOPE_IDENTITY and @@IDENTITY. All three functions return last-generated identity values. However, the scope and session on which last is defined in each of these functions differ:

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

When the IDENT_CURRENT value is NULL (because the table has never contained rows or has been truncated), the IDENT_CURRENT function returns the seed value.

Question 11

@@IDENTITY-def, syntax, remarks

Answer 11

def: Is a system function that returns the last-inserted identity value.
syntax: @@IDENTITY

remarks:

After an INSERT, SELECT INTO, or bulk copy statement is completed, @@IDENTITY contains the last identity value that is generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY returns the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

Basiclly it Returns the last identity value inserted into an identity column anywhere in the system.

Question 12

CREATE TABLE IDENTITY

Answer 12

def: Creates an identity column in a table. This property is used with the CREATE TABLE and ALTER TABLE Transact-SQL statements.
syntaxsql: IDENTITY [(seed , increment)]

remarks:

Identity columns can be used for generating key values. The identity property on a column guarantees the following:

Each new value is generated based on the current seed & increment.

Each new value for a particular transaction is different from other concurrent transactions on the table.

The identity property on a column does not guarantee the following:

• Uniqueness of the value - Uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index;
• Consecutive values within a transaction - A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level;
• Consecutive values after server restart or other failures -SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use its own mechanism to generate key values. Using a sequence generator with the NOCACHE option can limit the gaps to transactions that are never committed;
• Reuse of values - For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.

Question 13

What is FORMATMESSAGE?

Answer 13

Constructs a message from an existing message in sys.messages or from a provided string. The functionality of FORMATMESSAGE resembles that of the RAISERROR statement. However, RAISERROR prints the message immediately, while FORMATMESSAGE returns the formatted message for further processing.

FORMATMESSAGE takes either a message number or a message string. The message string can take a number of parameter values.

• Error number: %u(unsigned integer)
• Error reason: %s(string)
• EX:FORMATMESSAGE(N’Cannot insert customer. Error reason: %s. Error number: %u’, ERROR_MESSAGE(), ERROR_NUMER())

Question 14

What is nesting transactions?

Answer 14

Explicit transactions can be nested. This is primarily intended to support transactions in stored procedures that can be called either from a process already in a transaction or from processes that have no active transaction.

The following example shows the intended use of nested transactions. The procedure TransProc enforces its transaction regardless of the transaction mode of any process that executes it.

Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.

Question 15

Error Message def, syntax, remarks

Answer 15

def: Returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to be run.
syntax: ERROR_MESSAGE may be called anywhere within the scope of a CATCH block;

remarks:

• ERROR_MESSAGE may be called anywhere within the scope of a CATCH block.
• ERROR_MESSAGE returns the error message regardless of how many times it is run, or where it is run within the scope of the CATCH block. This is in contrast to functions like @@ERROR, which only returns an error number in the statement immediately after the one that causes an error, or the first statement of a CATCH block.
• In nested CATCH blocks, ERROR_MESSAGE returns the error message specific to the scope of the CATCH block in which it is referenced. For example, the CATCH block of an outer TRY…CATCH construct could have a nested TRY…CATCH construct. Within the nested CATCH block, ERROR_MESSAGE returns the message from the error that invoked the nested CATCH block. If ERROR_MESSAGE is run in the outer CATCH block, it returns the message from the error that invoked that CATCH block.

Question 16

ERROR_SEVERITY-def, syntax, remarks

Answer 16

def: Returns the severity of the error that caused the CATCH block of a TRY…CATCH construct to be run.
syntax: ERROR_SEVERITY ( )

remarks:

ERROR_SEVERITY may be called anywhere within the scope of a CATCH block.

ERROR_SEVERITY returns the error severity regardless of how many times it is run, or where it is run within the scope of the CATCH block. This is in contrast to functions like @@ERROR, which only returns the error number in the statement immediately after the one that causes an error, or in the first statement of a CATCH block.

In nested CATCH blocks, ERROR_SEVERITY returns the error severity specific to the scope of the CATCH block in which it is referenced. For example, the CATCH block of an outer TRY…CATCH construct could have a nested TRY…CATCH construct. Within the nested CATCH block, ERROR_SEVERITY returns the severity from the error that invoked the nested CATCH block. If ERROR_SEVERITY is run in the outer CATCH block, it returns the severity from the error that invoked that CATCH block.

Question 17

ERROR_NUMBER()-def, syntax, remarks

Answer 17

def: Returns the error number of the error that caused the CATCH block of a TRY…CATCH construct to be run.
syntax: ERROR_NUMBER ( )

remarks:

• This function may be called anywhere within the scope of a CATCH block.
• ERROR_NUMBER returns the error number regardless of how many times it is run, or where it is run within the scope of the CATCH block. This is in contrast to @@ERROR, which only returns the error number in the statement immediately after the one that causes an error, or the first statement of a CATCH block.
• In nested CATCH blocks, ERROR_NUMBER returns the error number specific to the scope of the CATCH block in which it is referenced. For example, the CATCH block of an outer TRY…CATCH construct could have a nested TRY…CATCH construct. Within the nested CATCH block, ERROR_NUMBER returns the number from the error that invoked the nested CATCH block. If ERROR_NUMBER is run in the outer CATCH block, it returns the number from the error that invoked that CATCH block.

Question 18

ROLLBACK TRANSACTION def, syntax, remarks

Answer 18

def:

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

This does not include changes made to local variables or table variables. These are not erased by this statement.

syntax:

ROLLBACK { TRAN | TRANSACTION } [transaction_name | @tran_name_variable | savepoint_name | @savepoint_variable] [;]

General Remarks:

ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction.

When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement.

In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.

ROLLBACK TRANSACTION cannot reference a savepoint_name in distributed transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or escalated from a local transaction.

A transaction cannot be rolled back after a COMMIT TRANSACTION statement is executed, except when the COMMIT TRANSACTION is associated with a nested transaction that is contained within the transaction being rolled back. In this instance, the nested transaction is rolled back, even if you have issued a COMMIT TRANSACTION for it.

Within a transaction, duplicate savepoint names are allowed, but a ROLLBACK TRANSACTION using the duplicate savepoint name rolls back only to the most recent SAVE TRANSACTION using that savepoint name.

Question 19

GRANT Object Permissions def, syntax, remarks

Answer 19

def: Grants permissions on a table, view, table-valued function, stored procedure, extended stored procedure, scalar function, aggregate function, service queue, or synonym.

syntax:

GRANT [,…n] ON

[OBJECT ::][schema_name]. object_name [( column [ ,…n] ) ]

TO [,…n]

[WITH GRANT OPTION]

[AS]

::=

ALL [PRIVILEGES] | permission [( column [ ,…n] ) ]

::=

Database_user

| Application_role

| Database_user_mapped_to_Windows_User

| Database_user_mapped_to_Windows_Group

| Database_user_mapped_to_certificate

| Database_user_mapped_to_asymmetric_key

| Database_user_with_no_login

remarks:

• A combination of ALTER and REFERENCE permissions in some cases could allow the grantee to view data or execute unauthorized functions. For example: A user with ALTER permission on a table and REFERENCE permission on a function can create a computed column over a function and have it be executed. In this case the user would also need SELECT permission on the computed column.

Database_role

Question 20

Database-Level Roles

Answer 20

To easily manage the permissions in your databases, SQL Server provides several roles which are security principals that group other principals. They are like groups in the Microsoft Windows operating system. Database-level roles are database-wide in their permissions scope.

To add and remove users to a database role, use the ADD MEMBER and DROP MEMBER options of the ALTER ROLE statement. Parallel Data Warehouse and Azure Synapse does not support this use of ALTER ROLE. Use the older sp_addrolemember and sp_droprolemember procedures instead.

There are two types of database-level roles: fixed-database roles that are predefined in the database and user-defined database roles that you can create.

Fixed-database roles are defined at the database level and exist in each database. Members of the db_owner database role can manage fixed-database role membership. There are also some special-purpose database roles in the msdb database.

You can add any database account and other SQL Server roles into database-level roles.

Tip:Do not add user-defined database roles as members of fixed roles. This could enable unintended privilege escalation.

Question 21

Fixed-Database Roles

Answer 21

The following table shows the fixed-database roles and their capabilities. These roles exist in all databases. Except for the public database role, the permissions assigned to the fixed-database roles cannot be changed.

db_owner-Members of the db_owner fixed database role can perform all configuration and maintenance activities on the database, and can also drop the database in SQL Server. (In SQL Database and Azure Synapse, some maintenance activities require server-level permissions and cannot be performed by db_owners.)

db_securityadmin-Members of the db_securityadmin fixed database role can modify role membership for custom roles only and manage permissions. Members of this role can potentially elevate their privileges and their actions should be monitored.

db_accessadmin-Members of the db_accessadmin fixed database role can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.

db_backupoperator-Members of the db_backupoperator fixed database role can back up the database.

db_ddladmin-Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.

db_datawriter-Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

db_datareader-Members of the db_datareader fixed database role can read all data from all user tables.

db_denydatawriter-Members of the db_denydatawriter fixed database role cannot add, modify, or delete any data in the user tables within a database.

db_denydatareader-Members of the db_denydatareader fixed database role cannot read any data in the user tables within a database.