Programming SQL Server 2000


Scripts, Batches, and Transactions

T-SQL programs are technically called scripts . A script is usually contained within a text file on disk, which is then loaded into some tool (the Query Analyzer, or the command-line equivalent called OSQL) and can be executed. A script is made of one or more batches . Each batch is made up of zero, one, or more transactions. Programming is discussed in Chapter 6.

Variable Types

You can create variables of nearly any type that you can store in a table, with the restriction that you can't create variables of type TEXT , NTEXT , or IMAGE . So, you can create int , tinyint , smallint , datetime , smalldatetime , uniqueidentifier , varchar , nvarchar , char , nchar , and so on. You can (and should) specify lengths where appropriate, such as varchar(30) or nchar(15) . You can create a string variable that holds up to 8,000 bytes, so you can build a varchar(8000) or an nvarchar(4000) in a variable (remember that the nchar and nvarchar types are double-wide characters , so they take up twice as much room as a varchar ).

Global Variables

Global variables are not variables. They are actually system functions that return various pieces of information about the current user environment for SQL Server. At one point, they were called "Global Variables," but that nomenclature is dying out. A global variable is a variable with two at-signs in front of it, like @@CONNECTIONS , which returns the current number of user connections. You cannot declare global variables, and you cannot directly change them with a SET statement or a SELECT statement. So, if you use one of these special functions somewhere, realize that although it looks like a variable and can act like one, you can't assign any data to it ”you can only read data from it. Table 5 shows a list of the global variables in SQL Server 2000.

Table 5. Global Variables in SQL Server 2000

Global Variable

Description

@@CONNECTIONS

Returns the current number of connections that applications currently have open to SQL Server.

@@CPU_BUSY

The time, in milliseconds , that SQL Server has been busy since the last time it was restarted.

@@CURSOR_ROWS

The number of rows that are in the previously opened cursor. If no cursor has been opened, returns 0; if the cursor is asynchronous, returns a negative number representing the number of rows that have been used so far; if the number is positive, it's the number of rows in the cursor; and if the value is -1 , the cursor is dynamic, so there's no telling how many rows are in it. We'll talk more about cursors later in this chapter.

@@DATEFIRST

Returns the number of the first day of the week. For example, if the first day of the week is set to Sunday, it will return 1; if it's set to Monday, it will return 2, and so on.

@@DBTS

Returns the current value of the time-stamp for the database. This has absolutely no relation to system time and is used only to manage certain inserts .

@@ERROR

Returns the error number for the last SQL statement executed.

@@FETCH_STATUS

Returns the status of the last cursor fetch operation: 0 for success, “1 for failure, or “2 for missing record.

@@IDENTITY

Returns the value used for the last INSERT INTO an identity column for the current connection.

@@IDLE

Returns the time in milliseconds that SQL Server has been idle since the last restart.

@@IO_BUSY

Returns the time in milliseconds that SQL Server has spent waiting for IO to return from a read or write request.

@@LANGID

Returns the language identifier of the current language in use.

@@LANGUAGE

Returns the name of the language currently in use. This is probably more useful than the ID number.

@@LOCK_TIMEOUT

Returns the number of milliseconds that the current connection will wait for a lock to clear to complete its work.

@@MAX_CONNECTIONS

Returns the maximum number of simultaneous user connections allowed on the current SQL Server.

@@MAX_PRECISION

This is the precision used by the DECIMAL and NUMERIC data types on the server. By default, this is 38.

@@NESTLEVEL

This is the current nesting level during stored procedure execution. We'll cover this more in Chapter 9.

@@OPTIONS

Returns an integer representing the settings of the user options for the current connection. See discussion in text under the heading "User Options".

@@PACK_RECEIVED

Returns the number of network packets received by the SQL Server since it was last restarted.

@@PACK_SENT

Returns the number of packets sent by the SQL Server since it was last restarted.

@@PACKET_ERRORS

Returns the number of packet errors that SQL Server has seen since it was last restarted.

@@PROCID

Returns the stored procedure identifier of the currently executing stored procedure.

@@REMSERVER

Returns the name of the SQL Server running the remote stored procedure.

@@ROWCOUNT

Returns the number of rows returned by the last statement. See the discussion in the text following the heading "Using @@ROWCOUNT".

@@SERVERNAME

Returns the name of the current server.

@@SERVICENAME

Returns the name of the service that SQL Server is running under.

@@SPID

Returns the current process identifier used by SQL Server.

@@TEXTSIZE

This is the maximum number of bytes that will be returned in a resultset to the current connection from selecting a TEXT or IMAGE column.

@@TIMETICKS

Returns the number of microseconds that occur in one tick of the computer's clock.

@@TOTAL_ERRORS

Returns the total number of disk read/write errors that SQL Server has had since last restart.

@@TOTAL_READ

Returns the total number of physical disk reads that SQL Server has done since it was last started.

@@TOTAL_WRITE

Returns the total number of physical disk writes that SQL Server has done since it was last started.

@@TRANCOUNT

Returns the number of transactions "deep" the current statement is in a nested transaction.

@@VERSION

Returns the version string (date, version, and processor type) for the SQL Server.

Using Cursors

Cursors are a way to take the results of a SELECT statement and assign the output from the recordset to a set of variables one at a time. This allows you to walk through the recordset one record at a time and use the information in the recordset to do interesting things.

Creating a cursor has 5 steps. First, you have to DECLARE the cursor with the DECLARE CURSOR statement. Next, open the cursor with the OPEN statement. After that, you have to FETCH rows from the cursor, and when you're done you have to CLOSE the cursor and DEALLOCATE it. Table 6 shows a list of T-SQL cursor extensions.

Table 6. T-SQL Cursor Extensions

Extension

Description

LOCAL

This is the optional state for a cursor. It means the cursor is available only for the current batch and the current connection. To change the default behavior, set the Default to Local Cursor database option.

GLOBAL

Global in this case means "Global to the current connection." Declaring a cursor as global will make it available to subsequent batches or stored procedures that are run by the connection. The cursor is not available to other connections, even if the connection is from the same user.

FORWARD_ONLY

This tells SQL Server that the cursor is going to run only from the beginning of the recordset to the end of the recordset. The cursor is not allowed to go backward or skip around. The only fetch that works is FETCH NEXT . This is an optimization; it allows SQL Server to consume less overhead for the cursor.

STATIC

This does the same thing as the INSENSITIVE keyword in the SQL-92 syntax.

KEYSET

If you use this extension, your cursor will not be able to access data inserted by other users after the cursor is opened. If a row is deleted by another user, an @@FETCH_STATUS of “2 (row is missing) message will be returned if you attempt to fetch a deleted row. This type of cursor has less overhead than a DYNAMIC cursor, but (unless FORWARD_ONLY is also specified) all the different FETCH options are available.

DYNAMIC

A DYNAMIC cursor is the opposite of a KEYSET cursor. All inserts and deletes done by users are immediately available to the cursor. However, FETCH ABSOLUTE will not work with a DYNAMIC cursor because the underlying data may change what position the records are in.

FAST_FORWARD

This is a cursor that has all the properties of a FORWARD_ONLY and READ_ONLY cursor, and is designed to go forward quickly with little overhead.

READ_ONLY

Does not allow updates to the cursor.

SCROLL_LOCKS

This causes SQL Server to exclusively lock each row that is touched by the cursor as they are read in, to prevent other users from updating the record.

OPTIMISTIC

This causes SQL Server to not lock any rows during the scrolling of the cursor. The script will just hope that none of the rows being changed by the cursor are being changed simultaneously by somebody else. Attempting to change a row through the cursor will result in an error.

TYPE_WARNING

If your cursor somehow changes type implicitly, a warning will be issued.

Lock Isolation Levels

SQL Server knows that sometimes it's critical that the data you are reading from the database is one hundred percent committed data, while sometimes you just want the data to be read quickly, and incomplete or uncommitted transactions just don't matter.

To allow for this, SQL Server supports four different transaction isolation levels:

  • READ UNCOMMITTED . This isolation level will show you all the data without getting a shared lock before reading the data. Another connection may change the data while it is being read. This can be great for applications that are doing system monitoring or reporting, where minimal impact to the rest of the system is desired. This is also called "dirty reads."

  • READ COMMITTED . This will acquire a shared lock during the read of the data but won't keep the shared lock for the entire transaction. The resulting data will be complete, but may change after successive reads, showing new data or missing data with each successive read. This is the default transaction isolation level and is generally an acceptable tradeoff between reading dirty data and minimizing contention .

  • REPEATABLE READ . This acquires a shared lock on the rows for the duration of the transaction, but still allows other users to add rows into the resultset. This means that later reads may contain more data, but they won't contain any less.

  • Serializable . This will acquire a shared lock on the entire range of data that is being queried, preventing inserts or updates from happening for the duration of the transaction. This is a very dangerous thing to do from a concurrency perspective, because it generates a lot of locks and can more easily result in deadlock problems.

Setting the isolation levels is performed through the use of the SET TRANSACTION ISOLATION LEVEL command. When set, the level will affect all SELECT operations issued through the connection.

Designing and Managing Transactions

If you don't explicitly tell SQL Server to treat a group of statements as a transaction, it implicitly puts each statement in its own transaction. For the purposes of an implicit transaction, the only statements that really count are the statements that interact with a database: SELECT , INSERT , UPDATE , and DELETE .

To explicitly put a group of statements into a transaction, you can use the BEGIN TRANSACTION command. This command tells SQL Server that all commands that follow are part of the transaction up until the end of the transaction, which is noted with a COMMIT TRANSACTION . In the event of a problem with the data being manipulated, you can also call ROLLBACK TRANSACTION . If there is an error during the execution of the transaction, such as a server shutdown, a disk error of some type, or lock contention, then the transaction will automatically roll back.

Statement Permissions

Statement permissions are assigned to allow users to do things like create databases, define user-defined functions and stored procedures, and back up the database or transaction log. Statement permissions are assigned by using the GRANT statement.

Object Permissions

Object permissions are permissions granted to access objects in certain ways. For tables and views, you can grant SELECT , DELETE , UPDATE, and INSERT permissions, and for stored procedure and function objects you can grant EXECUTE permissions. Permissions are granted to users, so the user must exist in the database prior to granting permission. To give a user permission to access certain database objects, use the GRANT command.

User Roles

In order to provide the capability to grant multiple users access to the same objects the same way, SQL Server provides a mechanism for creating collections of users, called roles .

Fixed Roles

SQL Server provides you with a set of roles you can use to assign different levels of permission to users. There are two types of fixed roles. Fixed server roles are server-wide permissions that can be used regardless of the database you are in. Then there are fixed database roles, which apply to only one database.

Application Roles

One of the handy features of this security model is the ability to have an application role. An application role is similar to other roles, but the role has no members associated with it. The GRANT and REVOKE statements work the same way with an application role as with any other role. To create an application role, use the sp_addapprole system stored procedure. Application roles have no members because they operate on a password. The use of the role is restricted to those who know the password as opposed to membership.



MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
MCSE Training Guide (70-229). Designing and Implementing Databases with MicrosoftR SQL ServerT 2000 Enterprise Edition
ISBN: N/A
EAN: N/A
Year: 2003
Pages: 228

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net