是否有必要使用 # 在 SQL Server 中创建临时表? [英] Is it necessary to use # for creating temp tables in SQL server?

问题描述

在SQL server中创建临时表之前是否需要使用#?

Is it necessary to use # before creating a temporary table in SQL server?

示例:

SELECT column1, column2, someInt, someVarChar 
INTO ItemBack1 
FROM table2
WHERE table2.ID = 7

对于 ItemBack1 是否需要使用 # 符号?

For ItemBack1 is it necessary to use the # symbol?

如果不是，那么#在创建临时表时有什么用?

If not, then what is the use of # in creating temp tables?

推荐答案

是的.您需要在表名前加上#"(哈希)以创建临时表.

Yes. You need to prefix the table name with "#" (hash) to create temporary tables.

如果您以后不需要该表，请继续创造它.临时表与普通表非常相似.但是，它是在 tempdb 中创建的.此外，它只能通过当前会话访问，即对于 EG:如果另一个用户尝试访问您创建的临时表，他将无法访问.

If you do NOT need the table later, go ahead & create it. Temporary Tables are very much like normal tables. However, it gets created in tempdb. Also, it is only accessible via the current session i.e. For EG: if another user tries to access the temp table created by you, he'll not be able to do so.

##"(双哈希创建全局"临时表，其他会话也可以访问该表.

"##" (double-hash creates "Global" temp table that can be accessed by other sessions as well.

有关临时表的基础知识，请参阅以下链接:http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in-SQL-Server-2005

Refer the below link for the Basics of Temporary Tables: http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in-SQL-Server-2005

如果您的表格内容少于 5000 行 &不包含 nvarchar(MAX)、varbinary(MAX) 等数据类型，请考虑使用表变量.

If the content of your table is less than 5000 rows & does NOT contain data types such as nvarchar(MAX), varbinary(MAX), consider using Table Variables.

它们是最快的，因为它们就像存储在 RAM 中的任何其他变量一样. 它们也存储在 tempdb 中，而不是存储在 RAM 中.

DECLARE @ItemBack1 TABLE
(
 column1 int,
 column2 int,
 someInt int,
 someVarChar nvarchar(50)
);

INSERT INTO @ItemBack1
SELECT column1, 
       column2, 
       someInt, 
       someVarChar 
  FROM table2
 WHERE table2.ID = 7;

关于表变量的更多信息:http://odetocode.com/articles/365.aspx

More Info on Table Variables: http://odetocode.com/articles/365.aspx

这篇关于是否有必要使用 # 在 SQL Server 中创建临时表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！