SQL Commands List

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Here is an alphabetical list of all the SQL commands SingleStore currently supports. This list is subject to change.

Command Name	Command Description	Grouping
ADD AGGREGATOR	Adds a new child aggregator to a cluster.	Cluster Management
ADD LEAF	Add a leaf node to a cluster.	Cluster Management
AGGREGATOR SET AS MASTER	Promote a child aggregator to master aggregator.	Cluster Management
AGGREGATOR SYNC AUTO_INCREMENT	Sets the auto-increment counter on the aggregators (master or child).	Data Manipulation Language
ALTER DATABASE	Specify if an existing database should begin using synchronous or asynchronous replication.	Data Definition Language
ALTER NODE	The `ALTER NODE` command can be used to set: An external hostname or IP address with which to address a node. An external port with which to connect to a node.	Cluster Management
ALTER PIPELINE	The `ALTER PIPELINE` clause changes an existing pipeline’s configuration.	Pipeline
ALTER RESOURCE POOL	Changes the resource settings in an existing resource pool.	Resource Pool
ALTER TABLE	Modify the structure of an existing table.	Data Definition Language
ALTER USER	Alters settings associated with the user. To change a user password, use the SET PASSWORD command.	Security Management
ALTER VIEW	Atomically replace a view with a new view defined by a `SELECT` statement. `ALTER VIEW` is an online operation and will not cause concurrently executing queries to fail.	Data Definition Language
ANALYZE	Statistics for a table can be collected and updated on an ad hoc basis by running the `ANALYZE` command:	Operational
ARRAY	SingleStore Procedural SQL (PSQL) supports the `ARRAY` data type, which is a collection of elements that share the same data type.	Procedural
ATTACH DATABASE	Restores an unlimited storage database at a restore point and then brings the database online.	Operational
ATTACH LEAF	Attaches a detached leaf into the cluster, re-introducing its data (if possible).	Cluster Management
ATTACH LEAF ALL	Attaches all leaves in a cluster that are currently in the `detached` state.	Cluster Management
BACKUP DATABASE	Backs up a database. The backup files are saved on the master aggregator and on the leaves.	Operational
BEGIN	Commits any existing open transaction on the current connection and starts a new transaction.	Data Manipulation Language
BOOTSTRAP AGGREGATOR	Promotes a node to Master Aggregator.	Cluster Management
CALL	Executes the specified stored procedure. Does not return results.	Procedural
CHECK BLOB CHECKSUM	`CHECK BLOB CHECKSUM` checks for and displays columnstore blob file corruptions. Applies to the blobs in local disk and local blob cache.	Operational
CHECK BOTTOMLESS	`CHECK BOTTOMLESS CHECKSUM` checks the checksum of the blob files stored in the remote blob store.	Operational
CLEAR LOAD ERRORS	Removes load errors from the `information_schema.LOAD_DATA_ERRORS` table. These errors are populated by `LOAD DATA ... ERRORS HANDLE <string>` when it runs.	Data Manipulation Language
CLEAR ORPHAN DATABASES	Removes orphaned partitions. Orphaned partitions are partition databases and tables on leaf nodes that are no longer considered part of the cluster.	Operational
COLLECT	After `COLLECT` executes you will typically iterate through the array to perform processing on its elements.	Procedural
COMMIT	Commits the open transaction if one exists.	Data Manipulation Language
CONTINUE REPLICATING	Resume replicating the database after pausing via PAUSE REPLICATING.	Operational
CREATE EXTERNAL FUNCTION	Creates or replaces an external function, which is a function that calls code that is executed outside of a SingleStore database.	Procedural
CREATE TEMPORARY PROCEDURE	When the session terminates, the temporary stored procedure is no longer available.	Procedural
CREATE AGGREGATE	Creates a user-defined aggregate function (UDAF). A UDAF is a callable routine that accepts input parameters, executes programmatic logic in the function body, and returns a scalar-type value.	Procedural
CREATE DATABASE	Creates a database.	Data Definition Language
Trace Event Commands	Creates a trace event.	Trace Events
CREATE FUNCTION (TVF)	Creates a user-defined table-valued function (TVF). A TVF is a callable routine that accepts input parameters, executes a single `SELECT` statement in the function body, and returns a single table-type value (similar to a view).	Procedural
CREATE FUNCTION (UDF)	Creates a user-defined scalar-valued function (UDF). A UDF is a callable routine that accepts input parameters, executes programmatic logic, and returns a value (or values).	Procedural
CREATE GROUP	Create a group on a SingleStore cluster.	Security Management
CREATE INDEX	Create an index on the specified table.	Data Definition Language
CREATE LINK	Create a new connection link to S3, Azure, GCS, HDFS, HTTP, Kafka, MongoDB®, or MySQL for a permitted user.	Security Management
CREATE MILESTONE	Records a milestone, which is a named point to which you can restore an unlimited storage database. Restoring to the milestone will bring the database to the point in time when the milestone was created.	Data Definition Language
CREATE PIPELINE	Create a new Pipeline to continuously extract, shape, and load data into a table or stored procedure.	Pipeline
CREATE PROCEDURE	Creates a stored procedure. A stored procedure is a callable routine that accepts input parameters, executes programmatic logic, and optionally returns a single value.	Procedural
CREATE PROJECTION	Creates a projection.	Data Definition Language
CREATE RESOURCE POOL	Creates a resource pool.	Resource Pool
CREATE ROLE	Create a role on a SingleStore cluster.	Security Management
CREATE TABLE	Creates a new table.	Data Definition Language
CREATE USER	Create a new user account.	Security Management
CREATE VIEW	Creates an unmaterialized view.	Data Definition Language
CREATE_ARRAY	Creates a new array of the specified length that is initialized with default values for the array’s inferred data type.	Procedural
CURRENT_SECURITY_GROUPS	Returns a comma separated list of the current user's security groups on a SingleStore cluster.	Security Management
CURRENT_SECURITY_ROLES	Returns a comma separated list in one row of the current user's security roles on a SingleStore cluster.	Security Management
CURRENT_USER	Returns the details (username and hostname) of the user executing the query.	Security Management
DELETE	`DELETE` is a DML statement that deletes rows in a table.	Data Manipulation Language
DESCRIBE	Describe the specified table.	Data Definition Language
DETACH DATABASE	Takes an unlimited storage database offline. The data remains on remote storage and should be deleted separately if you wish to completely remove it.	Operational
DETACH LEAF	Detaches a leaf.	Cluster Management
DETACH LEAVES	Detaches all leaves at once.	Cluster Management
DETACH PIPELINE	The `DETACH PIPELINE` clause is similar to the `STOP PIPELINE` clause with the difference being `ALTER TABLE` can be performed on a detached pipeline for the table that is currently being referenced by the pipeline.	Pipeline
DROP … FROM PLANCACHE	Forces a plan to be optimized and generated from scratch the next time it is run.	Data Definition Language
DROP AGGREGATE	Removes a single user-defined aggregate function (UDAF) from the specified database.	Procedural
DROP DATABASE	Drops a local storage database.	Data Definition Language
Trace Event Commands	Drops a trace event.	Trace Events
DROP FUNCTION	Removes a single function from the specified database, including user-defined scalar-valued functions (UDFs) and user-defined table-valued functions (TVFs).	Procedural
DROP GROUP	Remove a group from a SingleStore cluster.	Security Management
DROP INDEX	Drop the specified index on the specified table.	Data Definition Language
DROP LINK	Remove a connection link on S3, Azure, GCS, HDFS, or Kafka for a permitted user.	Security Management
DROP MILESTONE	This command deletes a milestone of a currently attached database.	Data Definition Language
DROP PIPELINE	The `DROP PIPELINE` clause deletes a pipeline from your SingleStore cluster. If the pipeline is currently running, it will stop it before deleting it.	Pipeline
DROP PROCEDURE	Removes a single stored procedure from the specified database.	Procedural
DROP PROJECTION	Drops an existing projection.	Data Definition Language
DROP RESOURCE POOL	Drop the specified resource pool.	Resource Pool
DROP RESULT TABLE	Drops the specified result table.	Data Definition Language
DROP ROLE	Remove a role from a SingleStore cluster.	Security Management
DROP TABLE	Drop the specified table.	Data Definition Language
DROP USER	Removes one or more user accounts.	Security Management
DROP VIEW	Drop a view.	Data Definition Language
ECHO	Executes a stored procedure and returns a set of rows as a result.	Procedural
ECHO SELECT	Within a stored procedure, executes the specified `SELECT` statement and returns a set of rows to the console or to the application that called the stored procedure.	Procedural
EXCEPT and MINUS	`EXCEPT` and `MINUS` are synonymous operators which can be used to compare the results of two queries and return the distinct rows from the first query that are not output by the second.	Data Manipulation Language
EXECUTE IMMEDIATE	Runs a SQL query.	Procedural
EXISTS AND NOT EXISTS	Used with a subquery to check if the subquery returns a record.	Data Manipulation Language
EXPLAIN	Returns detailed information on how the query is executed.	Data Manipulation Language
EXPLAIN REBALANCE ALL DATABASES	Returns the set of steps that `REBALANCE ALL DATABASES` would run if you executed it now.	Cluster Management
EXPLAIN REBALANCE PARTITIONS	Returns the set of steps that REBALANCE PARTITIONS would run if you executed it now.	Cluster Management
EXPLAIN RESTORE REDUNDANCY	Returns the set of steps `RESTORE REDUNDANCY` would run if executed.	Cluster Management
EXPORT TELEMETRY	Writes usage telemetry data to a file.	Operational
FILL and FLUSH CONNECTION POOLS	Used to open and close (reset) connections to leaf nodes.	Cluster Management
FLUSH HOSTS	Flushes the forward and reverse caches for DNS/IP lookup.	Cluster Management
FULLTEXT SERVICE STOP	Stops the full-text V2 service.	Operational
GRANT	Grants the specified privileges to an existing user.	Security Management
GRANT GROUP	Add a user to a group.	Security Management
GRANT ROLE	Add a role to a group.	Security Management
INSERT	Inserts data into a table.	Data Manipulation Language
INSERT_ALL	Inserts an array of records into a table in a single operation.	Procedural
INTERSECT	`INTERSECT` combines the result set of two queries and returns distinct rows that are returned by both queries.	Data Manipulation Language
KILL CONNECTION and KILL QUERY	Kill the connection on the specified thread. Rolls back any query running on the thread.	Operational
KILLALL QUERIES	Kills all running queries or closes all connections.	Operational
LOAD DATA	Import data stored in a CSV, JSON, or Avro file into a SingleStore table (referred to as the destination table in this topic).	Data Manipulation Language
OPTIMIZE TABLE	Performs optimizations on the storage of rows in a table.	Data Definition Language
PAUSE REPLICATING	Pause replicating a database.	Operational
PIVOT	The `PIVOT` operation lets you transform non-aggregated data into a pivot table output format.	Data Manipulation Language
PROFILE	Provides detailed resources usage metrics about a query.	Data Manipulation Language
PROFILE PIPELINE	Runs a single batch in the foreground to provide detailed resource metrics for each operation in your pipeline. The batch itself is not persisted.	Pipeline
PROMOTE AGGREGATOR … TO MASTER	This command promotes a child aggregator node to master, while demoting the current master to child.	Cluster Management
QUERY	SingleStore Procedural SQL (PSQL) supports the `QUERY` data type, whose value represents a SQL `SELECT` statement, optionally with bound variable values.	Procedural
REBALANCE ALL DATABASES	Rebalances the partitions on all databases in the cluster.	Cluster Management
REBALANCE PARTITIONS	Rebalances the partitions for a database.	Cluster Management
RECORD	SingleStore Procedural SQL (PSQL) supports the `RECORD` data type, which is an encapsulated group of named fields.	Procedural
REMOVE AGGREGATOR	Removes a child aggregator from a cluster.	Cluster Management
REMOVE LEAF	Removes a leaf node from a cluster without deleting its data.	Cluster Management
REMOVE LEAVES	Removes all leaf nodes from a cluster without deleting its data.	Cluster Management
REOPTIMIZE	Reoptimize the most recently profiled query based on previous executions of it.	Data Manipulation Language
REPAIR DATABASE	This command is deprecated.	Operational
REPLACE	If the table has a `PRIMARY KEY` or `UNIQUE` index, `REPLACE` will check rows for a matching value and, if a match is found, will delete the old row and replace it with the new row.	Data Manipulation Language
REPLICATE DATABASE	Starts or continues replicating a database from a remote host to the local host.	Operational
RESTORE DATABASE	Restores a database from a binary backup file.	Operational
RESTORE REDUNDANCY	Restore redundancy by replicating any partitions with only one instance.	Cluster Management
REVOKE	Revoke privileges from a SingleStore user or role.	Security Management
REVOKE GROUP	Remove a user from a group.	Security Management
REVOKE ROLE	Remove a role from a group.	Security Management
ROLLBACK	Rolls back the open transaction if one exists.	Data Manipulation Language
ROW	The `ROW()` function constructs a `RECORD` object that may be used in an expression or assigned into a `RECORD` type variable.	Procedural
SCALAR	The `SCALAR` function executes a query that returns a single value.	Procedural
SECURITY_LISTS_INTERSECT	`SECURITY_LISTS_INTERSECT()` is a function that returns a list of a assigned roles for a user.	Security Management
SELECT	Retrieves data from a table.	Data Manipulation Language
SELECT GLOBAL	Inspect the setting of a global engine variable.	Operational
SET and SET SESSION	Sets a session variable. The variable’s value remains in effect during an active connection to the database.	Operational
SET AGGREGATOR	Sets a sync variable to propagate to all aggregators in the cluster.	Operational
SET CLUSTER	Sets a sync variable. Depending on the variable, the updated value is propagated to all nodes in the cluster or all aggregators in the cluster.	Operational
SET GLOBAL	Sets an engine variable globally.	Operational
SET LEAF	Sets a sync variable to propagate to all leaves in the cluster.	Operational
SET PASSWORD	Sets a user’s password.	Security Management
SHOW AGGREGATES	Lists any existing user-defined aggregate functions (UDAF) in the specified database.	Procedural
SHOW AGGREGATORS	Show the aggregators in a cluster.	Cluster Management
SHOW CDC EXTRACTOR POOL	Displays information about the CDC-in pipelines.	Show Commands
SHOW CLUSTER STATUS	Shows information about partitions within a cluster including replication role, replication state, and replication position, across all databases.	Cluster Management
SHOW COLUMNS	Show the list of columns within a given table.	Show Commands
SHOW CREATE AGGREGATE	Shows the function definition and other attributes for a user-defined aggregate function (UDAF).	Procedural
SHOW CREATE FUNCTION	Outputs configuration information about an existing user-defined function, including user-defined scalar value functions (UDFs) and user-defined table-valued functions (TVFs).	Procedural
SHOW CREATE LINK	Shows the `CREATE LINK` command used to create a link.	Show Commands
SHOW CREATE PIPELINE	You can use the `SHOW CREATE PIPELINE` clause to output a `CREATE PIPELINE` statement that can be used to duplicate the configuration and state of an existing pipeline.	Show Commands
SHOW CREATE PROJECTION	Shows the `CREATE PROJECTION` statement that was used to create the projection.	Show Commands
SHOW CREATE PROCEDURE	Shows the definition, procedure body, and other attributes for a stored procedure.	Procedural
SHOW CREATE TABLE	Shows the `CREATE TABLE` statement that was used to create the table.	Show Commands
SHOW CREATE VIEW	Shows the `CREATE VIEW` statement that was used to create the view.	Show Commands
SHOW DATABASE STATUS	Shows SingleStore database status.	Show Commands
SHOW DATABASES	Shows the list of databases that exist on this SingleStore instance.	Show Commands
SHOW ERRORS	Displays errors as a result of execution of an invalid statement.	Show Commands
SHOW FULLTEXT SERVICE METRICS	Displays metrics for the JLucene full-text search in JSON format.	Show Commands
SHOW FULLTEXT SERVICE STATUS	Shows the status of the full-text V2 service.	Show Commands
SHOW FUNCTIONS	Lists any existing user-defined functions in the specified database, including user-defined scalar-valued functions (UDFs) and user-defined table-valued functions (TVFs).	Procedural
SHOW GLOBAL STATUS	Show global SingleStore server status information.	Show Commands
SHOW GRANTS	Show permissions (privileges) for the specified user (or role).	Security Management
SHOW GROUPS	Shows the list of groups on a cluster/workspace group.	Security Management
SHOW GROUPS FOR ROLE	Show groups for a specific role.	Security Management
SHOW GROUPS FOR USER	Show groups for a specific user.	Security Management
SHOW INDEX, SHOW INDEXES, SHOW KEYS	Displays the list of indexes associated with a given table.	Show Commands
SHOW LEAF STATUS	Shows the leaf status.	Show Commands
SHOW LEAVES	Shows the leaves in a cluster.	Cluster Management
SHOW LINKS	Show all connection links on S3, Azure, GCS, HDFS, or Kafka for a permitted user.	Security Management
SHOW LOAD ERRORS	This command is deprecated. You can now query the information_schema.LOAD_DATA_ERRORS table to see the errors that occurred after running the `LOAD DATA` command.	Show Commands
SHOW LOAD WARNINGS	This command is deprecated. You can now query the information_schema.LOAD_DATA_ERRORS table to see the warnings that occurred after running the `LOAD DATA` command.	Show Commands
SHOW PARTITIONS	Shows partitions and relevant information about them.	Show Commands
SHOW PIPELINES	You can see all existing pipelines in a database and their current state by using the `SHOW PIPELINES` statement.	Show Commands
SHOW PLAN	Displays the EXPLAIN plan of a query as per the plan ID.	Show Commands
SHOW PLANCACHE	Shows all query statements that SingleStore has compiled and executed, as well as cumulative query execution statistics associated with each plan.	Show Commands
SHOW PROCESSLIST	Shows details about currently running threads.	Show Commands
SHOW PROCEDURES	Lists existing stored procedures in the current or the specified database.	Procedural
SHOW PROJECTIONS	Shows projections created on the current database or specified table.	Show Commands
SHOW REBALANCE ALL DATABASES STATUS	Shows the status of the currently executing `REBALANCE ALL DATABASES` operation.	Cluster Management
SHOW REBALANCE STATUS	Shows the status of the currently executing REBALANCE PARTITIONS, RESTORE REDUNDANCY, RESTORE, ATTACH LEAF, auto-failover or auto-attach operation on a database.	Cluster Management
SHOW REPLICATION STATUS	Shows the list of all connections between the replication master and secondary databases.	Operational
SHOW REPRODUCTION	Collects troubleshooting data for query optimizer and code generation issues for queries that fail in the compilation phase.	Show Commands
SHOW RESOURCE POOLS	Lists all resource pools and their field values.	Resource Pool
SHOW ROLES	Shows the list of roles on a cluster/workspace group.	Security Management
SHOW ROLES FOR GROUP	Show roles for a specific group.	Security Management
SHOW ROLES FOR USER	Show roles for a specific user.	Security Management
SHOW SCHEMAS	Shows the list of schemas that exist on this SingleStore instance.	Show Commands
SHOW SESSION STATUS	Show SingleStore server status information for a session.	Show Commands
SHOW STATUS EXTENDED	Shows SingleStore server status information with detailed SingleStore-specific memory and performance metrics.	Show Commands
SHOW TABLE STATUS	Show tables status information in a SingleStore database.	Show Commands
SHOW TABLES	Shows the list of tables in the currently selected database, or in another database if db_name is specified.	Show Commands
SHOW USERS	Show users on a SingleStore cluster.	Security Management
SHOW USERS FOR GROUP	Show users for a specific group.	Security Management
SHOW USERS FOR ROLE	Show users for a specific role.	Security Management
SHOW VARIABLES	Shows a list of variable bindings.	Show Commands
SHOW WARNINGS	Display warnings as a result of an invalid statement execution.	Show Commands
SNAPSHOT DATABASE	Takes a snapshot of the given database and truncates the transaction log.	Operational
START PIPELINE	The `START PIPELINE` statement starts an existing pipeline in a SingleStore database. An error will occur if the pipeline is already running.	Pipeline
STOP PIPELINE	The `STOP PIPELINE` statement stops a currently running pipeline in a SingleStore database.	Pipeline
STOP REPLICATING	Stops replicating a database, and promotes the secondary database to the primary database.	Operational
SYNC BOTTOMLESS DATABASE	Synchronizes any currently committed data in the database with the remote object storage.	Operational
TABLE	It is a table-valued function that converts a PSQL array to a set of rows.	Data Manipulation Language
TEST PIPELINE	The `TEST PIPELINE` statement tests an existing pipeline and looks for any new files to import.	Pipeline
TO_QUERY	Allows you to convert a dynamic SQL statement to a query type value.	Procedural
TRIGGER GC	Triggers the garbage collector on the source aggregator and propagates to all the leaves.	Cluster Management
TRUNCATE	Removes all rows from the table.	Data Definition Language
UNION	Combines results from multiple SELECT statements.	Data Manipulation Language
UNLOCK TABLES	Releases all table locks on all databases in the cluster.	Data Definition Language
UPDATE	`UPDATE` is a DML statement that modifies rows in a table.	Data Manipulation Language
USE	Use a database or schema.	Data Manipulation Language
WITH (Common Table Expressions)	Specifies a temporary named result set, referred to as a Common Table Expression (CTE).	Data Manipulation Language

SQL Commands List

Was this article helpful?

Was this article helpful?