We know that the Oracle Database installation architecture can be 1 databases corresponding to 1 or more instances. In SQL Server, the schema is the exact opposite of Oracle, which is 1 instances (the default instance named hostname hostname) contains multiple databases, in the SQL Server database (2000,2005,2008, etc.), and mainly contains 4 default databases. The master database, model database, tempdb database, and msdb database , respectively. These databases are the heart and soul of SQL Server. In addition, two instance databases are installed by default: TheNorthwind database and the pubs database.
System Database
master database |
The master database controls all aspects of SQL Server. This database includes all configuration information, user logon information, and information about the process that is currently running on the server. |
Model database |
The model database is the template that is created for all user databases. When you create a new database, SQL Server creates a copy of all the objects in the model database and moves it to the new database. After the template object is copied to the new user database, all the extra space for the database is filled with empty pages.
|
tempdb database |
The tempdb database is a very special database that is used by all users who access your SQL Server. This library is used to hold all temporary tables, stored procedures, and other temporary objects created by SQL Server. For example, the tempdb database is used for sorting. The data is put into the tempdb database, and the results are returned to the user after the sequence is completed. Every time SQL Server restarts, it empties the tempdb database and rebuilds it. Never create a table that needs to be persisted in the tempdb database |
msdb database |
The msdb database is a special case in SQL Server. If you look at the actual definition of this database, you will find that it is actually a user database. The difference is what SQL Server does with this database. All task schedules, alarms, and operators are stored in the msdb database. Another function of the library is to store all the backup history. The SQL Server Agent will use this library. |
Sample Database
Northwind sample Database |
The Northwind Traders sample database contains sales data for a fictitious company named Northwind Traders, which engages in the import and export trade of specialty foods from around the world |
The Pubs sample Database |
The pubs sample database is modeled on a book publishing company and is used to demonstrate Microsoft? SQL Server? Many of the options available in the database. The database and the tables in it are often used in the examples described in document content. |
The role of default tables in SQL Server creation in databases
name |
Address |
Description |
Sysaltfiles |
Primary database |
Save the file for the database |
Syscharsets |
Primary database |
Character Set and sort order |
Sysconfigures |
Primary database |
Configuration options |
Syscurconfigs |
Primary database |
Current configuration options |
sysdatabases |
Primary database |
Databases in the server |
Syslanguages |
Primary database |
Language |
syslogins |
Primary database |
Login account Information |
Sysoledbusers |
Primary database |
Link Server login Information |
sysprocesses |
Primary database |
Process |
Sysremotelogins |
Primary database |
Telnet account |
|
|
|
syscolumns |
Each database |
Column |
Sysconstrains |
Each database |
Limit |
Sysfilegroups |
Each database |
File groups |
Sysfiles |
Each database |
File |
Sysforeignkeys |
Each database |
External keywords |
sysindexes |
Each database |
Index |
Sysmenbers |
Each database |
Role members |
sysobjects |
Each database |
All database objects |
Syspermissions |
Each database |
Permissions |
Systypes |
Each database |
User-defined data types |
sysusers |
Each database |
User |
???? Role of default database and default table in SQL Server