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.
Command Name |
Command Description |
Grouping |
---|---|---|
Adds a new child aggregator to a cluster. |
Cluster Management |
|
Add a leaf node to a cluster. |
Cluster Management |
|
Promote a child aggregator to master aggregator. |
Cluster Management |
|
Sets the auto-increment counter on the aggregators (master or child). |
Data Manipulation Language |
|
Specify if an existing database should begin using synchronous or asynchronous replication. |
Data Definition Language |
|
The
|
Cluster Management |
|
The |
Pipeline |
|
Changes the resource settings in an existing resource pool. |
Resource Pool |
|
Modify the structure of an existing table. |
Data Definition Language |
|
Alters settings associated with the user. |
Security Management |
|
Atomically replace a view with a new view defined by a |
Data Definition Language |
|
Statistics for a table can be collected and updated on an ad hoc basis by running the |
Operational |
|
SingleStore Procedural SQL (PSQL) supports the |
Procedural |
|
Restores an unlimited storage database at a restore point and then brings the database online. |
Operational |
|
Attaches a detached leaf into the cluster, re-introducing its data (if possible). |
Cluster Management |
|
Attaches all leaves in a cluster that are currently in the |
Cluster Management |
|
Backs up a database. |
Operational |
|
Commits any existing open transaction on the current connection and starts a new transaction. |
Data Manipulation Language |
|
Promotes a node to Master Aggregator. |
Cluster Management |
|
Executes the specified stored procedure. |
Procedural |
|
|
Operational |
|
|
Operational |
|
Removes load errors from the |
Data Manipulation Language |
|
Removes orphaned partitions. |
Operational |
|
After |
Procedural |
|
Commits the open transaction if one exists. |
Data Manipulation Language |
|
Resume replicating the database after pausing via PAUSE REPLICATING. |
Operational |
|
Creates or replaces an external function, which is a function that calls code that is executed outside of a SingleStore database. |
Procedural |
|
When the session terminates, the temporary stored procedure is no longer available. |
Procedural |
|
Creates a user-defined aggregate function (UDAF). |
Procedural |
|
Creates a database. |
Data Definition Language |
|
Creates a trace event. |
Trace Events |
|
Creates a user-defined table-valued function (TVF). |
Procedural |
|
Creates a user-defined scalar-valued function (UDF). |
Procedural |
|
Create a group on a SingleStore cluster. |
Security Management |
|
Create an index on the specified table. |
Data Definition Language |
|
Create a new connection link to S3, Azure, GCS, HDFS, HTTP, Kafka, MongoDB®, or MySQL for a permitted user. |
Security Management |
|
Records a milestone, which is a named point to which you can restore an unlimited storage database. |
Data Definition Language |
|
Create a new Pipeline to continuously extract, shape, and load data into a table or stored procedure. |
Pipeline |
|
Creates a stored procedure. |
Procedural |
|
Creates a projection. |
Data Definition Language |
|
Creates a resource pool. |
Resource Pool |
|
Create a role on a SingleStore cluster. |
Security Management |
|
Creates a new table. |
Data Definition Language |
|
Create a new user account. |
Security Management |
|
Creates an unmaterialized view. |
Data Definition Language |
|
Creates a new array of the specified length that is initialized with default values for the array’s inferred data type. |
Procedural |
|
Returns a comma separated list of the current user's security groups on a SingleStore cluster. |
Security Management |
|
Returns a comma separated list in one row of the current user's security roles on a SingleStore cluster. |
Security Management |
|
Returns the details (username and hostname) of the user executing the query. |
Security Management |
|
|
Data Manipulation Language |
|
Describe the specified table. |
Data Definition Language |
|
Takes an unlimited storage database offline. |
Operational |
|
Detaches a leaf. |
Cluster Management |
|
Detaches all leaves at once. |
Cluster Management |
|
The |
Pipeline |
|
Forces a plan to be optimized and generated from scratch the next time it is run. |
Data Definition Language |
|
Removes a single user-defined aggregate function (UDAF) from the specified database. |
Procedural |
|
Drops a local storage database. |
Data Definition Language |
|
Drops a trace event. |
Trace Events |
|
Removes a single function from the specified database, including user-defined scalar-valued functions (UDFs) and user-defined table-valued functions (TVFs). |
Procedural |
|
Remove a group from a SingleStore cluster. |
Security Management |
|
Drop the specified index on the specified table. |
Data Definition Language |
|
Remove a connection link on S3, Azure, GCS, HDFS, or Kafka for a permitted user. |
Security Management |
|
This command deletes a milestone of a currently attached database. |
Data Definition Language |
|
The |
Pipeline |
|
Removes a single stored procedure from the specified database. |
Procedural |
|
Drops an existing projection. |
Data Definition Language |
|
Drop the specified resource pool. |
Resource Pool |
|
Drops the specified result table. |
Data Definition Language |
|
Remove a role from a SingleStore cluster. |
Security Management |
|
Drop the specified table. |
Data Definition Language |
|
Removes one or more user accounts. |
Security Management |
|
Drop a view. |
Data Definition Language |
|
Executes a stored procedure and returns a set of rows as a result. |
Procedural |
|
Within a stored procedure, executes the specified |
Procedural |
|
|
Data Manipulation Language |
|
Runs a SQL query. |
Procedural |
|
Used with a subquery to check if the subquery returns a record. |
Data Manipulation Language |
|
Returns detailed information on how the query is executed. |
Data Manipulation Language |
|
Returns the set of steps that |
Cluster Management |
|
Returns the set of steps that REBALANCE PARTITIONS would run if you executed it now. |
Cluster Management |
|
Returns the set of steps |
Cluster Management |
|
Writes usage telemetry data to a file. |
Operational |
|
Used to open and close (reset) connections to leaf nodes. |
Cluster Management |
|
Flushes the forward and reverse caches for DNS/IP lookup. |
Cluster Management |
|
Stops the full-text V2 service. |
Operational |
|
Grants the specified privileges to an existing user. |
Security Management |
|
Add a user to a group. |
Security Management |
|
Add a role to a group. |
Security Management |
|
Inserts data into a table. |
Data Manipulation Language |
|
Inserts an array of records into a table in a single operation. |
Procedural |
|
|
Data Manipulation Language |
|
Kill the connection on the specified thread. |
Operational |
|
Kills all running queries or closes all connections. |
Operational |
|
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 |
|
Performs optimizations on the storage of rows in a table. |
Data Definition Language |
|
Pause replicating a database. |
Operational |
|
The |
Data Manipulation Language |
|
Provides detailed resources usage metrics about a query. |
Data Manipulation Language |
|
Runs a single batch in the foreground to provide detailed resource metrics for each operation in your pipeline. |
Pipeline |
|
This command promotes a child aggregator node to master, while demoting the current master to child. |
Cluster Management |
|
SingleStore Procedural SQL (PSQL) supports the |
Procedural |
|
Rebalances the partitions on all databases in the cluster. |
Cluster Management |
|
Rebalances the partitions for a database. |
Cluster Management |
|
SingleStore Procedural SQL (PSQL) supports the |
Procedural |
|
Removes a child aggregator from a cluster. |
Cluster Management |
|
Removes a leaf node from a cluster without deleting its data. |
Cluster Management |
|
Removes all leaf nodes from a cluster without deleting its data. |
Cluster Management |
|
Reoptimize the most recently profiled query based on previous executions of it. |
Data Manipulation Language |
|
This command is deprecated. |
Operational |
|
If the table has a |
Data Manipulation Language |
|
Starts or continues replicating a database from a remote host to the local host. |
Operational |
|
Restores a database from a binary backup file. |
Operational |
|
Restore redundancy by replicating any partitions with only one instance. |
Cluster Management |
|
Revoke privileges from a SingleStore user or role. |
Security Management |
|
Remove a user from a group. |
Security Management |
|
Remove a role from a group. |
Security Management |
|
Rolls back the open transaction if one exists. |
Data Manipulation Language |
|
The |
Procedural |
|
The |
Procedural |
|
|
Security Management |
|
Retrieves data from a table. |
Data Manipulation Language |
|
Inspect the setting of a global engine variable. |
Operational |
|
Sets a session variable. |
Operational |
|
Sets a sync variable to propagate to all aggregators in the cluster. |
Operational |
|
Sets a sync variable. |
Operational |
|
Sets an engine variable globally. |
Operational |
|
Sets a sync variable to propagate to all leaves in the cluster. |
Operational |
|
Sets a user’s password. |
Security Management |
|
Lists any existing user-defined aggregate functions (UDAF) in the specified database. |
Procedural |
|
Show the aggregators in a cluster. |
Cluster Management |
|
Displays information about the CDC-in pipelines. |
Show Commands |
|
Shows information about partitions within a cluster including replication role, replication state, and replication position, across all databases. |
Cluster Management |
|
Show the list of columns within a given table. |
Show Commands |
|
Shows the function definition and other attributes for a user-defined aggregate function (UDAF). |
Procedural |
|
Outputs configuration information about an existing user-defined function, including user-defined scalar value functions (UDFs) and user-defined table-valued functions (TVFs). |
Procedural |
|
Shows the |
Show Commands |
|
You can use the |
Show Commands |
|
Shows the |
Show Commands |
|
Shows the definition, procedure body, and other attributes for a stored procedure. |
Procedural |
|
Shows the |
Show Commands |
|
Shows the |
Show Commands |
|
Shows SingleStore database status. |
Show Commands |
|
Shows the list of databases that exist on this SingleStore instance. |
Show Commands |
|
Displays errors as a result of execution of an invalid statement. |
Show Commands |
|
Displays metrics for the JLucene full-text search in JSON format. |
Show Commands |
|
Shows the status of the full-text V2 service. |
Show Commands |
|
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 SingleStore server status information. |
Show Commands |
|
Show permissions (privileges) for the specified user (or role). |
Security Management |
|
Shows the list of groups on a cluster/workspace group. |
Security Management |
|
Show groups for a specific role. |
Security Management |
|
Show groups for a specific user. |
Security Management |
|
Displays the list of indexes associated with a given table. |
Show Commands |
|
Shows the leaf status. |
Show Commands |
|
Shows the leaves in a cluster. |
Cluster Management |
|
Show all connection links on S3, Azure, GCS, HDFS, or Kafka for a permitted user. |
Security Management |
|
This command is deprecated. |
Show Commands |
|
This command is deprecated. |
Show Commands |
|
Shows partitions and relevant information about them. |
Show Commands |
|
You can see all existing pipelines in a database and their current state by using the |
Show Commands |
|
Displays the EXPLAIN plan of a query as per the plan ID. |
Show Commands |
|
Shows all query statements that SingleStore has compiled and executed, as well as cumulative query execution statistics associated with each plan. |
Show Commands |
|
Shows details about currently running threads. |
Show Commands |
|
Lists existing stored procedures in the current or the specified database. |
Procedural |
|
Shows projections created on the current database or specified table. |
Show Commands |
|
Shows the status of the currently executing |
Cluster Management |
|
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 |
|
Shows the list of all connections between the replication master and secondary databases. |
Operational |
|
Collects troubleshooting data for query optimizer and code generation issues for queries that fail in the compilation phase. |
Show Commands |
|
Lists all resource pools and their field values. |
Resource Pool |
|
Shows the list of roles on a cluster/workspace group. |
Security Management |
|
Show roles for a specific group. |
Security Management |
|
Show roles for a specific user. |
Security Management |
|
Shows the list of schemas that exist on this SingleStore instance. |
Show Commands |
|
Show SingleStore server status information for a session. |
Show Commands |
|
Shows SingleStore server status information with detailed SingleStore-specific memory and performance metrics. |
Show Commands |
|
Show tables status information in a SingleStore database. |
Show Commands |
|
Shows the list of tables in the currently selected database, or in another database if db_ |
Show Commands |
|
Show users on a SingleStore cluster. |
Security Management |
|
Show users for a specific group. |
Security Management |
|
Show users for a specific role. |
Security Management |
|
Shows a list of variable bindings. |
Show Commands |
|
Display warnings as a result of an invalid statement execution. |
Show Commands |
|
Takes a snapshot of the given database and truncates the transaction log. |
Operational |
|
The |
Pipeline |
|
The |
Pipeline |
|
Stops replicating a database, and promotes the secondary database to the primary database. |
Operational |
|
Synchronizes any currently committed data in the database with the remote object storage. |
Operational |
|
It is a table-valued function that converts a PSQL array to a set of rows. |
Data Manipulation Language |
|
The |
Pipeline |
|
Allows you to convert a dynamic SQL statement to a query type value. |
Procedural |
|
Triggers the garbage collector on the source aggregator and propagates to all the leaves. |
Cluster Management |
|
Removes all rows from the table. |
Data Definition Language |
|
Combines results from multiple SELECT statements. |
Data Manipulation Language |
|
Releases all table locks on all databases in the cluster. |
Data Definition Language |
|
|
Data Manipulation Language |
|
Use a database or schema. |
Data Manipulation Language |
|
Specifies a temporary named result set, referred to as a Common Table Expression (CTE). |
Data Manipulation Language |
Last modified: June 3, 2025