Database governance built for humans and agents — controlling changes and access across every major database.
Go cicddata-governancedata-maskingdata-securitydatabase-accessdatabase-governancedevopsdevsecopsflywaygitopsliquibasemongodbmysqloraclepostgresqlschema-migrationssecuritysql-clientsqlservertidb
- description: users
schema: |
CREATE TABLE dbo.users (
id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(255) NOT NULL,
email NVARCHAR(255) UNIQUE
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "users",
"columns": [
{
"name": "id",
"position": 1,
"type": "INT",
"is_identity": true,
"identity_seed": 1,
"identity_increment": 1
},
{
"name": "name",
"position": 2,
"type": "NVARCHAR(255)"
},
{
"name": "email",
"position": 3,
"nullable": true,
"type": "NVARCHAR(255)"
}
],
"indexes": [
{
"name": "PK_users_1",
"expressions": [
"id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
},
{
"name": "UQ_users_2",
"expressions": [
"email"
],
"descending": [
false
],
"unique": true,
"is_constraint": true
}
]
}
]
}
]
}
- description: departments_employees
schema: |
CREATE TABLE dbo.departments (
dept_id INT PRIMARY KEY,
dept_name NVARCHAR(100) NOT NULL
);
CREATE TABLE dbo.employees (
emp_id INT PRIMARY KEY,
emp_name NVARCHAR(100) NOT NULL,
dept_id INT,
CONSTRAINT FK_employees_departments FOREIGN KEY (dept_id)
REFERENCES dbo.departments(dept_id) ON DELETE CASCADE
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "departments",
"columns": [
{
"name": "dept_id",
"position": 1,
"type": "INT"
},
{
"name": "dept_name",
"position": 2,
"type": "NVARCHAR(100)"
}
],
"indexes": [
{
"name": "PK_departments_1",
"expressions": [
"dept_id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
}
]
},
{
"name": "employees",
"columns": [
{
"name": "emp_id",
"position": 1,
"type": "INT"
},
{
"name": "emp_name",
"position": 2,
"type": "NVARCHAR(100)"
},
{
"name": "dept_id",
"position": 3,
"nullable": true,
"type": "INT"
}
],
"indexes": [
{
"name": "PK_employees_2",
"expressions": [
"emp_id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
}
],
"foreign_keys": [
{
"name": "FK_employees_departments",
"columns": [
"dept_id"
],
"referenced_schema": "dbo",
"referenced_table": "departments",
"referenced_columns": [
"dept_id"
],
"on_delete": "CASCADE",
"on_update": "NO ACTION"
}
]
}
]
}
]
}
- description: products_1
schema: |
CREATE TABLE dbo.products (
product_id INT PRIMARY KEY,
price DECIMAL(10,2),
CONSTRAINT CK_price_positive CHECK (price > 0)
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "products",
"columns": [
{
"name": "product_id",
"position": 1,
"type": "INT"
},
{
"name": "price",
"position": 2,
"nullable": true,
"type": "DECIMAL(10,2)"
}
],
"indexes": [
{
"name": "PK_products_1",
"expressions": [
"product_id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
}
],
"check_constraints": [
{
"name": "CK_price_positive",
"expression": "price \u003e 0"
}
]
}
]
}
]
}
- description: products_2
schema: |
CREATE TABLE dbo.products (
id INT PRIMARY KEY,
name NVARCHAR(255),
category NVARCHAR(50)
);
CREATE INDEX IX_products_category ON dbo.products (category);
CREATE UNIQUE INDEX UX_products_name ON dbo.products (name);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "products",
"columns": [
{
"name": "id",
"position": 1,
"type": "INT"
},
{
"name": "name",
"position": 2,
"nullable": true,
"type": "NVARCHAR(255)"
},
{
"name": "category",
"position": 3,
"nullable": true,
"type": "NVARCHAR(50)"
}
],
"indexes": [
{
"name": "PK_products_1",
"expressions": [
"id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
},
{
"name": "IX_products_category",
"expressions": [
"category"
],
"descending": [
false
]
},
{
"name": "UX_products_name",
"expressions": [
"name"
],
"descending": [
false
],
"unique": true
}
]
}
]
}
]
}
- description: orders
schema: |
CREATE TABLE dbo.orders (
order_id INT,
customer_id INT,
order_date DATETIME,
CONSTRAINT PK_orders PRIMARY KEY CLUSTERED (order_id)
);
CREATE NONCLUSTERED INDEX IX_orders_customer ON dbo.orders (customer_id);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "orders",
"columns": [
{
"name": "order_id",
"position": 1,
"nullable": true,
"type": "INT"
},
{
"name": "customer_id",
"position": 2,
"nullable": true,
"type": "INT"
},
{
"name": "order_date",
"position": 3,
"nullable": true,
"type": "DATETIME"
}
],
"indexes": [
{
"name": "PK_orders",
"expressions": [
"order_id"
],
"descending": [
false
],
"type": "CLUSTERED",
"unique": true,
"primary": true,
"is_constraint": true
},
{
"name": "IX_orders_customer",
"expressions": [
"customer_id"
],
"descending": [
false
],
"type": "NONCLUSTERED"
}
]
}
]
}
]
}
- description: active_users
schema: |
CREATE VIEW dbo.active_users AS
SELECT id, name, email
FROM users
WHERE is_active = 1;
metadata: |
{
"schemas": [
{
"name": "dbo",
"views": [
{
"name": "active_users",
"definition": "CREATE VIEW dbo.active_users AS\nSELECT id, name, email\nFROM users\nWHERE is_active = 1;"
}
]
}
]
}
- description: getuserbyid
schema: |
CREATE PROCEDURE dbo.GetUserById
@UserId INT
AS
BEGIN
SELECT * FROM users WHERE id = @UserId;
END;
metadata: |
{
"schemas": [
{
"name": "dbo",
"procedures": [
{
"name": "GetUserById",
"definition": "CREATE PROCEDURE dbo.GetUserById\n @UserId INT\nAS\nBEGIN\n SELECT * FROM users WHERE id = @UserId;\nEND;"
}
]
}
]
}
- description: getfullname
schema: |
CREATE FUNCTION dbo.GetFullName
(
@FirstName NVARCHAR(50),
@LastName NVARCHAR(50)
)
RETURNS NVARCHAR(101)
AS
BEGIN
RETURN @FirstName + ' ' + @LastName;
END;
metadata: |
{
"schemas": [
{
"name": "dbo",
"functions": [
{
"name": "GetFullName",
"definition": "CREATE FUNCTION dbo.GetFullName\n(\n @FirstName NVARCHAR(50),\n @LastName NVARCHAR(50)\n)\nRETURNS NVARCHAR(101)\nAS\nBEGIN\n RETURN @FirstName + ' ' + @LastName;\nEND;"
}
]
}
]
}
- description: ordernumberseq
schema: |
CREATE SEQUENCE dbo.OrderNumberSeq
AS INT
START WITH 1000
INCREMENT BY 1;
metadata: |
{
"schemas": [
{
"name": "dbo",
"sequences": [
{
"name": "OrderNumberSeq",
"data_type": "INT"
}
]
}
]
}
- description: sales_hr_orders
schema: |
CREATE SCHEMA sales;
CREATE SCHEMA hr;
CREATE TABLE sales.orders (
order_id INT PRIMARY KEY,
amount DECIMAL(10,2)
);
CREATE TABLE hr.employees (
emp_id INT PRIMARY KEY,
name NVARCHAR(100)
);
metadata: |
{
"schemas": [
{
"name": "hr",
"tables": [
{
"name": "employees",
"columns": [
{
"name": "emp_id",
"position": 1,
"type": "INT"
},
{
"name": "name",
"position": 2,
"nullable": true,
"type": "NVARCHAR(100)"
}
],
"indexes": [
{
"name": "PK_employees_2",
"expressions": [
"emp_id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
}
]
}
]
},
{
"name": "sales",
"tables": [
{
"name": "orders",
"columns": [
{
"name": "order_id",
"position": 1,
"type": "INT"
},
{
"name": "amount",
"position": 2,
"nullable": true,
"type": "DECIMAL(10,2)"
}
],
"indexes": [
{
"name": "PK_orders_1",
"expressions": [
"order_id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
}
]
}
]
}
]
}
- description: settings
schema: |
CREATE TABLE dbo.settings (
id INT PRIMARY KEY,
created_at DATETIME DEFAULT GETDATE(),
is_active BIT DEFAULT 1,
status NVARCHAR(20) DEFAULT N'pending'
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "settings",
"columns": [
{
"name": "id",
"position": 1,
"type": "INT"
},
{
"name": "created_at",
"position": 2,
"default": "GETDATE()",
"nullable": true,
"type": "DATETIME"
},
{
"name": "is_active",
"position": 3,
"default": "1",
"nullable": true,
"type": "BIT"
},
{
"name": "status",
"position": 4,
"default": "N'pending'",
"nullable": true,
"type": "NVARCHAR(20)"
}
],
"indexes": [
{
"name": "PK_settings_1",
"expressions": [
"id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
}
]
}
]
}
]
}
- description: logs
schema: |
CREATE TABLE dbo.logs (
id INT PRIMARY KEY,
user_id INT,
action NVARCHAR(50),
timestamp DATETIME
);
CREATE INDEX IX_logs_user_action ON dbo.logs (user_id ASC, action DESC, timestamp DESC);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "logs",
"columns": [
{
"name": "id",
"position": 1,
"type": "INT"
},
{
"name": "user_id",
"position": 2,
"nullable": true,
"type": "INT"
},
{
"name": "action",
"position": 3,
"nullable": true,
"type": "NVARCHAR(50)"
},
{
"name": "timestamp",
"position": 4,
"nullable": true,
"type": "DATETIME"
}
],
"indexes": [
{
"name": "PK_logs_1",
"expressions": [
"id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
},
{
"name": "IX_logs_user_action",
"expressions": [
"user_id",
"action",
"timestamp"
],
"descending": [
false,
true,
true
]
}
]
}
]
}
]
}
- description: test_1
schema: |
CREATE TABLE dbo.test (
id INT NOT NULL PRIMARY KEY CLUSTERED,
email NVARCHAR(255) UNIQUE NONCLUSTERED
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "test",
"columns": [
{
"name": "id",
"position": 1,
"type": "INT"
},
{
"name": "email",
"position": 2,
"nullable": true,
"type": "NVARCHAR(255)"
}
],
"indexes": [
{
"name": "PK_test_1",
"expressions": [
"id"
],
"descending": [
false
],
"type": "CLUSTERED",
"unique": true,
"primary": true,
"is_constraint": true
},
{
"name": "UQ_test_2",
"expressions": [
"email"
],
"descending": [
false
],
"type": "NONCLUSTERED",
"unique": true,
"is_constraint": true
}
]
}
]
}
]
}
- description: test_2
schema: |
CREATE TABLE dbo.test (
id INT CONSTRAINT PK_test_id PRIMARY KEY,
email NVARCHAR(255) CONSTRAINT UQ_test_email UNIQUE
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "test",
"columns": [
{
"name": "id",
"position": 1,
"type": "INT"
},
{
"name": "email",
"position": 2,
"nullable": true,
"type": "NVARCHAR(255)"
}
],
"indexes": [
{
"name": "PK_test_id",
"expressions": [
"id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
},
{
"name": "UQ_test_email",
"expressions": [
"email"
],
"descending": [
false
],
"unique": true,
"is_constraint": true
}
]
}
]
}
]
}
- description: customers
schema: |
CREATE TABLE dbo.customers (
customer_id INT IDENTITY(1,1) NOT NULL,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
email VARCHAR(100),
phone VARCHAR(20),
address NVARCHAR(200),
city NVARCHAR(50),
state CHAR(2),
zip_code VARCHAR(10),
country VARCHAR(50) DEFAULT 'USA',
created_date DATETIME DEFAULT GETDATE(),
modified_date DATETIME,
is_active BIT DEFAULT 1,
credit_limit DECIMAL(10,2) DEFAULT 0.00,
CONSTRAINT PK_customers PRIMARY KEY CLUSTERED (customer_id),
CONSTRAINT UQ_customers_email UNIQUE (email),
CONSTRAINT CK_customers_credit_limit CHECK (credit_limit >= 0)
);
CREATE NONCLUSTERED INDEX IX_customers_name ON dbo.customers (last_name, first_name);
CREATE NONCLUSTERED INDEX IX_customers_email ON dbo.customers (email) WHERE email IS NOT NULL;
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "customers",
"columns": [
{
"name": "customer_id",
"position": 1,
"type": "INT",
"is_identity": true,
"identity_seed": 1,
"identity_increment": 1
},
{
"name": "first_name",
"position": 2,
"type": "VARCHAR(50)"
},
{
"name": "last_name",
"position": 3,
"type": "VARCHAR(50)"
},
{
"name": "email",
"position": 4,
"nullable": true,
"type": "VARCHAR(100)"
},
{
"name": "phone",
"position": 5,
"nullable": true,
"type": "VARCHAR(20)"
},
{
"name": "address",
"position": 6,
"nullable": true,
"type": "NVARCHAR(200)"
},
{
"name": "city",
"position": 7,
"nullable": true,
"type": "NVARCHAR(50)"
},
{
"name": "state",
"position": 8,
"nullable": true,
"type": "CHAR(2)"
},
{
"name": "zip_code",
"position": 9,
"nullable": true,
"type": "VARCHAR(10)"
},
{
"name": "country",
"position": 10,
"default": "'USA'",
"nullable": true,
"type": "VARCHAR(50)"
},
{
"name": "created_date",
"position": 11,
"default": "GETDATE()",
"nullable": true,
"type": "DATETIME"
},
{
"name": "modified_date",
"position": 12,
"nullable": true,
"type": "DATETIME"
},
{
"name": "is_active",
"position": 13,
"default": "1",
"nullable": true,
"type": "BIT"
},
{
"name": "credit_limit",
"position": 14,
"default": "0.00",
"nullable": true,
"type": "DECIMAL(10,2)"
}
],
"indexes": [
{
"name": "PK_customers",
"expressions": [
"customer_id"
],
"descending": [
false
],
"type": "CLUSTERED",
"unique": true,
"primary": true,
"is_constraint": true
},
{
"name": "UQ_customers_email",
"expressions": [
"email"
],
"descending": [
false
],
"unique": true,
"is_constraint": true
},
{
"name": "IX_customers_name",
"expressions": [
"last_name",
"first_name"
],
"descending": [
false,
false
],
"type": "NONCLUSTERED"
},
{
"name": "IX_customers_email",
"expressions": [
"email"
],
"descending": [
false
],
"type": "NONCLUSTERED"
}
],
"check_constraints": [
{
"name": "CK_customers_credit_limit",
"expression": "credit_limit \u003e= 0"
}
]
}
]
}
]
}
- description: inventory
schema: |
CREATE TABLE dbo.inventory (
product_id INT NOT NULL,
warehouse_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 0,
last_updated DATETIME2 DEFAULT SYSDATETIME(),
CONSTRAINT PK_inventory PRIMARY KEY (product_id, warehouse_id),
CONSTRAINT CK_inventory_quantity CHECK (quantity >= 0)
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "inventory",
"columns": [
{
"name": "product_id",
"position": 1,
"type": "INT"
},
{
"name": "warehouse_id",
"position": 2,
"type": "INT"
},
{
"name": "quantity",
"position": 3,
"default": "0",
"type": "INT"
},
{
"name": "last_updated",
"position": 4,
"default": "SYSDATETIME()",
"nullable": true,
"type": "DATETIME2"
}
],
"indexes": [
{
"name": "PK_inventory",
"expressions": [
"product_id",
"warehouse_id"
],
"descending": [
false,
false
],
"unique": true,
"primary": true,
"is_constraint": true
}
],
"check_constraints": [
{
"name": "CK_inventory_quantity",
"expression": "quantity \u003e= 0"
}
]
}
]
}
]
}
- description: audit_log
schema: |
CREATE TABLE dbo.audit_log (
log_id BIGINT IDENTITY(1,1) PRIMARY KEY,
table_name SYSNAME NOT NULL,
operation CHAR(1) NOT NULL,
user_name NVARCHAR(128) DEFAULT SUSER_SNAME(),
timestamp DATETIME2 DEFAULT SYSDATETIME(),
old_values NVARCHAR(MAX),
new_values NVARCHAR(MAX),
CONSTRAINT CK_operation CHECK (operation IN ('I', 'U', 'D'))
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "audit_log",
"columns": [
{
"name": "log_id",
"position": 1,
"type": "BIGINT",
"is_identity": true,
"identity_seed": 1,
"identity_increment": 1
},
{
"name": "table_name",
"position": 2,
"type": "SYSNAME"
},
{
"name": "operation",
"position": 3,
"type": "CHAR(1)"
},
{
"name": "user_name",
"position": 4,
"default": "SUSER_SNAME()",
"nullable": true,
"type": "NVARCHAR(128)"
},
{
"name": "timestamp",
"position": 5,
"default": "SYSDATETIME()",
"nullable": true,
"type": "DATETIME2"
},
{
"name": "old_values",
"position": 6,
"nullable": true,
"type": "NVARCHAR(MAX)"
},
{
"name": "new_values",
"position": 7,
"nullable": true,
"type": "NVARCHAR(MAX)"
}
],
"indexes": [
{
"name": "PK_audit_log_1",
"expressions": [
"log_id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
}
],
"check_constraints": [
{
"name": "CK_operation",
"expression": "operation IN ('I', 'U', 'D')"
}
]
}
]
}
]
}
- description: documents
schema: |
CREATE TABLE dbo.documents (
doc_id UNIQUEIDENTIFIER DEFAULT NEWID() PRIMARY KEY,
title NVARCHAR(200) NOT NULL,
content NVARCHAR(MAX),
file_data VARBINARY(MAX),
file_name NVARCHAR(255),
file_size BIGINT,
mime_type VARCHAR(100),
created_by INT NOT NULL,
created_date DATETIMEOFFSET DEFAULT SYSDATETIMEOFFSET(),
version INT DEFAULT 1,
is_published BIT DEFAULT 0
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "documents",
"columns": [
{
"name": "doc_id",
"position": 1,
"default": "NEWID()",
"type": "UNIQUEIDENTIFIER"
},
{
"name": "title",
"position": 2,
"type": "NVARCHAR(200)"
},
{
"name": "content",
"position": 3,
"nullable": true,
"type": "NVARCHAR(MAX)"
},
{
"name": "file_data",
"position": 4,
"nullable": true,
"type": "VARBINARY(MAX)"
},
{
"name": "file_name",
"position": 5,
"nullable": true,
"type": "NVARCHAR(255)"
},
{
"name": "file_size",
"position": 6,
"nullable": true,
"type": "BIGINT"
},
{
"name": "mime_type",
"position": 7,
"nullable": true,
"type": "VARCHAR(100)"
},
{
"name": "created_by",
"position": 8,
"type": "INT"
},
{
"name": "created_date",
"position": 9,
"default": "SYSDATETIMEOFFSET()",
"nullable": true,
"type": "DATETIMEOFFSET"
},
{
"name": "version",
"position": 10,
"default": "1",
"nullable": true,
"type": "INT"
},
{
"name": "is_published",
"position": 11,
"default": "0",
"nullable": true,
"type": "BIT"
}
],
"indexes": [
{
"name": "PK_documents_1",
"expressions": [
"doc_id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
}
]
}
]
}
]
}
- description: hierarchical_data
schema: |
CREATE TABLE dbo.hierarchical_data (
node_id INT IDENTITY(1,1) PRIMARY KEY,
parent_id INT NULL,
node_name NVARCHAR(100) NOT NULL,
node_path HIERARCHYID,
level AS node_path.GetLevel(),
CONSTRAINT FK_hierarchical_self FOREIGN KEY (parent_id)
REFERENCES dbo.hierarchical_data(node_id)
);
CREATE INDEX IX_hierarchical_path ON dbo.hierarchical_data (node_path);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "hierarchical_data",
"columns": [
{
"name": "node_id",
"position": 1,
"type": "INT",
"is_identity": true,
"identity_seed": 1,
"identity_increment": 1
},
{
"name": "parent_id",
"position": 2,
"nullable": true,
"type": "INT"
},
{
"name": "node_name",
"position": 3,
"type": "NVARCHAR(100)"
},
{
"name": "node_path",
"position": 4,
"nullable": true,
"type": "HIERARCHYID"
},
{
"name": "level",
"position": 5,
"nullable": true
}
],
"indexes": [
{
"name": "PK_hierarchical_data_1",
"expressions": [
"node_id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
},
{
"name": "IX_hierarchical_path",
"expressions": [
"node_path"
],
"descending": [
false
]
}
],
"foreign_keys": [
{
"name": "FK_hierarchical_self",
"columns": [
"parent_id"
],
"referenced_schema": "dbo",
"referenced_table": "hierarchical_data",
"referenced_columns": [
"node_id"
],
"on_delete": "NO ACTION",
"on_update": "NO ACTION"
}
]
}
]
}
]
}
- description: edge_cases
schema: |
CREATE TABLE dbo.edge_cases (
[Column With Spaces] INT,
[Column-With-Dashes] NVARCHAR(50),
[Column.With.Dots] VARCHAR(100),
[列名] NVARCHAR(50),
[SELECT] INT,
[FROM] VARCHAR(50),
[WHERE] BIT
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "edge_cases",
"columns": [
{
"name": "Column With Spaces",
"position": 1,
"nullable": true,
"type": "INT"
},
{
"name": "Column-With-Dashes",
"position": 2,
"nullable": true,
"type": "NVARCHAR(50)"
},
{
"name": "Column.With.Dots",
"position": 3,
"nullable": true,
"type": "VARCHAR(100)"
},
{
"name": "列名",
"position": 4,
"nullable": true,
"type": "NVARCHAR(50)"
},
{
"name": "SELECT",
"position": 5,
"nullable": true,
"type": "INT"
},
{
"name": "FROM",
"position": 6,
"nullable": true,
"type": "VARCHAR(50)"
},
{
"name": "WHERE",
"position": 7,
"nullable": true,
"type": "BIT"
}
]
}
]
}
]
}
- description: all_data_types
schema: |
CREATE TABLE dbo.all_data_types (
-- Exact numerics
col_bit BIT,
col_tinyint TINYINT,
col_smallint SMALLINT,
col_int INT,
col_bigint BIGINT,
col_decimal DECIMAL(18,4),
col_numeric NUMERIC(15,2),
col_smallmoney SMALLMONEY,
col_money MONEY,
-- Approximate numerics
col_float FLOAT,
col_real REAL,
-- Date and time
col_date DATE,
col_time TIME,
col_datetime DATETIME,
col_datetime2 DATETIME2,
col_smalldatetime SMALLDATETIME,
col_datetimeoffset DATETIMEOFFSET,
-- Character strings
col_char CHAR(10),
col_varchar VARCHAR(50),
col_varchar_max VARCHAR(MAX),
col_text TEXT,
-- Unicode character strings
col_nchar NCHAR(10),
col_nvarchar NVARCHAR(50),
col_nvarchar_max NVARCHAR(MAX),
col_ntext NTEXT,
-- Binary strings
col_binary BINARY(10),
col_varbinary VARBINARY(50),
col_varbinary_max VARBINARY(MAX),
col_image IMAGE,
-- Other data types
col_uniqueidentifier UNIQUEIDENTIFIER,
col_xml XML,
col_timestamp TIMESTAMP,
col_hierarchyid HIERARCHYID,
col_geography GEOGRAPHY,
col_geometry GEOMETRY,
col_sql_variant SQL_VARIANT
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "all_data_types",
"columns": [
{
"name": "col_bit",
"position": 1,
"nullable": true,
"type": "BIT"
},
{
"name": "col_tinyint",
"position": 2,
"nullable": true,
"type": "TINYINT"
},
{
"name": "col_smallint",
"position": 3,
"nullable": true,
"type": "SMALLINT"
},
{
"name": "col_int",
"position": 4,
"nullable": true,
"type": "INT"
},
{
"name": "col_bigint",
"position": 5,
"nullable": true,
"type": "BIGINT"
},
{
"name": "col_decimal",
"position": 6,
"nullable": true,
"type": "DECIMAL(18,4)"
},
{
"name": "col_numeric",
"position": 7,
"nullable": true,
"type": "NUMERIC(15,2)"
},
{
"name": "col_smallmoney",
"position": 8,
"nullable": true,
"type": "SMALLMONEY"
},
{
"name": "col_money",
"position": 9,
"nullable": true,
"type": "MONEY"
},
{
"name": "col_float",
"position": 10,
"nullable": true,
"type": "FLOAT"
},
{
"name": "col_real",
"position": 11,
"nullable": true,
"type": "REAL"
},
{
"name": "col_date",
"position": 12,
"nullable": true,
"type": "DATE"
},
{
"name": "col_time",
"position": 13,
"nullable": true,
"type": "TIME"
},
{
"name": "col_datetime",
"position": 14,
"nullable": true,
"type": "DATETIME"
},
{
"name": "col_datetime2",
"position": 15,
"nullable": true,
"type": "DATETIME2"
},
{
"name": "col_smalldatetime",
"position": 16,
"nullable": true,
"type": "SMALLDATETIME"
},
{
"name": "col_datetimeoffset",
"position": 17,
"nullable": true,
"type": "DATETIMEOFFSET"
},
{
"name": "col_char",
"position": 18,
"nullable": true,
"type": "CHAR(10)"
},
{
"name": "col_varchar",
"position": 19,
"nullable": true,
"type": "VARCHAR(50)"
},
{
"name": "col_varchar_max",
"position": 20,
"nullable": true,
"type": "VARCHAR(MAX)"
},
{
"name": "col_text",
"position": 21,
"nullable": true,
"type": "TEXT"
},
{
"name": "col_nchar",
"position": 22,
"nullable": true,
"type": "NCHAR(10)"
},
{
"name": "col_nvarchar",
"position": 23,
"nullable": true,
"type": "NVARCHAR(50)"
},
{
"name": "col_nvarchar_max",
"position": 24,
"nullable": true,
"type": "NVARCHAR(MAX)"
},
{
"name": "col_ntext",
"position": 25,
"nullable": true,
"type": "NTEXT"
},
{
"name": "col_binary",
"position": 26,
"nullable": true,
"type": "BINARY(10)"
},
{
"name": "col_varbinary",
"position": 27,
"nullable": true,
"type": "VARBINARY(50)"
},
{
"name": "col_varbinary_max",
"position": 28,
"nullable": true,
"type": "VARBINARY(MAX)"
},
{
"name": "col_image",
"position": 29,
"nullable": true,
"type": "IMAGE"
},
{
"name": "col_uniqueidentifier",
"position": 30,
"nullable": true,
"type": "UNIQUEIDENTIFIER"
},
{
"name": "col_xml",
"position": 31,
"nullable": true,
"type": "XML"
},
{
"name": "col_timestamp",
"position": 32,
"nullable": true,
"type": "TIMESTAMP"
},
{
"name": "col_hierarchyid",
"position": 33,
"nullable": true,
"type": "HIERARCHYID"
},
{
"name": "col_geography",
"position": 34,
"nullable": true,
"type": "GEOGRAPHY"
},
{
"name": "col_geometry",
"position": 35,
"nullable": true,
"type": "GEOMETRY"
},
{
"name": "col_sql_variant",
"position": 36,
"nullable": true,
"type": "SQL_VARIANT"
}
]
}
]
}
]
}
- description: collation_test
schema: |
CREATE TABLE dbo.collation_test (
id INT PRIMARY KEY,
case_sensitive VARCHAR(50) COLLATE Latin1_General_CS_AS,
case_insensitive VARCHAR(50) COLLATE Latin1_General_CI_AS,
accent_sensitive NVARCHAR(50) COLLATE Latin1_General_CI_AS,
accent_insensitive NVARCHAR(50) COLLATE Latin1_General_CI_AI,
binary_sort VARCHAR(50) COLLATE Latin1_General_BIN2
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "collation_test",
"columns": [
{
"name": "id",
"position": 1,
"type": "INT"
},
{
"name": "case_sensitive",
"position": 2,
"nullable": true,
"type": "VARCHAR(50)",
"collation": "Latin1_General_CS_AS"
},
{
"name": "case_insensitive",
"position": 3,
"nullable": true,
"type": "VARCHAR(50)",
"collation": "Latin1_General_CI_AS"
},
{
"name": "accent_sensitive",
"position": 4,
"nullable": true,
"type": "NVARCHAR(50)",
"collation": "Latin1_General_CI_AS"
},
{
"name": "accent_insensitive",
"position": 5,
"nullable": true,
"type": "NVARCHAR(50)",
"collation": "Latin1_General_CI_AI"
},
{
"name": "binary_sort",
"position": 6,
"nullable": true,
"type": "VARCHAR(50)",
"collation": "Latin1_General_BIN2"
}
],
"indexes": [
{
"name": "PK_collation_test_1",
"expressions": [
"id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
}
]
}
]
}
]
}
- description: sales_data
schema: |
CREATE TABLE dbo.sales_data (
sale_id INT IDENTITY(1,1) PRIMARY KEY,
product_id INT NOT NULL,
sale_date DATE NOT NULL,
quantity INT NOT NULL,
amount DECIMAL(10,2) NOT NULL
);
CREATE CLUSTERED COLUMNSTORE INDEX CCI_sales_data ON dbo.sales_data;
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "sales_data",
"columns": [
{
"name": "sale_id",
"position": 1,
"type": "INT",
"is_identity": true,
"identity_seed": 1,
"identity_increment": 1
},
{
"name": "product_id",
"position": 2,
"type": "INT"
},
{
"name": "sale_date",
"position": 3,
"type": "DATE"
},
{
"name": "quantity",
"position": 4,
"type": "INT"
},
{
"name": "amount",
"position": 5,
"type": "DECIMAL(10,2)"
}
],
"indexes": [
{
"name": "PK_sales_data_1",
"expressions": [
"sale_id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
},
{
"name": "CCI_sales_data",
"type": "CLUSTERED COLUMNSTORE"
}
]
}
]
}
]
}
- description: analytics_data
schema: |
CREATE TABLE dbo.analytics_data (
id INT NOT NULL,
year INT NOT NULL,
month INT NOT NULL,
revenue DECIMAL(15,2),
expenses DECIMAL(15,2)
);
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_analytics ON dbo.analytics_data (year, month, revenue, expenses);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "analytics_data",
"columns": [
{
"name": "id",
"position": 1,
"type": "INT"
},
{
"name": "year",
"position": 2,
"type": "INT"
},
{
"name": "month",
"position": 3,
"type": "INT"
},
{
"name": "revenue",
"position": 4,
"nullable": true,
"type": "DECIMAL(15,2)"
},
{
"name": "expenses",
"position": 5,
"nullable": true,
"type": "DECIMAL(15,2)"
}
],
"indexes": [
{
"name": "NCCI_analytics",
"expressions": [
"year",
"month",
"revenue",
"expenses"
],
"descending": [
false,
false,
false,
false
],
"type": "NONCLUSTERED COLUMNSTORE"
}
]
}
]
}
]
}
- description: fact_sales
schema: |
CREATE TABLE dbo.fact_sales (
sale_id INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
sale_date DATE NOT NULL,
amount DECIMAL(10,2) NOT NULL,
INDEX IX_fact_sales CLUSTERED COLUMNSTORE
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "fact_sales",
"columns": [
{
"name": "sale_id",
"position": 1,
"type": "INT"
},
{
"name": "product_id",
"position": 2,
"type": "INT"
},
{
"name": "customer_id",
"position": 3,
"type": "INT"
},
{
"name": "sale_date",
"position": 4,
"type": "DATE"
},
{
"name": "amount",
"position": 5,
"type": "DECIMAL(10,2)"
}
],
"indexes": [
{
"name": "IX_fact_sales",
"type": "CLUSTERED COLUMNSTORE"
}
]
}
]
}
]
}
- description: hybrid_table
schema: |
CREATE TABLE dbo.hybrid_table (
id INT PRIMARY KEY NONCLUSTERED,
category VARCHAR(50) NOT NULL,
value DECIMAL(10,2),
created_date DATETIME,
INDEX IX_hybrid_columnstore NONCLUSTERED COLUMNSTORE (category, value, created_date),
INDEX IX_hybrid_regular NONCLUSTERED (category, created_date DESC)
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "hybrid_table",
"columns": [
{
"name": "id",
"position": 1,
"type": "INT"
},
{
"name": "category",
"position": 2,
"type": "VARCHAR(50)"
},
{
"name": "value",
"position": 3,
"nullable": true,
"type": "DECIMAL(10,2)"
},
{
"name": "created_date",
"position": 4,
"nullable": true,
"type": "DATETIME"
}
],
"indexes": [
{
"name": "PK_hybrid_table_1",
"expressions": [
"id"
],
"descending": [
false
],
"type": "NONCLUSTERED",
"unique": true,
"primary": true,
"is_constraint": true
},
{
"name": "IX_hybrid_columnstore",
"expressions": [
"category",
"value",
"created_date"
],
"descending": [
false,
false,
false
],
"type": "NONCLUSTERED COLUMNSTORE"
},
{
"name": "IX_hybrid_regular",
"expressions": [
"category",
"created_date"
],
"descending": [
false,
true
],
"type": "NONCLUSTERED"
}
]
}
]
}
]
}
- description: products_3
schema: "CREATE TABLE dbo.products (\n id INT IDENTITY(1,1) PRIMARY KEY,\n name NVARCHAR(255) NOT NULL,\n price DECIMAL(10,2)\n);\n\n-- Add table comment\nEXEC sp_addextendedproperty \n 'MS_Description', 'Table storing product information',\n 'SCHEMA', 'dbo',\n 'TABLE', 'products';\n\n-- Add column comments\nEXEC sp_addextendedproperty \n 'MS_Description', 'Unique product identifier',\n 'SCHEMA', 'dbo',\n 'TABLE', 'products',\n 'COLUMN', 'id';\n\nEXEC sp_addextendedproperty \n 'MS_Description', 'Product display name',\n 'SCHEMA', 'dbo',\n 'TABLE', 'products',\n 'COLUMN', 'name';\n\nEXEC sp_addextendedproperty \n 'MS_Description', 'Product price in USD',\n 'SCHEMA', 'dbo',\n 'TABLE', 'products',\n 'COLUMN', 'price';\n"
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "products",
"columns": [
{
"name": "id",
"position": 1,
"type": "INT",
"comment": "Unique product identifier",
"is_identity": true,
"identity_seed": 1,
"identity_increment": 1
},
{
"name": "name",
"position": 2,
"type": "NVARCHAR(255)",
"comment": "Product display name"
},
{
"name": "price",
"position": 3,
"nullable": true,
"type": "DECIMAL(10,2)",
"comment": "Product price in USD"
}
],
"indexes": [
{
"name": "PK_products_1",
"expressions": [
"id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
}
],
"comment": "Table storing product information"
}
]
}
]
}
- description: locations
schema: |
CREATE TABLE dbo.locations (
id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(100) NOT NULL,
position GEOMETRY NOT NULL,
area GEOGRAPHY
);
CREATE SPATIAL INDEX idx_locations_position ON dbo.locations (position);
CREATE SPATIAL INDEX idx_locations_area ON dbo.locations (area);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "locations",
"columns": [
{
"name": "id",
"position": 1,
"type": "INT",
"is_identity": true,
"identity_seed": 1,
"identity_increment": 1
},
{
"name": "name",
"position": 2,
"type": "NVARCHAR(100)"
},
{
"name": "position",
"position": 3,
"type": "GEOMETRY"
},
{
"name": "area",
"position": 4,
"nullable": true,
"type": "GEOGRAPHY"
}
],
"indexes": [
{
"name": "PK_locations_1",
"expressions": [
"id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
},
{
"name": "idx_locations_position",
"expressions": [
"position"
],
"descending": [
false
],
"type": "SPATIAL",
"spatial_config": {
"method": "SPATIAL",
"tessellation": {},
"dimensional": {
"dimensions": 2,
"data_type": "GEOMETRY"
}
}
},
{
"name": "idx_locations_area",
"expressions": [
"area"
],
"descending": [
false
],
"type": "SPATIAL",
"spatial_config": {
"method": "SPATIAL",
"tessellation": {},
"dimensional": {
"dimensions": 2,
"data_type": "GEOMETRY"
}
}
}
]
}
]
}
]
}
- description: geo_world_map
schema: |
CREATE SCHEMA geo;
-- Multiple spatial indexes on same table
CREATE TABLE geo.world_map (
feature_id INT IDENTITY(1,1) PRIMARY KEY,
feature_name NVARCHAR(200) NOT NULL,
feature_type NVARCHAR(50),
geometry_data GEOMETRY NOT NULL,
geography_data GEOGRAPHY NOT NULL,
bbox_geometry GEOMETRY,
center_point GEOMETRY NOT NULL,
area_polygon GEOGRAPHY
);
-- Create multiple spatial indexes
CREATE SPATIAL INDEX idx_geometry ON geo.world_map (geometry_data);
CREATE SPATIAL INDEX idx_geography ON geo.world_map (geography_data);
CREATE SPATIAL INDEX idx_bbox ON geo.world_map (bbox_geometry);
CREATE SPATIAL INDEX idx_center ON geo.world_map (center_point);
CREATE SPATIAL INDEX idx_area ON geo.world_map (area_polygon);
-- Also create regular indexes
CREATE INDEX idx_feature_name ON geo.world_map (feature_name);
CREATE INDEX idx_feature_type ON geo.world_map (feature_type);
metadata: |
{
"schemas": [
{
"name": "geo",
"tables": [
{
"name": "world_map",
"columns": [
{
"name": "feature_id",
"position": 1,
"type": "INT",
"is_identity": true,
"identity_seed": 1,
"identity_increment": 1
},
{
"name": "feature_name",
"position": 2,
"type": "NVARCHAR(200)"
},
{
"name": "feature_type",
"position": 3,
"nullable": true,
"type": "NVARCHAR(50)"
},
{
"name": "geometry_data",
"position": 4,
"type": "GEOMETRY"
},
{
"name": "geography_data",
"position": 5,
"type": "GEOGRAPHY"
},
{
"name": "bbox_geometry",
"position": 6,
"nullable": true,
"type": "GEOMETRY"
},
{
"name": "center_point",
"position": 7,
"type": "GEOMETRY"
},
{
"name": "area_polygon",
"position": 8,
"nullable": true,
"type": "GEOGRAPHY"
}
],
"indexes": [
{
"name": "PK_world_map_1",
"expressions": [
"feature_id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
},
{
"name": "idx_geometry",
"expressions": [
"geometry_data"
],
"descending": [
false
],
"type": "SPATIAL",
"spatial_config": {
"method": "SPATIAL",
"tessellation": {},
"dimensional": {
"dimensions": 2,
"data_type": "GEOMETRY"
}
}
},
{
"name": "idx_geography",
"expressions": [
"geography_data"
],
"descending": [
false
],
"type": "SPATIAL",
"spatial_config": {
"method": "SPATIAL",
"tessellation": {},
"dimensional": {
"dimensions": 2,
"data_type": "GEOMETRY"
}
}
},
{
"name": "idx_bbox",
"expressions": [
"bbox_geometry"
],
"descending": [
false
],
"type": "SPATIAL",
"spatial_config": {
"method": "SPATIAL",
"tessellation": {},
"dimensional": {
"dimensions": 2,
"data_type": "GEOMETRY"
}
}
},
{
"name": "idx_center",
"expressions": [
"center_point"
],
"descending": [
false
],
"type": "SPATIAL",
"spatial_config": {
"method": "SPATIAL",
"tessellation": {},
"dimensional": {
"dimensions": 2,
"data_type": "GEOMETRY"
}
}
},
{
"name": "idx_area",
"expressions": [
"area_polygon"
],
"descending": [
false
],
"type": "SPATIAL",
"spatial_config": {
"method": "SPATIAL",
"tessellation": {},
"dimensional": {
"dimensions": 2,
"data_type": "GEOMETRY"
}
}
},
{
"name": "idx_feature_name",
"expressions": [
"feature_name"
],
"descending": [
false
]
},
{
"name": "idx_feature_type",
"expressions": [
"feature_type"
],
"descending": [
false
]
}
]
}
]
}
]
}
- description: spatial_test
schema: |
-- Comprehensive spatial index test with full parameters
CREATE TABLE dbo.spatial_test (
id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(100) NOT NULL,
geo_point GEOMETRY NOT NULL,
geo_polygon GEOMETRY NOT NULL,
geo_location GEOGRAPHY NOT NULL
);
-- Create spatial index with GEOMETRY_GRID and full parameters
CREATE SPATIAL INDEX idx_geo_point ON dbo.spatial_test (geo_point)
USING GEOMETRY_GRID
WITH (
BOUNDING_BOX = (-100, -100, 100, 100),
GRIDS = (LEVEL_1 = LOW, LEVEL_2 = MEDIUM, LEVEL_3 = HIGH, LEVEL_4 = LOW),
CELLS_PER_OBJECT = 64,
PAD_INDEX = ON,
FILLFACTOR = 90,
SORT_IN_TEMPDB = ON,
ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON,
MAXDOP = 4,
DATA_COMPRESSION = ROW
);
-- Create spatial index with GEOGRAPHY_GRID
CREATE SPATIAL INDEX idx_geo_location ON dbo.spatial_test (geo_location)
USING GEOGRAPHY_GRID
WITH (
GRIDS = (LEVEL_1 = MEDIUM, LEVEL_2 = HIGH, LEVEL_3 = MEDIUM, LEVEL_4 = HIGH),
CELLS_PER_OBJECT = 16
);
-- Create spatial index with GEOMETRY_AUTO_GRID
CREATE SPATIAL INDEX idx_geo_polygon ON dbo.spatial_test (geo_polygon)
USING GEOMETRY_AUTO_GRID
WITH (
CELLS_PER_OBJECT = 8
);
metadata: |
{
"schemas": [
{
"name": "dbo",
"tables": [
{
"name": "spatial_test",
"columns": [
{
"name": "id",
"position": 1,
"type": "INT",
"is_identity": true,
"identity_seed": 1,
"identity_increment": 1
},
{
"name": "name",
"position": 2,
"type": "NVARCHAR(100)"
},
{
"name": "geo_point",
"position": 3,
"type": "GEOMETRY"
},
{
"name": "geo_polygon",
"position": 4,
"type": "GEOMETRY"
},
{
"name": "geo_location",
"position": 5,
"type": "GEOGRAPHY"
}
],
"indexes": [
{
"name": "PK_spatial_test_1",
"expressions": [
"id"
],
"descending": [
false
],
"unique": true,
"primary": true,
"is_constraint": true
},
{
"name": "idx_geo_point",
"expressions": [
"geo_point"
],
"descending": [
false
],
"type": "SPATIAL",
"spatial_config": {
"method": "SPATIAL",
"tessellation": {
"scheme": "GEOMETRY_GRID",
"bounding_box": {
"xmin": -100,
"ymin": -100,
"xmax": 100,
"ymax": 100
},
"grid_levels": [
{
"level": 1,
"density": "LOW"
},
{
"level": 2,
"density": "MEDIUM"
},
{
"level": 3,
"density": "HIGH"
},
{
"level": 4,
"density": "LOW"
}
],
"cells_per_object": 64
},
"storage": {
"fillfactor": 90,
"pad_index": true,
"sort_in_tempdb": "ON",
"allow_row_locks": true,
"allow_page_locks": true,
"maxdop": 4,
"data_compression": "ROW"
},
"dimensional": {
"dimensions": 2,
"data_type": "GEOMETRY"
}
}
},
{
"name": "idx_geo_location",
"expressions": [
"geo_location"
],
"descending": [
false
],
"type": "SPATIAL",
"spatial_config": {
"method": "SPATIAL",
"tessellation": {
"scheme": "GEOGRAPHY_GRID",
"grid_levels": [
{
"level": 1,
"density": "MEDIUM"
},
{
"level": 2,
"density": "HIGH"
},
{
"level": 3,
"density": "MEDIUM"
},
{
"level": 4,
"density": "HIGH"
}
],
"cells_per_object": 16
},
"storage": {
"allow_row_locks": true,
"allow_page_locks": true
},
"dimensional": {
"dimensions": 2,
"data_type": "GEOGRAPHY"
}
}
},
{
"name": "idx_geo_polygon",
"expressions": [
"geo_polygon"
],
"descending": [
false
],
"type": "SPATIAL",
"spatial_config": {
"method": "SPATIAL",
"tessellation": {
"scheme": "GEOMETRY_AUTO_GRID",
"cells_per_object": 8
},
"storage": {
"allow_row_locks": true,
"allow_page_locks": true
},
"dimensional": {
"dimensions": 2,
"data_type": "GEOMETRY"
}
}
}
]
}
]
}
]
}