如何在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 find the object "dbo.DistCritGroupData" because it does not exist or you do not have permissions.

我基本上想要添加主键或聚簇索引。我试过这个，但是我收到错误'找不到对象“dbo.DistCritGroupData”，因为它不存在或者你没有权限。

  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?

我在用户定义的表类型的对象资源管理器中看到，有“列”，“键”，“约束”和“索引”的部分。问题是，我如何添加密钥或索引？

3 个解决方案

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

要么

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.

CREATE TYPE不允许命名约束。像表变量一样。

#2

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

@bernd_K和@ gbn的答案是有用的，如果它是单列PK。对于多列，它将是：

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.

简而言之，您可以拥有PK和UNIQUE表约束，但您无法命名它们。这种方式是有道理的，因为您将要创建多个相同类型的对象，并且您唯一想要使用这些约束的时间将是对整个表类型的更改。

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

您也无法定义索引，因为这些索引主要是围绕物理存储的工件。

#3

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.

我从未理解为什么数据库人员需要主键名称。它们应该被称为表xyz的主键。

#1