Notes on SQL 6

Previous related posts:

• Notes on SQL 1


• Notes on SQL 2


• Notes on SQL 3


• Notes on SQL 4


• Notes on SQL 5

Stored procedures

These statements cannot be used in an SP: USE <database_name>, SET SHOWPLAN_TEXT, SET SHOWPLAN_ALL, SET PARSEONLY, SET SHOWPLAN_XML, CREATE AGGREGATE, CREATE RULE, CREATE DEFAULT, CREATE SCHEMA, CREATE/ALTER FUNCTION, CREATE/ALTER TRIGER, CREATE/ALTER PROCEDURE, CREATE/ALTER VIEW. In other words, you cannot define what database to use, cannot ask the server for information for each executed statement, cannot ask the server to only parse the SP, and cannot create/alter objects.

The ENCRYPTION option when creating an SP is not really encryption, and is easily reversed.

WAITFOR:

• WAITFOR DELAY: obvious. WAITFOR DELAY '00:00:02'.


• WAITFOR TIME: waits for a specified time to occurr.


• WAITFOR RECEIVE: used in conjunction with Service Broker.

Query (or execution) plans are created when an SP is going to be executed, and then reused when it gets re-executed. If the SP behaves differently due to conditions inside it (control flow constructs), it might be better to re-generate the execution plan each time to avoid using non-optimal plans in some cases. This can be done by creating the SP with the RECOMPILE option. Optionally, different SPs can be created for each block of code in the conditions, so the "master" execution plan is simple but always the same, and each "sub-SP" has its own execution plan created when it is needed.

The EXECUTE AS clause can be used to run a SP under a specific security context:

• LOGIN


• USER


• CALLER

Variables

If you assign the results of a SELECT statement that returns several rows, to a variable, the last value (row) is assigned, and the rest discarded.

The +=, -=, *= and /= operators are new to SQL Server 2008 (2005 and older require @var = @var + 1 instead of @var += 1).

Errors


Severity levels go from 0 to 25. 16 and up is logged automatically to the SQL Server error log and the Windows Application Event Log. 19 to 25 can only be specified by members of the sysadmin role. 20 to 25 are considered fatal and cause the connection to be terminated, and any open transactions to be rolled back.

In the CATCH part of a TRY-CATCH block, you can use the ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE() functions for error handling. Also, the XACT_STATE function returns 1 if there are open transactions that can be committed or rolled back, 0 if there are no open transactions, and -1 if transactions that can only be rolled back (due to the type of error) exist. If you use SET XACT_ABORT ON before a TRY-CATCH block, any error passes control to the CATCH block but XACT_STATE always returns -1.

Cursors

You need to DECLARE, OPEN, FETCH, CLOSE, and DEALLOCATE a cursor (CLOSE and DEALLOCATE are performed automatically when an SP terminates). FETCHs are normally done together with with a WHILE @@FETCH_STATUS = 0

Cursor options:

• FAST_FORWARD: can only move forward one row at a time, and scrolling is not supported. You cannot modify the underlying table.


• STATIC: the result set is stored in tempdb, so changes to the tables are not seen by the cursor. Modifications are not supported.


• KEYSET: the keys for the rows in the result set are stored, so modifications to these rows are seen by the cursor. Inserts into the source table are not. Accessing a row that has been deleted causes @@FETCH_STATUS to return -2.


• DYNAMIC: all changes to the underlying table (including inserts) are reflected in the cursor. You cannot use FETCH ABSOLUTE with these.

Updates/deletes to the source tables can be done by an UPDATE/DELETE statement with the WHERE CURRENT OF <cursor name> clause. If modifications are not necessary, the cursor should be declared as READ_ONLY.

Instead of FORWARD_ONLY, you can declare a cursor as SCROLL, in which case you can:

• FETCH FIRST


• FETCH LAST


• FETCH NEXT: equivalent to FETCH


• FETCH PRIOR


• FETCH ABSOLUTE n: fetches the n'th row.


• FETCH RELATIVE n: n rows forward.

Concurrency options for cursors are:

• READ_ONLY: no lock acquired.


• SCROLL_LOCKS: locks acquired as each row is read into the cursor.


• OPTIMISTIC: uses timestamps or checksums instead of locks, and if the data has changed when a modification through the cursor is attempted, it fails.

User-defined Functions

THey cannot:

• Perform an action that changes the state of an instance or database


• Modify data in a table


• Call a function that has an external effect, such as the RAND function


• Create or access temporary tables


• Execute code dynamically

Scalar-valued functions are straightforward, table-valued functions can be inline (treated like views), or multi-statement (with a BEGIN-END block which does calculations and returns a table.

Can be created with:

• SCHEMABINDING: if specified, prevents dropping of objects on which the function depends (e.g. tables).


• EXECUTE AS: same as for SPs


• ENCRYPTION: same as for SPs


• RETURNS NULL ON NULL INPUT/CALLED ON NULL INPUT: the former causes NULL to be returned immediately if a NULL parameter is passed to the function; the latter lets the function code execute with a NULL parameter.

Random notes:

A GO inside a comment is STILL considered as a batch delimiter in T-SQL. Careful.

SPs allow GOTO statements. Discouraged.

The @@IDENTITY global variable contains the last identity inserted by any statement in the current connection. If an INSERT activates triggers that insert other rows, @@IDENTITY contains the ID of the last row inserted by the trigger(s). The SCOPE_IDENTITY() function should be used to retrieve just-inserted IDs instead.