sql临时表索引

‘壹’ sql如何把查询出来的多个表创建成一个临时表

SELECT * INTO #TEMPTABLENAME

FROM

(

SELECT xxxxxx //你的查询语句

)AS table_source //这个别名是必须的

WHERE xxxxxxxx //你需要的where判断;

COMMIT或ROLLBACK后可自动删除该临时表

1、sql server使用select into会自动生成临时表，不需要事先创建。

select * into #temp from sysobjects

2、sql要把多个表合并成一个要用到union或union all的关键字。

3、union或union all的区别是：union会自动压缩多个结果集合中的重复结果，而union all则将所有的结果全部显示出来。

(1)sql临时表索引扩展阅读

sql语言特点如下：

1、一体化：SQL集数据定义DDL、数据操纵DML和数据控制DCL于一体，可以完成数据库中的全部工作。

2、使用方式灵活：它具有两种使用方式，即可以直接以命令方式交互使用；也可以嵌入使用，嵌入到C、C++、FORTRAN、COBOL、JAVA等主语言中使用。

3、语言简洁，语法简单，好学好用：在ANSI标准中，只包含了94个英文单词，核心功能只用6个动词，语法接近英语口语。

‘贰’ sql server 临时表怎样加索引

你说的是临时表还是表变量？
如果是临时表的话跟普通表没有区别
比如
CREATE TABLE #Test(a int,b int)
CREATE INDEX IX_test ON #Test(a)
如果是表变量不支持直接创建索引，但是可疑在声明表变量的时候设置主键
比如
declare @table table(id int IDENTITY PRIMARY KEY,a int,b int);

‘叁’ sql如何创建临时表

网上其实很多相关文章，你可以搜一下，并不一定需要在这里提问。
转帖一篇给你吧

drop table #Tmp --删除临时表#Tmp
create table #Tmp --创建临时表#Tmp
(
ID int IDENTITY (1,1) not null, --创建列ID,并且每次新增一条记录就会加1
WokNo varchar(50),
primary key (ID) --定义ID为临时表#Tmp的主键
);
Select * from #Tmp --查询临时表的数据
truncate table #Tmp --清空临时表的所有数据和约束

相关例子：

Declare @Wokno Varchar(500) --用来记录职工号
Declare @Str NVarchar(4000) --用来存放查询语句
Declare @Count int --求出总记录数
Declare @i int
Set @i = 0
Select @Count = Count(Distinct(Wokno)) from #Tmp
While @i < @Count
Begin
Set @Str = 'Select top 1 @Wokno = WokNo from #Tmp Where id not in (Select top ' + Str(@i) + 'id from #Tmp)'
Exec Sp_ExecuteSql @Str,N'@WokNo Varchar(500) OutPut',@WokNo Output
Select @WokNo,@i --一行一行把职工号显示出来
Set @i = @i + 1
End

临时表
可以创建本地和全局临时表。本地临时表仅在当前会话中可见；全局临时表在所有会话中都可见。
本地临时表的名称前面有一个编号符 (#table_name)，而全局临时表的名称前面有两个编号符 (##table_name)。

SQL 语句使用 CREATE TABLE 语句中为 table_name 指定的名称引用临时表：

CREATE TABLE #MyTempTable (cola INT PRIMARY KEY)
INSERT INTO #MyTempTable VALUES (1)

如果本地临时表由存储过程创建或由多个用户同时执行的应用程序创建，则 SQL Server 必须能够区分由不同用户创建的表。为此，SQL
Server 在内部为每个本地临时表的表名追加一个数字后缀。存储在 tempdb 数据库的 sysobjects 表中的临时表，其全名由
CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。为了允许追加后缀，为本地临时表指定的表名 table_name 不能超过
116 个字符。

除非使用 DROP TABLE 语句显式除去临时表，否则临时表将在退出其作用域时由系统自动除去：

当存储过程完成时，将自动除去在存储过程中创建的本地临时表。由创建表的存储过程执行的所有嵌套存储过程都可以引用此表。但调用创建此表的存储过程的进程无法引用此表。

所有其它本地临时表在当前会话结束时自动除去。

全局临时表在创建此表的会话结束且其它任务停止对其引用时自动除去。任务与表之间的关联只在单个 Transact-SQL 语句的生存周期内保持。换言之，当创建全局临时表的会话结束时，最后一条引用此表的 Transact-SQL 语句完成后，将自动除去此表。
在
存储过程或触发器中创建的本地临时表与在调用存储过程或触发器之前创建的同名临时表不同。如果查询引用临时表，而同时有两个同名的临时表，则不定义针对哪
个表解析该查询。嵌套存储过程同样可以创建与调用它的存储过程所创建的临时表同名的临时表。嵌套存储过程中对表名的所有引用都被解释为是针对该嵌套过程所
创建的表，例如：

CREATE PROCEDURE Test2
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (2)
SELECT Test2Col = x FROM #t
GO
CREATE PROCEDURE Test1
AS
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (1)
SELECT Test1Col = x FROM #t
EXEC Test2
GO
CREATE TABLE #t(x INT PRIMARY KEY)
INSERT INTO #t VALUES (99)
GO
EXEC Test1
GO

下面是结果集：

(1 row(s) affected)

Test1Col
-----------
1

(1 row(s) affected)

Test2Col
-----------
2

当创建本地或全局临时表时，CREATE TABLE 语法支持除 FOREIGN KEY 约束以外的其它所有约束定义。如果在临时表中指定
FOREIGN KEY 约束，该语句将返回警告信息，指出此约束已被忽略，表仍会创建，但不具有 FOREIGN KEY 约束。在 FOREIGN
KEY 约束中不能引用临时表。

考虑使用表变量而不使用临时表。当需要在临时表上显式地创建索引时，或多个存储过程或函数需要使用表值时，临时表很有用。通常，表变量提供更有效的查询处理。

‘肆’ SQL Server 表变量和临时表的区别

一、表变量

表变量在SQL Server 2000中首次被引入。表变量的具体定义包括列定义，列名，数据类型和约束。而在表变量中可以使用的约束包括主键约束，唯一约束，NULL约束和CHECK约束(外键约束不能在表变量中使用)。定义表变量的语句是和正常使用Create Table定义表语句的子集。只是表变量通过DECLARE @local_variable语句进行定义。

表变量的特征：

表变量拥有特定作用域(在当前批处理语句中，但不在任何当前批处理语句调用的存储过程和函数中)，表变量在批处理结束后自动被清除。

表变量较临时表产生更少的存储过程重编译。

针对表变量的事务仅仅在更新数据时生效，所以锁和日志产生的数量会更少。

由于表变量的作用域如此之小，而且不属于数据库的持久部分，所以事务回滚不会影响表变量。

表变量可以在其作用域内像正常的表一样使用。更确切的说，表变量可以被当成正常的表或者表表达式一样在SELECT,DELETE,UPDATE,INSERT语句中使用，但是表变量不能在类似"SELECT select_listINTOtable_variable"这样的语句中使用。而在SQL Server2000中，表变量也不能用于INSERTINTOtable_variable EXEC stored_procere这样的语句中。

表变量不能做如下事情:

虽然表变量是一个变量，但是其不能赋值给另一个变量。

check约束，默认值和计算列不能引用自定义函数。

不能为约束命名。

不能Truncate表变量。

不能向标识列中插入显式值(也就是说表变量不支持SET IDENTITY_INSERT ON)

下面来玩玩表变量吧。

定义一个表变量，插入一条数据，然后查询：

DECLARE @tb1 Table
(
Id int,
Name varchar(20),
Age int
)

INSERT INTO @tb1 VALUES(1,'刘备',22)

SELECT * FROM @tb1

输出结果如下：

SQL Server不支持定义表变量时对Constraint命名，也不支持定义表变量后，对其建Constraint。

更多的不允许，请查看上面的要求。

二、临时表

在深入临时表之前，我们要了解一下会话(Session)，一个会话仅仅是一个客户端到数据引擎的连接。在SQL Server Management Studio中，每一个查询窗口都会和数据库引擎建立连接。一个应用程序可以和数据库建立一个或多个连接，除此之外，应用程序还可能建立连接后一直不释放知道应用程序结束，也可能使用完释放连接需要时建立连接。

临时表和Create Table语句创建的表有着相同的物理工程，但临时表与正常的表不同之处有：

1、临时表的名称不能超过116个字符，这是由于数据库引擎为了辨别不同会话建立不同的临时表，所以会自动在临时表的名字后附加一串。

2、局部临时表(以"#"开头命名的)作用域仅仅在当前的连接内，从在存储过程中建立局部临时表的角度来看，局部临时表会在下列情况下被Drop：
a、显示调用Drop Table语句
b、当局部临时表在存储过程内被创建时，存储过程结束也就意味着局部临时表被Drop。
c、当前会话结束，在会话内创建的所有局部临时表都会被Drop。

3、全局临时表(以"##"开头命名的)在所有的会话内可见，所以在创建全局临时表之前首先检查其是否存在，否则如果已经存在，你将会得到重复创建对象的错误。
a、全局临时表会在创建其的会话结束后被Drop，Drop后其他会话将不能对全局临时表进行引用。
b、引用是在语句级别进行，如:
1.新建查询窗口，运行语句：

CREATE TABLE ##temp(RowID int)
INSERT INTO ##temp VALUES(3)

2.再次新建一个查询窗口，每5秒引用一次全局临时表

While 1=1
BEGIN
SELECT * FROM ##temp
WAITFOR delay '00:00:05'
END

3.回到第一个窗口，关闭窗口。
4.下一次第二个窗口引用时，将产生错误。

4、不能对临时表进行分区。

5、不能对临时表加外键约束。

6、临时表内列的数据类型不能定义成没有在TempDb中没有定义自定义数据类型(自定义数据类型是数据库级别的对象，而临时表属于TempDb)。由于TempDb在每次SQL Server重启后会被自动创建，所以你必须使用startup stored procere来为TempDb创建自定义数据类型。你也可以通过修改Model数据库来达到这一目标。

7、XML列不能定义成XML集合的形式，除非这个集合已经在TempDb中定义。

临时表既可以通过Create Table语句创建，也可以通过"SELECT <select_list> INTO #table"语句创建。你还可以针对临时表用"INSERT INTO #table EXEC stored_procere"这样的语句。
临时表可以拥有命名的约束和索引。但是，当两个用户在同一时间调用同一存储过程时，将会产生”There is already an object named ‘<objectname>’ in the database”这样的错误。所以最好的做法是不用为建立的对象进行命名，而使用系统分配的在TempDb中唯一的。

三、误区

误区1.表变量仅仅在内存中。

误区2.临时表仅仅存储在物理介质中。

这两种观点都是错误的，只有内存足够，表变量和临时表都会在内存中创建和处理。他们也同样可以在任何时间被存入磁盘。

注意表变量的名字是系统分配的，表变量的第一个字符”@”并不是一个字母，所以它并不是一个有效的变量名。系统会在TempDb中为表变量创建一个系统分配的名称，所以任何在sysobjects或sys.tables查找表变量的方法都会失败。

正确的方法应该是我前面例子中的方法，我看到很多人使用如下查询查表变量：

select * from sysobjects where name like'#tempTables%'

上述代码看上去貌似很好用，但会产生多用户的问题。你建立两个连接，在第一个连接中创建临时表，在第二个窗口中运行上面的语句能看到第一个连接创建的临时表，如果你在第二个连接中尝试操作这个临时表，那么可能会产生错误，因为这个临时表不属于你的会话。

误区3.表变量不能拥有索引。

这个误区也同样错误。虽然一旦你创建一个表变量之后，就不能对其进行DDL语句了，这包括Create Index语句。然而你可以在表变量定义的时候为其创建索引)比如如下语句。

declare @MyTableVariable table (RowID intPRIMARY KEY CLUSTERED)

这个语句将会创建一个拥有聚集索引的表变量。由于主键有了对应的聚集索引，所以一个系统命名的索引将会被创建在RowID列上。

下面的例子演示你可以在一个表变量的列上创建唯一约束以及如何建立复合索引。

declare @temp TABLE (
RowID int NOT NULL,
ColA int NOT NULL,
ColB char(1)UNIQUE,
PRIMARY KEY CLUSTERED(RowID, ColA))

1) SQL 并不能为表变量建立统计信息，就像其能为临时表建立统计信息一样。这意味着对于表变量，执行引擎认为其只有1行，这也意味着针对表变量的执行计划并不是最优。虽然估计的执行计划对于表变量和临时表都为1，但是实际的执行计划对于临时表会根据每次存储过程的重编译而改变。如果临时表不存在，在生成执行计划的时候会产生错误。

2) 一旦建立表变量后就无法对其进行DDL语句操作。因此如果需要为表建立索引或者加一列，你需要临时表。

3) 表变量不能使用select …into语句，而临时表可以。

4) 在SQL Server 2008中，你可以将表变量作为参数传入存储过程。但是临时表不行。在SQL Server 2000和2005中表变量也不行。

5) 作用域:表变量仅仅在当前的批处理中有效,并且对任何在其中嵌套的存储过程等不可见。局部临时表只在当前会话中有效，这也包括嵌套的存储过程。但对父存储过程不可见。全局临时表可以在任何会话中可见，但是会随着创建其的会话终止而DROP，其它会话这时就不能再引用全局临时表。

6) 排序规则：表变量使用当前数据库的排序规则，临时表使用TempDb的排序规则。如果它们不兼容，你还需要在查询或者表定义中进行指定。

7) 你如果希望在动态SQL中使用表变量，你必须在动态SQL中定义表变量。而临时表可以提前定义，在动态SQL中进行引用。

四、如何选择

微软推荐使用表变量，如果表中的行数非常小，则使用表变量。很多”网络专家”会告诉你100是一个分界线,因为这是统计信息创建查询计划效率高低的开始。但是我还是希望告诉你针对你的特定需求对临时表和表变量进行测试。很多人在自定义函数中使用表变量，如果你需要在表变量中使用主键和唯一索引，你会发现包含数千行的表变量也依然性能卓越。但如果你需要将表变量和其它表进行join,你会发现由于不精准的执行计划，性能往往会非常差。

为了证明这点，请看本文的附件。附件中代码创建了表变量和临时表.并装入了AdventureWorks数据库的Sales.SalesOrderDetail表。为了得到足够的测试数据，我将这个表中的数据插入了10遍。然后以ModifiedDate 列作为条件将临时表和表变量与原始的Sales.SalesOrderDetail表进行了Join操作，从统计信息来看IO差别显着。从时间来看表变量做join花了50多秒，而临时表仅仅花了8秒。

如果你需要在表建立后对表进行DLL操作，那么选择临时表吧。

临时表和表变量有很多类似的地方。所以有时候并没有具体的细则规定如何选择哪一个。对任何特定的情况，你都需要考虑其各自优缺点并做一些性能测试。下面的表格会让你比较其优略有了更详细的参考。

参考：http://www.cnblogs.com/kissdodog/archive/2013/07/03/3169470.html

‘伍’ sql 如何用临时表优化性能

InnoDB 类型的临时表存在的潜在问题

尽管使用 InnoDB 是性能最佳的，但可能会出现新的潜在问题。在某些特定情况下，您可能会出现磁盘耗尽和服务器中断。

与数据库中的任何其他 InnoDB 表一样，临时表具有自己的表空间文件。新文件与通用表空间一起位于数据目录中，名称为 ibtmp1。它存储所有 tmp 表。不运行手动运行 OPTIMIZE TABLE，表空间文件就会不断增长。如果你不能使用 OPTIMIZE，那么唯一能将 ibtmp1 大小缩小为零的方法，就是重新启动服务器。幸运的是，即使文件无法减小，在执行查询后，临时表也会自动删除，表空间可回收使用。现在，我们想一想以下情境：

• 存在未优化的查询，需要在磁盘上创建非常大的的临时表

• 存在优化的查询，但他们正在磁盘上创建非常大的临时表，因为你正在对此数据集进行计算（统计，分析）

• 高并发连接时，运行相同的查询，伴随临时表的创建

• 没有很多可用空间

• 在这些情况下，文件 ibtmp1 大大增加，很容易耗尽可用空间。这种情况每天发生几次，并且必须重启服务器才能完全缩小 ibtmp1 表空间。使用不可收缩的文件可以轻松耗尽磁盘空间！

那么，如何避免磁盘耗尽和中断呢？




简单的解决方案：使用更大的磁盘

• 虽然可以暂时解决问题，但这不是最佳解决方案。实际上，您可以通过逐步增加磁盘大小，来猜测具体需要的空间。如果环境位于云中，或者在非常大的虚拟平台，这很容易实现。但是使用这种解决方案，您可能会面临不必要的开支。您还可以通过设置以下配置变量将 ibtmp1 文件移动到专用大型磁盘上： [mysqld] innodb_temp_data_file_path = ../../tmp/ibtmp1:12M:autoextend



需要重启 MySQL 。注意，必须将路径指定为相对于数据目录。

设置 ibtmp1 大小的上限

• 例如： [mysqld] innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:10G



在这种情况下，文件不能超过 10GB。可以降低宕机概率，但也是一个危险的解决方案。当数据文件达到最大值时，会查询失败并显示一个错误，提示表已满。



• 退回 MyISAM 将临时表存储在磁盘上

这个解决方案似乎违反直觉，但它可能是快速避免中断的最佳方法，并保证使用所有需要的临时表。

• internal_tmp_disk_storage_engine = MYISAM


• 由于变量是动态的，您也可以在运行时设置它： SET GLOBAL internal_tmp_disk_storage_engine = MYISAM;


• 回到 MyISAM，您将大大降低写满磁盘空间的可能性。实际上，临时表将创建到不同的文件中，并在查询结束时立即删除。

虽然总是有可能看到相同的问题，以防你可以在同一时间运行查询或非常接近。在我的实际案例中，这是避免所有中断的解决方案。

优化你的查询

• 在将存储引擎退回到 MyISAM 以减轻中断发生后，必须花时间分析查询。目标是减小磁盘上临时表的大小。本文的目的不是解释如何调查查询，而是可以依赖慢速日志，像 pt-query-digest 和 EXPLAIN 这样的工具。一些技巧：

• 在表上创建缺少的索引

• 如果不需要，可以在查询中添加更多过滤条件以更少收集的数据

• 重写查询以优化执行计划

• 可以在应用程序中使用队列管理器来序列化它们的执行或减少并发性

但希望在所有优化之后，您可以返回将临时存储引擎设置为 InnoDB 以获得更好的性能。

结论

有时这些改进会产生意想不到的副作用。用于磁盘上临时表的 InnoDB 存储引擎是一个很好的改进，但在某些特定情况下，例如，如果您有未优化查询和很少的可用空间，则可能因“磁盘已满”错误而中断。将 tmp 存储引擎退回到 MyISAM 是避免中断的最快方法，但是为了返回到 InnoDB，查询的优化是更重要的事情。更大或专用的磁盘也可能有所帮助。但这是一个微不足道的建议。

‘陆’ SQL 临时表创建索引会提高效率吗

只要你索引设置合理，当然会提升效率，其实临时表你也可以理解为存储在TempDB数据库中的物理表。
我在做复杂业务流程处理时，经常用到临时表，这样就能避免对原始大数据表的频繁检索，明显对速度有提升

‘柒’ SQL Server中怎样在临时表中创建索引

你说的是临时表还是表变量？
如果是临时表的话跟普通表没有区别：
比如
CREATE TABLE #Test(a int,b int)
CREATE INDEX IX_test ON #Test(a)
如果是表变量不支持直接创建索引，但是可疑在声明表变量的时候设置主键。
比如
declare @table table(id int IDENTITY PRIMARY KEY,a int,b int);

‘捌’ SQL Server 表变量和临时表的区别

临时表与表变量都可以起到“临时”的作用，那么两者主要的区别是什么呢？
这里不讨论创建方式，以及全局临时表、会话临时表这些，主要记录一下个人对两者的主要区别以及适用情况的看法，有什么不对或补充的地方，欢迎讨论。
区别：
1. 表变量存储在内存中，当创建表变量时，sql server不会产生日志，也不会维护统计信息；表变量的字段不能建立索引；不能有约束和默认值。sql server 认为表变量通常只有很少量的数据。
2. 当创建临时表时，sql server会产生日志，统计信息；临时表的字段可以建立索引，所以它可以存储相对较多的数据；可以有约束和默认值，并且有锁机制。可以说临时表与实际的表几乎是一样的，只不过它是轻量和临时的，可以在想要的时候创建和销毁。
适用情况：
表变量： 如果是很少量数据，那么就用表变量。使用表变量的开销要小于临时表。实际项目用到的地方，例如批量删除，需要传递多个id 作为参数，有的人会拼接id字符串，然后在数据库去解析；我的做法通常是传递xml，然后用xml生成表变量，然后链接表删除。
临时表：有多张表链接查询得到一个小结果集，该结果集需要在本次会话或多个会话里多次使用。

‘玖’ mysql临时表会用到索引吗

、UNION查询；
2、用到TEMPTABLE算法或者是UNION查询中的视图；
3、ORDER BY和GROUP BY的子句不一样时；
4、表连接中，ORDER BY的列不是驱动表中的；
5、DISTINCT查询并且加上ORDER BY时；
6、SQL中用到SQL_SMALL_RESULT选项时；
7、FROM中的子查询；
8、子查询或者semi-join时创建的表；