Description of common global variables reproduced in SQL Server

Source: Internet
Author: User
Tags configuration settings

Variable

There are two forms of variables in the Transact-SQL language, one is a local variable defined by the user, and the other is a global variable provided by the system.
Local variables

A local variable is an object that can have a specific data type, and its scope is limited to the inside of the program. A local variable can be used as a counter to calculate the number of times a loop is executed, or to control how many times the loop executes. In addition, local variables can be used to hold data values for control flow statement testing and for saving data values returned by stored procedures. When a local variable is referenced, it is preceded by its name with the flag "@" and must be defined with the DECLARE command before it can be used.

Global variables
Global variables are variables used internally by SQL Server systems and are scoped not only to a program, but to any program that can be called at any time. Global variables typically store configuration settings and statistics for some SQL Server. The user can use global variables in the program to test the system's SetPoint or the state value after the Transact-SQL command executes.

The following points should be noted when using global variables:
① Global variables are not defined by the user's program, they are defined at the server level.
② users can only use pre-defined global variables.
When ③ references a global variable, it must begin with the marker "@@".
The name of the ④ local variable cannot be the same as the name of the global variable, otherwise unpredictable results will occur in the application.

SELECT @ @ERROR Returns the error code (integer) of the last Transact-SQL statement executed (0, indicating no error; 1, indicating an error)

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

SELECT @ @IDENTITY--Returns the last inserted identity value
Select USER_NAME ()--Return user database user name

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.

Description of common global variables reproduced in SQL Server

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.