T-SQL Constants

T-SQL constants are literal or scalar values that represent a data type. The following constants are supported by the language (the data types are discussed later in this chapter):

• Character strings

• Unicode strings

• Binary constants

• Bit constants

• Datetime constants

• Integer constants

• Decimal constants

• Float and real constants

• Money constants

• Unique identifier constants

Character String Constants

Character string constants are surrounded by single quotation marks and can include alphanumeric characters (a-z, A-Z, and 0–9) and the additional characters, such as exclamation point (!), at sign (@), and pound sign (#). The bounding quotation marks are the default delimiter recognized by SQL Server. Setting the QUOTED_IDENTIFIER option for a connection to OFF can, however, change this, if using single quotation marks causes problems in your development environment, where strings are usually bounded by single quote marks, as in Visual Basic 2005 (VB), or by double quotes, as in C#.

The OLE DB drivers automatically set the QUOTED_IDENTIFIER to ON upon connection, and often an apostrophe can trash an application because SQL Server raises hell when it sees the apostrophe and thinks it’s an identifier. In this case, the “official” solution is to add an extra quote so that you send something like ‘St. Elmo’s Fire’ to the server as “St. Elmo’s Fire”.

Asking your end users to do that, however, is a cockamamie solution, to say the least, because it is unacceptable for your data entry people to have to remember to type an apostrophe twice. If you have this problem, and you most likely do, you can use a function like REPLACE(), which is a VB function (and there are equivalent functions in all languages), to add the second quote mark under the “sheets.” You could also use a data-bound “text” control (which I am not fond of) to make the necessary adjustments automatically.

Also, if the QUOTED_IDENTIFIER option has been set OFF for a connection, character strings can also be enclosed in double quotation marks, but the OLE DB provider and ODBC driver automatically use SET QUOTED_IDENTIFIER ON when they connect to SQL Server. The use of single quotation marks is, however, recommended.

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 necessary in strings embedded in double quotation marks.

Collations and code pages are also important considerations when it comes to strings. The character string constants are assigned the default collation of the current database attached to in the connection. However, you can use the COLLATE clause (discussed a little later in this chapter) to specify a different collation. The character strings you enter at the client usually conform to the code page of the computer. They are translated to the database code page, if necessary, upon transmission to the server.

Empty strings are represented as two single quotation marks with nothing in between. However, if you are working in database compatibility mode 6.x, an empty string is treated as a single space. The SQL Server Unicode strings support the concept of enhanced collations.

Unicode String Constants

The Unicode strings have a format very similar to character strings, but they are preceded by what we call the N identifier. The N stands for National Language in the SQL standard. Usage requires that the N prefix be uppercase. In the following example, “Jeffrey” is the character constant, but in order to provide a Unicode constant, I would have to provide N’Jeffrey’.

Unicode constants are interpreted as Unicode data. They are not evaluated using a code page, but they do have a collation, which primarily controls comparisons and case sensitivity. When you use the Unicode constant, you are assigned the default collation of the database you are connected to. But you can change this with the COLLATE clause to specify a collation. (See “Nchar and Nvarchar” later in this chapter.) The SQL Server Unicode strings support the concept of enhanced collations supported by SQL Server 2005.

Tip 

Consider replacing all char, varchar, and text data types with their Unicode equivalents. This will help you avoid code page conversion issues.

Binary Constants

The binary constants are identified with the suffix Ox (an empty binary string) and are strings composed of hexadecimal numbers. They are not enclosed in quotation marks.

Bit Constants

The number zero or one represents a bit constant. These do not get enclosed in quotation marks. If you use a number larger than 1, SQL Server converts it to 1.

Datetime Constants

You can use the datetime constants as character date values, in specific formats. They are enclosed in single quotation marks as follows:

 'October 9, 1959' '9 October, 1959' '591009' '10/09/59' 

I have discussed the formats for the datetime constants later in this chapter.

Integer Constants

The integer constants are represented by strings of numbers and must be whole numbers. They do not get enclosed in quotation marks like strings and cannot contain decimal points. Integer constants are illustrated as follows:

 2006 6

Decimal Constants

The decimal constants are represented by strings of numbers that are not enclosed in quotation marks but can contain a decimal point. The following examples represent decimal constants:

 146.987 5.1

Float and Real Constants

The float and real constants are represented using scientific notation (see “SQL Server Data Types” later in this chapter). They are not enclosed in single quotes and appear as follows:

 101.5E5 2E+100

Money Constants

The money constants are represented as strings of numbers. They can be whole numbers, or they can include the optional decimal point. You can also use a currency symbol as a prefix. They are not enclosed in quotation marks. Examples of money constants are as follows:

 1200.08 $500.00 R35.05

Uniqueidentifier Constants

The uniqueidentifier is a string that represents the globally unique identifier (GUID), pronounced “gwid” or often as “goo ID.” These constants can be specified in either character or binary string notation. The following example represents the same GUID:

 '82B7A80F-OBD5–4343–879D-C6DDDCF4CF16' 0×FE4B4D38D5539C45852DD4FB4C687E47

You can use either notation, but the character string requires single quotes, as demonstrated here.

Signing Constants

To sign a numeric constant, merely apply the + or −unary operator to it. The default sign is positive if the operator is not applied. The following examples are signed:

 +$500.00 −2001