sql, vsql: Non-standard Table Options

By ChatGPT.

Here's how you can add non-standard options (such as indexes, constraints, and custom storage settings) to tables in different databases like MS SQL, Oracle, and Cassandra:

1. MS SQL Server: Adding Non-Standard Options

In MS SQL Server, you can add various options like constraints, indexes, and filegroups when creating or altering tables. Here's an example:

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID INT NOT NULL,
    Amount DECIMAL(10, 2),
    CONSTRAINT FK_Customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
)
ON [PRIMARY]  -- Non-standard option: Storing the table on a specific filegroup
WITH (DATA_COMPRESSION = PAGE)  -- Non-standard option: Data compression applied

This example adds a filegroup option and data compression to the table creation process, which are non-standard settings.

2. Oracle: Adding Non-Standard Options

In Oracle, you can customize tables with options like tablespaces, constraints, and indexes. Here's an example:

CREATE TABLE Orders (
    OrderID NUMBER PRIMARY KEY,
    OrderDate DATE NOT NULL,
    CustomerID NUMBER NOT NULL,
    Amount NUMBER(10, 2),
    CONSTRAINT FK_Customer FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
)
TABLESPACE users -- Non-standard option: Storing the table in a specific tablespace
PCTFREE 10 -- Non-standard option: Specifies the percentage of space reserved for updates
STORAGE (INITIAL 100K NEXT 50K) -- Non-standard option: Customizing storage parameters
LOGGING -- Non-standard option: Enabling logging for the table

Here, tablespace, PCTFREE, and STORAGE options are non-standard options that influence how the table is created and managed within Oracle.

3. Cassandra: Adding Non-Standard Options

Cassandra uses different terminology, but non-standard options like replication, compression, and caching can be applied. Here's an example:

CREATE TABLE orders (
    order_id UUID PRIMARY KEY,
    order_date TIMESTAMP,
    customer_id UUID,
    amount DECIMAL
)
WITH compaction = {'class': 'LeveledCompactionStrategy'}  -- Non-standard option: Custom compaction strategy
AND compression = {'sstable_compression': 'LZ4Compressor'}  -- Non-standard option: Custom compression
AND caching = {'keys': 'ALL', 'rows_per_partition': '10'}  -- Non-standard option: Caching settings
AND read_repair_chance = 0.1;  -- Non-standard option: Read repair probability

Here, compaction, compression, and caching options control performance and storage behavior, which are considered non-standard settings for the table in Cassandra.

Summary:

MS SQL Server: Filegroups, data compression, constraints, etc.
Oracle: Tablespaces, storage parameters, PCTFREE, logging, etc.
Cassandra: Compaction, compression, caching, read repair probability, etc.

These examples show how you can apply non-standard options to tables during creation or modification in each of these databases.

In PostgreSQL, you can specify various non-standard options when creating or altering tables. These options control aspects such as storage, indexing, constraints, and performance tuning. Here are some examples of non-standard options you can apply in PostgreSQL:

1. Tablespace Option

You can specify a custom tablespace for the table data, which determines where the table's data will be physically stored.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    amount NUMERIC(10, 2)
)
TABLESPACE my_custom_tablespace;  -- Non-standard option: Specifying a tablespace

2. Fillfactor Option

The FILLFACTOR option controls how much of each page is filled with data before moving on to the next page. It allows for reserving space for updates without causing page splits.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    amount NUMERIC(10, 2)
)
WITH (FILLFACTOR = 70);  -- Non-standard option: Reserving space for updates

3. Partitioning

PostgreSQL supports table partitioning, where a table is divided into smaller, more manageable pieces (partitions). Partitioning can be used to improve performance, particularly for large tables.

CREATE TABLE orders (
    order_id SERIAL,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    amount NUMERIC(10, 2)
) PARTITION BY RANGE (order_date);  -- Non-standard option: Creating a partitioned table

4. Storage Parameters

PostgreSQL allows you to customize various storage parameters for tables, such as the amount of memory reserved for the table.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    amount NUMERIC(10, 2)
)
WITH (autovacuum_enabled = false, toast_tuple_target = 2048);  -- Non-standard options: Disabling autovacuum and customizing TOAST settings

5. Inheritance

PostgreSQL allows for table inheritance, which lets you create a table that inherits from another table. This can be used to create shared structures among multiple tables.

CREATE TABLE parent_table (
    id SERIAL PRIMARY KEY,
    common_column TEXT
);

CREATE TABLE child_table (
    specific_column INT
) INHERITS (parent_table);  -- Non-standard option: Creating an inherited table

6. UNLOGGED Tables

An unlogged table is a non-standard option where changes to the table are not written to the write-ahead log (WAL). This can improve performance but at the cost of durability in the event of a crash.

CREATE UNLOGGED TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    amount NUMERIC(10, 2)
);  -- Non-standard option: Creating an unlogged table for performance gains

7. Foreign Data Wrappers (FDW)

PostgreSQL allows tables to access external data sources using Foreign Data Wrappers. This is a non-standard option for integrating external data into your database.

CREATE FOREIGN TABLE foreign_orders (
    order_id INT,
    order_date DATE,
    customer_id INT,
    amount NUMERIC(10, 2)
) SERVER my_foreign_server
OPTIONS (schema_name 'public', table_name 'remote_orders');  -- Non-standard option: Creating a foreign table

8. Parallel Workers

You can specify the number of parallel workers that PostgreSQL can use when scanning large tables to speed up queries.

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id INT NOT NULL,
    amount NUMERIC(10, 2)
)
WITH (parallel_workers = 4);  -- Non-standard option: Defining parallel workers for faster querying

Summary of Non-Standard Options for PostgreSQL:

Tablespace: Specifies where the table’s data is stored.
Fillfactor: Controls page fill percentage.
Partitioning: Splits the table into manageable pieces.
Storage Parameters: Customizes table storage behaviors like autovacuum and TOAST.
Inheritance: Creates shared structures across tables.
UNLOGGED Tables: Disables WAL for better performance.
Foreign Data Wrappers (FDW): Integrates external data sources.
Parallel Workers: Specifies the number of workers used in parallel queries.

These non-standard options provide flexibility in terms of performance optimization, data organization, and storage customization in PostgreSQL.

voedger / kb