23 August 2010

tempdb Database

The tempdb is a  system database which is a global resource, available to all users connected to the instance of SQL Server and is used to hold the following:
  • Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
  • Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for sorting.
tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database

Advantages
       One Of The important feature of tempdb is Temporary tables and table variables may be cached. Caching allows operations that drop and create the temporary objects to execute very quickly and reduces page allocation contention. 
Disadvantages:-
      Some basic drawbacks in te,pdb include we cant drop the db,creation of database snapshot is not possible,cant change the database owner(Its owned by dbo.) ,Setting the database to OFFLINE