The SQL Server Management Studio Main Screen
The SSMS main screen includes some standard elements, such as a menu bar and toolbar at the top of the screen and a status bar at the bottom. These are used to perform actions within the tool. The main area of the window is divided into two sections. To the left is the Object Explorer. This displays an expandable tree containing the hierarchy of objects from the SQL Server instance. To the right, the Object Explorer Details section shows the contents or configuration of the selected item in the Object Explorer.
System Databases
On starting SSMS, you will find that some databases already exist. These will be separated into two distinct areas that are accessible by expanding the Databases section of the Object Explorer tree. Within this branch of the tree is a further, expandable node named "System Databases". Databases created by the system appear here whilst user-created databases are displayed at the higher level.
All SQL Server instances include four system databases. These are named master, model, msdb and tempdb.
The "master" Database
The master database contains a series of system tables, stored procedures and other database objects. This database is critical to the correct operation of the entire SQL Server instance. It includes all security information, configuration information for all databases and many other system settings. In some, rare situations it can be useful to query this database. However, in general your only interaction with the master database should be ensuring that it is backed up. Incorrect changes made to this database or corruption of its data can potentially render the entire system unusable.
The "model" Database
The model database is a template for all databases that you create. When you add a new database to a SQL Server instance, a copy of the model is made under the name that you provide. This means that any changes that you make to model will be duplicated in all new databases.
The "msdb" Database
One of the Windows Services that forms part of Microsoft SQL Server is the SQL Server Agent. This service permits the scheduling of tasks such as backups and maintenance jobs. When a SQL Server Agent task is created, the configuration of the job is held in the msdb database.
The "tempdb" Database
The tempdb database is a special system database that is not persisted when the SQL Server service is inactive. Each time the service is started, tempdb is rebuilt. This database is used to hold information temporarily during complex queries, either because SQL Server requires it or because a procedure or query that you write creates a temporary table.
Creating a Database
Creating a new database is a relatively simple task. However, there are a number of options that can be set during creation to alter the behaviour of the new database. In the remainder of this article we will create the JoBS database whilst examining the most relevant configuration items.
NB: In this tutorial we will be creating a database named JoBS. In the event that your SQL Server instance already has a JoBS database, select a different name and use this new name in place of JoBS for the rest of the tutorial.
The New Database Dialog Box
The simplest manner in which to begin the creation of a new database is to right-click the "Databases" tree node in the Object Explorer. This displays a context-sensitive menu. From here, select "New Database..." The New Database dialog box will appear:
8 June 2008