text (len) Variable length string of chracters (or other 8 bit data) The defined length is

used to indicate the expected average length of the data. Any data longer

than the specified length will be split between the data table and external

overflow buffers.

Note

G   

 : text fields are slower to access than char fields and cannot be

used in an index nor in LIKE tests.

int

Signed integer values

real

Decimal or Scientific Notation real values

The table structure shown in the example would benefit greatly from the creation of some

indices

. It is assumed that the 

emp_id

 field would be a unique value that is used to identify an

employee. Such a field would normally be defined as the primary key. mSQL 2.0 has removed

support for the primary key construct within the table creation syntax although the same result

can be achieved with an index. Similarly, a common query may be to access an employee

based on the combination of the first and last names. A compound index (i.e. constructed from

more than 1 field) would improve performance. We could construct these indices using :

CREATE UNIQUE INDEX idx1 ON emp_details (emp_id)

CREATE INDEX idx2 ON emp_details (first_name, last_name)

These indices will be used automatically whenever a query is sent to the

database engine that uses those fields in its WHERE clause. The user is not

required to specify any special values in the query to ensure the indices are used

to increase performance.

Sequences

 provide a mechanism via which a sequence value can be maintained

by the mSQL server. This allows for atomic operations (such as getting the next

sequence value) and removes the concerns associated with performing these

operations in client applications. A sequence is associated with a table and a

table may contain at most one sequence.

Once a sequence has been created it can be accessed by SELECTing the _seq

system variable from the table in which the sequence is defined. For example

CREATE SEQUENCE ON test STEP 1 VALUE 5

SELECT _seq FROM test

The above CREATE operation would define a sequence on the table called 

test

that had an initial value of 5 and would be incremented each time it is accessed

(i.e. have a step of 1). The SELECT statement above would return the value 5.

If the SELECT was issued again, a value of 6 would be returned. Each time the

_seq field is selected from 

test

 the current value is returned to the caller and the

sequence value itself is incremented.

Using the STEP and VALUE options a sequence can be created that starts at

any specified number and is incremented or decremented by any specified

value. The value of a sequence would decrease by 5 each time it was accessed if

it was defined with a step of  5.