#Temp表和索引 [英] #Temp tables and indexes

问题描述

当我 创建一个临时表我需要能够添加索引，只有正在运行的会话才知道这个表是： 示例 创建表#TableName（...） 为正在运行的会话创建临时表。

When I  create a temp table I need to be able to add indexes tat are known only to the session running just as the table is:  Example   Create Table #TableName(….)  creates the temp table for the running session.

如何向该表添加索引以使它们不是全局可见的？

How can I add indexes to this table so that they are not globally visible?

示例：I有两个用户运行一个报告，这个报告实际上是一个传入参数的存储过程。该过程创建了几个临时表，比如＃Table1,2，等等，并且你对每个用户的会话都是独立的。到目前为止没问题。我需要做的下一件事是
（例如）是在＃Table1上创建主键约束（或任何其他索引/约束）（这就是问题所在）。

Example: I have two users running a report which is actually a stored procedure with parameters passed in. The procedure creates several temp tables, say #Table1, 2, etc. and thy are independent for each user's session. No problem so far. The next thing I need to do (for example) is create a primary key constraint (or any other index/constraint) on #Table1 (and this is where the problem is).

使用以下内容：

更改表＃tblTemp1添加CONSTRAINT PK_IndexName主键群集（Col1，Col2） ，...）

这就是问题所在;上面的索引名称  PK_IndexName 因为索引已经存在，所以两个正在运行的程序都知道并且崩溃其中一个程序。不支持语法#PK_IndexName。那么我如何创建这些索引，以便它们只在运行会话和表的本地知道？

This is where the problem lies; the index name above PK_IndexName  becomes known to both of the 2 running procedures and crashes one of them because the index already exists. The syntax #PK_IndexName is not supported. So how do I create these indexes so they are known only locally to the running session and table?

Lee

推荐答案

这不是索引问题。 两个会话可以创建一个具有相同名称的临时表，并且每个表都可以具有相同名称的索引，并且可以正常工作。 

It is not indexes that are the problem.  Two sessions can create a temp table with the same name and each of those tables can have an index with the same name and that works just fine. 

问题在于约束。 如果两个会话尝试创建具有相同名称的约束，则第二个会话将出错。 对于临时表的约束，此问题的最简单解决方案是不命名约束。 所以你的
alter语句看起来应该是

The problem is with constraints.  If two sessions try to create constraints with the same name, the second one will get an error.  For constraints on temp tables the easiest solution to this problem is to not name the constraint.  So your alter statement should look like

Alter Table #tblTemp1 Add primary key clustered(Col1, Col2, …)

那你就不会有问题。 

Then you will not have a problem. 

Tom

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