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¶
Read more about supported data types.
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 |
Contraints¶
Item | Supported | Further information |
---|---|---|
Not null | ✓ | NOT NULL |
Default values | ✓ | DEFAULT |
AUTO INCREMENT |
✓ Different name | IDENTITY |
Transactions¶
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.
Read more about transactions in SQream DB.
Indexes¶
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 |
✓ | 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 |
Clauses¶
Item | Supported | Further information |
---|---|---|
LIMIT / TOP |
✓ | |
LIMIT with OFFSET |
✗ | |
WHERE |
✓ | |
HAVING |
✓ | |
OVER |
✓ |
Table expressions¶
Item | Supported | Further information |
---|---|---|
Tables, Views | ✓ | |
Aliases, AS |
✓ | |
JOIN - INNER , LEFT [ OUTER ] , RIGHT [ OUTER ] , CROSS |
✓ | |
Table expression subqueries | ✓ | |
Scalar subqueries | ✗ |
Scalar expressions¶
Read more about Scalar expressions.
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 , ~ , >> , << |
Permissions¶
Read more about Access control in SQream DB.
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) |