Global variables are actually system functions that return various pieces of information about the current user environment for SQL Server. A global variable is a variable with two @ signs in front of it, like @@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. The following table shows a list of the 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. |
| @@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. |
| @@OPTIONS |
Returns an integer representing the settings of the user options for the current connection. |
| @@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. |
| @@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. |
