是否需要使用 # 在 SQL 服务器中创建临时表？

在 SQL 服务器中创建临时表之前是否需要使用 #？

例如:

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

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

如果没有，那么 #在创建临时表中的用途是什么？

159760 次浏览
小开
最佳答案

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

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.

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

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.

They are the fastest as they are just like any other variables which are stored in the RAM. They are stored in tempdb as well, not in 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;

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

小开

The difference between this two tables ItemBack1 and #ItemBack1 is that the first on is persistent (permanent) where as the other is temporary.

Now if take a look at your question again

Is it necessary to Use # for creating temp table in sql server?

The answer is Yes, because without this preceding # the table will not be a temporary table, it will be independent of all sessions and scopes.


提交答案