Updating multiple tables sql server
Multiple users can't share a local temporary table because it is local to one user session. A global temporary table, also exists for the duration of a user session or the procedure that created the table.
You also can't grant or revoke permissions on the local temporary table. When the last user session that references the table disconnects, the global temporary table is lost.
"Temporary tables are similar to permanent tables, except temporary tables are stored in tempdb and are deleted automatically when no longer in use.
The two types of temporary tables, local and global, differ from each other in their names, their visibility, and their lifetimes." The local table we created in our procedure by using sp_executesql won't be accessible to either the procedure or its child procedures.
Executive Summary: The script included with this article lets database administrators move tables and indexed views between filegroups in SQL Server 2000 and SQL Server 2005 more efficiently than moving tables and indexed views manually.
Filegroups are useful tools for controlling a database’s location and size because they let DBAs easily move files between different disks or network computers.
Multiple users could create a temp table named #Table X and any queries run simultaneously would not affect one another - they would remain autonomous transactions and the tables would remain autonomous objects.
You may notice that my sample temporary table name started with a "#" sign.
But you can't grant or revoke permissions on the global temporary table because it's always available to everyone.
Local and global temporary tables differ in a subtle way.
Let's look at what SQL Server says about temporary tables.
If created inside a stored procedure they are destroyed upon completion of the stored procedure.
Furthermore, the scope of any particular temporary table is the session in which it is created; meaning it is only visible to the current user.