T-SQL query advanced-Variable

Overview

Variables are an essential part of a language and, of course, the same for T-SQL. Variables are rarely used in simple queries, but variables are an essential part of complex queries or stored procedures.

 

Variable type

In T-SQL, variables can be dividedGlobal variable)AndLocal variable ).

1. Global variables are defined by the system and can be accessed throughout the SQL server instance. The global variables take @ as the first character. Users can only access them and cannot assign values.

2. The local variable is defined by the user, and the lifecycle is valid only in one batch. The local variable uses @ as the first character, which is defined and assigned by the user.

A simple example is as follows:

Because the global variables are only used to read some system parameters, please google for the specific meaning of each global variable... This topic describes local variables (custom variables ).

 

Usage of local variables

In the T-SQL, local variables are an object that stores a single value of the specified data type. The definition of variables in the T-SQL is actually the same as in most advanced languages.

Local variables are often used for the following purposes:

1. record the number of cycles or the number of cycles used to control.

2. It is used to store process statements to control the process trend.

3. Store the returned values of stored procedures or functions

In fact, local variables that store any business data belong to this type of application.

 

Local variable Declaration

The declaration of local variables must use "declare" as the keyword, and the variable name must use "@" as the first character of the variable name. A data type and length must be provided for declared variables. for example:

The data type of a local variable cannot be text, ntext, or image. When only the data type is provided for a local variable, the Data Length is 1 by default.

The initial values of all local variables that are not assigned values are "null ".

 

Assignment of local variables

In the T-SQL, the assignment of local variables is implemented by the "set" keyword and the "select" keyword.

In fact, using set or select depends on the following factors:

1. When assigning values to multiple variables

The Select keyword supports assigning values to multiple variables, while the set keyword only supports assigning values to one value at a time.

 

2. Number of return values of the expression when values are assigned

When you use set to assign values, an error is returned when the expression returns multiple values. The select keyword returns the last value when the value expression returns multiple values.

For example, assume that the xxx table only contains the following data:

When the select keyword is used, the last return value can be obtained.

 

3. When the expression does not return a value

When you use set to assign values to a local variable, if the value assignment expression does not return a value, the local variable becomes null. When the select clause assigns a value to the expression, if the expression does not return a value, the local variable remains the original value.

4. When... When you are a standards enthusiast

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

 

5. When... When you use set or select

Okay, I admit that I am also very lazy. Then you should make a simple difference:When your value 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 the characteristics of all local variables. in the query, because the local table variables exist in the memory instead of the hard disk, the speed is much faster than that of the temporary table or the actual table, when using the most local table variables, it acts as the intermediate table when multiple tables are connected in the query. For example:

This greatly improves the query speed of Multi-Table connections.

 

 

Summary

This section describes the types of variables, the scope of use of local variables, the definition and the assignment method, and the table variables. In complex queries, the system's understanding of T-SQL variables is an essential part of a good query statement.