Defining Tables for SQL Server - Community

Overview

Tables are defined via JSON files in the tables folder of your product in the form <Schema name.Table name.json>. After a little use, the format will feel very comfortable. Arrays of objects are used to define collections like Columns, Indexes and ForeignKeys. Each object in the collection has the properties you would expect.

Columns have required properties like Name and DataType, and optional properties like Nullable or Default. Indexes and Foreign Keys are similar.

Example

A complete table definition showing columns, indexes, and foreign keys. View the Person.Person table on GitHub .


    {
      "Schema": "[Person]",
      "Name": "[Person]",
      "CompressionType": "NONE",
      "Columns": [
        {
          "Name": "[AdditionalContactInfo]",
          "DataType": "XML([Person].[AdditionalContactInfoSchemaCollection])",
          "Nullable": true,
          "Persisted": false,
          "ExtendedProperties": {
            "MS_Description": "Additional contact information about the person stored in xml format. "
          }
        },
        {
          "Name": "[BusinessEntityID]",
          "DataType": "INT",
          "Nullable": false,
          "Persisted": false,
          "ExtendedProperties": {
            "MS_Description": "Primary key for Person records."
          }
        },
        {
          "Name": "[Demographics]",
          "DataType": "XML([Person].[IndividualSurveySchemaCollection])",
          "Nullable": true,
          "Persisted": false,
          "ExtendedProperties": {
            "MS_Description": "Personal information such as hobbies, and income collected from online shoppers. Used for sales analysis."
          }
        },
        {
          "Name": "[EmailPromotion]",
          "DataType": "INT",
          "Nullable": false,
          "Default": "0",
          "CheckExpression": "[EmailPromotion]>=(0) AND [EmailPromotion]<=(2)",
          "Persisted": false,
          "ExtendedProperties": {
            "MS_Description": "0 = Contact does not wish to receive e-mail promotions, 1 = Contact does wish to receive e-mail promotions from AdventureWorks, 2 = Contact does wish to receive e-mail promotions from AdventureWorks and selected partners. "
          }
        },
        {
          "Name": "[FirstName]",
          "DataType": "NAME",
          "Nullable": false,
          "Persisted": false,
          "ExtendedProperties": {
            "MS_Description": "First name of the person."
          }
        },
        {
          "Name": "[LastName]",
          "DataType": "NAME",
          "Nullable": false,
          "Persisted": false,
          "ExtendedProperties": {
            "MS_Description": "Last name of the person."
          }
        },
        {
          "Name": "[MiddleName]",
          "DataType": "NAME",
          "Nullable": true,
          "Persisted": false,
          "ExtendedProperties": {
            "MS_Description": "Middle name or middle initial of the person."
          }
        },
        {
          "Name": "[ModifiedDate]",
          "DataType": "DATETIME",
          "Nullable": false,
          "Default": "getdate()",
          "Persisted": false,
          "ExtendedProperties": {
            "MS_Description": "Date and time the record was last updated."
          }
        },
        {
          "Name": "[NameStyle]",
          "DataType": "NAMESTYLE",
          "Nullable": false,
          "Default": "0",
          "Persisted": false,
          "ExtendedProperties": {
            "MS_Description": "0 = The data in FirstName and LastName are stored in western style (first name, last name) order.  1 = Eastern style (last name, first name) order."
          }
        },
        {
          "Name": "[PersonType]",
          "DataType": "NCHAR(2)",
          "Nullable": false,
          "CheckExpression": "[PersonType] IS NULL OR (upper([PersonType])='GC' OR upper([PersonType])='SP' OR upper([PersonType])='EM' OR upper([PersonType])='IN' OR upper([PersonType])='VC' OR upper([PersonType])='SC')",
          "Persisted": false,
          "ExtendedProperties": {
            "MS_Description": "Primary type of person: SC = Store Contact, IN = Individual (retail) customer, SP = Sales person, EM = Employee (non-sales), VC = Vendor contact, GC = General contact"
          }
        },
        {
          "Name": "[rowguid]",
          "DataType": "UNIQUEIDENTIFIER ROWGUIDCOL",
          "Nullable": false,
          "Default": "newid()",
          "Persisted": false,
          "ExtendedProperties": {
            "MS_Description": "ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample."
          }
        },
        {
          "Name": "[Suffix]",
          "DataType": "NVARCHAR(10)",
          "Nullable": true,
          "Persisted": false,
          "ExtendedProperties": {
            "MS_Description": "Surname suffix. For example, Sr. or Jr."
          }
        },
        {
          "Name": "[Title]",
          "DataType": "NVARCHAR(8)",
          "Nullable": true,
          "Persisted": false,
          "ExtendedProperties": {
            "MS_Description": "A courtesy title. For example, Mr. or Ms."
          }
        }
      ],
      "Indexes": [
        {
          "Name": "[AK_Person_rowguid]",
          "CompressionType": "NONE",
          "PrimaryKey": false,
          "Unique": true,
          "UniqueConstraint": false,
          "Clustered": false,
          "ColumnStore": false,
          "FillFactor": 0,
          "IndexColumns": "[rowguid]",
          "ExtendedProperties": {
            "MS_Description": "Unique nonclustered index. Used to support replication samples."
          }
        },
        {
          "Name": "[IX_Person_LastName_FirstName_MiddleName]",
          "CompressionType": "NONE",
          "PrimaryKey": false,
          "Unique": false,
          "UniqueConstraint": false,
          "Clustered": false,
          "ColumnStore": false,
          "FillFactor": 0,
          "IndexColumns": "[LastName],[FirstName],[MiddleName]"
        },
        {
          "Name": "[PK_Person_BusinessEntityID]",
          "CompressionType": "NONE",
          "PrimaryKey": true,
          "Unique": true,
          "UniqueConstraint": false,
          "Clustered": true,
          "ColumnStore": false,
          "FillFactor": 0,
          "IndexColumns": "[BusinessEntityID]",
          "ExtendedProperties": {
            "MS_Description": "Primary key (clustered) constraint Clustered index created by a primary key constraint."
          }
        }
      ],
      "XmlIndexes": [
        {
          "Name": "PXML_Person_AddContact",
          "Column": "AdditionalContactInfo",
          "IsPrimary": true
        },
        {
          "Name": "PXML_Person_Demographics",
          "Column": "Demographics",
          "IsPrimary": true
        },
        {
          "Name": "XMLPATH_Person_Demographics",
          "Column": "Demographics",
          "IsPrimary": false,
          "PrimaryIndex": "PXML_Person_Demographics",
          "SecondaryIndexType": "PATH"
        },
        {
          "Name": "XMLPROPERTY_Person_Demographics",
          "Column": "Demographics",
          "IsPrimary": false,
          "PrimaryIndex": "PXML_Person_Demographics",
          "SecondaryIndexType": "PROPERTY"
        },
        {
          "Name": "XMLVALUE_Person_Demographics",
          "Column": "Demographics",
          "IsPrimary": false,
          "PrimaryIndex": "PXML_Person_Demographics",
          "SecondaryIndexType": "VALUE"
        }
      ],
      "ForeignKeys": [
        {
          "Name": "[FK_Person_BusinessEntity_BusinessEntityID]",
          "Columns": "[BusinessEntityID]",
          "RelatedTableSchema": "[Person]",
          "RelatedTable": "[BusinessEntity]",
          "RelatedColumns": "[BusinessEntityID]",
          "CascadeOnDelete": false,
          "CascadeOnUpdate": false,
          "ExtendedProperties": {
            "MS_Description": "Foreign key constraint referencing BusinessEntity.BusinessEntityID."
          }
        }
      ],
      "ExtendedProperties": {
        "MS_Description": "Human beings involved with AdventureWorks: employees, customer contacts, and vendor contacts."
      }
    }

Supported Concepts

Schema
Table compression (row and page)
Temporal tables (system-versioned)
All column data types
Check and column expressions
Persisted and computed columns
Sparse columns and dynamic data masking
Indexes, statistics, and columnstore indexes
Foreign keys
Table level check constraints
XML indexes and full-text indexes

If you do not require a property, for example, table compression, simply do not include it in the definition. In most cases, as long it is not something required, like name or data type, you can reasonably expect it to default as you would think. If there is any doubt, you can always look at the update logic.

Renaming Tables

To rename a table, first, rename your table json file to match your new table name. Within the metadata file add a property named OldName with the current name. Finally, change the Name property to be your new name. SchemaQuench will use this to rename the table when the current name does not yet exist and the old name is still present. After the rename has occurred in all of your environments, you can safely remove the OldName property.

Renaming Columns

Similar to renaming a table, add an OldName property with the current column name. Change the Name property to your new column name. After SchemaQuench has run in all of your environments and the rename has been rolled out, you can safely remove the OldName property.

Property Reference

Complete reference for all table definition components. Each section documents the JSON properties available for that component type.

Table Properties

Property	Type	Description
`Schema`	string	Schema name. Defaults to `[dbo]`.
`Name`	string	Table name Required
`CompressionType`	string	Table-level data compression: `NONE`, `ROW`, or `PAGE`. Defaults to `NONE`.
`IsTemporal`	boolean	Enable system-versioned temporal table. When `true`, SchemaQuench manages the history table and period columns automatically.
`UpdateFillFactor`	boolean	Allow SchemaQuench to update fill factor on indexes for this table.
`OldName`	string	Previous table name for renaming. See Renaming Tables.

Column Properties

Property	Type	Description
`Name`	string	Column name Required
`DataType`	string	SQL Server data type (e.g., `INT`, `NVARCHAR(50)`, `DATETIME`). Required unless `ComputedExpression` is set.
`Nullable`	boolean	Allow NULL values. Defaults to `false`.
`Default`	string	Default value expression (e.g., `getdate()`, `0`, `newid()`)
`CheckExpression`	string	Column-level CHECK constraint expression (e.g., `[Status] IN ('Active','Inactive')`)
`ComputedExpression`	string	Formula for computed columns (e.g., `[Quantity] * [UnitPrice]`). When set, `DataType` is not required.
`Persisted`	boolean	Store computed column values physically. Only applies when `ComputedExpression` is set.
`Sparse`	boolean	Use sparse storage optimization for columns with many NULL values.
`Collation`	string	Column-level collation override (e.g., `Latin1_General_CI_AS`)
`DataMaskFunction`	string	Dynamic data masking function (e.g., `default()`, `email()`, `partial(0,"XXX",4)`)
`OldName`	string	Previous column name for renaming. See Renaming Columns.

Index Properties

Property	Type	Description
`Name`	string	Index name Required
`PrimaryKey`	boolean	Whether this index backs the primary key constraint.
`Unique`	boolean	Enforce uniqueness on the indexed columns.
`UniqueConstraint`	boolean	Whether this is a unique constraint (as opposed to a unique index).
`Clustered`	boolean	Create as a clustered index. Only one clustered index per table.
`ColumnStore`	boolean	Create as a columnstore index for analytical workloads.
`CompressionType`	string	Index-level compression: `NONE`, `ROW`, or `PAGE`. Defaults to `NONE`.
`FillFactor`	integer	Percentage of each leaf-level page to fill (0-100). `0` uses the server default.
`IndexColumns`	string	Comma-separated list of indexed columns (e.g., `[LastName],[FirstName]`) Required
`IncludeColumns`	string	Comma-separated list of non-key columns to include in the index leaf level.
`FilterExpression`	string	WHERE clause for a filtered index (e.g., `[IsActive] = 1`).
`UpdateFillFactor`	boolean	Allow SchemaQuench to update this index's fill factor.

Foreign Key Properties

Property	Type	Description
`Name`	string	Foreign key constraint name Required
`Columns`	string	Comma-separated columns in this table Required
`RelatedTableSchema`	string	Schema of the referenced table (e.g., `[Person]`)
`RelatedTable`	string	Referenced table name Required
`RelatedColumns`	string	Comma-separated columns in the referenced table Required
`CascadeOnDelete`	boolean	CASCADE on delete. Defaults to `false` (NO ACTION).
`CascadeOnUpdate`	boolean	CASCADE on update. Defaults to `false` (NO ACTION).

Check Constraint Properties

Property	Type	Description
`Name`	string	Constraint name Required
`Expression`	string	CHECK expression (e.g., `[EndDate] > [StartDate]`) Required

XML Index Properties

Property	Type	Description
`Name`	string	XML index name Required
`Column`	string	XML column to index Required
`IsPrimary`	boolean	Whether this is a primary XML index. A primary XML index must exist before secondary indexes can be created.
`PrimaryIndex`	string	Name of the primary XML index this secondary index is based on. Required when `IsPrimary` is `false`.
`SecondaryIndexType`	string	Type of secondary XML index: `PATH`, `VALUE`, or `PROPERTY`

Full-Text Index Properties

Community edition supports one full-text index per table, defined as a single FullTextIndex object on the table (not an array).

Property	Type	Description
`FullTextCatalog`	string	Full-text catalog name
`KeyIndex`	string	Unique index used as the full-text key
`ChangeTracking`	string	Change tracking mode: `AUTO` (default), `MANUAL`, or `OFF`
`StopList`	string	Stoplist name. Defaults to `SYSTEM`.
`Columns`	string	Comma-separated columns to include in the full-text index Required

Statistics Properties

Property	Type	Description
`Name`	string	Statistics object name Required
`StatisticsColumns`	string	Comma-separated columns for the statistics object Required
`FilterExpression`	string	WHERE clause for filtered statistics

Additional Resources

Forge Deeper with SchemaQuench. Deep dive Product Demos - Sample databases converted to the SchemaSmith metadata format. Examples What is metadata? Related Product Folder Structure - See the basic layout on disk for your product. Reference SchemaQuench - Learn how to quench your databases. Related

Defining Tables - SQL Server (Community)