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.

Table 6-1: Adaptive Server system datatypes

Datatypes by category

Synonyms

Range

Bytes of storage

Exact numeric: integers

bigint

Whole numbers between 263 and -263 - 1 (from -9,223,372,036,854,775,808 to +9,223,372,036,854,775,807, inclusive.

8

int

integer

231 -1 (2,147,483,647) to -231 (-2,147,483,648

4

smallint

215 -1 (32,767) to -215 (-32,768)

2

tinyint

0 to 255 (Negative numbers are not permitted)

1

unsigned bigint

Whole numbers between 0 and 18,446,744,073,709,551,615

8

unsigned int

Whole numbers between 0 and 4,294,967,295

4

unsigned smallint

Whole numbers between 0 and 65535

2

Exact numeric: decimals

numeric (p, s)

1038 -1 to -1038

2 to 17

decimal (p, s)

dec

1038 -1 to -1038

2 to 17

Approximate numeric

float (precision)

machine dependent

4 for default precision < 16, 8 for default precision >= 16

double precision

machine dependent

8

real

machine dependent

4

Money

smallmoney

214,748.3647 to -214,748.3648

4

money

922,337,203,685,477.5807 to -922,337,203,685,477.5808

8

Date/time

smalldatetime

January 1, 1900 to June 6, 2079

4

datetime

January 1, 1753 to December 31, 9999

8

date

January 1, 0001 to December 31, 9999

4

time

12:00:00AM to 11:59:59:999PM

4

bigdatetime

January 1, 0001 to December 31, 9999 and 12:00:00.000000 AM to 11:59:59.999999 PM

8

bigtime

12:00:00.000000 AM to 11:59:59.999999 PM

8

Character

char(n)

character

pagesize

n

varchar(n)

character varying, char varying

pagesize

actual entry length

unichar

Unicode character

pagesize

n * @@unicharsize (@@unicharsize equals 2)

univarchar

Unicode character varying, char varying

pagesize

actual number of characters * @@unicharsize

nchar(n)

national character, national char

pagesize

n * @@ncharsize

nvarchar(n)

nchar varying, national char varying, national character varying

pagesize

@@ncharsize * number of characters

text

231 -1 (2,147,483,647) bytes or fewer

0 when uninitialized; multiple of 2K after initialization

unitext

1,073,741,823 Unicode characters or fewer

0 when uninitialized; multiple of 2K after initialization

Binary

binary(n)

pagesize

n

varbinary(n)

pagesize

actual entry length

image

231 -1 (2,147,483,647) bytes or fewer

0 when uninitialized; multiple of 2K after initialization

Bit   

bit

0 or 1

1 (one byte holds up to 8 bit columns)