SQL Global Variables (reprinted)

In SQL Server, global variables are a special type of variable, and the server maintains the values of those variables. Global variables begin with the @@ 前缀 and do not have to be declared, they belong to system-defined functions.
There are also methods for obtaining system information, such as when the server was last started. [@[email protected]]

Global variable Name	Describe
@ @CONNECTIONS	Returns the number of connections that SQL Server has attempted since it was last started.
@ @CPU_BUSY	Returns the working time of SQL Server since it was last started.
@ @CURSOR_ROWS	Returns the number of currently qualified rows in the previous cursor opened on the connection, Determines the number of rows that the cursor is eligible to retrieve when it is invoked.
@ @DATEFIRST	Returns the current value of set Datefirst for the session, and set DATEFIRST represents the specified The first day of the week.
@ @DBTS	Returns the value of the current timestamp data type for the current database. This timestamp value must be unique in the database.
@ @ERROR	Returns the error number of the last Transact-SQL statement executed. Returns 0 if the previous Transact-SQL statement executes without errors.
@ @FETCH_STATUS	Returns the previous cursor FETCH for any cursor currently open on the connection The state of the statement.
@ @IDENTITY	Returns the last inserted identity value.
@ @IDLE	Returns the time that SQL Server has been idle since the last startup. Results with CPU time increments or "clock cycles" are expressed, and are cumulative for all CPUs.
@ @IO_BUSY	Back since SQL Server was last started, Microsoft SQL Server The time that has been used to perform input and output operations. The result is a CPU time increment (clock cycle), and is the cumulative value of all CPUs
@ @LANGID	Returns the local language identifier (ID) of the currently used language.
@ @LANGUAGE	Returns the name of the currently used language.
@ @LOCK_TIMEOUT:	Returns the current lock timeout setting (in milliseconds) for the current session.
@ @MAX_CONNECTIONS	Returns the maximum number of simultaneous user connections that a SQL Server instance allows. The returned value is not necessarily the current configured value.
@ @MAX_PRECISION	Returns the decimal and numeric data types according to the current settings in the server The level of precision used.
@ @NESTLEVEL	Returns the level of nesting for the current stored procedure executed on the local server (the initial value is 0).
@ @OPTIONS	Returns information about the current SET option.
@ @PACK_RECEIVED	Returns the number of input packets that SQL Server has read from the network since it was last started.
@ @PACK_SENT	Returns the number of output packets that SQL Server has written to the network since it was last started.
@ @PACKET_ERRORS	Returns the network that occurred on a SQL Server connection since the last time SQL Server was started The number of packet errors.
@ @PROCID	Returns the object identifier (ID) of the Transact-SQL current module. Transact-SQL A module can be a stored procedure, user-defined function, or trigger.
@ @REMSERVER	Returns the name of the remote SQL Server database server that is displayed in the logon record.
@ @ROWCOUNT	Returns the number of rows affected by the previous statement.
@ @SERVERNAME	Returns the name of the local server running SQL Server.
@ @SERVICENAME	Returns the name of the registry key under which SQL Server is running. If the current instance is The default instance, the @ @SERVICENAME returns MSSQLSERVER.
@ @SPID	Returns the session ID of the current user process.
@ @TEXTSIZE	Returns the current value of the TEXTSIZE option in the SET statement.
@ @TIMETICKS	Returns the number of microseconds per clock cycle.
@ @TOTAL_ERRORS	Returns the number of disk write errors encountered by SQL Server since the last startup.
@ @TOTAL_READ	Returns the number of times that SQL Server has read a disk (not read cache) since it was last started.
@ @TOTAL_WRITE	Returns the number of disk writes that SQL Server has performed since it was last started.
@ @TRANCOUNT	Returns the number of active transactions for the current connection.
@ @VERSION	Returns the current version of SQL Server installation, processor architecture, build date and operating system.

Many of the above global variables are to get the computer-related version of the CPU and related properties of the acquisition, there are some of the global variables that we are often used to write Transact-SQL, the following several global variables to explain:

@ @DATEFIRST: Look at the results of the following example run:

SET Datefirst 5 SELECT @ @DATEFIRST as ' FirstDay ', DATEPART (DW, GETDATE ()) as ' Today '

The above meaning is set every day of Friday for the first day, then today is Sunday, the results of the implementation are as follows:

FirstDay Today

------- -----------

5 3

@ @ERROR: The frequency of use is particularly high, especially in the use of some stored procedures, such as we in every update of an operation want to judge and detect its anomalies, this time I will be based on the value of @ @ERROR, such as:

IF @ @ERROR <> 0 BEGIN --Throws the wrong exception here --Exit the stored procedure END

@ @IDENTITY: After an INSERT, SELECT into, or bulk copy statement is complete, the @ @IDENTITY contains the last identity value generated by the statement. If the statement does not affect any table that contains an identity column, the @ @IDENTITY returns NULL. If more than one row is inserted and multiple identity values are generated, the @ @IDENTITY returns the last generated identity value. If the statement triggers one or more triggers, and the trigger performs an insert operation that generates an identity value, then the @ @IDENTITY is called immediately after the statement executes to return the last identity value generated by the trigger. If a trigger is triggered after an insert operation on a table that contains an identity column, and the trigger performs an insert operation on another table that does not have an identity column, the @ @IDENTITY returns the identity value that was first inserted. When an INSERT or SELECT into statement fails or a bulk copy fails, or if a transaction is rolled back, the @ @IDENTITY value does not revert to the previous setting.

@ @ROWCOUNT: Returns the number of rows affected by the previous statement, which is commonly used when a statement that updates/deletes/inserts or finds data is judged using this statement, which holds the number of rows affected by the previous step, such as:

--Number of rows affected by database operations IF @@ ROWCOUNT >0 BEGIN --Insert Success END

Sys.dm_os_sys_info information about the server operating system.