The language of the Database Engine, Transact-SQL, has the same basic features as other common programming languages:
- Literal values (also called constants)
- Identifiers
- Delimiters
- Comments
- Reserved keywords
The following sections describe these features.
1. Literal Values
A literal value is, for example, an alphanumerical, hexadecimal, or numeric constant. A string constant contains one or more characters of the character set enclosed in two single straight quotation marks (‘ ‘) or double straight quotation marks (“”). (Single quotation marks are preferred due to the multiple uses of double quotation marks, as discussed in a moment.)
If you want to include a single quotation mark within a string delimited by single quotation marks, use two consecutive single quotation marks within the string. Hexadecimal constants are used to represent nonprintable characters and other binary data. Each hexadecimal constant begins with the characters ‘0x’ followed by an even number of characters or numbers. Examples 4.1 and 4.2 illustrate some valid and invalid string constants and hexadecimal constants.
Example 4.1
Some valid string constants and hexadecimal constants follow:
‘Philadelphia’
“Berkeley, CA 94710”
‘9876’
Apostrophe is displayed like this: can’ ‘t’ (note the two consecutive single quotation marks) 0x53514C0D
Example 4.2
The following are not string constants:
AB’C’ (odd number of single quotation marks)
‘New York” (same type of quotation mark-single or double-must be used at each end of the string)
The numeric constants include all integer, fixed-point, and floating-point values with and without signs (see Example 4.3).
Example 4.3
The following are numeric constants:
130
-130.00
-0.357E5 (scientific notation—nErn means n multiplied by 10”)
22.3E-3
A constant always has a data type and a length, and both depend on the format of the constant. Additionally, every numeric constant has a precision and a scale factor. (The data types of the different kinds of literal values are explained later in this chapter.)
2. Identifiers
In Transact-SQL, identifiers are used to identify database objects such as databases, tables, and indices. They are represented by character strings that may include up to 128 characters and may contain letters, numerals, or the following characters: _, @, #, and $. Each name must begin with a letter or one of the following characters: _, @, or #. The character # at the beginning of a table or stored procedure name denotes a temporary object, while @ at the beginning of a name denotes a variable. As explained in the next section, these rules don’t apply to delimited identifiers (also known as quoted identifiers), which can contain, or begin with, any character (other than the delimiters themselves).
3. Delimiters
In Transact-SQL, double quotation marks have two meanings. In addition to enclosing strings, double quotation marks can also be used as delimiters for so-called delimited identifiers. Delimited identifiers are a special kind of identifier usually used to allow the use of reserved keywords as identifiers and also to allow spaces in the names of database objects.
NOTE Two key differences between regular and delimited identifiers are that delimited identifiers are enclosed in double quotation marks and are case sensitive. (Transact-SQL also supports the use of square brackets instead of double quotation marks.) Double quotation marks are used only for delimiting strings. Generally, delimited identifiers were introduced to allow the specification of identifiers, which are otherwise identical to reserved keywords. Also, delimited identifiers may contain characters that are normally illegal within identifier names, such as blanks.
In Transact-SQL, the use of double quotation marks is defined using the QUOTED_ IDENTIFIER option of the SET statement. If this option is set to ON, which is the default value, an identifier in double quotation marks will be defined as a delimited identifier. In this case, double quotation marks cannot be used for delimiting strings.
4. Comments
There are two different ways to specify a comment in a Transact-SQL statement. Using the pair of characters /* and */ marks the enclosed text as a comment. In this case, the comment may extend over several lines. Furthermore, the characters — (two hyphens) indicate that the remainder of the current line is a comment. (The two — comply with the ANSI SQL standard, while /* and */ are the extensions of Transact-SQL.)
5. Reserved Keywords
Each programming language has a set of names with reserved meanings, which must be written and used in the defined format. Names of this kind are called reserved keywords. Transact-SQL uses a variety of such names, which, as in many other programming languages, cannot be used as object names, unless the objects are specified as delimited identifiers.
NOTE In Transact-SQL, the names of all data types and system functions, such as CHARACTER and INTEGER, are not reserved keywords. Therefore, they can be used to denote objects. (Do not use data types and system functions as object names! Such use makes Transact-SQL statements difficult to read and understand.)
Source: Petkovic Dusan (2020), Microsoft SQL Server 2019: A Beginner’s Guide, Seventh Edition-McGraw-Hill Education.