- It is exclusive to the SQL session in which it is created; it is not visible outside of the session and does not share its data with other sessions.
- It can be joined to any table in the database. Indexes and column default values can be defined for a temporary table and persist during the life of the table.
- It can have the same name as a permanent table created during a later session, but not the same name as a permanent table created during the same or a prior session
- Any queries submitted by the user of a temporary table are automatically queried against that table. This is true even if a permanent table with the same name exists or is subsequently created by a user in another session. Temporary tables always take precedence over permanent tables.
- It does not have to be locked while it is being updated because other users cannot access the temporary table.
- To avoid system table contention, it is not permanently cataloged in the system tables. Information about a temporary table resides in memory and appears in the system tables during the user's session but disappears when the session ends.
- It is automatically dropped at the end of the SQL session; however, it can also be dropped during the session with the DROP TABLE statement.
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
Temporary tables are created in tempdb. If you run this query:
select name from tempdb..sysobjects where name like '#Emp%'
No comments:
Post a Comment