SQL Data Types

Complete reference of data types supported in AVA Database®

Overview

AVA Database® supports 16 core data types optimized for high-performance analytics. All types are stored in columnar format with automatic compression and vectorized operations support.

Numeric Types

Integer Types

Type	Size	Range	Description
`SHORT`	2 bytes	-32,768 to 32,767	16-bit signed integer
`USHORT`	2 bytes	0 to 65,535	16-bit unsigned integer
`INT`	4 bytes	-2,147,483,648 to 2,147,483,647	32-bit signed integer
`UINT`	4 bytes	0 to 4,294,967,295	32-bit unsigned integer
`LONG`	8 bytes	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	64-bit signed integer
`ULONG`	8 bytes	0 to 18,446,744,073,709,551,615	64-bit unsigned integer

Floating-Point Types

Type	Size	Precision	Description
`FLOAT`	4 bytes	~7 decimal digits	32-bit single precision IEEE 754
`DOUBLE`	8 bytes	~15 decimal digits	64-bit double precision IEEE 754

Note: All numeric types support AVX-512 SIMD vectorization for high-performance analytical operations.

String Types

Type	Max Size	Description
`CHAR`	1 byte	Fixed-length single character
`VARCHAR(n)`	16,384 bytes	Variable-length string up to n characters (max 16,384)

Usage Example

-- Create table with string types
CREATE TABLE customers (
    id INT,
    initial CHAR,
    name VARCHAR(100),
    email VARCHAR(255)
);

Compression: VARCHAR columns are automatically compressed using dictionary encoding and LZMA, achieving up to 90% compression ratio.

Temporal Types

Type	Size	Format	Description
`DATE`	8 bytes	YYYY-MM-DD	Calendar date (stored as days since epoch)
`TIME`	4 bytes	HH:MM:SS.mmm	Time of day with millisecond precision
`DATETIME`	8 bytes	YYYY-MM-DD HH:MM:SS	Combined date and time (stored as double)

Usage Example

-- Create table with temporal types
CREATE TABLE transactions (
    id INT,
    transaction_date DATE,
    transaction_time TIME,
    created_at DATETIME
);

-- Insert temporal data
INSERT INTO transactions VALUES
    (1, '2024-01-15', '14:30:00', '2024-01-15 14:30:00');

Boolean Type

Type	Size	Values	Description
`BOOL`	1 byte	TRUE, FALSE	Boolean true/false value

Usage Example

-- Create table with boolean column
CREATE TABLE products (
    id INT,
    name VARCHAR(100),
    in_stock BOOL,
    featured BOOL
);

-- Query with boolean conditions
SELECT * FROM products WHERE in_stock = TRUE AND featured = TRUE;

Special Types

Type	Size	Description
`TOKEN`	4 bytes	Integer token derived from string (for dictionary encoding)
`VIRTUAL`	Variable	Virtual column containing computed or aggregated data

Internal Use: TOKEN and VIRTUAL types are primarily used internally by AVA Database® for optimization. Direct use in SQL DDL is not recommended.

Type Conversion

AVA Database® supports automatic type conversion between compatible types and explicit casting using the CAST function.

Numeric Type Hierarchy

Types are automatically promoted during operations based on precision ranking:

DOUBLE (9) > FLOAT (8) > LONG (7) > ULONG (6) > INT (5) > UINT (4) > SHORT (3) > USHORT (2) > BOOL (1)

Explicit Type Casting

-- Cast integer to double
SELECT CAST(quantity AS DOUBLE) * price FROM orders;

-- Cast string to integer
SELECT CAST(year_string AS INT) FROM data;

-- Cast date to string
SELECT CAST(order_date AS VARCHAR) FROM orders;

Type Constraints and Limits

Constraint	Limit
Maximum VARCHAR length	16,384 bytes
Date range	±292 billion years (64-bit days since epoch)
Time precision	Milliseconds (0.001 seconds)
NULL representation	Maximum value for each numeric type (e.g., INT_MAX for INT)

Performance Considerations

Optimization Tips

Use the smallest integer type that fits your data range for optimal memory usage
DOUBLE operations are fastest due to AVX-512 vectorization optimizations
VARCHAR columns benefit from automatic dictionary encoding and compression
DATE and TIME types are highly optimized for temporal analytics
Consider using TOKEN type for high-cardinality string dimensions