Home

	Expand Search

Transact-SQL User's Guide

Chapter 6 Using and Creating Datatypes

Using system-supplied datatypes

Using system-supplied datatypes

Table 6-1 lists the system-supplied datatypes provided for various types of information, the synonyms recognized by Adaptive Server, and the range and storage size for each. The system datatypes are printed in lowercase characters, although Adaptive Server allows you to enter them in either uppercase or lowercase. Most Adaptive Server-supplied datatypes are not reserved words and can be used to name other objects.

Exact numeric types: integers

Adaptive Server provides three datatypes, tinyint, smallint, and int, to store integers (whole numbers). These types are exact numeric types; they preserve their accuracy during arithmetic operations.

Choose among the integer types based on the expected size of the numbers to be stored. Internal storage size varies by datatype.

Exact numeric types: decimal numbers

Use the exact numeric types, numeric and decimal, for numbers that include decimal points. Data stored in numeric and decimal columns is packed to conserve disk space and preserves its accuracy to the least significant digit after arithmetic operations. The numeric and decimal types are identical in all respects but one: Only numeric types with a scale of 0 can be used for the IDENTITY column.

The exact numeric types accept two optional parameters, precision and scale, enclosed within parentheses and separated by a comma:

datatype [(precision  [, scale ])]

Adaptive Server defines each combination of precision and scale as a distinct datatype. For example, numeric(10,0) and numeric(5,0) are two separate datatypes. The precision and scale determine the range of values that can be stored in a decimal or numeric column:

• The precision specifies the maximum number of decimal digits that can be stored in the column. It includes all digits to the right or left of the decimal point. You can specify a precision of 1-38 digits or use the default precision of 18 digits.

• The scale specifies the maximum number of digits that can be stored to the right of the decimal point. The scale must be less than or equal to the precision. You can specify a scale of 0-38 digits or use the default scale of 0 digits.

Exact numeric types with a scale of 0 display without a decimal point. You cannot enter a value that exceeds either the precision or the scale for the column.

The storage size for a numeric or decimal column depends on its precision. The minimum storage requirement is 2 bytes for a 1- or 2-digit column. Storage size increases by 1 byte for each additional 2 digits of precision, to a maximum of 17 bytes.

Approximate numeric datatypes

The numeric types float, double precision, and real store numeric data that can tolerate rounding during arithmetic operations.

Approximate numeric datatypes store, as binary tractions, slightly inaccurate representations of real numbers, stored as binary fractions. Anytime an approximate numeric value is displayed, printed, transferred between hosts, or used in calculations, the numbers lose precision. Note that isql displays only six significant digits after the decimal point and rounds the remainder. For more information on precision and approximate numeric datatypes, see the Reference Manual.

Use the approximate numeric types for data that covers a wide range of values. They support all aggregate functions and all arithmetic operations except modulo (%).

The real and double precision types are built on types supplied by the operating system. The float type accepts an optional precision in parentheses. float columns with a precision of 1-15 are stored as real; those with higher precision are stored as double precision. The range and storage precision for all three types is machine dependent.

Money datatypes

The money datatypes, money and smallmoney, store monetary data. You can use these datatypes for U.S. dollars and other decimal currencies, although Adaptive Server provides no means to convert from one currency to another. You can use all arithmetic operations except modulo, and all aggregate functions, with money and smallmoney data.

Both money and smallmoney are accurate to one ten-thousandth of a monetary unit, but round values up to two decimal places for display purposes. The default print format places a comma after every three digits.

Date and time datatypes

Use the datetime and smalldatetime datatypes to store date and time information from January 1, 1753 through December 31, 9999. Dates outside this range must be entered, stored, and manipulated as char or varchar values.

• datetime columns hold dates between January 1, 1753 and December 31, 9999. datetime values are accurate to 1/300 second on platforms that support this level of granularity. Storage size is 8 bytes: 4 bytes for the number of days since the base date of January 1, 1900 and 4 bytes for the time of day.

• smalldatetime columns hold dates from January 1, 1900 to June 6, 2079, with accuracy to the minute. Its storage size is 4 bytes: 2 bytes for the number of days after January 1, 1900, and 2 bytes for the number of minutes after midnight.

Enclose date and time information in single or double quotes. You can enter it in either uppercase or lowercase letters and include spaces between data parts. Adaptive Server recognizes a wide variety of data entry formats, which are described in Chapter 8, "Adding, Changing, and Deleting Data." However, Adaptive Server rejects values such as 0 or 00/00/00, which are not recognized as dates.

The default display format for dates is "Apr 15 1987 10:23PM". You can use the convert function for other styles of date display. You can also do some arithmetic calculations on datetime values with the built-in date functions, though Adaptive Server may round or truncate millisecond values.

Character datatypes

Use the character datatypes to store strings consisting of letters, numbers, and symbols entered within single or double quotes. You can use the like keyword to search these strings for particular characters and the built-in string functions to manipulate their contents. Strings consisting of numbers can be converted to exact and approximate numeric datatypes with the convert function, and then used for arithmetic.

The char(n) datatype stores fixed-length strings, and the varchar(n) datatype stores variable-length strings, in single-byte character sets such as English. Their national character counterparts, nchar(n) and nvarchar(n), store fixed- and variable-length strings in multibyte character sets such as Japanese.The unichar and univarchar datatypes store Unicode characters, which are a constant size. You can specify the maximum number of characters with n or use the default column length of one character. For strings larger than the page size, use the text datatype.

Adaptive Server truncates entries to the specified column length without warning or error, unless you set string_rtruncation on. See the set command in the Reference Manual for more information. The empty string, ""or '', is stored as a single space rather than as NULL. Thus, "abc" + "" + "def" is equivalent to "abc def", not to "abcdef".

Fixed- and variable-length columns behave somewhat differently:

• Data in fixed-length columns is blank-padded to the column length. For the char and unichar datatypes, storage size is n bytes, (unichar = n*@@unicharsize); for the nchar datatype, n times the average national character length (@@ncharsize). When you create a char, unichar, or nchar column that allows nulls, Adaptive Server converts it to a varchar, univarchar, or nvarchar column and uses the storage rules for those datatypes. This is not true of char and nchar variables and parameters.

• Data in variable-length columns is stripped of trailing blanks; storage size is the actual length of the data. For varchar or univarchar columns, this is the number of characters; for nvarchar columns, it is the number of characters times the average character length. Variable-length character data may require less space than fixed-length data, but it is accessed somewhat more slowly.

The unichar and univarchar datatypes support the UTF-16 encoding of Unicode in the Adaptive Server. These datatypes are independent of the char and varchar datatypes, but mirror their behavior.

For example, the built-in functions that operate on char and varchar also operate on unichar and univarchar. However, unichar and univarchar store only UTF-16 characters and have no connection to the default character set ID or the default sortorder ID as char and varchar do.

To use the unichar and univarchar datatypes, the default character set for the server must be set to UTF-8.

Each unichar/univarchar character requires two bytes of storage. The declaration of a unichar/univarchar column is the number of 16-bit Unicode values. The following example creates a table with one unichar column for 10 Unicode values requiring 20 bytes of storage:

create table unitbl (unicol unichar(10))

The length of a unichar/univarchar column is limited by the size of a data page, just as the length of char/varchar columns.

Note that Unicode surrogate pairs use the storage of two 16-bit Unicode values (i.e., four bytes). Be aware of this when declaring columns intended to store Unicode surrogate pairs. By default, Adaptive Server Enterprise correctly handles surrogates, taking care not to split the pair. Truncation of Unicode data is handled in a manner similar to that of char and varchar data.

Unichar expressions can be used anywhere char expressions are used, including comparison operators, joins, subqueries, etc. Note however, that mixed-mode expressions of both unichar and char are performed as unichar. The number of Unicode values that can participate in such operations is limited to the maximum size of a unichar string.

The normalization process modifies Unicode data so there is only a single representation in the database for a given sequence of abstract characters. Often, characters followed by combined diacritics are replaced by pre-combined forms. This allows significant performance optimizations. By default, the server assumes all Unicode data should be normalized.

Relational expressions

All relational expressions involving at least one expression of unichar or univarchar, will be based on the default Unicode sort order. If one expression is unichar and the other is varchar (nvarchar, char, or nchar), the latter will be implicitly converted to unichar.

The following expressions are most often used in where clauses, where it may be combined with logical operators.

When comparing Unicode character data "less than" means closer to the beginning of the default Unicode default sort order, and "greater than" means closer to the end. "Equality "means the Unicode default sort order makes no distinction between two values (although they need not be identical). For example, the precomposed character ê must be considered equal to the combining sequence consisting of the letter e followed by U+0302. If the Unicode normalization feature is turned on (the default), the Unicode data is automatically normalized and the server never sees unnormalized data.

Join operators

Join operators appear in the same manner as comparison operators. In fact, any comparison operator can be used in a join. Expressions involving at least one expression of type unichar will be based on the default Unicode sort order. If one expression is of type unichar and the other type varchar (nvarchar, char, or nchar), the latter will be implicitly converted to unichar.

Union operators

The union operator will operate with unichar data much like it does with varchar data. Corresponding columns from individual queries must be implicitly convertible to unichar, or explicit conversion must be used.

Clauses and modifiers

When unichar and univarchar columns are used in group by and order by clauses, equality is judged according to the default Unicode sort order. This is also true when using the distinct modifier.

The text datatype stores up to 2,147,483,647 bytes of printable characters on linked lists of separate data pages. Each page stores a maximum of 1800 bytes of data.

To save storage space, define text columns as NULL. When you initialize a text column with a non-null insert or update, Adaptive Server assigns a text pointer and allocates an entire 2K data page to hold the value.

You cannot use the text datatype:

• For parameters to stored procedures, as values passed to these parameters, or for local variables

• For parameters to remote procedure calls (RPCs)

• In order by, compute, group by, or union clauses

• In an index

• In subqueries or joins

• In a where clause, except with the keyword like

• With the + concatenation operator

• In the if update clause of a trigger

If you are using databases connected with Component Integration Services, there are several differences in the way text datatypes are handled. See the Component Integration Services User's Guide for more information.

For more information about the text datatype, see "Changing text and image data".

Binary datatypes

The binary datatypes store raw binary data, such as pictures, in a hexadecimal-like notation. Binary data begins with the characters "0x" and includes any combination of digits and the uppercase and lowercase letters A-F. The two digits following "0x" in binary and varbinary data indicate the type of number: "00" represents a positive number and "01" represents a negative number.

If the input value does not include the "0x", Adaptive Server assumes that the value is an ASCII value and converts it.

Adaptive Server manipulates the binary types in a platform-specific manner. For true hexadecimal data, use the hextoint and inttohex functions. See Chapter 10, "Using the Built-In Functions in Queries."

Use the binary(n) and varbinary(n) datatypes to store data up to 255 bytes in length. Each byte of storage holds 2 binary digits. Specify the column length with n, or use the default length of 1 byte. If you enter a value longer than n, Adaptive Server truncates the entry to the specified length without warning or error.

• Use the fixed-length binary type, binary(n), for data in which all entries are expected to have a similar length. Because entries in binary columns are zero-padded to the column length, they may require more storage space than those in varbinary columns, but they are accessed somewhat faster.

• Use the variable-length binary type, varbinary(n), for data that is expected to vary greatly in length. Storage size is the actual size of the data values entered, not the column length. Trailing zeros are truncated.

When you create a binary column that allows nulls, Adaptive Server converts it to a varbinary column and uses the storage rules for that datatype.

You can search binary strings with the like keyword and operate on them with the built-in string functions. Because the exact form in which you enter a particular value depends upon the hardware you are using, calculations involving binary data may produce different results on different platforms.

Use the image datatype to store larger blocks of binary data on external data pages. An image column can store up to 2,147,483,647 bytes of data on linked lists of data pages separate from other data storage for the table.

When you initialize an image column with a non-null insert or update, Adaptive Server assigns a text pointer and allocates an entire 2K data page to hold the value. Each page stores a maximum of 1800 bytes.

To save storage space, define image columns as NULL. To add image data without saving large blocks of binary data in your transaction log, use writetext. See the Reference Manual for details on writetext.

You cannot use the image datatype:

• For parameters to stored procedures, as values passed to these parameters, or for local variables

• For parameters to remote procedure calls (RPCs)

• In order by, compute, group by, or union clauses

• In an index

• In subqueries or joins

• In a where clause, except with the keyword like

• With the + concatenation operator

• In the if update clause of a trigger

If you are using databases connected with Component Integration Services, there are several differences in the way image datatypes are handled. See the Component Integration Services User's Guide for more information.

For more information about the image datatype, see "Changing text and image data".

The bit datatype

Use bit columns for true and false or yes and no types of data. bit columns hold either 0 or 1. Integer values other than 0 or 1 are accepted, but are always interpreted as 1. Storage size is 1 byte. Multiple bit datatypes in a table are collected into bytes. For example, 7-bit columns fit into 1 byte; 9-bit columns take 2 bytes.

Columns of datatype bit cannot be NULL and cannot have indexes on them. The status column in the syscolumns system table indicates the unique offset position for bit columns.

The timestamp datatype

The timestamp user-defined datatype is necessary for columns in tables that are to be browsed in Open Client(TM) DB-Library applications.

Every time a row containing a timestamp column is inserted or updated, the timestamp column is automatically updated. A table can have only one column of the timestamp datatype. A column named timestamp automatically has the system datatype timestamp. Its definition is:

varbinary(8) "NULL"

Because timestamp is a user-defined datatype, you cannot use it to define other user-defined datatypes. You must enter it as "timestamp" in all lowercase letters.

The sysname datatype

sysname is a user-defined datatype that is used in the system tables. Its definition is:

varchar(30) "NULL"

You cannot use the sysname datatype to create a column. You can, however, create a user-defined datatype with a base type of sysname. You can then use this user-defined datatype to create columns. For more information about user-defined datatypes, see "Creating user-defined datatypes".