SQL Feature Checklist¶

To understand which ANSI SQL and other SQL features SQream DB supports, use the tables below.

In this topic:

Data types and values ¶

SQream DB treats each statement as an auto-commit transaction. Each transaction is isolated from other transactions with serializable isolation.

If a statement fails, the entire transaction is cancelled and rolled back. The database is unchanged.

SQream DB has a range-index collected on all columns as part of the metadata collection process.

SQream DB does not support explicit indexing, but does support clustering keys.

Schema changes¶
Item	Supported	Further information
`ALTER TABLE`	✓	ALTER TABLE - Add column, alter column, drop column, rename column, rename table, modify clustering keys
Rename database	✗
Rename table	✓	RENAME TABLE
Rename column	✓	RENAME COLUMN
Add column	✓	ADD COLUMN
Remove column	✓	DROP COLUMN
Alter column data type	✗
Add / modify clustering keys	✓	CLUSTER BY
Drop clustering keys	✓	DROP CLUSTERING KEY
Add / Remove constraints	✗
Rename schema	✗
Drop schema	✓	DROP SCHEMA
Alter default schema per user	✓	ALTER DEFAULT SCHEMA

Statements¶
Item	Supported	Further information
SELECT	✓	SELECT
CREATE TABLE	✓	CREATE TABLE
CREATE FOREIGN / EXTERNAL TABLE	✓	CREATE FOREIGN TABLE
DELETE	✓	Deleting data
INSERT	✓	INSERT, COPY FROM
TRUNCATE	✓	TRUNCATE_IF_EXISTS
UPDATE	✗
VALUES	✓	VALUES

Table expressions¶
Item	Supported	Further information
Tables, Views	✓
Aliases, `AS`	✓
`JOIN` - `INNER`, `LEFT [ OUTER ]`, `RIGHT [ OUTER ]`, `CROSS`	✓
Table expression subqueries	✓
Scalar subqueries	✗

Read more about Access control in SQream DB.

Permissions¶
Item	Supported	Further information
Roles as users and groups	✓
Object default permissions	✓
Column / Row based permissions	✗
Object ownership	✗

Extra functionality¶
Item	Supported	Further information
Information schema	✓	Catalog reference
Views	✓	CREATE VIEW
Window functions	✓	Window functions
CTEs	✓	Common table expressions (CTEs)
Saved queries, Saved queries with parameters	✓	Saved queries
Sequences	✓	Identity (sequence)

Item	Supported	Further information
`BOOL`	✓	Boolean values
`TINTINT`	✓	Unsigned 1 byte integer (0 - 255)
`SMALLINT`	✓	2 byte integer (-32,768 - 32,767)
`INT`	✓	4 byte integer (-2,147,483,648 - 2,147,483,647)
`BIGINT`	✓	8 byte integer (-9,223,372,036,854,775,808 - 9,223,372,036,854,775,807)
`REAL`	✓	4 byte floating point
`DOUBLE`, `FLOAT`	✓	8 byte floating point
`DECIMAL`, `NUMERIC`	Planned	Fixed-point numbers. Use `DOUBLE` instead
`VARCHAR`	✓	Variable length string - ASCII only
`TEXT`, `NVARCHAR`	✓	Variable length string - UTF-8 encoded
`DATE`	✓	Date
`DATETIME`, `TIMESTAMP`	✓	Date and time
`NULL`	✓	`NULL` values
`TIME`	✗	Can be stored as a text string or as part of a `DATETIME`

Item	Supported	Further information
Common functions	✓	`CURRENT_TIMESTAMP`, `SUBSTRING`, `TRIM`, `EXTRACT`, etc.
Comparison operators	✓	`<`, `<=`, `>`, `>=`, `=`, `<>, !=`, `IS`, `IS NOT`
Boolean operators	✓	`AND`, `NOT`, `OR`
Conditional expressions	✓	`CASE .. WHEN`
Conditional functions	✓	`COALESCE`
Pattern matching	✓	`LIKE`, `RLIKE`, `ISPREFIXOF`, `CHARINDEX`, `PATINDEX`
REGEX POSIX pattern matching	✓	`RLIKE`, `REGEXP_COUNT`, `REGEXP_INSTR`, `REGEXP_SUBSTR`,
`EXISTS`	✗
`IN`, `NOT IN`	Partial	Literal values only
Bitwise arithemtic	✓	`&`, `\|`, `XOR`, `~`, `>>`, `<<`