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.

Read more about clustering keys and our metadata system.

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

Statements¶
Item	Supported	Further information
SELECT	Supported	SELECT
CREATE TABLE	Supported	CREATE TABLE
CREATE FOREIGN / EXTERNAL TABLE	Supported	CREATE FOREIGN TABLE
DELETE	Supported	Deleting Data
INSERT	Supported	INSERT, COPY FROM
TRUNCATE	Supported	TRUNCATE
UPDATE	Not supported
VALUES	Supported	VALUES

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

Read more about Access Control in SQream DB.

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

Extra Functionality¶
Item	Supported	Further information
Information schema	Supported	Catalog Reference Guide
Views	Supported	CREATE VIEW
Window functions	Supported	Window Functions
CTEs	Supported	Common Table Expressions
Saved queries, Saved queries with parameters	Supported	Saved Queries
Sequences	Supported	Identity

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

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