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

I have this user-defined type that I would like to add a primary key or index to:

IF NOT EXISTS (
  SELECT * 
  FROM sys.types st 
  JOIN sys.schemas ss 
    ON st.schema_id = ss.schema_id 
  WHERE st.name = N'DistCritGroupData' 
    AND ss.name = N'dbo')
BEGIN

  CREATE TYPE [dbo].[DistCritGroupData] AS TABLE
  (
    [DistCritTypeId] [int] NOT NULL,
    [ItemAction] [int] NOT NULL,        
    [ObjectId] [int] NOT NULL,
    [OperatorType] [int] NOT NULL     
  );

END;
GO  

I basically would like to either add a primary key or a clustered index. I tried this but I get the error 'Cannot fin开发者_如何学JAVAd the object "dbo.DistCritGroupData" because it does not exist or you do not have permissions.

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

I see in the Object Explorer on my user-defined table type that there are sections for "Columns", "Keys", "Constraints", and "Indexes". The questions is, how I do I add a Key or Index?

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.

@bernd_K and @gbn's answers work if it's a single column PK. For multi column, it would be:

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

In short, you can have PKs and UNIQUE table constraints, but you cannot name them. This kind of makes sense, since you're going to be creating multiple objects of the same type, and the only time you're going to want to work with these constraints would be an alteration of the entire table type.

You also cannot define indexes, since those are primarily an artifact around physical storage.

It's worth noting that you can now add certain indexes in table types in SQL 2014 with the new inline index syntax. For example:

  CREATE TYPE [dbo].[DistCritGroupData] AS TABLE
  (
    [DistCritTypeId] [int] NOT NULL UNIQUE,
    [ItemAction] [int] NOT NULL,        
    [ObjectId] [int] NOT NULL,
    [OperatorType] [int] NOT NULL,

    PRIMARY KEY NONCLUSTERED 
    (
       [ObjectId] ASC
    ),

    INDEX CIX CLUSTERED (ObjectId, OperatorType)    
  );

You can specify your type like this:

  CREATE TYPE [dbo].[DistCritGroupData] AS TABLE
  (
    [DistCritTypeId] [int] NOT NULL primary key,
    [ItemAction] [int] NOT NULL,        
    [ObjectId] [int] NOT NULL,
    [OperatorType] [int] NOT NULL
  );

I never learned why database people need names for primary keys. They ought be called The primary key of table xyz.