SQL Server Global Variables

SQL Server Global Variables

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 @@ 前缀, do not have to be declared, they belong to the system-defined functions, they have no way to define global variables, if necessary, you can use a workaround, such as your table as a global variable.
Previous versions of SQL Server 7.0 global variables are system-supplied and pre-declared variables that are distinguished from local variables by preserving two (@@) symbols before the name. SQL Server 7.0 and later versions of Transact-SQL global variables are functional forms and are now referenced as functions.
Here are some common global variables in SQL Server .

*******************************************************************************

Select App_name () as W--the current session of the application

SELECT @ @ERROR--Returns the error code (integer) of the last Transact-SQL statement executed

SELECT @ @IDENTITY--Returns the last inserted identity value

SELECT user_name ()--Return user database user name

SELECT @ @ERROR--Returns the error code for the last Transact-SQL statement executed

SELECT @ @CONNECTIONS-Returns the number of connections or attempts to connect since the last SQL startup.

SELECT GETDATE ()--Current time

SELECT @ @CPU_BUSY/100--Returns the working time of the CPU in milliseconds since the last time SQL was started

Use tempdb SELECT @ @DBTS as W-Returns the value of the current timestamp data type for the current database. This timestamp value is guaranteed to be unique in the database.

SELECT @ @IDENTITY as W--Returns the last inserted identity value

SELECT @ @IDLE as W--Returns the time that SQL has been idle since the last startup, in milliseconds

SELECT @ @IO_BUSY as W--Returns the time, in milliseconds, that SQL has been used to perform input and output operations since the last startup

SELECT @ @LANGID As W-Returns the local language identifier (ID) of the currently used language.

SELECT @ @LANGUAGE as W--returns the currently used language name

SELECT @ @LOCK_TIMEOUT as W-the current lock time-out setting for the current session, in milliseconds.

SELECT @ @MAX_CONNECTIONS As W-Returns the maximum number of simultaneous user connections allowed on SQL. The number returned does not have to be the current configured value

EXEC sp_configure--Displays the global configuration settings for the current server

SELECT @ @MAX_PRECISION As W-returns the precision level used for the decimal and numeric data types, which is the precision currently set in the server. The default maximum precision is 38.

SELECT @ @OPTIONS as W-returns information for the current SET option.

SELECT @ @PACK_RECEIVED As W-Returns the number of input packets read from the network since SQL started.

SELECT @ @PACK_SENT As W-Returns the number of output packets that have been written to the network since the last startup.

SELECT @ @PACKET_ERRORS As W-Returns the number of network packet errors that occurred on the SQL connection since SQL startup.

SELECT @ @SERVERNAME as W--Returns the name of the running SQL Server.

SELECT @ @SERVICENAME as W--Returns the registry key name under which SQL is running

SELECT @ @TIMETICKS As W-Returns the number of microseconds in a SQL Server moment

SELECT @ @TOTAL_ERRORS As W-Returns the number of disk read/write errors encountered by the SQL Server since it was started.

SELECT @ @TOTAL_READ As W-returns the number of times the SQL Server has read the disk since it started.

SELECT @ @TOTAL_WRITE As W-returns the number of times the SQL Server has written to disk since startup.

SELECT @ @TRANCOUNT As W-Returns the number of active transactions for the current connection.

SELECT @ @VERSION As W-Returns the date, version, and processor type of the SQL Server installation.

SELECT @ @REMSERVER as W: Returns the name of the remote SQL Server server as recorded in the login record.

SELECT @ @CURSOR_ROWS as W: Returns the number of qualifying rows that currently exist in the cursor that was last connected and opened.

SELECT @ @PROCID as W: Returns the ID value of the current stored procedure.

SELECT @ @SERVICENAME as W: Returns the status of the service that SQL Server is running under: such as MS SQL Server, MSDTC, SQLServerAgent.

SELECT @ @SPID as W: Returns the server processing ID value processed by the current user.

SELECT @ @TEXTSIZE as W: Returns the TEXTSIZE option value of the SET statement the SET statement defines the text or image in the SELECT statement. The maximum length of the data type base unit is bytes.

SELECT @ @ROWCOUNT as W: Returns the number of rows affected by the previous statement, and any statement that does not return a row sets this variable to 0.

SELECT @ @DATEFIRST as W: Returns the value of the Datafirst parameter that is assigned using the SET DATEFIRST command. The SET datefirst command is used to specify the day of the week for the first days of the week.

SELECT @ @FETCH_STATUS as W: Returns the status value of the last FETCH statement.

SELECT @ @NESTLEVEL as W: Returns the nested progression of the currently executing stored procedure with an initial value of 0.

SELECT @ @IO_BUSY as W: Returns the amount of time (in milliseconds) that the CPU takes to perform an input-output operation since SQL Server was last started.

********************************************************************************

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:

SETDATEFIRST5
[Email protected] @DATEFIRSTAS ' 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:

[Email protected] @ERROR <>0
BEGIN
--Throws the wrong exception here
--Exit the stored procedure
END

SQL Server Global Variables