2006/04/12
by admin
1 Comment
Dear MySQL users, MaxDB users and friends,
The previous “please wait” posting was caused by a new guideline on series postings. The new guideline says that postings must be written one week before they get published. The week between the writing and the publication is used for corrections. I’m writing these lines on April, 4th but the article won’t be published before Wednesday April, 12th. We hope that in the time between writing and publishing we can catch most of the Gotchas to further improve the quality of the series. However, this series will continue to be more like a collection of blog postings than a book, a magazine article or any other “official documentation”. We try to do our best, but unfortunately we cannot apply the same amount of quality assurance and proof-reading to the MaxDB series that we do for magazine articles, official documentation or official class materials.
In this issue
In the past, we recognized that most MaxDB users do have some SQL knowledge. Hardly any of the typical MaxDB users seem the be beginners in the field of databases. Therefore SQL basics are not of interest. For example, most users who asked us SQL questions in the past did know what kind of joins exist, what the difference between a right join, left join and a full join are and how to define tables. Also, there seems to be no demand on describing the basics of views as many articles did when MySQL 5.0 introduced views to the MySQL server.
MaxDB users seem to be more interested in the limits and additional features over the SQL92 Entry Level compatible SQL dialect implemented by MaxDB. For this reason, we will often provide you only with brief overview information on some topics. If you need to details, check the SQL Tutorial chapter in the MaxDB documentation. Other online resources like the SQL Course and the SQL Course 2 may also be worth reading if you want to learn the very basics. Those who like printed books better and prefer reading on a sunny balcony, can check the book listing on the MySQL Developer Zone.
Data types for table columns
Let us start the discussion of the MaxDB SQL dialect with something very basic: data types. In general you should always select the smallest data type for a column that can hold all values you want to store in the column. Try to keep your data as compact as possbile to save costy disk-read operations and to hold the most records in as short a buffer cache as possible.
For comparisons with MySQL data types, check what mappings have been choosen for the MaxDB plugin of the MySQL Migration Toolkit. The mappings are desribed in two german language blog postings. Although the texts are written in german, no german language knowledge is required to understand the tables given in the articles on mapping strings, BLOB/LONG, numeric types and temporal types.
| Datatype |
Value range |
Storage |
Notes |
| Numeric types |
| SMALLINT |
-32768 to 32767, 16-bit |
5 bytes |
Equal to FIXED(5, 0) |
| INT[EGER] |
-2147483648 to 2147483647, 32-bit |
7 bytes |
Equal to FIXED(7, 0) |
| FLOAT(p), precision p (0 < p <= 38) |
9. 9999999999999999999999999999999999999E +62 to 1E-64, 0.0, +1E-64 – +9. 9999999999999999999999999999999999999E +62 |
(p+1) DIV 2 + 2 |
Floating point numbers.
For MySQL users: try SUM(float_column / 3) * 3 or summing up small values. MaxDB FLOAT is MySQL Precision Math.
|
| FIXED (p,s), 0 <p <= 38, s <= p |
Depends on p and s, see FLOAT/SMALLINT/INT |
(p+1) DIV 2 + 2 |
Fixed point number.
MaxDB is using a packed decimal storage format for INTEGER, FLOAT and FIXED values. This means that if you store the same values in three columns of the three data types (with comparable precisions), you will get the same performance when doing comparisons.
For MySQL users: FIXED is for Precision Math.
|
| String types |
| CHAR[ACTER](n), n < = 8000 (UNICODE: 4000) |
|
see Manual |
UNICODE requires 2 bytes per character
Code attributes: ASCII, BINARY, UNICODE
Depending on n (<30, >=30) strings will be stored with a variable length |
| VARCHAR(n), n <= 8000 (UNICODE: 4000) |
|
see Manual |
UNICODE requires 2 bytes per character
Code attributes: ASCII, BINARY, UNICODE |
| LONG [VARCHAR] |
|
up to 2GB of bytes |
Also known as BLOB, LOB, etc.
Code attributes: ASCII, BINARY, UNICODE |
| Temporal types |
| TIME |
ISO: ’00:00:00′ to ’23:59:59′ |
9 bytes |
Check the manual for database session settings and the database parameter DATE_TIME_FORMAT |
| DATE |
ISO: ‘0001ÂÂ-01ÂÂ-01’ to ‘9999ÂÂ-12-ÂÂ31’ |
9 bytes |
Check the manual for database session settings and the database parameter DATE_TIME_FORMAT |
| TIMESTAMP |
ISO: ‘0001-01-ÂÂ01 00:00:00.000000’ to ‘9999-12-31 23:59:59.999999’ |
21 bytes |
Check the manual for database session settings and the database parameter DATE_TIME_FORMAT
Supports Microseconds |
| Other types |
| BOOLEAN |
TRUE | FALSE | NULL |
2 bytes |
|
Check the manual for several aliases for data types, for example DECIMAL, NUMERIC, REAL and many more.
Continue Reading →
