在 SQL Server 2005 中动态命名索引? [英] Dynamically name indexes in SQL Server 2005?

问题描述

大多数使用 Sql Server 的人都知道，您可以像这样声明一个临时表:

As most people who work with Sql Server know, you can declare a temporary table like so:

create table #foo 
    (
    row_id int identity not null primary key,
    bar varchar(50) not null default 'YoMomma'
    );

...这将在这个临时表上创建一个主键和一个默认约束；这些对象的名称将是唯一的，由 Sql Server 动态创建.

...this will create a primary key and a default constraint on this temporary table; the names for these objects will be unique, dynamically created by Sql Server.

是否可以在表创建后为其创建动态命名的索引?我有一个使用临时表的存储过程可能同时运行多个实例的情况，我想提高性能，而不会冒 tempdb 数据库中同名对象之间发生冲突的风险.CREATE INDEX 命令需要一个明确的索引名称.

Is it possible to create a dynamically named index for the table after it's been created? I have a case where a stored procedure using temporary tables may be running multiple instances at the same time, and I'd like to increase the performance without risking a conflict between identically named objects in the tempdb database. CREATE INDEX command requires an explicit index name.

我正在寻找一种不涉及动态 SQL，只涉及动态名称的解决方案.

I am looking for a solution that does not involve dynamic SQL, just dynamic names.

推荐答案

这不是问题.索引名称只需在表范围内唯一，而不是跨表范围全局唯一.只有约束名称在整个数据库架构中必须是唯一的.

This is a non issue. Index names only have to be unique within a table scope, not globally across table scopes. Only constraint names have to be unique within an entire database schema.

因此，例如，您可以在多个并发连接中运行它而不会出现问题

So, for example, you can run this in multiple concurrent connections with no problems

CREATE TABLE #T
(
C INT
)

CREATE UNIQUE CLUSTERED INDEX ix on #T(C)

但是在并发情况下会失败

But this would fail under concurrency

ALTER TABLE #T
ADD CONSTRAINT UQ UNIQUE NONCLUSTERED (C)

这篇关于在 SQL Server 2005 中动态命名索引?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！