Databases in MS SQL Server 2000

A Microsoft SQL Server database is a collection of objects that hold and manipulate data. A typical SQL Server installation has only a handful of databases, but it’s not unusual for a single installation to contain several dozen databases. Theoretically, one SQL Server installation can have as many as 32,767 databases. But practically speaking, this limit would never be reached. SQL Server has 6 Built In databases that provide Template Databases, Hold System Information and Backup Information etc. The details of each database is given below.

--> MASTER
The master database is composed of system tables that keep track of the server installation as a whole and all other databases that are subsequently created. Although every database has a set of system catalogs that maintain information about objects it contains, the master database has system catalogs that keep information about disk space, file allocations, usage, systemwide configuration settings, login accounts, the existence of other databases, and the existence of other SQL servers (for distributed operations). The master database is absolutely critical to your system, so be sure to always keep a current backup copy of it. Operations such as creating another database, changing configuration values, and modifying login accounts all make modifications to master, so after performing such activities, you should back up master.

-->MODEL
The model database is simply a template database. Every time you create a new database, SQL Server makes a copy of model to form the basis of the new database. If you’d like every new database to start out with certain objects or permissions, you can put them in model, and all new databases will inherit them.

-->TEMPDB
The temporary database, tempdb, is a workspace. SQL Server’s tempdb database is unique among all other databases because it’s re-created—not recovered—every time SQL Server is restarted. It’s used for temporary tables explicitly created by users, for worktables to hold intermediate results created internally by SQL Server during query processing and sorting, and for the materialization of static cursors and the keys of keyset cursors. Operations within tempdb are logged so that transactions on temporary tables can be rolled back, but the records in the log contain only enough information to roll back a transaction, not to recover (or redo) it. No recovery information is needed because every time SQL Server is started, tempdb is completely re-created; any previous user-created temporary objects (that is, all your tables and data) will be gone. Logging only enough information for rolling back transactions in tempdb was a new feature in SQL Server 7 and can potentially increase the performance of INSERT statements to make them up to four times faster than inserts in other (fully logged) databases.
All users have the privileges to create and use private and global temporary tables that reside in tempdb. However, by default, users don’t have the privileges to USE tempdb and then create a table there (unless the table name is prefaced with # or ##). But you can easily add such privileges to model, from which tempdb is copied every time SQL Server is restarted, or you can grant the privileges in an autostart procedure that runs each time SQL Server is restarted. If you choose to add those privileges to the model database, you must remember to revoke them on any other new databases that you subsequently create if you don’t want them to appear there as well.