How to add an index or primary key to a user-defined table type in SQL Server?

SQL Server’s CREATE TYPE DDL statement supports at least two different ways of declaring the PRIMARY KEY constraint and other options.

• The simplest is an inline PRIMARY KEY modifier on a single column (see the <column_definition> syntax rule, and the CLUSTERED/NONCLUSTERED keyword is optional).

  CREATE TYPE dbo.DistCritGroupData AS TABLE (
  
      DistCritTypeId int NOT NULL PRIMARY KEY CLUSTERED,
      ItemAction     int NOT NULL,        
      ObjectId       int NOT NULL,
      OperatorType   int NOT NULL
  );
  

  • Note that UNIQUE and CHECK constraints can also be declared on a single column. For example:

    CREATE TYPE dbo.DistCritGroupData AS TABLE (
    
        DistCritTypeId int NOT NULL PRIMARY KEY CLUSTERED,
        ItemAction     int NOT NULL,        
        ObjectId       int NOT NULL UNIQUE,
        OperatorType   int NOT NULL CHECK ( OperatorType >= 1 AND OperatorType <= 10 )
    );
    

• You can also declare the PRIMARY KEY, UNIQUE, and CHECK constraints as a table-type constraint, located right after the column definitions:

  • You must use this approach if you have a composite PRIMARY KEY – or if you want to reference multiple columns in your UNIQUE or CHECK constraint.

  • Unlike in a normal CREATE TABLE statement these constraints are always anonymous and lack the CONSTRAINT keyword.

    • i.e. use just PRIMARY KEY ( DistCritTypeId, ItemAction ) but not CONSTRAINT PK_DistCritGroupData PRIMARY KEY ( DistCritTypeId, ItemAction ).

    CREATE TYPE dbo.DistCritGroupData AS TABLE (
    
        DistCritTypeId int NOT NULL,
        ItemAction     int NOT NULL,        
        ObjectId       int NOT NULL,
        OperatorType   int NOT NULL,
    
        PRIMARY KEY ( DistCritTypeId ),
        UNIQUE ( ObjectId ),
        CHECK ( OperatorType >= 1 AND OperatorType <= 10 )
    );
    

• You can also specify additional arbitrary INDEX objects, which are declared after the PRIMARY KEY constraint (if any).

  • Unlike CREATE TABLE DDL statements, you cannot use CREATE INDEX to define an index on a table-type; the INDEX definition must be part of the CREATE TYPE statement.

  • Also, unlike the PRIMARY KEY, CHECK, and UNIQUE constraints we’ve seen so far, INDEX objects on table-types are not anonymous.

    CREATE TYPE dbo.DistCritGroupData AS TABLE (
    
        DistCritTypeId int NOT NULL,
        ItemAction     int NOT NULL,        
        ObjectId       int NOT NULL,
        OperatorType   int NOT NULL,
    
        PRIMARY KEY ( DistCritTypeId ),
    
        INDEX IX_ObjectId_OperatorType ( ObjectId, OperatorType )
    );
    

• Note there is no FOREIGN KEY constraint type here: table-types cannot participate in foreign-key constraints.