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

Question

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 find 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?

Answer 1

Why not this?

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        ); 

or

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

CREATE TYPE does not allow naming of contraints. Like table variables.

Answer 2

@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.