The Database Cheatsheet: Essential Table Operations

Are you tired of remembering different database commands or scouring the internet for a complete source?

Well, you’ve come to the right place! This blog series, The Database Cheatsheet, contains everything you need to know about database commands, covering everything developers and database professionals need to know to use and operate their databases.

First, here is a quick disclaimer: these commands are specifically tailored for SingleStore. If you plan to use them on other database platforms, ensure you check the documentation to confirm compatibility or required syntax changes. Need a database to get started with? Sign up for a free SingleStore Helios® trial to get up and running in minutes.

SingleStore Database Cheat Sheet

SingleStore core concepts
SQL + Kai commands
Vector operations tips

Try Notebook

In this second blog, we’re focusing on table operations — the cornerstone of any database system. In SingleStore, tables can be distributed, reference-based or columnstore, each serving unique performance and scalability needs. By the end of this article, you’ll know how to create different table types, manage them and optimize for your use case. Remember while these examples are tailored for SingleStore, minor tweaks may be necessary if you’re using other SQL platforms.

Create a table

One of the most basic things you need within a SQL database is a place to store data. That place is a table. The following command is the basic syntax for creating a distributed table.

CREATE TABLE posts (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    category VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    SHARD KEY (id)  -- Required for distributed tables
);

By running this command, you will:

Create a distributed table named posts horizontally scaled across multiple nodes.
The SHARD KEY (id) clause is essential for distributing data evenly based on the id column.

Once the command is done executing, a new table named posts will appear in your active database. Rows inserted will be distributed across nodes, allowing for faster reads and writes at scale.

Create a reference table

Need a table that is the perfect spot for small, commonly accessed lookup data, ensuring quick joins without network hops? This is where reference tables can come into play. The following shows the basic syntax for creating one:

CREATE REFERENCE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(50)
    -- No SHARD KEY needed for reference tables
);

By running this command, you will create a reference table named categories. This reference table will be fully replicated on each node in the cluster.

Create a columnstore table

Planning to run heavier analytics workloads on a table? This is the perfect use case for a columnstore table. The command to create a columnstore table looks the same as a basic CREATE TABLE command but includes an ENGINE=columnstore clause. This clause tells SingleStore to store data in a column-oriented format, optimizing for large-scale reads and aggregations. Here is an example of this command, including a SORT KEY (timestamp) field that improves query performance on time-based lookups or aggregations.

CREATE TABLE analytics (
    id BIGINT,
    event_type VARCHAR(50),
    timestamp TIMESTAMP,
    data JSON,
    SORT KEY (timestamp),
    SHARD KEY (id)  -- Required for columnstore tables
) ENGINE=columnstore;

After executing the command, users will have a column-store table suited for analytics-heavy queries, with each column stored separately for efficient data compression and faster queries on large data sets.

Table management

Need to know more about the tables within your database? Various commands exist to inspect and delete/drop tables from your database. These commands include:


-- Show tables
SHOW TABLES;

This command lists all tables in your current database. Great for a quick inventory check.


-- Describe table
DESCRIBE table_name;

This command gives you the structure of the table supplied as a parameter in the command, including column names, types and keys.


-- Drop table
DROP TABLE table_name;

This command removes the referenced table completely from the database. Use this with caution, as once a table is gone, it’s irretrievable.

Try SingleStore free

In this blog, we examined the different ways to create and manage tables in SingleStore. Whether you need a horizontally scaled distributed table, a fully replicated reference table or an analytics-optimized columnstore table, SingleStore has you covered. Check out our other Database Cheatsheet articles or head to the notebook in SingleStore Spaces for easy reference.

Disclaimer

These commands are specifically tailored for SingleStore. If you plan to use them on other SQL platforms, ensure you check the documentation to confirm compatibility or required syntax changes. Need a database to get started with? Sign up for a free SingleStore Helios® trial to get up and running in minutes.