March 22, 2010 at 6:07 am
Can you please give me some details about temporary tables?
1. If I use in function. Can I call the values outside the function?
2. Will the temporary table dropped automatically?
3. Why we use temp tables instead of creating tables? because of database space?
4. Do I need to give spec for temp table also if i want to create temp table?
Please help me to know this?
March 22, 2010 at 6:18 am
A temp table is dropped when the connection that created it is closed or when the procedure that it was created in ends.
You can't create a temp table in a function, it's not permitted. You can declare a table variable.
You use temp tables not normal tables because normal users should never have create table permissions in the application database. Temp tables don't need create table permissions.
What do you mean by 'spec of table'? The syntax for creating a temp table is almost the same as for a user table. The table's name is just prefixed with a #.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2010 at 6:21 am
Start reading here:
http://msdn.microsoft.com/en-us/library/ms186986.aspx
There are two types of temporary tables: local and global. Local temporary tables are visible only to their creators during the same connection to an instance of SQL Server as when the tables were first created or referenced. Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server.
http://msdn.microsoft.com/en-us/library/ms177399.aspx
Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when they are no longer used.
There are two types of temporary tables: local and global. They differ from each other in their names, their visibility, and their availability. Local temporary tables have a single number sign (#) as the first character of their names; they are visible only to the current connection for the user, and they are deleted when the user disconnects from the instance of SQL Server. Global temporary tables have two number signs (##) as the first characters of their names; they are visible to any user after they are created, and they are deleted when all users referencing the table disconnect from the instance of SQL Server.
For example, if you create the table employees, the table can be used by any person who has the security permissions in the database to use it, until the table is deleted. If a database session creates the local temporary table #employees, only the session can work with the table, and it is deleted when the session disconnects. If you create the global temporary table ##employees, any user in the database can work with this table. If no other user works with this table after you create it, the table is deleted when you disconnect. If another user works with the table after you create it, SQL Server deletes it after you disconnect and after all other sessions are no longer actively using it.
March 22, 2010 at 6:24 am
Can I declare a temp table outside the procedure and call it inside the procedure?
In procedure we can create a temp table?
March 22, 2010 at 6:27 am
If I am creating global temporary table. it will not deleted once i disconnect the server? or if any one using at that time only it will not be deleted?
March 22, 2010 at 6:29 am
chandrasekaran.ganapathy (3/22/2010)
Can I declare a temp table outside the procedure and call it inside the procedure?In procedure we can create a temp table?
Yes and yes
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2010 at 6:39 am
When it will be deleted automatically?
March 22, 2010 at 6:52 am
chandrasekaran.ganapathy
When it will be deleted automatically?
From previous posting in this forum
Local temporary tables are deleted after the user disconnects from the instance of SQL Server. Global temporary tables are visible to any user and any connection after they are created, and are deleted when all users that are referencing the table disconnect from the instance of SQL Server
March 22, 2010 at 7:20 am
Did you bother to read the links that bitbucket provided? If not, please do so. If you still have questions after reading them, come back and ask.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 22, 2010 at 7:27 am
chandrasekaran.ganapathy (3/22/2010)
When it will be deleted automatically?
Gosh... haven't you stopped to read any of the responses or visit any of the web sites that BitBucket posted?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply