T-SQL query advanced--variable

Overview

Variables are an essential part of a language, and of course, the same is true for T-SQL. Variables are often seldom used in simple queries, but they are an essential part of complex queries or stored procedures.

Types of variables

In T-SQL, variables can be divided into global variables (Globals Variable) and local variables (local Variable) in terms of survival.

1. A global variable is a system-defined variable that can be accessed throughout an instance of SQL Server. Global variables with @@ 作为 The first character, the user can only access and cannot be assigned a value.

2. Local variables are defined by the user, and the life cycle is only valid within one batch. Local variables are defined and assigned by the user at @ as the first character.

A simple example is as follows

Because global variables are only used to read some of the system's parameters, each global variable represents the meaning of Google's ... This article mainly introduces local variables (i.e. user-defined variables).

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.

use of local variables

in T-SQL, a local variable is an object that stores a single value of the specified data type. The definition of a variable in T-SQL is actually the same as in most high-level languages.

Local variables are often used in the following three uses:

1. The number of times a loop is recorded in a looping statement or the number of times it is used to control loops.

2. Used to store process statements to control process trends

3. Store the return value of the stored procedure or function

In fact, local variables that store any business data belong to this category of applications.

declaration of a local variable

The declaration of a local variable must be "DECLARE" as the keyword, and the name of the variable must be "@" as the first character of the variable name. You must provide a data type and data length for the declared variable. For example:

The data type of a local variable cannot be text,ntext, and the image type, which defaults to 1 when the data type is provided only for a character variable, and no data length is provided.

Everything only declares that the initial value of a local variable that does not have an assignment is "NULL".

assigning values to local variables

In T-SQL, the assignment of local variables is achieved through the "Set" keyword and the "select" keyword.

In fact, using set or select depends on several factors

1. When assigning values to multiple variables

The SELECT keyword supports multiple variable assignments, and the SET keyword only supports assigning one value at a time

2. The number of expressions returned when the value is assigned

When using set for assignment, an error occurs when the expression returns multiple values. The SELECT keyword takes the last one when it returns multiple values in an assignment expression.

For example, suppose the XXX table has only the following data:

When you use the SELECT keyword, you can take the last of the returned values.

3. When an expression does not return a value

When a local variable is assigned a value using set, if the assignment expression does not return a value, the local variable becomes null, and when the select assigns a value to the expression, the local variable remains the original value if the expression does not return a value.

4. When ... When you are a standard enthusiast

Use the Set keyword strongly to assign values to local variables, because set is ANSI standard ...

5. When ... You don't bother remembering when to use set or when to use select

Well, I admit I'm lazy, too. Then you make a simple difference: when your assignment statement needs to reference a data source (table), use SELECT. In addition, use set.

Local Table Variables

A local table variable is a special local variable. Unlike temporary tables, local table variables have all the characteristics of local variables. In the query, because local table variables are present in memory, not on the hard disk, so the speed is much faster than the temporary table or the actual table, when the local table variable is used in the query to serve as multiple tables when connecting the intermediate table Like what:

This greatly improves the query speed of the multi-table connection.

Summary

This paper introduces the types of variables and the scope, definition and assignment methods of local variables. Table variables are also briefly described. In complex queries, the system's understanding of T-SQL variables is an integral part of writing good query statements.

T-SQL query advanced--variable