1. Overview of constants

Constant Also known as literal or scalar values , A symbol that represents a specific data value , A constant is a constant quantity during the running of a program , The format of a constant depends on the data type of the value it represents ;
Depending on the type of constant ,T-SQL The constants are divided into Digital constant 、 String constant 、 Date and time constants, symbolic constants, etc ;

2. Digital constant

Numerical constants are numerical constants , Its format does not require any other symbols , Just assign values according to specific data types ;
T-SQL The numeric constants in mainly include bit Constant 、integer Constant 、decimal Constant 、money Constant 、float and real Constant ;

2.1 bit Constant

bit Constant Using a digital 0 or 1 Express , Do not use quotation marks ; If you use a value greater than 1 The number of , It will be converted to 1;

2.2. integer Constant

integer Constant Represented by a string of numbers that are not enclosed in quotation marks and do not contain a decimal point ,integer Constants must be integers , Cannot contain decimal points, such as 352、5;

2.3 decimal Constant

decimal Constant Represented by a string of numbers that are not enclosed in quotation marks and contain a decimal point , Such as 3.1415926、2.0;

2.4 flat and real Constant

float and real Constant Use scientific counting to express , Such as 101.5E5、0.3E-2;

2.5 money Constant

money Constant Expressed as a string of numbers prefixed with an optional decimal point and an optional currency symbol , These constants do not use quotation marks , Such as $15、$325.25;

3. String constant

T-SQL String constants are alphanumeric characters enclosed in single quotes （a — z、A — Z、0 — 9） And special characters , Such as exclamation mark （!）、@ operator （@）、 Number number （#）; String is divided into ASCII String and Unicode String constant ;

3.1 ASCII character string

ASCII character string ： In single quotation marks , Such as ‘A’,‘teacher’、‘Bye bye’ etc. ; An empty string uses two single quotation marks without any characters in the middle ‘ ’ Express ;

3.2 Unicode String constant

Unicode String constant ： The format is similar to an ordinary string , But there's one in front of it N identifier （N representative SQL-92 The international language in the standard （National Language））,N The prefix must be Capital ; Such as ,‘Teacher’ Is a string constant and N‘Teacher’ It is Unicode Constant ;
Unicode Constant Be interpreted as Unicode data , Do not use code pages for calculations ;
Unicode Constants have sorting rules , Mainly used to control comparison and case sensitivity ; for Unicode Constant assigns the default collation for the current database , Unless used COLLATE Clause specifies the collation for it ;
Unicode Each character in the data is stored in two bytes , Each character in character data is stored in a byte ;

4. Date and time constants

The date and time constants are in Single quotation marks Enclose a string representing the date and time ;
According to the different presentation formats of date and time ,T-SQL The date time constant can be expressed in many ways ：

1. Alphabetic date format ： Such as ‘March 23,2008’;
2. Number date format ： Such as ‘5/15/2008’ ‘2008-5-15’;
3. Unseparated character format ： Such as ‘20080527’;
4. Time constant ： Such as ‘15:30:25’ ‘05:30:PM’;
5. Date time constant ： Such as ‘March 23,2008 15:30:25’;

The date and time functions are shown in the following table ：

These scalar functions perform operations on date and time input values , And return a string 、 Numeric values or date and time values ;

4. Symbolic constant

Uniqueidentifier A constant is a globally unique identifier （GUID） String of values , Can be specified in character or binary string format ;
Both examples specify the same GUID：

'6F9619FF-8B86-D011-B42D-00C04FC964FF'
oxff1996f868b11d0b42d00c04fc964ff