Execute the following Microsoft SQL Server T-SQL example script in Management Studio Query Editor to create the global temporary table ##Washington with SELECT INTO.
A global temporary table is visible to any session (connection on server) while in existence. It lasts until all users that are referencing the table disconnect.
A local temporary table, like #California below, is visible only the local session (connection) and child session created by dynamic SQL (sp_executeSQL). The temporary table is localized by an auto-generated suffix which is added to the table name. It is deleted after the user disconnects.
-- Create global temporary table with select into - SQL select into create table
USE AdventureWorks;
SELECT c.LastName, c.FirstName, a.City, s.StateProvinceCode
INTO ##Washington
FROM Person.Contact c JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID
INNER JOIN HumanResources.EmployeeAddress ea
ON e.EmployeeID = ea.EmployeeID
INNER JOIN Person.Address a
ON ea.AddressID = a.AddressID
INNER JOIN Person.StateProvince s
ON a.StateProvinceID = s.StateProvinceID
WHERE s.StateProvinceCode = 'WA';
GO
SELECT TOP (5) * FROM ##Washington
ORDER BY NEWID()
GO
/* Results
LastName FirstName City StateProvinceCode
Ford Jeffrey Monroe WA
Zwilling Michael Edmonds WA
Tibbott Diane Kenmore WA
Harrington Mark Issaquah WA
Nay Lorraine Edmonds WA
*/
-- Cleanup
DROP TABLE ##Washington
GO
-- Create local temporary table with select into - SQL select into create table
USE AdventureWorks;
SELECT c.LastName, c.FirstName, a.City, s.StateProvinceCode
INTO #California
FROM Person.Contact c JOIN HumanResources.Employee e
ON c.ContactID = e.ContactID
INNER JOIN HumanResources.EmployeeAddress ea
ON e.EmployeeID = ea.EmployeeID
INNER JOIN Person.Address a
ON ea.AddressID = a.AddressID
INNER JOIN Person.StateProvince s
ON a.StateProvinceID = s.StateProvinceID
WHERE s.StateProvinceCode = 'CA';
GO
SELECT TOP (5) * FROM #California
ORDER BY NEWID()
GO
/* Results
LastName FirstName City StateProvinceCode
Raheem Michael San Francisco CA
Ito Shu San Francisco CA
*/
-- Cleanup
DROP TABLE #California
GO
Global Temporary Tables vs Local Temporary Tables
We are developing an application that (very simply put) will copy data from many local databases to one central. In this process lots of rows gets inserted to a temporary table. This temporary table is later used to check which rows are up to date, which rows should be deleted and which rows needs to be updated.
In a current test we noticed a huge performance difference when populating the temporary table. If we had a global temporary table it took about 3 minutes to populate the table with 700,000 rows. If we had a local temporary table, the same task took 9 minutes.
The only difference between the questions are that # gets switched to ##.
We've tested on two different SQL Server Std 2005 running on different hardware with the same result.
Server 1:
Microsoft SQL Server 2005 - 9.00.3159.00 (Intel X86) Mar 23 2007 16:15:11 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
Server 2:
Microsoft SQL Server 2005 - 9.00.3233.00 (X64) Mar 6 2008 21:58:47 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
Can anyone explain the differnce in performance?
If the performance gap is expected, would you recommend us to convert to global temp tables considering that under peak we will have 30-40 sessions doing synchronizations at the same time? Each temporary table is crated with a unique guid as a name.
CREATE TABLE #3ILRRRF920M64UVI (
[Table] VarChar(16),
[GUID] Char(16),
[DateChanged] DateTime,
[ExistingObjectDateChanged] DateTime
)
INSERT INTO #3ILRRRF920M64UVI VALUES( 'CFil', '2TKJQLNEV1PKK1P1', '2000-08-28 16:22:18.000', NULL )
INSERT INTO #3ILRRRF920M64UVI VALUES( 'CFil', '34DLCD7S0E0JFGDB', '2005-01-22 21:36:53.000', NULL )
... another 700.000 times...
CREATE INDEX Tmp_IGUID ON #3ILRRRF920M64UVI ([GUID])
Temporary Tables
By Bill Graziano on 17 January 2001 | 15 Comments | Tags: Application Design, Table Design
Sophie writes "Can you use a Stored Procedure to open a table and copy data to a sort of virtual table (or a records set) so that you can change the values with and not affect the actual data in the actual table. And then return the results of the virtual table? Thanks!" This article covers temporary tables and tables variables and is updated for SQL Server 2005.
I love questions like this. This question is just a perfect lead in to discuss temporary tables. Here I am struggling to find a topic to write about and I get this wonderful question. Thank you very much Sophie.
Temporary Tables
The simple answer is yes you can. Let look at a simple CREATE TABLE statement:
CREATE TABLE #Yaks ( YakID int, YakName char(30) )
You'll notice I prefixed the table with a pound sign (#). This tells SQL Server that this table is a local temporary table. This table is only visible to this session of SQL Server. When I close this session, the table will be automatically dropped. You can treat this table just like any other table with a few exceptions. The only real major one is that you can't have foreign key constraints on a temporary table. The others are covered in Books Online.
Temporary tables are created in tempdb. If you run this query:
CREATE TABLE #Yaks ( YakID int, YakName char(30) ) select name from tempdb..sysobjects where name like '#yak%' drop table #yaks
You'll get something like this:
name ------------------------------------------------------------------------------------ #Yaks_________________________ . . . ___________________________________00000000001D (1 row(s) affected)
except that I took about fifty underscores out to make it readable. SQL Server stores the object with a some type of unique number appended on the end of the name. It does all this for you automatically. You just have to refer to #Yaks in your code.
If two different users both create a #Yaks table each will have their own copy of it. The exact same code will run properly on both connections. Any temporary table created inside a stored procedure is automatically dropped when the stored procedure finishes executing. If stored procedure A creates a temporary table and calls stored procedure B, then B will be able to use the temporary table that A created. It's generally considered good coding practice to explicitly drop every temporary table you create. If you are running scripts through SQL Server Management Studio or Query Analyzer the temporary tables are kept until you explicitly drop them or until you close the session.
Now let's get back to your question. The best way to use a temporary table is to create it and then fill it with data. This goes something like this:
CREATE TABLE #TibetanYaks( YakID int, YakName char(30) ) INSERT INTO #TibetanYaks (YakID, YakName) SELECT YakID, YakName FROM dbo.Yaks WHERE YakType = 'Tibetan' -- Do some stuff with the table drop table #TibetanYaks
Obviously, this DBA knows their yaks as they're selecting the famed Tibetan yaks, the Cadillac of yaks. Temporary tables are usually pretty quick. Since you are creating and deleting them on the fly, they are usually only cached in memory.
Table Variables
If you are using SQL Server 2000 or higher, you can take advantage of the new TABLE variable type. These are similar to temporary tables except with more flexibility and they always stay in memory. The code above using a table variable might look like this:
DECLARE @TibetanYaks TABLE ( YakID int, YakName char(30) ) INSERT INTO @TibetanYaks (YakID, YakName) SELECT YakID, YakName FROM dbo.Yaks WHERE YakType = 'Tibetan' -- Do some stuff with the table
Table variables don't need to be dropped when you are done with them.
Which to Use
- If you have less than 100 rows generally use a table variable. Otherwise use a temporary table. This is because SQL Server won't create statistics on table variables.
- If you need to create indexes on it then you must use a temporary table.
- When using temporary tables always create them and create any indexes and then use them. This will help reduce recompilations. The impact of this is reduced starting in SQL Server 2005 but it's still a good idea.
Answering the Question
And all this brings us back to your question. The final answer to your question might look something like this:
DECLARE @TibetanYaks TABLE ( YakID int, YakName char(30) ) INSERT INTO @TibetanYaks (YakID, YakName) SELECT YakID, YakName FROM dbo.Yaks WHERE YakType = 'Tibetan' UPDATE @TibetanYaks SET YakName = UPPER(YakName) SELECT * FROM @TibetanYaks
Global Temporary Tables
You can also create global temporary tables. These are named with two pound signs. For example, ##YakHerders is a global temporary table. Global temporary tables are visible to all SQL Server connections. When you create one of these, all the users can see it. These are rarely used in SQL Server.
Summary
That shows you an example of creating a temporary table, modifying it, and returning the values to the calling program. I hope this gives you what you were looking for.
No comments:
Post a Comment