T-SQL syntax elements

First, identifiers

In the T-SQL language, the names of SQL Server databases and their data objects (such as tables, indexes, views, stored procedures, triggers, and so on) need to be named and distinguished by names, which are called identifiers.

Typically, SQL Server databases, databases, and various data objects should have an identifier, but for some objects, such as constraints, identifiers are optional. It is recommended that each object use an identifier.

　 1. Rules for regular identifiers

　　　(1), first character: the first character of an identifier must meet the following criteria:

The letters that are defined by the Unicode standard 3.2 are usually letters A~Z and a~z.

An underscore (_), an at character (@), or a number sign (#).

　　　(2), subsequent characters:

The letters that are defined in Unicode Standard 3.2.

The decimal number in the base Latin character or other country/region character.

At sign (@), dollar sign ($), number sign, or underscore.

　　　(3), cannot be reserved word:

Regular identifiers cannot use reserved words inside SQL Server, such as Char.

　　　(4), do not allow embedding spaces:

　 2. Delimited identifiers

When you must use reserved words, such as table, such identifiers are reserved words inside SQL Server, and if you have to use them, you must do so in the following way: "Table".

　　　(1), separator

T-SQL specifies that the following symbols are specific delimiters.

1, double quotation mark ("): Used to represent the identifier of the reference.

2, brackets ([]): Used to denote identifiers in parentheses.

　　　(2), what time to use separators

T-SQL often uses delimiters in the following cases.

1. When a reserved word is included in an object name or part of an object name.

2, the use of other special characters.

ii. Types of data

Data in the T-SQL language and SQL Server databases typically requires defining a data type that defines the kind of data that an object can hold.

　 1. Those objects require data type

　　　(1) columns for tables and views:

When you define a table or view, its columns need to define the data type.

　　　(2), the parameters of the stored procedure:

Define the data type of the parameter in the T-SQL code that defines the stored procedure.

　　　(3), variables:

If you use variables in T-SQL, you need to define the data type.

　　　(4), function with return value:

The return value in a T-SQL function that returns one or more specific types of data values requires a data type to be defined.

　　　(5), stored procedure with return code:

A stored procedure that has a return code.

　 2. How to choose the right data type

　　　(1), must

First determine the kind of data, the maximum length and size of the data that needs to be stored, and the precision and scale for the numeric data type, and then pick the alternate data type in T-SQL.

　　　(2), sufficient

You can then select the available types as the last option in the alternate data type. For example, to express the "ID number" field such as nvarchar and nchar data types can be, finally we can use NCHAR (18), because the identity card number is defined as a unified 18-bit, so select the fixed-length data type.

　　　(3), trade-offs

Weigh space and efficiency, weigh the way data is stored, and more.

Typically, the smaller the length of the data type, the less storage space is required to store large amounts of data. Therefore, the more data records I/O can read into memory, the more records will be stored in memory space of the same size, so that T-SQL statements are more likely to be read from memory when they use data, so natural efficiency is high, especially in systems with frequent online transactions, so the design benefits are obvious. "Detail determines success or failure", the choice of data type is often very important to the performance of the database system.

　 3. Basic Data Type

Name	Description	Range of values
Bit	Integer type	0, 1, or null, commonly used to represent Yes (No), True (False), and so on
Int	Integer type	-2147483648~2147483647
tinyint	Integer type	0~255
smallint	Integer type	-32768~32768
Bigint	Integer type	-9223372036854775808~9223372036854775807
Binary	Binary	Fixed-length binary data for 1~8000 characters, such as 0x2a
varbinary	Binary	1~8000 character's variable-length binary data, varbinary (max) can store 2^31 bytes
Image	Binary	Binary data up to 2 billion characters
Char	Character type	Fixed-length, non-Unicode character data for 1~8000 characters
varchar	Character type	1~8000 character's non-fixed-length, Unicode character data varchar (max) can store 2^31 characters
Text	Character type	Unicode character data up to 2 billion characters
NChar	Unicode character type	1~4000 fixed-length, Unicode character data
nvarchar	Unicode character type	1~4000 Characters of non-fixed-length, Unicode character data, nvarchar (max) can store 2^31-1 characters
ntext	Unicode character type	1~1073741823 characters, non-fixed-length, Unicode character data
Datetime	Date-Time Type	January 1, 1753 ~9999 date and time of year December 31
smalldatetime	Date-Time Type	January 1, 1900 ~2079 date and time of year June 6
Decimal	Precise numerical type	-Numerical data of fixed accuracy and range of 1038-square +1~1038-1
Numeric	Precise numerical type	Same decimal
Float	Approximate numerical type	Floating-point number between -1.79e+308~1.79e+308
Real	Approximate numerical type	Floating-point number between -3.40e+38~3.40e+38
Money	Currency type	-263~263-1 Currency data, accurate to one out of 10,000
SmallMoney	Currency type	-214748.3648~214748.3647 Currency data, accurate to one out of 10,000
Timestamp	Special types	Timestamp, which records the sequence of activities of SQL Server in a row of data
uniqueidentifier	Special types	Globally unique identifier (GUID) for 16-bit 16 binary data representation
sql_variant	Special types	Data types that store various data type values supported by SQL Server except text, ntext, image, and sql_variant

　　　　　　SQLServer2008 New data types

1. Date and Time type

Name	Range of values
Time	00:00:00.0000000 to 23:59:59.0000000, accurate representation of time in HH:MM:SS format
Date	00001-01-01 to 99999-12-31, representing the date in the format of the year-month-day
DateTime2	0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59:9999999, indicating date and time
DateTimeOffset	00000-01-01 00:00:00.0000000 to 99999-12-31 23:59:59:9999999

2, Hierarchid

New data type for creating a hierarchical table, or referencing a data hierarchy at another location.

three, constant

Constants are data in T-SQL code whose values are always constant. The definition format of a constant depends on the data type to which it belongs.

　　the use of constants does not need to be defined and is used directly in T-SQL, so it is necessary to focus on the format of a variety of typical constants.

　 1. Format of numeric constants

The format of a numeric constant does not require any other symbols, and it is only possible to assign values to a specific data type

　　　　(1), Bit constants:

0, 1

　　　　(2), int constants:

89, 22

　　　　(3), decimal (numeric) constants:

123.89, 89.0

　　　　(4), float (real) constants:

100.5E5

　　　　(5), Money (smallmoney) constants:

$123.90,

　 2, the format of the string type constant

The format of a string constant needs to be enclosed in single quotation marks (').

　　　　1. Non-Unicode string constants:

' Hello World '

　　　　2. Unicode string constants:

N ' Hello World '

N here means Unicode, which is a double-byte character. For Western characters, it is enough to be stored in one byte, and two bytes is required for the oriental character. Unicode is intended to be uniform, canonical, convenient, and compatible, and the western characters are also stored in two bytes. This means that adding N means that the string is stored in Unicode. But sometimes the same as not add, what is the reason? This is caused by an automatic conversion.

　　　　3, single quotation marks as a string constant processing:

If the single quotation mark itself also belongs to the contents of the string constant, it is necessary to enclose it in single quotation marks, such as ' O ' Brien ' is actually defined as the string O ' Brien. "Hello", the definition is ' hello '.

　　　　4. Format of date-time constants

The format of datetime constants needs to be enclosed in single quotation marks (' '), consistent with the format of the string constants.

such as: ' April 15,1998 ' 04/15/98 ' 14:30:24 ' 04:24 PM '

Iv. Notes

In T-SQL code, it is a good habit to add comment information, which facilitates the readability of the program.

　　 1. Add a single line comment

If you need to add a single line of comments, you can use two hyphens (--).

such as: SELECT * FROM Person--query all information

　　 2. Add multiple lines of comment information

If you need to add multiple lines of comment information, you can use the forward slash model character pair (/**/)

Such as:

/*    This is the first line of comments    This is the second line of comments */

v. Variables

In the T-SQL language, it is often necessary to use variables to temporarily assign values, and variables are often used as counters for storage execution in T-SQL code, or as temporary data storage sites.

　 1. Data types of variables

Data types supported by SQL Server.

　 2. Classification of variables

　　　(1), global variables

Global variables in T-SQL are variables defined and used by the SQL Server system. DBAs and users can use the values of global variables, but they cannot define global variables themselves. Global variables are marked with two @@ 为 such as @ @Connections.

　　　(2), local variables

Local variables in T-SQL are user-defined and used variables that are scoped only within the defined T-SQL program. Local variables are marked with an @, such as @a.

　 3. Declaration of local Variables

In a T-SQL program, the syntax for defining a local variable is as follows.

　　　　Declare {{@local_variable  data_type}  | {@cursor_variable_namecursor} | {table_type_definition}} [,... n]

The main parameters are described below.

@local_variable: The name of the local variable, which must begin with the at sign (@) and conform to the rules of the identifier.

Data_type: Any system-provided or user-defined data type, but cannot be a text, ntext, or image data type.

@cursor_variable_name: The name of the cursor variable.

Cursor: Specifies that the variable is a local cursor variable.

Table_type_definition: Defines the table data type.

　 4. Assigning values to local variables

There are three ways to assign values to local variables in a T-SQL program.

　　　1. Set Statement Assignment value

In a T-SQL program, the syntax for assigning a local variable with a SET statement is as follows:

SET @local_variable =expression

Where the @local_variable is the name of the local variable, and expression is valid, indicating that the value of the expression is assigned to a local variable.

　　　2. Select statement Assignment value

An assignment statement that is equivalent to the set above is to replace set with SELECT.

SELECT @local_variable =expression

　　　3. Assigning values by selecting statements

In many cases, a local variable needs to get the value of a field after querying a table through a SELECT statement.

Select @empId = Max　　(empId) --assigning values to local variables

If the query statement returns more than one row, and the variable refers to a non-scalar expression, the value of the last variable will be the value of the specific field in the last row of records.

　 5. Scope of local variables

The scope of a local variable is the range of T-SQL statements that can reference the variable. The scope of a local variable is from where the variable is declared to the end of the batch or stored procedure that declares the variable.

T-SQL syntax elements