Functions and stored procedures allow you to modify the security context under which the object is running by using the EXECUTE AS option. EXECUTE AS has three possible arguments:
n LOGIN Executes under the context of the specified login.
n USER Executes under the security context of the specified database user. This account can’t be a role, group, certificate, or asymmetric key.
n CALLER Executes under the security context of the routine that called the module. The EXECUTE AS clause also has two additional arguments: NO REVERT and COOKIE
INTO. The NO REVERT option specifies that once the security context is changed, it can’t be
changed back. The COOKIE INTO option sets a cookie that allows the security context to be returned to a specific, previous security context.
Cursors
SQL Server is built to process sets of data. However, there are times when you need to process data one row at a time. The result of a SELECT statement is returned to a server-side object called a cursor, which allows you to access one row at a time within the result set and even allows scrolling forward as well as backward through the result set.
note cURSOR PeRFORMance
SQL Server is built and optimized for set-based operations. A cursor causes the engine to perform row-based processing. A cursor never performs as well as an equivalent set-based process.
Cursors have five components. DECLARE is used to define the SELECT statement that is the basis for the rows in the cursor. OPEN causes the SELECT statement to be executed and load the rows into a memory structure. FETCH is used to retrieve one row at a time from the cursor. CLOSE is used to close the processing on the cursor. DEALLOCATE is used to remove the cursor and release the memory structures containing the cursor result set.
iMPortant DeaLLOcatinG cURSORS
If a cursor is used within a stored procedure, it is not necessary to close and deallocate the cursor. When the stored procedure exits, SQL Server automatically closes and deallocates any cursors created within the procedure to reclaim memory space.
note cURSOR USaGe
If you write a cursor that performs the same operation against every row retrieved by the cursor, you should rewrite the process to use a more efficient set-based operation.
The generic syntax for declaring a cursor is
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] [ FORWARD_ONLY | SCROLL ]
[ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] [ TYPE_WARNING ]
FOR select_statement
[ FOR UPDATE [ OF column_name [ ,...n ] ] ]
The following statements show three different ways of declaring the same cursor:
DECLARE curproducts CURSOR FAST_FORWARD FOR
SELECT ProductID, ProductName, ListPrice FROM Products.Product GO
DECLARE curproducts CURSOR READ_ONLY FOR
SELECT ProductID, ProductName, ListPrice FROM Products.Product GO
DECLARE curproducts CURSOR FOR
SELECT ProductID, ProductName, ListPrice FROM Products.Product FOR READ ONLY
GO
Once the cursor has been declared, you issue an OPEN command to execute the SELECT statement:
OPEN curproducts
You then need to retrieve data from the row in the cursor by using a FETCH statement. When you execute FETCH for the first time, a pointer is placed at the first row in the cursor result set. Each time a FETCH is executed, the cursor pointer is advanced one row in the result set until you run out of rows in the result set. Each execution of FETCH also sets a value for the global variable @@FETCH_STATUS. You usually use a WHILE loop to iterate across the cursor, fetching a row each iteration through the loop. You iterate the WHILE loop so long as
@@FETCH_STATUS = 0. Here is an example:.
DECLARE @ProductID INT, @ProductName VARCHAR(50), @ListPrice MONEY DECLARE curproducts CURSOR FOR
SELECT ProductID, ProductName, ListPrice FROM Products.Product FOR READ ONLY
OPEN curproducts
WHILE @@FETCH_STATUS = 0 BEGIN
SELECT @ProductID, @ProductName, @ListPrice
FETCH curproducts INTO @ProductID, @ProductName, @ListPrice END
CLOSE curproducts DEALLOCATE curproducts
note Set-baSeD PROceSSinG
If you are writing stored procedures that have cursors (especially multilevel cursors), you should reevaluate the process you are trying to write. You can probably replace the cursors with a set-based process that is more efficient.
You can declare four different types of cursors:
n FAST_FORWARD The fastest performing cursor type because it allows you only to move forward one row at a time. Scrolling (discussed later in this section) is not supported. A FAST_FORWARD cursor is the same as declaring a FORWARD_ONLY,
READ_ONLY cursor. FAST_FORWARD is the default option for cursors.
n STATIC The result set is retrieved and stored in a temporary table in the tempdb database. All fetches go against the temporary table and modifications to the underlying tables for the cursor are not visible. A STATIC cursor supports scrolling, but modifications are not allowed.
n KEYSET The set of keys that uniquely identify each row in the cursor result set is stored in a temporary table in tempdb. As you scroll within the cursor, non-key columns are retrieved from the underlying tables. Therefore, any modifications to rows are reflected as the cursor is scrolled. Any inserts into the underlying table are not accessible to the cursor. If you attempt to access a row that has been deleted,
@@FETCH_STATUS returns –2.
n DYNAMIC The most expensive cursor to use. The cursor reflects all changes made to the underlying result set, including newly inserted rows as the cursor is scrolled. The position and order of rows within the cursor can change each time a fetch is made. The
FETCH ABSOLUTE option is not available for dynamic cursors.
By default, all cursors are updatable. To make modifications to underlying table rows, you can execute an UPDATE or DELETE statement with the WHERE CURRENT OF <cursor name> clause to modify or delete the row in the underlying table that the cursor pointer is currently accessing.
iMPortant cURSOR OPtiOnS
If all you are going to do is read data within a cursor, make certain that you are declaring the cursor as read-only. Read-only cursors require less overhead than updatable cursors.
Access to rows within a cursor can be restricted by using the FORWARD_ONLY and SCROLL options. If a cursor is declared as FORWARD_ONLY, each row can be read only once as the cursor pointer advances through the result set. If you declare a cursor using the SCROLL option, the FETCH statement has the following options:
n FETCH FIRST Fetches the first row in the result set. n FETCH LAST Fetches the last row in the result set.
n FETCH NEXT Fetches the next row in the result set based on the current position of the pointer. FETCH NEXT is equivalent to just executing FETCH, which also moves forward one row at a time within the cursor result set.
n FETCH PRIOR Fetches the row in the result set just before the current position of the cursor pointer.
n FETCH ABSOLUTE n Fetches the nth row from the beginning of the result set. n FETCH RELATIVE n Fetches the nth row forward in the cursor result set from the
current position of the cursor pointer.
T-SQL has three concurrency options available for cursors:
n READ_ONLY SQL Server does not acquire a lock on the underlying row in the table because a cursor marked as READ_ONLY cannot be updated.
n SCROLL_LOCKS A lock is acquired as each row is read into the cursor, guaranteeing that any transaction executed against the cursor succeeds.
n OPTIMISTIC A lock is not acquired. SQL Server instead uses either a timestamp or a calculated checksum in the event that a timestamp column does not exist to detect if the data has changed since being read into the cursor. If the data has changed, the modification fails.