T-SQL Operators


T-SQL supports several operators that can be used to specify actions that are performed on one or more expressions. The following is a list of the operators that are supported in SQL Server 2005:

  • Arithmetic operators

  • Assignment operators

  • Bitwise operators

  • Comparison operators

  • Logical operators

  • String concatenation operator

  • Unary operators

The Arithmetic Operators

These operators are used to perform mathematical operations on two expressions of any numeric data type. Table 10–1 lists the arithmetic operators. As indicated in the table, the + and operators can also be used with the date data types discussed later in this chapter.

Table 10–1: Arithmetic Operators

Operator

Purpose

Add (+)

Used to add two numbers. Also adds a number of days to a date.

Subtract ()

Used to subtract two numbers. Also subtracts days from a date.

Multiply (*)

Used to multiply two numbers.

Divlde (/)

Used to divide one number by another number.

Modulo (%)

Used to obtain the remainder of one number divided by another.

The Assignment Operator

As in most programming languages, there is a single assignment operator. In T-SQL it is the equal sign. (This is unfortunate for experts in other languages where the equal sign is used to equate one expression [comparison] with another, as it is in Java which uses the colon-equal [:=] for assignment). In the following example, a simple use of the T-SQL demonstrates assigning a numeric value to a variable:

 DECLARE @RecordCounter INT SET @RecordCounter = 1 

You can also use the assignment operator to assign a string to provide a name for a column heading when you display a result set. The equal sign is also a T-SQL comparison operator.

Bitwise Operators

T-SQL provides bitwise operators that you can use within T-SQL statements to manipulate the bits between two expressions of any integer or binary string-based data types (except image). Also, operands cannot both be of the binary string data type. Table 10–2 lists the bitwise operators and their purposes. It also lists the Bitwise NOT (~) operator, which applies to one operand. (See also the unary operators discussed later in this section.)

Table 10–2: Bitwise Operators

Operator

Description

Purpose

&

Bitwise AND

Bitwise logical AND operation between two integers.

|

Bitwise OR

Bitwise logical OR between two integers as translated to binary expressions.

^

Bitwise Exclusive OR

Bitwise exclusive OR between two integers as translated to binary expressions.

Table 10–3 lists the supported operand data types.

Table 10–3: Operand Data Types

Left Operand

Right Operand

binary

int, smallint, or tinyint

bit

int, smallint, tinyint, or bit

int

int, smallint, tinyint, binary, or varbinary

smallint

int, smallint, tinyint, inary, or varbinary

tinyint

int, smallint, tinyint, binary, or varbinary

varbinary

int, smallint, or tinyint

Comparison Operators

The comparison operators test equality between two expressions and are often used in WHERE clauses to test for a column value. They can be used on all expressions except expressions of the text and image data types. Table 10–4 lists the comparison operators in SQL Server and their functions.

Table 10–4: Comparison Operator

Comparison Operator

Description

Purpose

=

Equals

Used to test for equality

>

Greater

Used to test if one expression is greater than another

<

Less

Used to test if one expression is less than another

>=

Greater or equal

Used to test if one expression is greater than or equal to another

<=

Less or equal

Used to test if one expression is less than or equal to another

<>

Not equal

Used to test if one expression is NOT equal to another

!=

Not equal

Used to test if one expression is NOT equal to another (non-SQL-92 or later)

!<

Not less

Used to test if one expression is NOT less than another (non-SQL-92 or later)

!>

Not greater

Used to test if one expression is NOT greater than another (non- SQL-92 or later)

The result of a comparison expression is that the data type of the return value is a Boolean of TRUE, FALSE, or UNKNOWN. It is also important to take into consideration that when SET ANSI_NULLS is ON, an operator between two NULL expressions returns UNKNOWN. If you switch SET ANSI_NULLS to OFF, the equal operator will return TRUE if it is between two NULLS.

You can also use the AND keyword to combine multiple comparison expressions like the following:

 WHEN UnitsInStock >= 5 AND UnitsInStock <= 15 THEN 'Average Mover'

Note 

You should also be aware that comparisons may be affected by the collations you are using.

Logical Operators

The logical operators test for the truth of some expression. Like comparison operators, they also return a Boolean data type with a value of TRUE or FALSE. These operators, listed in Table 10–5, are used extensively in queries and are most common in WHERE clauses. For example, the statement

 SELECT * FROM Customers WHERE LastName LIKE 'Shapiro%' 

returns rows where the entire value (the operator tests the whole value) looks like “Shapiro.”

Table 10–5: Logical Operators

Operator

Purpose

ALL

TRUE if all of a set of comparisons are TRUE.

AND

TRUE if both Boolean expressions are TRUE.

ANY

TRUE if any one of a set of comparisons are TRUE.

BETWEEN

TRUE if the operand is within a range.

EXISTS

TRUE if a subquery contains any rows.

IN

TRUE if the operand is equal to one of a list of expressions.

LIKE

TRUE if the operand matches a pattern.

NOT

Reverses the value of any other Boolean expression.

OR

TRUE if either Boolean expression is TRUE.

SOME

TRUE if some of a set of comparisons are TRUE.

String Concatenation Operator

The string concatenation operator is the addition sign (+), which is used to concatenate one substring to another to create a third derivative string. In other words, the expression ‘the small bro’+‘wn fox’ stores the value “the small brown fox.” However, be aware that concatenation behavior can vary from database level to database level.

A version 6.5 (the database versions are referenced as 65, 70, 80, or 90) database treats an empty constant as a single blank character. For example, if you run ‘the small bro’+”+‘wn fox’ on a 65 database, you’ll end up with the following being stored: “the small bro wn fox.” (See also the string manipulation functions discussed later in this chapter and also the discussion of collation precedence.)

Unary Operators

The unary operators perform an operation on only one expression of any numeric data type. Table 10–6 lists the unary operators (see the bitwise operators discussed earlier in this section).

Table 10–6: Unary Operators

Operator

Description

Purpose

+

Positive

The numeric value is positive.

Negative

The numeric value is negative.

~

Bitwise NOT

Bitwise logical NOT for one integer as translated to binary expressions. Can be also be used on data types of type integer.

Operator Precedence

As in all modern programming languages, T-SQL operators are governed according to rules of precedence. An operator of a higher level is evaluated before an operator of a lower level. In compound expressions, the operator precedence determines the order of operations.

The order of execution or computation can significantly influence the result. The following list is in order of precedence, from highest (+) to lowest (=).

  • + (Positive), (Negative), ~ (Bitwise NOT)

  • (Multiply), / (Divide), % (Modulo)

  • + (Add), + (Concatenate), (Subtract)

  • =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)

  • ^ (Bitwise Exclusive OR), & (Bitwise AND), | (Bitwise OR)

  • NOT

  • AND

  • ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

  • = (Assignment)

Operators used in an expression that have the same operator precedence level are evaluated according to their position in the expression from left to right. For example, in the expression used in the SET statement of this example, the subtraction operator is evaluated before the addition operator.

 SET @TheNumber = 3 – 3 + 9 

You can also use parentheses to override the defined precedence of the operators in an expression. The expression within the parentheses is evaluated first to obtain a single value. You can then use the value outside of the parentheses.

 5 * (3 + 2)

is the same as

 5 * 5

In expressions that contain expressions in parentheses (nesting), the deepest expression is evaluated first.

Data Type Precedence

Often it becomes necessary to convert a constant or variable of one data type to another, and you would use the CONVERT() function described later in this chapter to do this. However, when you combine two data types with an operator, a data type precedence rule decides which data type gets converted to the data type of the other.

The data type precedence rule dictates that if an implicit conversion is supported (you do not require the use of the conversion function), the data type that has the lower precedence is converted to the data type with the higher precedence. Table 10–7 lists the base data types in order of precedence; the first entry in the table has the highest order, and the last entry has the lowest order. Naturally, when both data types combined by the operator are of the same precedence, no precedence ruling is required.

Table 10–7: Base Data Types in Order of Preference

Data Type Order of Precedence

Type of Data

User-Defined Data Type (UDT) (highest)

User defined

xml

xml

sql_variant

sql_variant

datetime

datetime

smalldatetime

datetime

float

approximation

real

approximation

decimal

exact number

money

exact number

smallmoney

exact number

bigint

exact number

int

exact number

smallint

exact number

tinyint

exact number

bit

exact number

ntext

unicode

text

unicode

image

unicode

timestamp

unicode

uniqueidentifier

uniqueidentifier

nvarchar

unicode

nchar

unicode

varchar

unicode

char

unicode

varbinary

binary

binary

binary

SQL Server Data Types

The following section explains the data types. This discussion is not in any order of preference or precedence, as discussed previously.

Integer Types (Exact Numerics)

The following list presents integers in order of precedence:

  • bigint   Integer, a whole number, data from 2^63 (9223372036854775808) through 2^63–1 (9223372036854775807). The storage size is 8 bytes. Use bigint for large numbers that exceed the range of int. This integer costs more in terms of storage footprint. Your functions will return this data type only if the argument passed is a bigint data type. The smaller integer types are not automatically converted to bigint.

  • int   Integer, a whole number, data from 2^31 (2,147,483,648) through 2^311 (2,147,483,647). The storage size is 4 bytes. This integer should suffice for most needs and remains the primary integer type in use on SQL Server.

  • smallint   Integer data from 2^15 (32,768) through 2^15–1 (32,767). The storage size is 2 bytes.

  • tinyint   Integer data from 0–255. The storage size is 1 byte.

  • bit   This is an integer data type that takes 1, 0, or NULL. You can create columns of type bit, but they cannot be indexed. Also, if there are 8 or less bit columns in a table, the columns are stored as 1 byte by SQL Server, and if there are from 9 through 16 bit columns, they are stored as 2 bytes, and so on. This is a SQL Server conservation feature at work.

Decimal and Numeric Types (Exact Numerics)

The decimal[(p[, s])] and numeric[(p[, s])] types are data types with fixed precision and scale (p=precision and s=scale), as listed in Table 10–8. When maximum precision is used, the valid values are from 10^38–1 through 10^38–1.

Table 10–8: Decimal and Numeric types

Precision

Storage Footprint in bytes

1–9

5

10–19

9

20–28

13

29–38

17

The precision (p) specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38. The scale (s) specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. The default scale is 0; therefore, 0 <= s <= p. The maximum storage sizes vary according to the precision.

Money and Smallmoney (Exact Numerics)

The monetary data types are used for representing monetary or currency values as follows:

  • Money   Values from 2^63 (922,337,203,685,477.5808) through 2^63–1 (+922,337, 203,685,477.5807). This type has accuracy to one ten-thousandth of a monetary unit. The storage footprint is 8 bytes.

  • Smallmoney   Values from 214,748.3648 through +214,748.3647. This type has accuracy to one ten-thousandth of a monetary unit. The storage footprint is 4 bytes.

Float and Real (Approximate Numerics)

These data types are the approximate number data types you use with floating-point numeric data. Floating-point data is approximate, and not all values in the data type range can be precisely represented.

The float [(n)]: This is for floating-point number data from 1.79E+308 through 1.79E+308. The n parameter is the number of bits used to store the mantissa of the float number in scientific notation. This dictates the precision and storage size. The n parameter must be a value in the range 1–53. The float[(n)] data type conforms to the SQL-92 standard for all values of n in the range 1–53. Table 10–9 represents values for n and the corresponding precision and memory costs.

Table 10–9: Float and Real

Value for n

Precision

Footprint in bytes

1–24

7 digits

4 bytes

25–53

15 digits

8 bytes

The real is a floating-point number data type that ranges from 3.40E+38 through 3.40E+38. The footprint of real is 4 bytes.

Datetime and Smalldatetime

The date and time data types represent dates and times from January 1, 1753, through December 31, 9999, to an accuracy of one three-hundredth of a second (equivalent to 3.33 milliseconds or 0.00333 seconds). These values are rounded to increments of .000, .003, or .007 seconds. The footprint for datetime is two four-byte integers. The first four bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date (values for datetime earlier than January 1, 1753, are not permitted). The remaining four bytes store the time of day, represented as the number of milliseconds after midnight.

The smalldatetime type from January 1, 1900, through June 6, 2079, has accuracy to the minute. With smalldatetime, values with 29.998 seconds or lower are rounded down to the nearest minute. Values with 29.999 seconds or higher are rounded up to the nearest minute.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two two-byte integers, as opposed to two four-byte values in datetime. The first two bytes store the number of days after January 1, 1900. The remaining two bytes store the number of minutes since midnight.

Char and Varchar

These are fixed-length (char) or variable-length (varchar) character data types.

  • char[(n)]   Fixed-length non-Unicode character data with a length of n bytes, where n must be a value from 1 through 8,000. Storage size is n bytes. The SQL-92 synonym for “char” is “character.”

  • varchar[(n) | MAX]   Variable-length non-Unicode character data with a length of n bytes, where n must be a value from 1 through 8,000. Storage size is the actual length in bytes of the data entered, not n bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying. MAX indicates that the maximum storage size is 2^31–1 bytes. The storage size is the actual length of data entered plus two bytes.

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, the default length is 30. Objects using char or varchar are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause. The collation controls the code page used to store the character data.

Sites supporting multiple languages should consider using the Unicode nchar or nvarchar data type to minimize character conversion issues. If you use char or varchar,

  • Use char when the data values in a column are expected to be consistently close to the same size.

  • Use varchar when the data values in a column are expected to vary considerably in size.

  • Use varchar(max) when the data values in a column vary considerably, and the size might exceed 8,000 bytes.

When n is not specified in a data definition or variable declaration statement, the default length is 1. When n is not specified when using the CAST and CONVERT functions, the default length is 30.

In cases where you need to support multiple languages, you should use the Unicode nchar or nvarchar data types to minimize character conversion issues. However, if you use char or varchar and if SET ANSI_PADDING is OFF, when either CREATE TABLE or ALTER TABLE is executed, a char column that is defined as NULL is handled as varchar.

When collation code pages use double-byte characters, the storage size is still n bytes. Also, with character strings, the storage size of n bytes can be less than n characters.

Variable Usage   Variables of these types are used to store non-Unicode characters. The first data type is a fixed char variable that is padded with spaces to the length specified in n. The second data type stores data of variable length and is not padded. You can use either variable for storing strings that do not exceed 8,000 characters. These structures will also truncate the strings if they exceed the number of characters declared in n.

If you use SET ANSI_PADDING OFF in the CREATE TABLE or ALTER TABLE statement, then a char column defined as NULL will be handled as a varchar. If the collation code page uses double-byte characters, the storage size will still be n bytes. Depending on the character string, the actual or final storage size of n bytes can still be less than n characters.

Nchar and Nvarchar

Nchar and nvarchar represent Unicode character data types that are either fixed-length (nchar) or variable-length (nvarchar). They make use of the UNICODE UCS-2 character set.

  • nchar(n)   This is a fixed-length Unicode character data type comprising n characters. The characters in n must be a value in the range 1–4,000. The storage size of the string is two times n bytes. SQL-92 or higher has synonyms for these types; nchar is national char or national character. Best practice suggests using nchar when the data entries in a column are expected to be constantly in the same size range.

  • nvarchar(n | MAX)   This is a variable-length Unicode character data type comprising n characters. The string represented by n must be a value in the range 1–4,000. “MAX” indicates that the maximum storage size is 2^31–1 bytes. The storage size of the string, in bytes, is two times the number of characters entered, and you are not required to actually enter characters. The SQL-2003 synonyms for nvarchar are national char varying and national character varying. Best practice suggests using the nvarchar type when the sizes of the data entries in a column are expected to vary considerably

If you do not specify n in a data definition or a variable declaration statement, then the length defaults to 1. It defaults to 30 when you use n but do not use the CAST function. When you use nchar or nvarchar without specifying a collation in the COLLATE clause, the default database collation is used. Also the SET ANSI_PADDING OFF has no effect on these data types; SET ANSI_PADDING is always ON.

Binary and Varbinary

These are binary data types that can be either fixed-length, which is a binary value, or variable-length, which is represented by the varbinary data type.

  • binary [(n)]   A fixed-length binary data type of n bytes. The value for n must be from 1 through 8,000. The storage size is n+4 bytes. You would use binary when column data size remains constant.

  • varbinary [(n | MAX)]   This is the variable-length binary data type consisting of n bytes. The value for n must be a value from 1 through 8,000; however, the storage size is the actual length of the data entered +4 bytes, not the value for n bytes. MAX indicates that the maximum storage size is 2^31–1 bytes. The varbinary type can cater to a 0-byte length. The SQL-2003 synonym for varbinary is binary varying; you use this data type to hold values that vary in size.

Note 

If you do not specify n in a data definition or variable declaration statement, the default length is 1. When n is not specified with the CAST function, however, the default length of the data type is 30. data type is 30.

Ntext, Text, and Image

These are fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. Unicode data uses the UNICODE UCS-2 character set. These types are being phased out and will be removed from future versions of SQL Server.

The ntext type is variable-length Unicode data with a maximum length of 2^30–1 (1,073,741,823) characters. The storage size, in bytes, is twice the number of characters entered. The SQL-2003 synonym for ntext is national text.

The text type is variable-length non-Unicode data in the code page of the server and with a maximum length of 2^31–1 (2,147,483,647) characters. When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. The storage size may be less than 2,147,483,647 bytes.

The image type is variable-length binary data from 0 through 2^31–1 (2,147,483,647) bytes.

Cursor

The cursor is a data type for variables or stored procedure OUTPUT parameters that contain a reference to a cursor. Any variables created with the cursor data type are nullable. You should also take note that the cursor data type cannot be used for a column in a CREATE TABLE statement.

The operations that can reference variables and parameters having a cursor data type are

  • DECLARE @local_variable and SET @local_variable

  • OPEN, FETCH, CLOSE, and DEALLOCATE

  • Stored procedure output parameters

  • The CURSOR_STATUS() function

  • The sp_cursor_list, sp_describe_cursor, sp_describe_cursor_tables, and sp_describe_cursor_columns system stored procedures

Sql_Variant

The sql_variant is a SQL Server data type that stores the values of all standard SQL Server data types, except the data types containing large objects (LOBs), such as “MAX” text and image types, and the data types timestamp and sql_variant (itself). This type may be used in columns, parameters, and variables, as well as in return values of user-defined functions. The following rules apply to this data type:

  • A column of type sql_variant may contain the values of several different data types. For example, a column defined as sql_variant can hold int, binary, and char values.

  • A sql_variant data type must first be cast to its base data type value before participating in operations such as addition and subtraction. You may also assign it a default value. This data may also hold NULL as its underlying value. NULL values, however, will not have an associated base type, but that will change when you replace the NULL with data.

  • You can use the sql_variant in columns that have been defined as UNIQUE, primary, or foreign keys. However, the total length of the data values composing the key of a given row should not be greater than the maximum length of an index, which is currently 900 bytes.

  • Your table can have as many sql_variant columns as it needs.

  • You cannot use it in the CONTAINSTABLE and FREETEXTTABLE statements.

  • ODBC does not support sql_variant because it has no facility to cater to the notion of a variant data type. You should check out the specifics of the limitations in the SQL Server documentation. For example, queries of sql_variant columns are returned as binary data when using the Microsoft OLE DB Provider for ODBC.

  • Precedence of sql_variant values goes according to the rules of precedence for the base data types they represent. For example, when you compare two values of sql_variant and the base data types are in different data type families (say int and bigint), the value whose data type family is higher in the precedence hierarchy is considered the higher of the two values.

  • The precedence rule discussed previously applies to conversion as well. In other words, when sql_variant values of different base data types are compared, the value of the base data type that is lower in the hierarchy chart is implicitly converted to the other data type before comparison is made.

Table

The table data type (introduced in SQL Server 2005) can be used to store a result set for later processing. Its primary use is for temporary storage of a set. Use DECLARE @local_variable to declare variables of type table. The syntax for the table type is as follows:

 TABLE ({ column_definition | table_constraint} [ ,...n ] ) column_definition: column_name scalar_data_type [ COLLATE collation_definition ] [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ] [ ROWGUIDCOL ] [ column_constraint ] [ ...n ]     { [ NULL | NOT NULL ]     | [ PRIMARY KEY | UNIQUE ]     | CHECK ( logical_expression )     } table_constraint: { { PRIMARY KEY | UNIQUE } ( column_name [ ,...n ] ) | CHECK ( search_condition ) }

The parameters being passed to create a variable of type table make up the same subset of information used to create a persistent table object in CREATE TABLE. The table declaration includes column definitions, names, data types, and constraints. Note that the only constraint types allowed are PRIMARY KEY, UNIQUE KEY, and NULL.

Functions and variables can be declared to be of type table, and the variables can be used in functions, stored procedures, and batches. A table variable also behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.

Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table-expression is used in SELECT, INSERT, UPDATE, and DELETE statements.

Collations also need to be taken into consideration when creating this variable. (see Chapter 4). The type is also not suitable for use with INSERT INTO and SELECT INTO, and you cannot assign one table variable to another. Bear in mind that because the table type is not a persistent table in the database per se, it is unaffected by any transaction rollbacks.

Timestamp

This data type exposes automatically generated binary numbers that are guaranteed to be unique within a database. Timestamp is used typically as a mechanism for version-stamping table rows. The storage footprint is eight bytes.

The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.

SQL Server 2005 introduces a rowversion synonym for the timestamp data type. Use rowversion instead of timestamp wherever possible in DDL statements. This practice will ease the migration to a future release of SQL Server in which rowversion is expected to be introduced as a new data type.

In a CREATE TABLE or ALTER TABLE statement, you do not have to supply a column name for the timestamp data type. For example, the statement

 CREATE TABLE MyTable (PriKey int PRIMARY KEY, timestamp)

is devoid of a column name, so SQL Server will generate a column name of timestamp. The rowversion data type synonym does not follow this behavior. You must supply a column name when you use the rowversion synonym.

Naturally a table can have only one timestamp column. The value in the timestamp column is updated every time a row containing a timestamp column is inserted or updated. This property makes a timestamp column a poor candidate for keys, especially primary keys. Any update made to the row changes the timestamp value, thereby changing the key value. If the column is in a primary key, the old key value is no longer valid, and foreign keys referencing the old value are no longer valid. If the table is referenced in a dynamic cursor, all updates change the positions of the rows in the cursor. If the column is in an index key, all updates to the data row also generate updates of the index.

A nonnullable timestamp column is semantically equivalent to a binary(8) column. A nullable timestamp column is semantically equivalent to a varbinary(8) column.

Uniqueidentifier

This data type represents the globally unique identifier (GUID). A column or local variable of uniqueidentifier data type can be initialized to a value in two ways:

  • Using the NEWID function.

  • Converting from a string constant in the following form (xxxxxxxx-xxxx-xxxx- xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0–9 or a–f). For example, is a valid uniqueidentifier value.

The comparison operators can be used with uniqueidentifier values. However, ordering is not implemented by comparing the bit patterns of the two values. The only operations that are allowed against a uniqueidentifier value are comparisons (=, <>, <, >, <=, >=) and checking for NULL (IS NULL and IS NOT NULL). No other arithmetic operators are allowed. All column constraints and properties except IDENTITY are allowed on the uniqueidentifier data type. (See Chapters 13 and 14 for examples and tips using uniqueidentifier.)

XML

XML is a data type that lets you store XML data in a column, or a variable of xml type. The stored representation of xml data type instances cannot exceed 2 gigabytes (GB) in size. The T-SQL syntax is as follows:

 xml ( [ CONTENT | DOCUMENT ] xml_schema_collection )

The CONTENT variable, the default, restricts the xml instance to be a well-formed XML fragment. The XML data can contain multiple zero or more elements at the top level. Text nodes are also allowed at the top level.

The DOCUMENT variable restricts the XML instance to be a well-formed XML document. The XML data must have one and only one root element. Text nodes are not allowed at the top level. The xml_schema_collection represents the name of an XML schema collection. To create a typed xml column or variable, you can optionally specify the XML schema collection name.

Here is an example:

 DECLARE @y xml (Sales.IndividualSurveySchemaCollection) SET @y = (SELECT TOP 1 Demographics FROM Sales.Individual); SELECT @y;

Collation Precedence

The character string data types, char, varchar, text, nchar, nvarchar, and ntext are also governed by collation precedence rules. These rules determine the following:

  • The collation of the final result, the returned character string expression.

  • The collation used by collation-sensitive operators that use character string arguments but do not return character strings. Operators such as LIKE and IN are examples.

Data Type Synonyms

SQL Server 2005 provides data type synonym support for SQL-92 compatibility. Table 10–10 lists the SQL-92 types and the SQL Server 2005 synonyms.

Table 10–10: Data Type Synonyms

SQL-92

SQL Server 2005

Binary varying

Varbinary

char varying

Varchar

Character

Char

Character

char(1)

Character(n)

char(n)

Character varying(n)

varchar(n)

Dec

decimal

Double precision

float

float[(n)] for n=1–7

real

float[(n)] for n=8–15

float

Integer

int

National character(n)

nchar(n)

National char(n)

nchar(n)

National character varying(n)

nvarchar(n)

National char varying(n)

nvarchar(n)

National text

ntext

Rowversion

timestamp

These data type synonyms can be used in place of the corresponding base data type names in data definition language (DDL) statements, such as CREATE TABLE, CREATE PROCEDURE, or DECLARE @variable. The synonym has no use after the object is created because SQL Server references the base data type and has no notion of the high-level label.

This behavior also applies to metadata operations, such as sp_help and other system stored procedures, the information schema views, or the various data access API metadata operations that report the data types of table or result set columns.

The data type synonyms are expressed only in T-SQL statements. There is no support for them in the graphical administration utilities, such as SQL Server Management Studio. The following code demonstrates the creation of a table specifying national character varying:

 CREATE TABLE CustDetails (PKey int PRIMARY KEY, A_Varcharcolumn national character varying(10)) 

The column A_Varcharcolumn is actually assigned an nvarchar(10) data type. It is referenced in the catalog as an nvarchar(10) column, not according to the synonym supplied on creation of the object. In other words, metadata does not represent it as a national character varying(10) column.




Microsoft SQL Server 2005. The Complete Reference
Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features
ISBN: 0072261528
EAN: 2147483647
Year: 2006
Pages: 239

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net