SQL Server provides over 30 global variables.
Frequently used:
@ IDENTITY: returns the column value of the ID column of the last inserted row.
@ ERROR: return the ERROR code of the last executed Transact-SQL statement. If there is no error, it is zero.
@ ROWCOUNT: returns the number of rows affected by the previous statement. Any statement that does not return a row sets this variable to 0.
@ DBTS: The timestamp value of the current database must be unique in the database.
@ SERVERNAME: return the name of the local Server running SQL Server 2000.
@ REMSERVER: return the name of the Remote SQL Server recorded in the logon record.
@ CONNECTIONS: returns the number of CONNECTIONS or attempts to connect to the SQL Server since the last time the SQL Server was started. This allows the Administrator to easily understand the number of attempts to connect to the Server today.
@ CURSOR_ROWS: returns the number of currently qualified rows in the cursor that is connected and opened.
@ VERSION: returns the date, VERSION, and processor type currently installed on SQL Server.
@ CPU_BUSY: returns the CPU time in milliseconds since the last time SQL Server was started.
@ DATEFIRST: return the value of the DATAFIRST parameter assigned by the set datefirst command. The set datefirst command is used to specify the day of the week on the first day of the week.
@ FETCH_STATUS: returns the status value of the last FETCH statement.
@ IDLE: the length of time that the CPU has been empty and closed since the last time SQL Server was started, in milliseconds.
@ IO_BUSY: the time (in milliseconds) that the CPU has consumed to perform the input and output operations since the last SQL Server startup ).
@ LANGID: returns the current language ID.
@ LANGUAGE: returns the name of the currently used LANGUAGE.
@ LOCK_TIMEOUT: the length of time for the current session to wait for the lock, in milliseconds.
@ MAX_CONNECTIONS: returns the maximum number of connections allowed to connect to SQL Server.
@ MAX_PRECISION: returns the precision of the decimal and numeric data types.
@ NESTLEVEL: return the nested Level of the currently executed stored procedure. The initial value is 0.
@ OPTIONS: returns information about the current SET option.
@ PACK_RECEIVED: returns the number of input packets read by SQL Server over the network.
@ PACK_SENT: return the number of output packages written by SQL Server to the network.
@ PACKET_ERRORS: Number of network packet errors returned.
@ PROCID: return the ID value of the current stored procedure.
@ SERVICENAME: return the service status under which the SQL Server is running: such as MS SQLServer, MSDTC, SQLServerAgent.
@ SPID: return the server processing ID value processed by the current user.
@ TEXTSIZE: return the TEXTSIZE option value of the SET statement. The SET statement defines text or image in the SELECT statement. The maximum length of a data type is measured in bytes.
@ TIMETICKS: returns the number of microseconds of each clock.
@ TOTAL_ERRORS: returns the number of disk read/write errors.
@ TOTAL_READ: return the number of disk read operations.
@ TOTAL_WRITE: return the number of disk write operations.
@ TRANCOUNT: returns the number of activated transactions in the current connection.