Sybase Data Types
Implementing Database in Sybase - Part 3
Division 4
Forward: In this part of the series, we look at SQL Anywhere data types; SQL Anywhere is a Sybase package.
By: Chrysanthus Date Published: 26 Aug 2012
Division 4
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
The Character Data Type
The character data type is for text. It has many variations that we learn now:
The CHAR data type: One of the variations of the character data type is the CHAR. This type can store text in a table cell that is up to 32767 bytes long. Remember, a byte is a character, in simple terms.
The VARCHAR data type: This is the same as the CHAR type in SQL Anywhere. However they are different types in other database management systems.
The LONG VARCHAR data type: This type can store text in a table cell (column) of arbitrary (any) length; actually a maximum of 2GB (about 2,000,000,000 bytes).
The NCHAR data type: A character cannot be stored in a file as you type it. It is coded before being store. One of such coding system is called the Unicode. The NCHAR data type can store text that is up to 32767 characters long.
The NVARCHAR data type: This is the Unicode equivalent of VARCHAR.
The LONG NVARCHAR data type: This is the Unicode equivalent of LONG VARCHAR.
The XML data type: An XML document is a kind of document like the web page. The structure is created by text arranged in a particular way. A column (cells) in a database table for this data type, can allow only XML documents – one per cell. The XML data type can be of arbitrary length; actually a maximum of 2GB.
A numeric data type is a number data type. It has many variations:
The TINYINT data type: This is a positive integer that can have a maximum value of 255.
The SMALLINT data type: This is a positive or negative integer whose value lies between -32768 and +32767. You can precede the word, SMALLINT with the UNSIGNED modifier. In that case you would be referring to only positive numbers.
The INTEGER data type: This is a positive or negative integer whose value lies between -2147483648 and +2147483647. If you want only positive numbers, then you have to precede the word with UNSIGNED.
The BIGINT data type: This is a positive or negative integer whose value lies between -9223372036854775808 and +9223372036854775807. If you want only positive numbers, then you have to precede the word with UNSIGNED.
The BIT data type: This is a data type whose value can be either 1 or 0. It can be used to represent true or false.
The DECIMAL data type: This is a positive or negative number whose number can be up to 127 number of digits, and whose decimal part (after the decimal point) can be up to 127 number of digits.
The NUMERIC data type: Similar to DECIMAL data type.
The REAL data type: A floating-point number is a number expressed in standard form, e.g. 2.45e+12, meaning 2.45 times 10 raised to the power, 12. The REAL data type is a floating-point number in the range, -3.402823e+38 to 3.402823e+38. The REAL data type involves rounding of its numbers.
The DOUBLE data type: This is a floating-point number that is in the range: -1.79769313486231e+308 to 1.79769313486231e+308. The DOUBLE data type involves rounding of its numbers, but it is more precise (accurate) than the REAL data type.
The FLOAT data type: This is a REAL or DOUBLE data type.
You might be wondering why money should have its own data type instead of just using the decimal or float. Many currencies have two decimal places (after the decimal point), and accounting has to be accurate to two decimal places. That is why money has its own data type.
The SMALLMONEY data type: This is a data type that is less than one million currency units. The currency unit can be dollars, euros, etc. You can type the SMALLMONEY type as NUMERIC(10,2) – see explanation later
The MONEY data type is used for any amount of money in any currency unit. You can type the MONEY type as NUMERIC(19,2) – see explanation later
BIT Array Data Types
A bit array is a string consisting of 1’s and 0’s, e.g. “10011010”.
The VARBIT data type: With this type, the number of bits (1 or 0) in the array (string) is less than 32767.
LONG VARBIT data type: This type is used for a bit array of arbitrary length.
Binary Data Type
This data type is for data that is not interpreted by the database. An image data file content falls into this category.
The BINARY data type: This is a binary data type whose maximum size is 32767 bytes. So if it is an image, it cannot be more than 32767 bytes. The default size is 1 byte. So when using this data type, you have to quote the maximum size as in, BINARY(500), meaning the data in a column cell of up to 500 bytes. The value in the brackets should not be more than 32767.
The LONG BINARY data type: This is used to store BINARY data of arbitrary size in a table cell; actually, a maximum of 2GB.
The IMAGE data type: The BINARY and LONG BINARY data types can be used to store images, but the IMAGE type is the official Sybase type to store image file content of any size; actually, a maximum of 2GB. Each cell in such a column will have an image file content.
The VARBINARY data type: This is the same as the BINARY type.
Measurement of all Binary data types is in bytes.
In Sybase a DATE consist of the year, month and day of month. It ranges from 0001-01-01 to 9999-12-31. Note the order: year, month and day of month. In Sybase, a TIMESTAMP consists of year, month, day, hour, minute, second, and fraction of second accurate to 6 decimal places. It ranges from 0001-01-01 to 9999-12-31. Precision of the time portion of TIMESTAMP is within 1600-02-28 23:59:59 and after 7911-01-01 00:00:00. Note the order: year, month, day, hour, minute, second, and fraction of second. In order words should not use the timestamp out of that range.
The DATE data type: This is used to store the calendar date which consists of the year, month and day; e.g. 2010/07/19. Note the order: year, month, day. You can change this order in Sybase SQL Anywhere, but I will not go into that.
The TIME data type: This type is used to store the time of day, containing hour, minute, second and fraction of a second. You need at most, 6 decimal places to store the fraction.
The TIMESTAMP data type: This type stores a point in time containing the year, month, day, hour, minute, second and fraction of a second (6 decimal places). An example without the fraction is, 1600-02-28 23:59:59.
Well, let us end here for this part of the series. There are still things to learn about data types. We shall learn some of them in the following parts of the series. Rendezvous in the next part of the series.
Chrys
Related Courses
C++ CourseRelational Database and Sybase
Windows User Interface
Computer Programmer – A Jack of all Trade – Poem
NEXT