Constants (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
A constant, also known as a literal or a scalar value, is a symbol that represents a specific data value. The format of a constant depends on the data type of the value it represents.
Note
The term constant in application development and the concept of a constant in Transact-SQL (T-SQL) are not the same. There is no specific way to set a global static value in T-SQL. Constants in T-SQL are the equivalent of string literal values.
Character string constants
Character string constants are enclosed in single quotation marks and include alphanumeric characters (a-z, A-Z, and 0-9) and special characters, such as exclamation point (!), at sign (@), and number sign (#). Character string constants are assigned the default collation of the current database. If the COLLATE clause is used, the conversion to the database default code page still happens before the conversion to the collation specified by the COLLATE clause. Character strings typed by users are evaluated through the code page of the computer and are translated to the database default code page if it is required.
Note
When a UTF8-enabled collation is specified using the COLLATE clause, conversion to the database default code page still happens before the conversion to the collation specified by the COLLATE clause. Conversion is not done directly to the specified Unicode-enabled collation. For more information, see Unicode string.
If the QUOTED_IDENTIFIER option has been set OFF for a connection, character strings can also be enclosed in double quotation marks, but the Microsoft OLE DB Driver for SQL Server and ODBC Driver for SQL Server automatically use SET QUOTED_IDENTIFIER ON. We recommend using single quotation marks.
If a character string enclosed in single quotation marks contains an embedded quotation mark, represent the embedded single quotation mark with two single quotation marks. This isn't required in strings embedded in double quotation marks.
The following are examples of character strings:
'Cincinnati'
'O''Brien'
'Process X is 50% complete.'
'The level for job_id: %d should be between %d and %d.'
"O'Brien"
Empty strings are represented as two single quotation marks with nothing in between. In 6.x compatibility mode, an empty string is treated as a single space.
Character string constants support enhanced collations.
Note
Character constants greater than 8000 bytes are typed as varchar(max) data.
Unicode strings
Unicode strings have a format similar to character strings, but are prefixed with an N identifier (N stands for National Language in the SQL-92 standard).
Important
The N prefix must be uppercase.
For example, 'Michél' is a character constant while N'Michél' is a Unicode constant. Unicode constants are interpreted as Unicode data, and aren't evaluated by using a code page. Unicode constants do have a collation. This collation primarily controls comparisons and case sensitivity. Unicode constants are assigned the default collation of the current database. If the COLLATE clause is used, the conversion to the database default collation still happens before the conversion to the collation specified by the COLLATE clause. For more information, see Collation and Unicode Support.
Unicode string constants support enhanced collations.
Note
Unicode constants greater than 8000 bytes are typed as nvarchar(max) data.
Binary constants
Binary constants have the prefix 0x and are a string of hexadecimal numbers. They aren't enclosed in quotation marks.
The following are examples of binary strings are:
0xAE
0x12Ef
0x69048AEFDD010E
0x (empty binary string)
Note
Binary constants greater than 8000 bytes are typed as varbinary(max) data.
bit constants
bit constants are represented by the numbers 0 or 1, and aren't enclosed in quotation marks. If a number larger than 1 is used, it is converted to 1.
datetime constants
datetime constants are represented by using character date values in specific formats, enclosed in single quotation marks.
The following are examples of datetime constants:
'December 5, 1985'
'5 December, 1985'
'851205'
'12/5/98'
Examples of datetime constants are:
'14:30:24'
'04:24 PM'
integer constants
integer constants are represented by a string of numbers that aren't enclosed in quotation marks and don't contain decimal points. integer constants must be whole numbers; they can't contain decimals.
The following are examples of integer constants:
1894
2
decimal constants
decimal constants are represented by a string of numbers that aren't enclosed in quotation marks and contain a decimal point.
The following are examples of decimal constants:
1894.1204
2.0
float and real constants
float and real constants are represented by using scientific notation.
The following are examples of float or real values:
101.5E5
0.5E-2
money constants
money constants are represented as string of numbers with an optional decimal point and an optional currency symbol as a prefix. money constants aren't enclosed in quotation marks.
SQL Server doesn't enforce any kind of grouping rules such as inserting a comma (,) every three digits in strings that represent money.
Note
Commas are ignored anywhere in a string literal that is cast to the money data type.
The following are examples of money constants:
$12
$542023.14
$-23
uniqueidentifier constants
uniqueidentifier constants are a string representing a GUID. They can be specified in either a character or binary string format.
The following examples both specify the same GUID:
'6F9619FF-8B86-D011-B42D-00C04FC964FF'
0xff19966f868b11d0b42d00c04fc964ff
Specify negative and positive numbers
To indicate whether a number is positive or negative, apply the + or - unary operators to a numeric constant. This creates a numeric expression that represents the signed numeric value. Numeric constants use positive when the + or - unary operators aren't applied.
Signed integer expressions:
+145345234
-2147483648
Signed decimal expressions:
+145345234.2234
-2147483648.10
Signed float expressions:
+123E-3
-12E5
Signed money expressions:
-$45.56
+$423456.99
Enhanced collations
The Database Engine supports character and Unicode string constants that support enhanced collations. For more information, see the COLLATE (Transact-SQL) clause.