临时表上的聚集索引 [英] Clustered index on temp table

问题描述

我正在尝试优化具有如下代码的过程:

I'm trying to optimize a procedure that has code like the following:

CREATE TABLE #t1 (c1 int, c2 varchar(20), c3(varchar(50)...)

CREATE CLUSTERED INDEX ix_t1 ON #t1(c3) ON [PRIMARY]

我想通过将 CLUSTERED 索引移到表声明中来改进这一点(更适合缓存)，但是 c3 不是唯一的，所以这不起作用:

I wanted to improve that by moving the CLUSTERED index into the table declaration (more caching friendly), but c3 is not unique so this doesn't work:

CREATE TABLE #t1 (c1 int, c2 varchar(20), c3 varchar(50)..., UNIQUE CLUSTERED (c3))

有没有办法在临时表声明中声明一个不唯一的聚簇?

Is there a way to declare a clustered that is not unique in the temp table declaration?

推荐答案

不，没有……定义聚簇作为表创建中的一个选项的能力的存在是为了支持声明主键和唯一列约束，这自己创建索引.换句话说，CREATE TABLE 语句中的 CLUSTERED 指定了由 UNIQUE 约束创建的索引应该是聚簇的还是非聚簇的，即很重要，因为一张表只能有一个聚集索引.

No there is not...the existence of the ability to define clustered as an option in table creation is to support declaring primary key and unique column constraints, which themselves create indexes. In other words, CLUSTERED in the CREATE TABLE statement is specifying whether or not the index created by the UNIQUE constraint should be clustered or nonclustered, which is important because a table can only have one clustered index.

这篇关于临时表上的聚集索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！