Built in system databases in SQL Server 2005
Built in System Databases in SQL Server 2005 and Later Versions:
master
Purpose: This db holds a special set of tables (system tables) that keeps track of the system as a whole.
Example:
When you create a new db on the server, an entry is placed in the sysdatabases table in the master db.
Note: This db is critical to your system and cannot be deleted.
modelPurpose: As its name implies, it forms a Template for any new db that you create.
Example:
You could add a set of audit tables that you include in every db you build.
You could also create a user groups that would be cloned into every db that was created on the system.
Note: Since this db servers as the template for any other db’s, its required db and must be left on the system; you cannot delete it.
msdbPurpose: A process named SQL Agent Process stores any system tasks in this db.
Example:
Schedule a stored procedure for one time execution, and yes, it has an entry in msdb.
Note: you cannot delete it.
tempdb
Purpose:
i. Whenever you issue a complex or large query that SQL Server needs to build interim tables to solve, it does so in tempdb.
ii. Whenever you create a temporary table of your own, it is created in tempdb.
iii. Whenever there is a need for data to be stored in temporarily, it’s probably stored in tempdb. iv. tempdb is very different from any other db. Not only are the objects within it temporary; the database itself is temporary. It has the distinction of being the only db in your system that is completely rebuilt from scratch every time you start your SQL Server.
Note: Creating objects directly in tempdb is different than creating from your own db.
master
Purpose: This db holds a special set of tables (system tables) that keeps track of the system as a whole.
Example:
When you create a new db on the server, an entry is placed in the sysdatabases table in the master db.
Note: This db is critical to your system and cannot be deleted.
modelPurpose: As its name implies, it forms a Template for any new db that you create.
Example:
You could add a set of audit tables that you include in every db you build.
You could also create a user groups that would be cloned into every db that was created on the system.
Note: Since this db servers as the template for any other db’s, its required db and must be left on the system; you cannot delete it.
msdbPurpose: A process named SQL Agent Process stores any system tasks in this db.
Example:
Schedule a stored procedure for one time execution, and yes, it has an entry in msdb.
Note: you cannot delete it.
tempdb
Purpose:
i. Whenever you issue a complex or large query that SQL Server needs to build interim tables to solve, it does so in tempdb.
ii. Whenever you create a temporary table of your own, it is created in tempdb.
iii. Whenever there is a need for data to be stored in temporarily, it’s probably stored in tempdb. iv. tempdb is very different from any other db. Not only are the objects within it temporary; the database itself is temporary. It has the distinction of being the only db in your system that is completely rebuilt from scratch every time you start your SQL Server.
Note: Creating objects directly in tempdb is different than creating from your own db.
Comments
Post a Comment