SQL Feature Checklist

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

Data Types and Values

Read more about supported data types.

Data Types and Values

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

Fixed-point numbers.

VARCHAR

Variable length string - ASCII only

TEXT

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

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

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

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

UPDATE

VALUES

VALUES

Clauses

Clauses

Item

Supported

Further information

LIMIT / TOP

LIMIT with OFFSET

WHERE

HAVING

OVER

Table Expressions

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.

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 arithmetic

&, |, XOR, ~, >>, <<

Permissions

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

Extra Functionality

Item

Supported

Further information

Information schema

Catalog Reference Guide

Views

CREATE VIEW

Window functions

Window Functions

CTEs

Common table expressions (CTEs)

Saved queries, Saved queries with parameters

Saved Queries

Sequences

Identity