Defining Tables for SQL Server - Enterprise

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

An example of a fairly complex table definition can be seen by looking at the AdventureWorks.Person.Person table.


{
  "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
Should Apply Expressions
All column data types
Check and column expressions
Persisted columns
Indexes and statistics
Optionally update index fill factor at the table or index level (to allow slow rolling the true up)
Foreign keys
Table level check constraints
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.

Defining Tables