Define SQL Server Tables in JSON: Complete Reference

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, XML indexes, and foreign keys. View the Person.Person table on GitHub .


    {
      "Schema": "[Person]",
      "Name": "[Person]",
      "CompressionType": "NONE",
      "IsTemporal": false,
      "Columns": [
        {
          "Name": "[AdditionalContactInfo]",
          "DataType": "XML([Person].[AdditionalContactInfoSchemaCollection])",
          "Nullable": true,
          "Persisted": false,
          "Sparse": false,
          "Collation": "",
          "DataMaskFunction": "",
          "EncryptionType": "NONE",
          "EnableCDC": false,
          "ExtendedProperties": {
            "MS_Description": "Additional contact information about the person stored in xml format. "
          }
        },
        {
          "Name": "[BusinessEntityID]",
          "DataType": "INT",
          "Nullable": false,
          "Persisted": false,
          "Sparse": false,
          "Collation": "",
          "DataMaskFunction": "",
          "EncryptionType": "NONE",
          "EnableCDC": false,
          "ExtendedProperties": {
            "MS_Description": "Primary key for Person records."
          }
        },
        {
          "Name": "[Demographics]",
          "DataType": "XML([Person].[IndividualSurveySchemaCollection])",
          "Nullable": true,
          "Persisted": false,
          "Sparse": false,
          "Collation": "",
          "DataMaskFunction": "",
          "EncryptionType": "NONE",
          "EnableCDC": 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,
          "Sparse": false,
          "Collation": "",
          "DataMaskFunction": "",
          "EncryptionType": "NONE",
          "EnableCDC": 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,
          "Sparse": false,
          "Collation": "",
          "DataMaskFunction": "",
          "EncryptionType": "NONE",
          "EnableCDC": false,
          "ExtendedProperties": {
            "MS_Description": "First name of the person."
          }
        },
        {
          "Name": "[LastName]",
          "DataType": "NAME",
          "Nullable": false,
          "Persisted": false,
          "Sparse": false,
          "Collation": "",
          "DataMaskFunction": "",
          "EncryptionType": "NONE",
          "EnableCDC": false,
          "ExtendedProperties": {
            "MS_Description": "Last name of the person."
          }
        },
        {
          "Name": "[MiddleName]",
          "DataType": "NAME",
          "Nullable": true,
          "Persisted": false,
          "Sparse": false,
          "Collation": "",
          "DataMaskFunction": "",
          "EncryptionType": "NONE",
          "EnableCDC": false,
          "ExtendedProperties": {
            "MS_Description": "Middle name or middle initial of the person."
          }
        },
        {
          "Name": "[ModifiedDate]",
          "DataType": "DATETIME",
          "Nullable": false,
          "Default": "getdate()",
          "Persisted": false,
          "Sparse": false,
          "Collation": "",
          "DataMaskFunction": "",
          "EncryptionType": "NONE",
          "EnableCDC": false,
          "ExtendedProperties": {
            "MS_Description": "Date and time the record was last updated."
          }
        },
        {
          "Name": "[NameStyle]",
          "DataType": "NAMESTYLE",
          "Nullable": false,
          "Default": "0",
          "Persisted": false,
          "Sparse": false,
          "Collation": "",
          "DataMaskFunction": "",
          "EncryptionType": "NONE",
          "EnableCDC": 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,
          "Sparse": false,
          "Collation": "",
          "DataMaskFunction": "",
          "EncryptionType": "NONE",
          "EnableCDC": 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,
          "Sparse": false,
          "Collation": "",
          "DataMaskFunction": "",
          "EncryptionType": "NONE",
          "EnableCDC": 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,
          "Sparse": false,
          "Collation": "",
          "DataMaskFunction": "",
          "EncryptionType": "NONE",
          "EnableCDC": false,
          "ExtendedProperties": {
            "MS_Description": "Surname suffix. For example, Sr. or Jr."
          }
        },
        {
          "Name": "[Title]",
          "DataType": "NVARCHAR(8)",
          "Nullable": true,
          "Persisted": false,
          "Sparse": false,
          "Collation": "",
          "DataMaskFunction": "",
          "EncryptionType": "NONE",
          "EnableCDC": 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]",
          "UpdateFillFactor": false,
          "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]",
          "UpdateFillFactor": false
        },
        {
          "Name": "[PK_Person_BusinessEntityID]",
          "CompressionType": "NONE",
          "PrimaryKey": true,
          "Unique": true,
          "UniqueConstraint": false,
          "Clustered": true,
          "ColumnStore": false,
          "FillFactor": 0,
          "IndexColumns": "[BusinessEntityID]",
          "UpdateFillFactor": false,
          "ExtendedProperties": {
            "MS_Description": "Primary key (clustered) constraint Clustered index created by a primary key constraint."
          }
        }
      ],
      "XmlIndexes": [
        {
          "Name": "[PXML_Person_AddContact]",
          "IsPrimary": true,
          "Column": "[AdditionalContactInfo]",
          "ExtendedProperties": {
            "MS_Description": "Primary XML index."
          }
        },
        {
          "Name": "[PXML_Person_Demographics]",
          "IsPrimary": true,
          "Column": "[Demographics]",
          "ExtendedProperties": {
            "MS_Description": "Primary XML index."
          }
        },
        {
          "Name": "[XMLPATH_Person_Demographics]",
          "IsPrimary": false,
          "Column": "[Demographics]",
          "PrimaryIndex": "[PXML_Person_Demographics]",
          "SecondaryIndexType": "PATH",
          "ExtendedProperties": {
            "MS_Description": "Secondary XML index for path."
          }
        },
        {
          "Name": "[XMLPROPERTY_Person_Demographics]",
          "IsPrimary": false,
          "Column": "[Demographics]",
          "PrimaryIndex": "[PXML_Person_Demographics]",
          "SecondaryIndexType": "PROPERTY",
          "ExtendedProperties": {
            "MS_Description": "Secondary XML index for property."
          }
        },
        {
          "Name": "[XMLVALUE_Person_Demographics]",
          "IsPrimary": false,
          "Column": "[Demographics]",
          "PrimaryIndex": "[PXML_Person_Demographics]",
          "SecondaryIndexType": "VALUE",
          "ExtendedProperties": {
            "MS_Description": "Secondary XML index for value."
          }
        }
      ],
      "ForeignKeys": [
        {
          "Name": "[FK_Person_BusinessEntity_BusinessEntityID]",
          "Columns": "[BusinessEntityID]",
          "RelatedTableSchema": "[Person]",
          "RelatedTable": "[BusinessEntity]",
          "RelatedColumns": "[BusinessEntityID]",
          "DeleteAction": "NO ACTION",
          "UpdateAction": "NO ACTION",
          "ExtendedProperties": {
            "MS_Description": "Foreign key constraint referencing BusinessEntity.BusinessEntityID."
          }
        }
      ],
      "CheckConstraints": [],
      "Statistics": [],
      "UpdateFillFactor": false,
      "ShouldApplyExpression": "",
      "EnableCDC": false,
      "EnableCDCAllColumns": false,
      "ContentFile": "TableContents\\Person.Person.tabledata",
      "MergeType": "Insert/Update",
      "MatchColumns": "",
      "MergeFilter": "",
      "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)
Change Data Capture (CDC)
Should Apply Expressions
All column data types
Check and column expressions
Persisted and computed columns
Sparse columns and dynamic data masking
Indexes, statistics, and columnstore indexes
Optionally update index fill factor at the table or index level
Foreign keys
Table level check constraints
XML indexes and full-text indexes
Custom Table Properties fully supported at the table level and all components
Data Delivery

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 on the community edition side.

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.

Tip

SchemaHammer does this for you behind the scenes when you to do either a table or column rename.

Should Apply Expression

You can define an expression that must be satisfied in order to deploy a given table or component. This can be useful for things like specific version requirements for an index or data type. You could define the same index or column in two ways specific to the server version or compatibility level and set the expression so that only the supported one is deployed on the version of the server being updated. You could use the expression to define environment-specific or even customer-specific indexing or tables. You could also use this feature to define items you want to track for documentation or custom attribute purposes but not actually apply to your database.

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.
`EnableCDC`	boolean	Enable Change Data Capture on this table. Tracks row-level changes for ETL and auditing scenarios.
`EnableCDCAllColumns`	boolean	When CDC is enabled, capture all columns (not just the primary key and changed columns).
`UpdateFillFactor`	boolean	Allow SchemaQuench to update fill factor on indexes for this table. Useful for slow-rolling fill factor changes across environments.
`ShouldApplyExpression`	string	Conditional expression to include/exclude this table. See Should Apply Expression.
`OldName`	string	Previous table name for renaming. See Renaming Tables.
`ContentFile`	string	Path to the data file for data delivery. Supports CSV and JSON formats.
`MergeType`	string	How data is merged: `MergeInsertOnly`, `MergeInsertUpdate`, `MergeInsertUpdateDelete`, or `Truncate`. See Data Delivery.
`MatchColumns`	string	Comma-separated columns used to match source rows to target rows during merge.
`MergeFilter`	string	WHERE clause to limit which target rows are affected by the merge.
`MergeDisableTriggers`	boolean	Disable triggers during data delivery. Defaults to `false`.

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. Reduces storage for columns that are frequently NULL.
`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)`)
`EncryptionType`	string	Always Encrypted type: `DETERMINISTIC`, `RANDOMIZED`, or `NONE`
`EncryptionKey`	string	Column Encryption Key name for Always Encrypted columns
`EncryptionAlgorithm`	string	Encryption algorithm (e.g., `AEAD_AES_256_CBC_HMAC_SHA_256`)
`ShouldApplyExpression`	string	Conditional expression to include/exclude this column. See Should Apply Expression.
`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. Overrides the table-level setting.
`ShouldApplyExpression`	string	Conditional expression to include/exclude this index.

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
`DeleteAction`	string	Referential action on delete: `NO ACTION`, `CASCADE`, `SET NULL`, `SET DEFAULT`
`UpdateAction`	string	Referential action on update: `NO ACTION`, `CASCADE`, `SET NULL`, `SET DEFAULT`
`CascadeOnDelete`	boolean	Shorthand for `"DeleteAction": "CASCADE"`. Older format retained for compatibility.
`CascadeOnUpdate`	boolean	Shorthand for `"UpdateAction": "CASCADE"`. Older format retained for compatibility.
`ShouldApplyExpression`	string	Conditional expression to include/exclude this foreign key.

Check Constraint Properties

Property	Type	Description
`Name`	string	Constraint name Required
`Expression`	string	CHECK expression (e.g., `[EndDate] > [StartDate]`) Required
`ShouldApplyExpression`	string	Conditional expression to include/exclude this constraint.

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`
`ShouldApplyExpression`	string	Conditional expression to include/exclude this XML index.

Full-Text Index Properties

Property	Type	Description
`FullTextCatalog`	string	Full-text catalog name Required
`KeyIndex`	string	Unique index used as the full-text key Required
`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
`ShouldApplyExpression`	string	Conditional expression to include/exclude this full-text index.

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
`ShouldApplyExpression`	string	Conditional expression to include/exclude these statistics.

Additional Resources

Forge Deeper with SchemaQuench. Deep dive Product Demos - Sample databases converted to the SchemaSmith metadata format. Examples Product Folder Structure - See the basic layout on disk for your product. Reference Custom Properties - Extend your domain seamlessly into your model. Reference Data Delivery - Making data deployment easier. Reference

Also available for PostgreSQL and MySQL

Defining Tables - SQL Server (Enterprise)