Can you please tell importance of default databases provided by SQLserver?

Question

In Sqlsever Enterprise manager, there are some default databases are provided like tempdb and etc. What is significance of those databases?

Answer 1

TempDB is used for temporary work in SQL Server. Anytime you create a temp table that storage is done inside of TempDB. Here is a very good article from MSDN

Here are some points referenced from the MSDN:

The tempdb system database is a global resource that is 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 spools or sorting.

•Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.

•Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

There is also the master database (holds information about all databases), Model database, MSDB (stores information on the sql agent, dts, jobs, etc).

More info here as well

Answer 2

SQL Server uses tempdb to store internal objects such as the intermediate results of a query. You can get more details here.

Answer 3

MASTER - This keeps all server-level information, and meta-data about all databases on the server. Don't lose this :)

MSDB - Holds information about SQL Agent jobs and job run history

TEMPDB - Used as a temporary "work space" for temporary tables and lots of other stuff (like sorting and grouping)

MODEL - When you create a new, blank database, it makes a copy of MODEL as a template

DISTRIBUTION - (You will only see this on servers where you have set up replication) Holds records pending for replication.