SQL Building Blocks and Server Settings

This chapter is from the book

Transact-SQL Desk Reference: For Microsoft SQL Server

Learn More Buy

This chapter is from the book

This chapter is from the book 

Transact-SQL Desk Reference: For Microsoft SQL Server

Learn More Buy

2.4 SQL SERVER 2K DATA TYPES

SQL Server supports a wide variety of native data types which may be used for column data type in a CREATE TABLE statement and for local variables and function return types. Virtually all SQL-92 data types are supported as well as a few additional ones as shown in Table 2-15.

Table 2-15. Data Types

SQL Server data types are divided into three major categories: Numeric, String and Other as shown in the following tables.

2.4.1 Numeric Data Type Overview

Table 2-16 broadly describes the numeric data types. The last column gives the page where each is described in detail with examples given.

Table 2-16. Numeric Data Types

2.4.2 String Data Type Overview

Table 2-17 broadly describes the parameters of string, or character, data types. In Sections 2.4.12 through 2.4.16, the string data types listed here are described in detail.

Table 2-17. String Data Types

2.4.3 Other Data Type Overview

Table 2-18 broadly describes the characteristics of several other data types. The last column gives the page where each is described in detail with examples given. We should note that cursor and table data types may not be used as column data types but they may be used for local variables and function return types.

Table 2-18. Other Data Types

2.4.4 Transact-SQL Data Type Precedence

2.4.4.1 Implicit Data Type Conversions

Implicit data type conversions are those conversions that are done by SQL Server when neither the CAST or CONVERT function is specified. Implicit data type conversions use the Data Type Precedence List shown and are done to complete either of the following tasks.

• comparing two expressions of different data types: When comparing two expressions of different data types supported by implicit conversion, the expression of the lower precedence data type is implicitly converted to the data type of the higher precedence, and then the comparison is made. If implicit conversion is not supported, an error is returned. For a table containing all implicit data type conversions and which conversions are supported, see Books Online index: "CAST and CONVERT" and scroll to the table shown under "Remarks."

• evaluating two operand expressions of different types joined by a binary operator: When two expressions of different compatible data types are combined by a binary operator, the expression of lower precedence is implicitly converted to the data type of higher precedence, the operator's operation is performed and the resulting value is of the higher precedence data type. If implicit conversion is not supported, an error is returned.

2.4.5 Transact-SQL Data Type Hierarchy

SQL Server documentation divides similar data types into three major categories.

• Numeric Data Types

• Character and Binary String Data Types

• Other Data Types

It further arranges them in the hierarchy shown in Figure 2-2.

Figure 2-2. Data Type Hierarchy.

The three major categories of data type are described in the following sections.

2.4.5.1 sp_datatype_info

The sp_datatype_info system-stored procedure in Microsoft SQL Server returns information about the data types supported by the current environment. Table 2-19 gives a partial listing of output from sp_datatype_info run on SQL Server 2K. See Books Online for more details.

Table 2-19. sp_datatype_info

EXEC sp_datatype_info

TYPE_NAME            ...    PRECISION          LITERAL_PREFIX            LITERAL_SUFFIX
------------------   ...    ---------------    ------------------------- -------------------------
sql_variant          ...    8000               NULL                      NULL
uniqueidentifier     ...    36                 '                         '
ntext                ...    1073741823         N'                        '
nvarchar             ...    4000               N'                        '
sysname              ...    128                N'                        '
nchar                ...    4000               N'                        '
bit                  ...    1                  NULL                      NULL
tinyint              ...    3                  NULL                      NULL
bigint               ...    19                 NULL                      NULL
image                ...    2147483647         0x                        NULL
varbinary            ...    8000               0x                        NULL
binary               ...    8000               0x                        NULL
timestamp            ...    8                  0x                        NULL
text                 ...    2147483647         '                         '
char                 ...    8000               '                         '
numeric              ...    38                 NULL                      NULL
decimal              ...    38                 NULL                      NULL
money                ...    19                 $                         NULL
smallmoney           ...    10                 $                         NULL
int                  ...    10                 NULL                      NULL
smallint             ...    5                  NULL                      NULL
float                ...    15                 NULL                      NULL
real                 ...    7                  NULL                      NULL
datetime             ...    23                 '                         '
smalldatetime        ...    16                 '                         '
varchar              ...    8000               '                         '             

2.4.6 BIGINT, INT, SMALLINT and TINYINT Details

Table 2-20 provides a recap of the first four data types. Details and examples of these types follow.

Table 2-20. Recapping Data Types

2.4.6.1 Integer Data Type Constants (Literals)

A constant (literal) is a representation of a specific, fixed scalar data value.The format of a constant depends on the data type of the value it represents. Integer literals consist of a sequence of numeric characters not enclosed in quotation marks, containing neither decimal point nor comma and optionally preceded by plus or minus prefix.

Any fractions generated by arithmetic operations on these four integer data types are truncated, not rounded.

2.4.6.2 BIGINT — Special Considerations

The bigint data type is new with SQL Server 2K. It is intended for cases where the integer values may exceed the range supported by the int data type. The int data type remains the primary integer data type in SQL Server.

BIGINT with Expressions

SQL Server will not automatically promote tinyint, smallint or int values to bigint, although it sometimes does automatically promote tinyint or smallint to int.

BIGINT with Functions

Functions will return bigint only if the input parameter expression is a bigint data type.

CREATE TABLE a ( x int)
                  INSERT INTO a VALUES (2000000000)  -- 2 x 109,
                      almost the max int value
                  INSERT INTO a VALUES (2000000000) -- the sum of
                      exceeds int range
                  
                  SELECT SUM(x)  FROM a -- Error, Input parameter is
                      int, sum returns int
                  

Server: Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type int.

SELECT CAST(SUM(x) AS bigint) FROM a  -- Error happens
                  -- before the cast
                  

Server: Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type int.

SELECT SUM( CAST(x AS bigint) ) FROM a  -- Correct
                      result
                  

--------------------
4000000000                

Unless explicitly stated in the documentation, functions and system-stored procedures with int expressions for their parameters have not been changed to support implicit conversion of bigint expressions to those parameters. For this reason, SQL Server implicitly converts bigint to int only when the bigint value is within the range supported by the int data type. A conversion error occurs at run time if the bigint expression contains a value outside the range supported by the int data type.

When you provide bigint as input parameters and the return values are of type bigint, you may use the Transact-SQL functions shown in Table 2-21. See details under Functions, page 137.

Table 2-21. Functions

According to Books Online, certain aggregate functions will not return a bigint unless the input parameter expression is of type bigint.

CREATE TABLE b ( y bigint)
                  INSERT INTO b VALUES (2000000000) -- 2 x 109,
                      almost the max int value
                  INSERT INTO b VALUES (2000000000)
                  
                  SELECT AVG(y) FROM b  -- Input parameter y is
                      bigint, returns in int range
                  

SELECT SUM(y) FROM b  -- Input parameter y is bigint,
                  sum returns bigint
                  

Recall previous example, x is int so it must be CAST to bigint as input parameter to SUM so that result will be bigint to accommodate the large result value.

SELECT SUM( CAST(x AS bigint) ) FROM a -- Correct
                      result
                  

The functions shown in Table 2-22 may be used to reference bigint columns or variables though they do not return bigint values. See details under Functions, page 137.

Table 2-22. bigint References

SQL Server provides two functions just for bigint values, COUNT_BIG and ROWCOUNT_BIG.

COUNT_BIG Function

This function is used when counting the number of items in a very large group if the value might exceed the range supported by the int data type. It returns a bigint type.

SELECT COUNT_BIG( a_column )  FROM  verybigtable
                  -- Works like count() but returns bigint
                  -- The data type of column doesn't matter,
                  the number of rows does
                  

--------------------
4000000000

ROWCOUNT_BIG Function

Use this function when counting the number of rows affected in the last statement executed and when the value exceeds the range supported by the int data type. This function is similar to the @@ROWCOUNT function, except that ROWCOUNT_BIG() returns a bigint data type.

SELECT ROWCOUNT_BIG()  -- Like @@ROWCOUNT but
                      returns bigint.
                  Needed if table is HUGE.
                  

--------------------
4000000000

BIGINT with Stored Procedures and Other Transact-SQL Elements

SQL Server will not automatically promote tinyint, smallint or int values to bigint, although sometimes it automatically promotes tinyint or smallint to int.

CAST and CONVERT support bigint, applying similar conversion rules for bigint as for the other integer data types. The bigint data type fits above int and below smallmoney in the data type precedence chart. For more information about bigint conversions, see CAST and CONVERT page 166.

When using the CASE expression, you will get a result of type bigint if either the result_expression or the else_result_expression if present evaluates to bigint. See CASE page 164.

You may use bigint for exact numeric data type in these Transact-SQL statements.

• CREATE TABLE

  ALTER TABLE

  Example:

  CREATE TABLE c ( x int, y bigint)
  INSERT INTO c  VALUES ( 20 , 3000000000 )
  

• CREATE PROC[EDURE]

  ALTER PROC[EDURE]

  Example:

  CREATE PROC pr ( @parm bigint ) AS print @parm + 4
  EXEC pr 3000000000
  

• DECLARE variable

  Example:

  DECLARE  @var1   bigint
  SET  @var1 = 3000000000 ;   print @var1
  

2.4.6.3 Specifying BIGINT Constants

Whole number constants outside the range supported by the int data type continue to be interpreted as numeric, with a scale of 0 and a precision sufficient for the value specified.

INSERT INTO c  VALUES ( 20 , 3000000000 )

EXEC pr 3000000000

To explicitly create a bigint constant use the CAST function,

CAST( 3000000000 AS BIGINT).

print CAST( 3000000000 AS BIGINT)
                  

3000000000

2.4.7 BIT Data Type Details

The bit data type is described in Table 2-23.

Table 2-23. BIT Data Type

2.4.7.1 BIT Data Type Constants (Literals)

The numbers zero and one represent bit constants.. If a number larger than one is used, it is converted to one. (Empirically, any number other than 0 is stored as 1.)

No quote marks are used.

Example bit constants:

CREATE TABLE d ( x BIT )
                  
                  INSERT INTO d VALUES (0);
                  INSERT INTO d VALUES (1);
                  INSERT INTO d VALUES (12);
                  
                  SELECT * FROM d;
                  

x
----
0
1
1

2.4.8 DECIMAL and NUMERIC Data Type Details

Table 2-24 offers an overview of the DECIMAL and NUMERIC data types.

Table 2-24. DECIMAL and NUMERIC Data Type

DECIMAL and NUMERIC are exact numeric data types with fixed precision (p) and scale (s) containing numbers from (–10³⁸ + 1) through (+10³⁸ – 1). That is, they store an exact representation of any number up to 38 digits which may all be to the left or right of the decimal point (or some on the left and the rest on the right). FLOAT and REAL may store larger and smaller numbers, but they are approximate. The number for p (precision) 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 which is 38. If not specified p defaults to 18. In SQL Server 7.0 the maximum value for p is 28 unless the server is started with the /p option, sqlserver /p, in which case the maximum precision is 38.

The number for s (scale) 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, so, 0 <= s <= p. The default scale is 0.

Legal declarations are as follows:

• DECIMAL(p,s) where 0 <= p <= 38 and 0 <= s <= p.

• DECIMAL(p) where 0 <= p <= 38 and s defaults to 0.

• DECIMAL where p defaults to 18 and s defaults to 0.

Note: DECIMAL may be replaced by either DEC or NUMERIC with equivalent results.

For DECIMAL(p,s) and NUMERIC(p,s), the integer part may not exceed (p – s) digits. The result will be an error. If more than s digits are specified for the fractional part the value stored is rounded to s digits. Table 2-25 below shows how the storage sizes vary based on the precision.

Table 2-25. Varying Storage Size

2.4.8.1 Numeric Data Type Constants (Literals)

Table 2-26 summarizes the constant (literal) format for the numeric and decimal data types. Examples of these types follow.

Table 2-26. Numeric Data Type Constants

Constants for numeric data types are represented by a sequence of numeric digits optionally preceded by a plus symbol or a minus symbol and are optionally followed by a decimal point and another sequence of numeric digits. No quote marks are used.

CREATE TABLE e ( x DEC(5,2) , y DEC(5) , z DEC)
                  EXEC SP_HELP e
                  

Column_name    Type           Length        Prec          Scale
x              decimal        . . .  5          5             2
y              decimal        . . .  5          5             0
z              decimal        . . .  9          18            0

Length represents the number of bytes of storage as given in Table 2-26.

This sp_help output shows that is stored as

INSERT INTO e (x) VALUES (123.45)    -- Okay

INSERT INTO e (x) VALUES(1234.00)-- Error - overflow
                  

Any number of digits may be entered to the right of the decimal point. If more than s digits are entered, the number is rounded to have exactly s digits to the right of the decimal.

INSERT INTO e (x) VALUES (-999)      -- Okay
                  INSERT INTO e (x) VALUES (12.89)     -- Okay
                  INSERT INTO e (x) VALUES (123.899)   -- Okay
                  INSERT INTO e (x) VALUES (-123.899)  -- Okay
                  
                  SELECT  x  FROM e
                  

x
-------
123.45
–999.00
12.89
123.90
–123.90

2.4.9 MONEY and SMALLMONEY Data Type Details

The data types money and smallmoney are distinguished by their range, as shown in Table 2-27.

Table 2-27. MONEY and SMALLMONEY Data Type

The monetary data types, money and smallmoney, can store positive or negative amounts of money. However, if more than four decimal places are required, use the decimal data type instead.

The data type is versatile enough to accept many national currency symbols including the British pound, the Euro, the Yen and many others. See details in Books Online, search for "Using Monetary Data."

2.4.9.1 Monetary Data Type Constants (Literals)

The acceptable constant (literal) format for monetary data types is summarized in Table 2-28.

Table 2-28. Monetary Data Type

CREATE TABLE f ( m MONEY, s SMALLMONEY )
                  
                  INSERT INTO f VALUES ( 12, 12);          -- Okay
                  INSERT INTO f VALUES ( $12345678, 12);   -- Okay
                  INSERT INTO f VALUES ( 12, $12345678 );  -- Error
                      --  s too large
                  INSERT INTO f VALUES ( $12,345,678, 12); -- Error
                      --  no commas
                  INSERT INTO f VALUES
                  ( CAST('$12,345,678' AS MONEY), 12);   -- Okay
                  
                  SELECT * FROM f;
                  

m                     s
-------------------   ------------
12.0000               12.0000
12345678.0000         12.0000
12345678.0000         12.0000

2.4.10 FLOAT and REAL Data Type Details

The data types FLOAT and REAL allow numerical approximations. They are summarized in Table 2-29.

DECIMAL and NUMERIC contain an exact representation of any number up to 38 digits. MONEY and SMALLMONEY also store an exact representation of numbers with a smaller range than DECIMAL and NUMERIC. However, FLOAT and REAL contain an approximate representation with a range of much larger and smaller fractional numbers and take less storage space than the other data types for the same number.

Table 2-29. FLOAT and REAL Data Type

2.4.10.1 FLOAT and REAL Data Type Constants (Literals)

Constants for approximate numeric data types are represented by a sequence of numeric digits with an optional decimal point and optionally preceded by a plus symbol or a minus symbol and optionally suffixed with the letter E and an integer representing an exponent of 10. No quote marks are used. See Table 2-30.

Table 2-30. FLOAT and REAL Data Types

CREATE TABLE g ( r REAL , f FLOAT )
                  
                  INSERT INTO g VALUES ( 123, 1.23E2 );        -- Okay
                  INSERT INTO g VALUES ( .0123 , 1.23E-2 );    -- Okay
                  INSERT INTO g VALUES ( .00123 , 1.23E-3 );   -- Okay
                  
                  SELECT * FROM g;
                  

r                          f
------------------------   -----------------------------------------------------
123.0                      123.0
0.0123                     0.0123
1.2300001E-3               0.00123

Because the stored values of FLOAT and REAL, all other values are approximate. WHERE clause comparisons with FLOAT and REAL columns should use > , < , >= and <= relational operators and should avoid = and <> operators.

SELECT * FROM g -- For each row the same value was
                      entered for r and f
                  WHERE  r = f; -- But "=" only finds the first row
                      as the same
                  

r                          f
------------------------   -----------------------------------------------------
123.0                      123.0                

SELECT * FROM g
                  WHERE NOT ( r < f  AND r > f ); -- This finds all
                      three rows
                  

r                        f
------------------------ -----------------------------------------------------
123.0                    123.0
0.0123                   0.0123
1.2300001E-3             0.00123

2.4.11 DATETIME and SMALLDATETIME Data Type Details

Microsoft SQL Server 2000 has only datetime and smalldatetime data types (Table 2-31) to store both date and time data.

Table 2-31. DATETIME and SMALLDATETIME Data Type

There is no separate data type for just date or just time. A date with no time changes to the default time of midnight (12:00 AM). A time with no date changes to the default date of Jan 1, 1900.

Guidelines for searching for dates and/or times are as follows:

• To search for an exact match on both date and time, use an equal sign (=).

• The relational operators <, <=, >, >=, <> may be used to search for dates and times before or after a specified value.

• To search for a partial date or time value (year, day, hour), use the LIKE operator.

  • WHERE hiredate LIKE 'Jan 200%'

  However, because the standard display formats do not include seconds or milliseconds, you cannot search for them with LIKE and a matching pattern, unless you use the CONVERT function with the style parameter set to 9 or 109. (See details under Functions, page 137.) For more information about searching for partial dates or times, see LIKE on page 64.

• To search for an exact match on both date and time, use an equal sign (=).

• SQL Server evaluates datetime constants at run time.

• A date string that works for the date formats expected by one language may not work if the query is executed by a connection using a different language and date format setting. For more information see Books Online. Search for "Writing International Transact-SQL Statements."

2.4.11.1 DATETIME Data Type Constants (Literals) Formats—for INPUT

SQL Server recognizes date and time literals enclosed in single quotation marks (') in the formats shown in Tables 2-32 and 2-33 for DATE and TIME data. A DATETIME value may be specified as either DATE [TIME] or [DATE] TIME where DATE and TIME follow the formats below.

Table 2-32. DATE Formats

DATETIME values may be subtracted to give the number of days between, but test it carefully to make sure it gives what you want.

Example

SELECT CAST (
               CAST( '1/3/2003' AS DATETIME ) - CAST( '1/1
                  /2003'AS DATETIME )
               AS INT )
               

-------

Table 2-33. TIME Formats

Table 2-34 shows several examples of DATETIME Constants.

Table 2-34. DATETIME Constants

SET DATEFORMAT

SET DATEFORMAT sets the order of the dateparts (month/day/year) for entering datetime or smalldatetime data values as a string of 3 numbers separated by slash, /. See the example below which changes the order from the default mdy to the European format of dmy.

Syntax

SET DATEFORMAT {mdy | dmy | ymd | ydm | myd | dym }
   

See Server, Database and Session Settings, page 175.

Remarks

In the U.S. English, the default is mdy. Remember that this setting is used for input only, that is, only in the interpretation of character strings as they are converted to date values. It has no effect on the display of date values. All users may use SET DATEFORMAT without special permission.

DECLARE   @v_date   DATETIME
                  SET @v_date = '04/22/03'
                  PRINT @v_date
                  

SET DATEFORMAT dmy
    DECLARE   @v_date   DATETIME
    SET @v_date = '22/04/03'
                  
    PRINT @v_date
                  
    SET DATEFORMAT mdy --  Set input date format back
     to the default
                  

Apr 22 2003 12:00AM

DECLARE   @v_date   DATETIME
                  SET @v_date = '04/22/03'
                  
                  SELECT @v_date
                  

------------------------------------------------------
2003-04-22 00:00:00.000

2.4.11.2 Formatting DATETIME Data Type Constants (Literals) for OUTPUT

The default display format for DATETIME in SQL Server is arguably ugly: 2001-04-22 17:44:20.999. Table 2-35 gives some alternatives to obtain a more presentable output for a datetime column, variable or function.

Perhaps the easiest are CONVERT( VARCHAR[(19)] , datetimevariable ) and CAST( datetimevariable AS VARCHAR[(19)] ).

Table 2-35. Formatting DATETIME

SELECT CONVERT( VARCHAR(19), GETDATE() )
                  

-------------------------
Apr 22 2001  5:44PM

2.4.11.3 Avoiding the Problems of Y2K

The primary way to avoid problems such as that caused by Y2K is to always enter datetime values with four digits for the year. This should be standard procedure by now. Nonetheless, SQL Server does provide a two-digit year cutoff option which tells how two-digit years should be interpreted. A two-digit year that is less than or equal to the last two digits of the cutoff year is in the same century as the cutoff year. A two-digit year that is greater than the last two digits of the cutoff year is in the century that precedes the cutoff year.

The default two-digit year cutoff for SQL Server is 2049. That means that a two-digit year of 40 is interpreted as 2040.A two-digit year of 60 is interpreted as 1960.

See Books Online and search for "cutoff" to learn how to change the two-digit year cutoff value for the entire server.

2.4.12 CHAR and VARCHAR Data Type Details

An overview of char and varchar is contained in Table 2-36.

Table 2-36. CHAR and VARCHAR Data Type

Character string data types include CHAR, VARCHAR, TEXT, NCHAR, NVARCHAR, NTEXT and SYSNAME which is NVARCHAR(128).

String data types are for storing sequences of zero or more characters. Essentially any character on the keyboard may be stored in a string data type, including the following characters.

• Upper and lower case alphabetic characters such as a, b, c, ..., z, A, B, C, ..., Z

• Numeric characters such as 0, 1, ..., 9

• Punctuation and special characters such as ., ,, ;, [, ], @, #, &, !, ....

2.4.12.1 CHAR and VARCHAR Data Type Constants (Literals)

Character string constants consist of a sequence of characters enclosed in single quotes in accordance with ANSI SQL standard. Essentially any alphabetic, numeric or punctuation character on the keyboard may be stored in a string data type.

A literal single quote (apostrophe) in a string is represented by two consecutive single quote characters, as in 'O''Reilly' for O'Reilly.

Example string constants:

'Mary Smith'

'O''Reilly'

2.4.12.2 Storage of CHAR and VARCHAR Data Types

CHAR(n) and VARCHAR(n) are data types for storing string data in one byte per character, non-Unicode. The main difference between the two is as follows.

• CHAR(n) always stores n bytes to contain a string up to n characters long, filling in by right padding with blanks if the data is less than n characters.

• VARCHAR(n) stores one byte for each of the actual number of characters up to n.

• Both CHAR(n) and VARCHAR(n) truncate data longer than n bytes to exactly n bytes for a local variable but return an error on INSERT or UPDATE into a table column. See "Truncation Examples" below.

Storage Size Examples

'Sue' inserted into CHAR(6) will be stored as "Sue " with three blanks on the right. 'Sue' inserted into VARCHAR(6) will be stored as "Sue" plus number 3 for the length.

An explicit trailing space inserted into a VARCHAR will be retained, space permitting.

'Sue ' will correctly insert all four characters into VARCHAR(6) as "Sue". 'Sue ' will store six characters as usual in CHAR(6) as 'Sue ".

An empty string of zero characters may be stored by entering '' (<single quote><single quote>). Such an empty string is very different from NULL, which is "no domain value entered" (see NULL, page 39).

Truncation Examples for a Local Variable

'Sammy' inserted into CHAR(3) or VARCHAR(3) variable will be truncated to "Sam."

DECLARE  @name   VARCHAR(3)         -- or CHAR(3)
                  
                  SET @name  =   'Sammy'
                  
                  PRINT   @name
                  

Sam

Truncation Examples for a Table Column

If the string 'Sammy' is inserted into CHAR(3) or VARCHAR(3) column it returns an error and fails.

CREATE  TABLE   t ( name  VARCHAR(3) )-- or CHAR(3)
                  
                  INSERT INTO   t   VALUES  ( 'Sammy'  )
                  go
                  

Server: Msg 8152, Level 16, State 9, Line 1

String or binary data would be truncated.

The statement has been terminated.

SELECT  *   FROM  t
                  

name
           ----
           (0 row(s) affected) 

Concatenation Operator

The concatenation operator is the plus sign (+). It may be used with string concatenation types.

Example

PRINT 'Mary' + ' ' + 'Smith'
      --  The middle term is <single
       quote><space><single quote>
               

Relational Operators

These relational operators, shown in Table 2-37 may be used with string values as well as numeric or date values. They behave the same as if one is alphabetizing a list, "Al" is before "Alan."

Table 2-37. Relational Operators

Strings are compared according to the collation, and they generally behave according to dictionary order. So 'Mary Smith' > 'Ma' is TRUE and 'Mary Smith' < 'N' is TRUE.

2.4.12.3 Comparing CHAR() and VARCHAR() Values

If a value of CHAR() data type value is compared to a VARCHAR() value, then SQL Server trims trailing blanks, if any, from the CHAR() value to allow them to be compared as equal values if the leading strings of both are the same.

Note that Oracle will not compare the two as equal, so this behavior is RDBMS dependent.

This behavior is true whether the values are local variables or table column values. The following example demonstrates it using local variables.

DECLARE @charname CHAR(5) '@vchrname VARCHAR(5)
                  SET @charname = 'abc'    -- Declared as CHAR(5) so
                      it   will store 5 characters 'abc'
                  SET @vchrname = 'abc'    -- Declared as VARCHAR(5)
                      so it   will store 3 characters 'abc'
                  
                  SELECT 'Char-HELLO:   ' + @charname  + 'HELLO'
                  SELECT 'Varchar- HELLO:   ' + @vchrname  + 'HELLO'
                  
                  IF @charname = @vchrname
                  PRINT 'Same'
                  ELSE
                  PRINT 'Different' >
                  

--------------------------
Char-HELLO:   abc  HELLO

----------------------------
Varchar- HELLO:   abcHELLO

Same

This last behavior of comparing the two showing equality is caused by implicit data type conversion from CHAR() to VARCHAR() before doing the comparison. See the discussion of implicit data type conversion and precedence on pages 73 and 74.

2.4.12.4 String Functions

The scalar functions, shown in Table 2-38, perform an operation on a string input value and return a string or numeric value. See details under Functions, page 137.

Table 2-38. Scalar Functions

ANSI_PADDING ON

I suggest heeding the following good advice from Books Online—Warning: It is recommended that ANSI_PADDING always be set to ON.

• VARCHAR(n) — not trimmed, not padded

  - data inserted or updated retain and store trailing blanks provided (not trimmed).

  - only the characters provided are stored (not padded to the length of the column).

  - The four character string 'Sue ' inserted or updated into a VARCHAR(6) will retain the trailing blank explicitly inserted.

• CHAR(n) — padded (never trimmed)

ANSI_PADDING OFF

• VARCHAR(n) — trimmed, not padded.

  - The four-character string 'Sue ' inserted (or updated) into a VARCHAR(6) would be truncated storing only three characters, 'Sue'.

• CHAR(n) — NULLable not padded, NOT NULLable always padded, (neither ever trimmed)

  - CREATE TABLE t ( a CHAR(3) , b VARCHAR(3) ):a and b behave the same.

You can display the current setting of the ANSI_PADDING option as follows.

SELECT SESSIONPROPERTY ( 'ANSI_PADDING' ) 
                   ANSI_PADDING
               

The OLE DB and ODBC libraries always set ANSI_PADDING option to ON when making a connection. For more on OLE DB and ODBC automatic settings, see page 215.

Collation A COLLATE clause (new in SQL Server 2K) may be applied to a string expression, column definition, or database definition to determine comparison (search) and sorting characteristics. See Collation on page 233. Objects using CHAR or VARCHAR are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause. (The same is true for NCHAR or NVARCHAR.)

2.4.13 NCHAR and NVARCHAR Data Type Details

The data types of nchar and nvarchar can be summarized as shown in Table 2-39.

Table 2-39. NCHAR and NVARCHAR Data Types

2.4.13.1 UNICODE

UNICODE Data Types use two bytes per character to enable handling of international character sets. UNICODE Data Types were introduced with SQL Server 7.0.

Single-byte ASCII is able to accommodate European languages (Latin1) including English and German (with umlauts), but UNICODE is required for languages such as those of Asian and Middle Eastern countries. See Books Online: "Collations, overview."

UNICODE string literals have N prefixed to the string enclosed in single quotes.

Example UNICODE Literal

N'This specifies a UNICODE string literal'

The server will still have a default character set determined during installation. But you may now specify a column of a table to be of NCHAR, NVARCHAR or NTEXT data type able to contain any character set defined by the Unicode Standard (that is, any of the National Character data types in the ANSI SQL standard).

NCHAR(n) behaves similarly to CHAR(n) in that (n) characters are always stored, but the storage size for CHAR is one byte per character whereas NCHAR is two bytes per character. See the discussion of CHAR above.

NVARCHAR(n) behaves similarly to VARCHAR(n) in that only the number of characters in the string up to a maximum of (n) characters are stored. See the discussion of VARCHAR in section 2.4.12 above.

UNICODE Data Type FUNCTIONS

Two of the string functions listed in the previous section specifically support Unicode data types.

NCHAR ( integer_expression ) returns the Unicode character with the given integer code.

UNICODE ( 'ncharacter_expression' ) returns the integer value for the first character of the input expression.

PRINT NCHAR(252)
                  

ü

PRINT UNICODE( 'ü' )
                  

252

Books Online provides other examples of the use of NCHAR, NVARCHAR and the functions NCHAR() and UNICODE().

2.4.13.2 NCHAR and NVARCHAR Data Type Constants (Literals)

Unicode character string constants consist of a capital N followed by a sequence of characters enclosed in single quotes in accordance with ANSI SQL standard. The capital N stands for the National Language support in the SQL-92 standard. The N prefix must be uppercase.

Example Unicode (NCHAR and NVARCHAR) String Constants

N'Mary Smith'

N'O''Reilly'

2.4.13.3 Storage of NCHAR and NVARCHAR Data Types

NCHAR(n) always stores n characters, right padding with blanks if the data is less than n characters long. Storage space for these n characters is 2n bytes.

Example

If a column declared as NCHAR(5) has 'abc' inserted, then 'abc ' is stored, five characters occupying ten bytes.

NVARCHAR(n) stores the actual number of characters up to n, using two bytes per character.

Example

If a column declared as NVARCHAR(5) has 'abc' inserted, then those three characters are stored, occupying six bytes (plus the number 3 indicating the number of characters).

Collation

A COLLATE clause (new in SQL Server 2K) may be applied to a string expression, column definition or database definition to determine comparison (search) and sorting characteristics. See Collation on page 233.

Objects using NCHAR or NVARCHAR are assigned the default collation of the database, unless a specific collation is assigned using the COLLATE clause. The same is true for CHAR or VARCHAR.

2.4.13.4 Default Length for CHAR, VARCHAR, NCHAR, NVARCHAR with DDL

The default length of CHAR, VARCHAR, NCHAR and NVARCHAR with DDL is 1. This means that a DDL declaration of CHAR defaults to CHAR(1).

CREATE TABLE  t  (
                  a  CHAR                -- Defaults to  CHAR(1)
                  )
                  
                  INSERT INTO  t  ( a ) VALUES ( 'A' ) -- Succeeds
                  INSERT INTO  t  ( a ) VALUES ( 'AAA' ) --  Fails. 
                       A column
                  input too long fails
                  SELECT * FROM  t
                  CREATE TABLE  t  (
                  a  CHAR                -- Defaults to  CHAR(1)
                  )
                  
                  INSERT INTO  t  ( a ) VALUES ( 'A' ) -- Succeeds
                  INSERT INTO  t  ( a ) VALUES ( 'AAA' ) --  Fails,
                      column
                  input
                      too long
                  SELECT * FROM  t
                  

DECLARE  @variable CHAR -- Defaults to  CHAR(1)
                  
                  SET  @variable = 'V'    -- Succeeds
                  PRINT @variable
                  
                  SET  @variable = 'WWW'  -- Succeeds. A string
                   variable input
                  that is too long is
                      truncated.
                  PRINT @variable
                  

2.4.13.5 Default Length for CHAR, VARCHAR, NCHAR, NVARCHAR with CAST

The Default length of CHAR, VARCHAR, NCHAR and NVARCHAR with CAST is 30. This means that CAST ( object AS CHAR ) is the same as CAST (object AS CHAR(30) )

PRINT CAST(  GETDATE()   AS  CHAR ) -- Defaults to
                      CHAR(30)
                  

PRINT  CAST(  GETDATE()   AS  CHAR(30) )
                  

2.4.14 SYSNAME Data Type Details

SYSNAME is a system-supplied user-defined data type as NVARCHAR(128). SYSNAME is used to reference database object names.

2.4.15 TEXT, NTEXT and IMAGE Data Type

TEXT, NTEXT and IMAGE data types, as summarized in Table 2-40, are fixed and variable-length data types for storing large non-Unicode and Unicode character and binary data. They are generally for large data values up to 2 GB in size, which are more efficiently stored on their own pages than on the same page as the other columns of the row. They serve the function of BLOBs (Binary Large OBjects) in some systems.

Restrictions on their use include the inability to refer to them directly in a WHERE clause. But they may be used in WHERE clauses as input parameter to functions such as ISNULL(), SUBSTRING(), PATINDEX() as well as in IS NULL, IS NOT NULL and LIKE expressions. They may not be used as variables, although they may be parameters to stored procedures.

Table 2-40. TEXT, NTEXT and IMAGE Data Types

2.4.15.1 Text and Image Functions and Statements

Text and image functions, summarized in Table 2-41, perform an operation on a text or image value or column and return information about the value. All are nondeterministic (see page 162).

Table 2-41. Text and Image Functions and Statements

CREATE TABLE t (  id    INT , txtcol TEXT )
                  INSERT INTO t ( id , txtcol ) VALUES ( 1 , 'txtcol
                      initial data' )
                  SELECT  *  FROM  t
                  

DECLARE   @b_ptr   VARBINARY(16)
                  
                  SELECT  @b_ptr = TEXTPTR( txtcol ) FROM  t WHERE 
                      id = 1
                  
                  WRITETEXT t.txtcol @b_ptr  'This represents a very
                      long text message.
                  
                  
                  SELECT  *  FROM  t
                  

Arguments

Text in Row

If your data is 7000 bytes or less, you may set the "Text in Row" feature that lets you store the large object on the same page as the other columns. This feature is enabled for an entire table with sp_tableoption.

The next statement turns the feature on and sets an upper limit on data size to the default maximum size of 256 bytes. Objects larger than the maximum value are stored on separate pages, not in rows.

sp_tableoption  tablename , 'text in row', 'ON'
   -- ON must be enclosed in single quotes.
   

The next statement both turns the feature on and sets an upper limit on data size. The value specified must be between 24 and 7000.

sp_tableoption  tablename , 'text in row', 2000
   

2.4.16 BINARY(n) and VARBINARY(n) Data Type Details

BINARY and VARBINARY data types, summarized in Table 2-42, store strings of bits up to a maximum of 8000 bytes. From Books Online:

Use binary data when storing hexadecimal values such as a security identification number (SID), a GUID (using the uniqueidentifier data type), or using a complex number that can be stored using hexadecimal shorthand.

Table 2-42. BINARY(n) and VARBINARY (n) Data Type

2.4.16.1 BINARY and VARBINARY Data Type Constants (Literals)

Binary constants have a leading 0x (a zero and the lowercase letter x) followed by the hexadecimal representation of the bit pattern, each hex digit representing four bits.

So 0x3A (or 0x3a) represents the hexadecimal value of 3A or both four-bit "nibbles".

Therefore 0x3A represents the eight-bit Byte 00111010 which is equal to decimal 58.

PRINT CAST( 0x3A AS INT)
                  

PRINT CAST( 58 AS BINARY(2) )
                  

PRINT CAST( 58 AS BINARY ) -- Default length n
                      with CAST is 30
                  

2.4.17 CURSOR Data Type Details

As summarized in Table 2-43, a cursor data type contains a pointer to a cursor object, which is required for writing code that accesses a result set including more than one row of data. The cursor object in a sense contains the entire result set but only allows access to one result set row at a time.

Table 2-43. CURSOR Data Type

CURSOR data type is for variables or stored procedure OUTPUT parameters that contain a reference to a cursor. Any variables created with the cursor data type are nullable.

Operations that can reference variables and parameters having a cursor data type are as follows. Remember that the cursor data type cannot be used for a column in a CREATE TABLE statement.

• DECLARE @local_variable and SET @local_variable statements.

• OPEN, FETCH, CLOSE, and DEALLOCATE cursor statements as well as UPDATE and DELETE.

• 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.

For more in-depth treatment of cursors, see Cursors, page 638.

For a thorough coverage of using cursors see Professional SQL Server 2000 Programming by Robert Vieira and Advanced Transact-SQL for SQL Server 2000 by Itzik Ben-Gan and Tom Moreau.

2.4.18 SQL_VARIANT Data Type Details

SQL_VARIANT data type can be assigned to a column or variable into which you can put data with different base data types. (See Table 2-44.) Each SQL_VARIANT object stores both the data value and the data type (metadata) for the SQL_VARIANT value assigned, so extra space is required for using SQL_VARIANT. ODBC does not fully support SQL_VARIANT. See Books Online under SQL_VARIANT.

Table 2-44. SQL_VARIANT Data Type

2.4.18.1 Using SQL_VARIANT

General Value Assignment

• SQL_VARIANT objects can hold data of any SQL Server data type except TEXT, NTEXT, IMAGE, TIMESTAMP and SQL_VARIANT.

• Predicates or assignments referencing SQL_VARIANT columns can include constants of any data type.

• An SQL_VARIANT object assigned a value of NULL does not have an underlying base data type.

• When assigning the value of an SQL_VARIANT object to a non-SQL_VARIANT data object, the SQL_VARIANT value must be explicitly cast to the data type of the destination.

  SET @intvar = CAST( @variantvar AS INT )
  

  There are no implicit conversions from SQL_VARIANT to non-SQL_VARIANT.

• When doing arithmetic operations with an SQL_VARIANT object, the SQL_VARIANT value must be explicitly cast to the appropriate numeric data type.

• Catalog objects such as the DATALENGTH function that report the length of SQL_VARIANT objects report the length of the data only (not including the length of the meta data contained in a SQL_VARIANT object).

• SQL_VARIANT columns always operate with ANSI_PADDING ON. If CHAR, NCHAR, VARCHAR, NVARCHAR or VARBINARY values are assigned from a source that has ANSI_PADDING OFF, the values are not padded.

SQL_VARIANT in Tables

• SQL_VARIANT columns can be used in indexes and with unique keys, as long as the length of the data in the key columns does not exceed 900 bytes.

• SQL_VARIANT columns do not support the IDENTITY property, but SQL_VARIANT columns are allowed as part of primary or foreign keys.

• SQL_VARIANT columns cannot be used in a computed column.

• Use ALTER TABLE to change a column to SQL_VARIANT. All existing values (of the prior data type) are converted to SQL_VARIANT values.

• ALTER TABLE cannot be used to change the data type of an SQL_VARIANT column to any other data type.

Collation

• The COLLATE clause cannot be used to assign a column collation to an SQL_VARIANT column.

• When a value is assigned to an SQL_VARIANT instance, both the data value and base data type of the source are assigned. If the source value has a collation, the collation is also assigned. If the source value has a user-defined data type, the base data type of the user-defined data type is assigned (not the user-defined data type itself).

The new function SQL_VARIANT_PROPERTY(): is used to obtain property information about SQL_VARIANT values, such as data type, precision or scale.

The following example shows how to use and assign SQL_VARIANT.

DECLARE @intvar INT, @chvar CHAR(4)
        DECLARE @vrntvar SQL_VARIANT
                  
        SET @chvar = '123'
        SET @intvar = @chvar  -- implicit conversion okay
        PRINT @intvar
                  
        SET @vrntvar = @chvar
        PRINT CAST( @vrntvar AS CHAR )-- must cast SQL_VARIANT
                  
        -- SET @intvar = @vrntvar -- Fails, no implicit
         conversion
                  
                  SET @intvar = CAST( @vrntvar AS INT ) -- explicit
                  
                   conversion- ok
                  PRINT @intvar
                  

2.4.18.2 Comparisons with SQL_VARIANT Objects

Since an SQL_VARIANT object may contain any of various data types, it is always best to explicitly cast the SQL_VARIANT object to the data type you wish to compare against when doing your own comparisons, as in a WHERE search of in an IF statement.

For situations in which you can not cast each item, such as those on the following list, special comparison rules will apply.

• Using ORDER BY, GROUP BY

• Creating Indexes

• Using MAX and MIN aggregate functions

• Using UNION (without ALL)

• Evaluating CASE expressions and using comparison operators

For SQL_VARIANT comparisons, the SQL Server data type hierarchy order is grouped into data type families from highest (top of Table 2-45) to lowest.

Table 2-45. Data Type Families

For comparing SQL_VARIANT objects, the data type hierarchy shown in Table 2-45 is used.When comparing two SQL_VARIANT objects of different data type families, the object with the family higher in the table is deemed greater (regardless of data value). When comparing two SQL_VARIANT objects of the same data type family, both objects are implicitly converted to the higher data type and are compared based on value.

When comparing two SQL_VARIANT objects with data type of CHAR, VARCHAR, NCHAR or NVARCHAR, the comparison is based on integer comparison of the following four values in this order: LCID (locale ID), LCID version, comparison flags and sort ID.

LCID has to do with regional language settings and both LCID and LCID versions will usually be the same for all string objects in a given database. In these cases the comparison seems to behave the same as for non-SQL_VARIANT comparisons of the underlying data type values. If you can, however, it is always safer to explicitly cast and do testing of sample cases.

Examples Comparing SQL_VARIANT Objects

Since number families are a higher family than string families, the INT value 222 would evaluate as greater than CHAR value 444.

DECLARE @intvariant SQL_VARIANT,@charvariant
                            SQL_VARIANT
                        SET @intvariant = 222
                        SET @charvariant = '444'
                        
                        if @intvariant > @charvariant
                        PRINT '@intvariant is greater'
                        else
                        PRINT '@charvariant is greater'
                        

@intvariant is greater

DECLARE @intvariant SQL_VARIANT,@charvariant
                      SQL_VARIANT
                  SET @intvariant = 222
                  SET @charvariant = '444'
                  
                  if @intvariant > @charvariant
                  PRINT '@intvariant is greater'
                  else
                  PRINT '@charvariant is greater'
                  

@intvariant is greater

An explicit cast of both to INT would, of course, reverse this result.

DECLARE @intvariant SQL_VARIANT,@charvariant
                            SQL_VARIANT
                        SET @intvariant = 222
                        SET @charvariant = '444'
                        
                        if CAST(@intvariant AS INT) > CAST(@charvariant AS
                            INT)
                        PRINT '@intvariant is greater'
                        else
                        PRINT '@charvariant is greater'
                        

@charvariant is greater

DECLARE @intvariant SQL_VARIANT,@charvariant
                      SQL_VARIANT
                  SET @intvariant = 222
                  SET @charvariant = '444'
                  
                  if CAST(@intvariant AS INT) > CAST(@charvariant AS
                      INT)
                  PRINT '@intvariant is greater'
                  else
                  PRINT '@charvariant is greater'
                  

@charvariant is greater

String comparison examples. Explicit casting is suggested.

DECLARE @chvariant1 SQL_VARIANT
                        DECLARE @chvariant2 SQL_VARIANT
                        SET @chvariant1 = 'AAA'
                        SET @chvariant2 = 'MMM'
                        
                        if @chvariant1  > @chvariant2
                        PRINT '@chvariant1 is greater'
                        else
                        PRINT '@chvariant2 is greater'
                        

@chvariant2 is greater

DECLARE @chvariant1 SQL_VARIANT
                  DECLARE @chvariant2 SQL_VARIANT
                  SET @chvariant1 = 'AAA'
                  SET @chvariant2 = 'MMM'
                  
                  if @chvariant1  > @chvariant2
                  PRINT '@chvariant1 is greater'
                  else
                  PRINT '@chvariant2 is greater'
                  

@chvariant2 is greater

This example was run on a server installed as case-insensitive and behaves as hoped.

DECLARE @chvariant1 SQL_VARIANT
                        DECLARE @chvariant2 SQL_VARIANT
                        SET @chvariant1 = 'AAA'
                        SET @chvariant2 = N'aaa'       -- UNICODE
                        
                        if @chvariant1 = @chvariant2
                        PRINT 'They''re Equal'
                        else
                        PRINT 'They''re Not Equal'
                        

DECLARE @chvariant1 SQL_VARIANT
                  DECLARE @chvariant2 SQL_VARIANT
                  SET @chvariant1 = 'AAA'
                  SET @chvariant2 = N'aaa'       -- UNICODE
                  
                  if @chvariant1 = @chvariant2
                  PRINT 'They''re Equal'
                  else
                  PRINT 'They''re Not Equal'
                  

They're Equal

2.4.18.3 New Function SQL_VARIANT_PROPERTY()

The new function for an SQL VARIANT PROPERTY is used to obtain data type and other properties about an SQL_VARIANT value.

Syntax

SQL_VARIANT_PROPERTY ( expression, property )
   

Arguments

Return Type

Return Type sql_variant—see base type in Table 2-46.

Table 2-46. SQL VARIANT Properties

CHARINTMONEY  NCHARNTEXTNUMERIC
NVARCHARREALSMALLDATETIME
SMALLINT-SMALLMONEYTEXT
TIMESTAMPTINYINTVARBINARY
UNIQUEIDENTIFIERVARCHAR
       

DATETIME = 23  SMALLDATETIME = 16  
FLOAT = 53  REAL = 24  
DECIMAL (p,s) and NUMERIC 
(p,s) = p MONEY = 19
SMALLMONEY = 10  INT = 10  
SMALLINT = 5  TINYINT = 3  
BIT = 1  all other types = 0
       

DECIMAL (p,s) and NUMERIC (p,s)  =
s MONEY and SMALLMONEY = 4  DATETIME = 3 
all other types = 0

Examples Using SQL_VARIANT_PROPERTY() Function

Since number families are a higher family than string families,the INT value 222 would evaluate as greater than CHAR value 444.

DECLARE @chvariant1 SQL_VARIANT
                   DECLARE @chvariant2 SQL_VARIANT
                   SET @chvariant1 = 'AAA'
                   SET @chvariant2 = N'aaa'
                        
                   SELECT SQL_VARIANT_PROPERTY( @chvariant1 ,
                    'BaseType' )
                SELECT SQL_VARIANT_PROPERTY( @chvariant2 ,
                    'BaseType' )
                        

---------------------------------------------
varchar

---------------------------------------------
nvarchar

DECLARE @chvariant1 SQL_VARIANT
                  DECLARE @chvariant2 SQL_VARIANT
                  SET @chvariant1 = 'AAA'
                  SET @chvariant2 = N'aaa'
                  
                  SELECT SQL_VARIANT_PROPERTY( @chvariant1 ,
                      'BaseType' )
                  SELECT SQL_VARIANT_PROPERTY( @chvariant2 ,
                      'BaseType' )
                  

---------------------------------------------
varchar

---------------------------------------------
nvarchar

It should be noted that the PRINT operation requires an explicit CAST if used in place of SELECT in the code segment above.

PRINT CAST ( SQL_VARIANT_PROPERTY( @chvariant1 ,
              'BaseType' ) AS SYSNAME)
                        

Varchar

PRINT CAST ( SQL_VARIANT_PROPERTY( @chvariant1 ,
                      'BaseType' ) AS SYSNAME)
                  

Varchar

2.4.18.4 SQL_VARIANT Data with Functions

2.4.19 TABLE Data Type Details

The TABLE data type, new inMSS 2000, allows the user to declare a local variable capable of storing any table-structured data, such as a query result, set in any code and especially contain the return value from a table-valued function.

Table 2-47. TABLE Data Type

Syntax

DECLARE  @local_variable    table_type_definition
   
   table_type_definition ::=
   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 ]
   
   column_constraint ::=
   {[ NULL | NOT NULL ]
   | [ PRIMARY KEY | UNIQUE ]
   | CHECK ( logical_expression )
   }
   
   table_constraint ::=
   {{PRIMARY KEY | UNIQUE }( column_name [ ,...n ] )
   | CHECK ( search_condition )
   }
   

Arguments

Comments

• Functions and variables can be declared to be of type TABLE.

• TABLE variables can be used in functions, stored procedures and batches.

• Use TABLE variables instead of temporary TABLEs, whenever possible.

TABLE variable benefits (over temporary tables)

• A TABLE variable behaves like a local variable in that 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 may be used in SELECT, INSERT, UPDATE and DELETE statements.

• TABLE variables are automatically dropped at the end of the function, stored procedure or batch in which they are defined.

• TABLE variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.

• Table variables require fewer locking and logging resources because transactions involving table variables last only for the duration of the table variable update.

TABLE variable limitations

• TABLE may not be used in the following statements:

  - INSERT INTO table_variable EXEC stored_procedure

  - SELECT select_list INTO table_variable statements

• Assignment operation between table variables is not supported.

• Table variables are not impacted by transaction rollbacks because they have limited scope and are not part of the persistent database.

2.4.19.1 TABLE Data Type Examples

DECLARE @tablevar TABLE (
                  id    INT            PRIMARY KEY,
                  name  VARCHAR(10)    NOT NULL,
                  age   TINYINT
                  CHECK( age > 0 and age < 180 ) --    Table
                      variables may even have
                  Check
                      constraints
                  INSERT INTO @tablevar VALUES ( 1 , 'Sue' , 35)
                  INSERT INTO @tablevar VALUES ( 2 , 'Sam' , 25)
                  
                  SELECT * FROM @tablevar
                  

id             nam          age
----           -------      ------
1              Sue          35
2              Sam          25                

2.4.20 TIMESTAMP (ROWVERSION) Data Type Details

Table 2-48. TIMESTAMP Data Type

Although ROWVERSION (Table 2-48) is now a data type synonym for TIMESTAMP, it should always be used in place of TIMESTAMP as the latter may completely change definition and usage in a future release of Microsoft SQL Server. Books Online says the following.

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

A future release of Microsoft SQL Server may modify the behavior of the Transact-SQL timestamp data type to align it with the behavior defined in the standard. At that time, the current timestamp data type will be replaced with a rowversion data type.

Microsoft SQL Server 2000 introduces a rowversion synonym for the time-stamp data type. Use rowversion instead of timestamp wherever possible in DDL statements.

TIMESTAMP is as of now the SQL Server 2K base data type name. ROWVERSION is a synonym, which may be used interchangeably with the TIME-STAMP in Transact-SQL statements. It is the base data type TIMESTAMP and not the synonym that is stored and will be seen from operations such as sp_help. Nonetheless, in this case it is recommended to always use ROWVERSION and avoid TIMESTAMP as its definition is likely to change.

This book will use the term ROWVERSION exclusively except to note when the word TIMESTAMP has a different behavior.

2.4.20.1 Using ROWVERSION (TIMESTAMP) Data Type

A ROWVERSION (TIMESTAMP) data type column contains a database-wide unique number that gets updated every time a row gets updated. This column can act as a version number for the row, which gives some control over optimistic locking.

Example using ROWVERSION (TIMESTAMP):

CREATE TABLE table1 (
                        data    INT,
                        rowver  ROWVERSION
                        )
                        
                        INSERT INTO table1 (data) VALUES (1)
                        INSERT INTO table1 (data) VALUES (2)
                        INSERT INTO table1 (data) VALUES (3)
                        
                        SELECT * FROM table1
                        

data               rowver
-----------        ------------------
1                  0x00000000000003F3
2                  0x00000000000003F4
3                  0x00000000000003F5

CREATE TABLE table1 (
                  data    INT,
                  rowver  ROWVERSION
                  )
                  
                  INSERT INTO table1 (data) VALUES (1)
                  INSERT INTO table1 (data) VALUES (2)
                  INSERT INTO table1 (data) VALUES (3)
                  
                  SELECT * FROM table1
                  

data               rowver
-----------        ------------------
1                  0x00000000000003F3
2                  0x00000000000003F4
3                  0x00000000000003F5

Notice that the ROWEVERSION (TIMESTAMP) values under the rowver column have nothing to do with dates or time values but are unique integer values in the current database. When any updateable column for a row is updated, the ROWVERSION column is updated by the system to a new value as shown in the next code segment. The new value is not necessarily sequential within the table depending on what else is going on within the database. What's important is the fact that any update on a row causes the system to change its ROWVERSION value. Its use is discussed next under Optimistic Locking.

UPDATE table1 SET data = 20 WHERE data = 2
                  SELECT * FROM table1
                  

data               rowver
-----------        ------------------
1                  0x00000000000003F3
20                 0x00000000000003F7
3                  0x00000000000003F5

2.4.20.2 Optimistic Locking

Generally speaking "pessimistic locking" (pessimistic concurrency) is the scheme in which exclusive locks are obtained as they are required when data is to be changed. This ensures consistency within the database, but it introduces the possibility of deadlock and has overhead due to doing the locking. It also decreases concurrency by holding exclusive (write) locks for a relatively long time.

Under optimistic locking (optimistic concurrency) schemes locking is deferred or omitted, and a check is made to see if a data value has been changed by another process between the time it was read and the time a new value is to be written. If not changed by another process, the writing may proceed. If changed by another process, the programmer can choose to abort the current attempt, try again or even offer the user the choice to overwrite the data value, abort or start over. Optimistic locking is useful in high transaction environments in which the chance of conflict on the same piece of data is small. Savings by less locking overhead and increase in concurrency can make up for the very rare conflicts that do occur.

One optimistic locking scheme is for a program to use a ROWVERSION column. The program reads the target row and releases the shared read lock immediately allowing other processes to access the row. The data value read may be used in doing some work and then calculating the new value. When the new value is ready for update, the row is again read and then, if the ROWVERSION column has not changed, the data value is updated to the new value.

This scheme is often used with cursors where the client program may obtain several rows into a cursor (read operation) releasing the lock. The user may then take several minutes to study the data and decide to change a value on one row.

Having a ROWVERSION column and declaring the cursor as OPTIMISTIC, when a row has been changed between read time and update time the system generates a 16934 error, which reads as follows: "Optimistic concurrency check failed. The row was modified outside of this cursor." Your program then refetches the row in question and may either abort, start over or overwrite the value with your new value or present the changed data to the user for a decision.

For a further discussion of the subjects of ROWVERSION (TIMESTAMP) data type, using cursors and optimistic locking, see Advanced Transact-SQL for SQL Server 2K by Itzik Ben-Gan and Tom Moreau. Also see Books Online under DECLARE CURSOR and Cursor Concurrency.

Comments

• In a CREATE TABLE or ALTER TABLE statement, you do not have to supply a column name for the TIMESTAMP data type:

  CREATE TABLE ExampleTable (
    PriKey      INT    PRIMARY KEY,
      TIMESTAMP  -- column name defaults to "timestamp"
  )
  

• If you do not supply a column name, SQL Server generates a column name of TIMESTAMP. The ROWVERSION data type synonym does not follow this behavior. You must supply a column name when you specify ROWVERSION.

  CREATE TABLE ExampleTable (
    PriKey         INT     PRIMARY KEY,
    timestamp_col  ROWVERSION  -- column name must be entered
  )
  

• A table can have only one ROWVERSION column.

• The value in a ROWVERSION (TIMESTAMP) column, like an IDENTITY column, is set by the system and cannot be updated by the user via the UPDATE statement. However, the value in the ROWVERSION column is updated every time a row containing a ROWVERSION column is inserted or updated. Do not use a ROWVERSION column as a primary key and do not put an index on it because the continual changes of ROWVERSION column value cause many problems.

• The only comparison operators allowed with ROWVERSION data types are the relational operators for equality or inequality. Usually the programmer doesn't do the comparison but lets the system raise an exception if the row has had a data change.

A nonnullable ROWVERSION column is semantically equivalent to a BINARY(8) column.A nullable ROWVERSION column is semantically equivalent to a VARBINARY(8) column.

2.4.21 UNIQUEIDENTIFIER Data Type Details

The UNIQUEIDENTIFIER data type (Table 2-49) lets you manage globally unique identifiers (GUID). It is used with the NEWID() function.

Table 2-49. UNIQUEIDENTIFIER Data Type

The UNIQUEIDENTIFIER data type used with the NEWID() function is similar to an integer data type with the IDENTITY property, although the latter just guarantees uniqueness within the table.

2.4.21.1 Using UNIQUEIDENTIFIER

• UNIQUEIDENTIFIER objects

  - may be compared using the relational operators (=, <>, <, >, <=, >=)

  - may be checked for NULL (IS NULL and IS NOT NULL)

  - allow no other arithmetic operators

• All column constraints and properties except IDENTITY are allowed on the UNIQUEIDENTIFIER data type.

• Multiple columns within a table may be assigned as UNIQUEIDENTIFIER data type.

• Declaring a column as UNIQUEIDENTIFIER data type does not preclude manually inserting the same value again.

• To make the values unique, it is suggested that the column also be specified as PRIMARY KEY and always be given a new value using the NEWID() function.

Suggested usage of UNIQUEIDENTIFIER

To have a column unique within the table and worldwide, declare the column as

columnname UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID()
   

The NEWID() Function

The function generates a unique value of type UNIQUEIDENTIFIER each time it's called.

DECLARE @uid UNIQUEIDENTIFIER
               SET @uid = NEWID()
               PRINT '@uid is:  '+ CONVERT(varchar(255), @uid)
               

@uid is: C24922A8-51B6-40DA-B53B-E40A81516C60

The values generated by NEWID() are not sequential from one call to the next but instead have a random appearance.

Example of a table using UNIQUEIDENTIFIER as Primary Key

To generate a new UNIQUEIDENTIFIER for each new row, give a DEFAULT as the NEWID() function.

CREATE TABLE table1 (
                  uid    UNIQUEIDENTIFIER  PRIMARY KEY  DEFAULT
                      NEWID(),
                  data   INT
                  )
                  
                  INSERT INTO table1 (data) VALUES (1)
                  INSERT INTO table1 (data) VALUES (2)
                  INSERT INTO table1 (data) VALUES (3)
                  
                  SELECT * FROM table1
                  

uid                                         data
------------------------------------      -----------
00516380-0291-4B90-A113-C10B92F2622B      1
64A88B51-1BCC-4FE0-81E4-69BC65A3E957      2
FF8BD9CB-8793-4E87-80F0-1AF46036C288      3                

The UNIQUEIDENTIFIER value is certainly bulky and awkward to work with, but when you need a world-wide globally unique identifier, it fills the bill.

SELECT * FROM table1
                  WHERE uid = '64A88B51-1BCC-4FE0-81E4-69BC65A3E957'
                  

uid                                       data
------------------------------------      -----------
64A88B51-1BCC-4FE0-81E4-69BC65A3E957      2
         

2.4.21.2 The ROWGUIDCOL Property

The ROWGUIDCOL column property is primarily used by SQL Server replication, but otherwise it does not seem to add much value.

• ROWGUIDCOL property can be assigned to only one column in a table, and that must be a UNIQUEIDENTIFIER data type.

• The table may contain other UNIQUEIDENTIFIER columns.

• ROWGUIDCOL property can only be assigned to a UNIQUEIDENTIFIER column, but neither ROWGUIDCOL property nor the UNIQUEIDENTIFIER data type ensures uniqueness within the table. So either a UNIQUE or PRIMARY KEY constraint (recommended) is still required to get that result.

• The OBJECTPROPERTY function can be used to determine if a table has a ROWGUIDCOL column, and the COLUMNPROPERTY function can be used to determine the name of the column.

• A column declared with the ROWGUIDCOL property can be referenced in a SELECT list either by the word ROWGUIDCOL or by the column name itself.

  This is similar to using the IDENTITYCOL keyword to reference an IDENTITY column.

CREATE TABLE table2 (
                  uid1    UNIQUEIDENTIFIER  ROWGUIDCOL ,
                  uid2    UNIQUEIDENTIFIER
                  )
                  
                  INSERT INTO table2 (uid1,uid2) VALUES ( NEWID() ,
                      NEWID() )
                  
                  SELECT * FROM table2
                  

INSERT INTO table2 (uid1,uid2) VALUES
                  ( 'A7B63E88-1B19-42E9-BADF-814CE00656A0' ,
                  '5E30BAC7-FEF6-4217-BEA9-ED78C247E273'
                  )
                  
                  SELECT uid1, uid2 FROM table2
                  

SELECT ROWGUIDCOL FROM table2
                  SELECT ROWGUIDCOL FROM table2
                  

2.4.21.3 UNIQUEIDENTIFIER Data Type Constants (Literals)

For completeness, the two ways to specify a UNIQUEIDENTIFIER constant are shown here, although typically the NEWID() function is always used to generate a new UNIQUEIDENTIFIER value.

• Character string format

  'FF8BD9CB-8793-4E87-80F0-1AF46036C288'

  '6F9619FF-8B86-D011-B42D-00C04FC964FF'

• Binary format

  0xff19966f868b11d0b42d00c04fc964ff

  0x46463842443943422D383739332D 344538372D383046302D314146343630

You almost never enter your own UNIQUEIDENTIFIER value as a constant because the UNIQUEIDENTIFIER column even with ROWGUIDCOL property does impose a uniqueness constraint; therefore, entering a duplicate value would not be detected. But if you use the NEWID() function, then uniqueness of the new value is guaranteed.