Constants (Transact-SQL)

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.

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, unless the COLLATE clause is used to specify a collation. 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.

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 SQL Native Client Provider and ODBC driver 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 is not required in strings embedded in double quotation marks.

'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 preceded by an N identifier (N stands for National Language in the SQL-92 standard). 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 are not 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, unless the COLLATE clause is used to specify a collation. Unicode data is stored by using 2 bytes per character instead of 1 byte per character for character data. For more information, see Using Unicode Data.

bit constants are represented by the numbers 0 or 1, and are not enclosed in quotation marks. If a number larger than one is used, it is converted to one.

datetime constants

datetime constants are represented by using character date values in specific formats, enclosed in single quotation marks. For more information about the formats for datetime constants, see Using Date and Time Data.

integer constants are represented by a string of numbers that are not enclosed in quotation marks and do not contain decimal points. integer constants must be whole numbers; they cannot contain decimals.

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 are not applied.