| Local temporary table | Global temporary table |
| A Local temporary table is defined by giving it a prefix of # and is scoped to the session in which you created it. | Global temporary tables can be seen by all sessions connected to the server and are defined by a prefix of ##. |
| An example that creates a local temporary table, populates it with one row, and then selects from it: CREATE TABLE #TempTable ( SSN INT, NAME CHAR(3) ) ; INSERT INTO #TempTable ( SSN, NAME ) VALUES ( 1, ‘Sachin’ ) ; GO SELECT * FROM #TempTable ; GO DROP TABLE #TempTable; | An example that creates a Global temporary table, populates it with one row, and then selects from it: CREATE TABLE ##TempTable ( SSN INT, NAME CHAR(3) ) ; INSERT INTO ##TempTable ( SSN, NAME ) VALUES ( 1, ‘Sachin’ ) ; GO SELECT * FROM ##TempTable ; GO DROP TABLE ##TempTable; |
| Local temporary tables are dropped by using the DROP statement or are automatically removed from memory when the user connection is closed. | Global temporary tables are removed from SQL Server if explicitly dropped by DROP TABLE. They are also automatically removed after the connection that created it disconnects and the global temporary table is no longer referenced by other connections. |
| It’s easy to be caught out by the scope of a temporary table with SSIS (SQL Server Integration Services). By default, each task in SSIS uses a different connection,which rules out the use of local temporary tables. You can configure SSIS to use a single connection for all tasks, which allows local temporary tables to be used, but be aware that you’ll probably be sacrificing some performance. | - |
References:
1. Difference between temporary variable and temporary table are also explained in depth in "PROFESSIONAL SQL SERVER 2008 INTERNALS AND TROUBLESHOOTING" book.
2. Book: Pro SQL Server 2008 Administration
3. Book: SQL Server 2008 Transact-SQL Recipes
No comments:
Post a Comment