Splice Machine SQL Summary

This topic summarizes the SQL-99+ features in Splice Machine SQL and some of the SQL optimizations that our database engine performs.

SQL Feature Summary

This table summarizes some of the ANSI SQL-99+ features available in Splice Machine:

Feature Examples
Aggregation functions AVG, COUNT, MAX, MIN, STDDEV_POP, STDDEV_SAMP, SUM
Conditional functions CASE, searched CASE
Data Types

INTEGER, REAL, CHARACTER, DATE, BOOLEAN, BIGINT

DDL CREATE TABLE, CREATE SCHEMA, CREATE INDEX, ALTER TABLE, DELETE, UPDATE
DML INSERT, DELETE, UPDATE, SELECT
Isolation Levels Snapshot isolation
Joins INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN
Predicates IN, BETWEEN, LIKE, EXISTS
Privileges Privileges for SELECT, DELETE, INSERT, EXECUTE
Query Specification SELECT DISTINCT, GROUP BY, HAVING
SET functions UNION, ABS, MOD, ALL, CHECK
String functions CHAR, Concatenation (||), INSTR, LCASE (LOWER), LENGTH,
LTRIM, REGEXP_LIKE, REPLACE, RTRIM, SUBSTR, UCASE (UPPER), VARCHAR
Sub-queries Yes
Transactions

COMMIT, ROLLBACK

Triggers Yes
User-defined functions (UDFs) Yes
Views Including grouped views
Window functions AVG, COUNT, DENSE_RANK, FIRST_VALUE, LAG, LAST_VALUE, LEAD, MAX, MIN, RANK, ROW_NUMBER, STDDEV_POP, STDDEV_SAMP, SUM

SQL Optimizations

Splice Machine performs a number of SQL optimizations that enhance the processing speed of your queries:

  • typed columns
  • sparse columns
  • flexible schema
  • secondary indices
  • real-time asynchronous statistics
  • cost-based optimizer