Physically Creating a Database

How Is Information Stored?

A SQL Server database contains the following files, as illustrated in Table 5-10.

The following list is a simplified version of the transactional process.

• Your application sends a group of T-SQL statements in a transaction. To learn more about transactions, see Chapter 10, Using Transactions to Provide Safe Database Concurrency in Microsoft SQL Server 2005: Applied Techniques Step by Step .

• The server reads the information needed to perform changes to the database. This information is read from primary and/or secondary files. If the information was previously in RAM memory, this step is skipped .

• The server modifies the information in RAM memory and marks the information as dirty. Dirty means that the information has been modified in RAM, but changes have not been saved to disk.

• The server saves all modifications in the transaction log. The transaction log keeps a record of all database modifications and will be used in case of server failure.

• The server informs the application that the transaction is committed. Committed means that the server has saved the modification and the application may continue.

• Eventually, an event called CHECKPOINT occurs in the server, and all dirty information of committed transactions is saved to disk in primary and secondary files.

Designing Database Storage

When designing database storage, consider the following guidelines.

• Use formatted disks from the New Technology File System (NTFS) to store database files. NTFS offers better security and recoverability than a file allocation table (FAT) file system.

• Do not use compressed volumes or files. Compressed volumes dramatically decrease the performance of a database.

• Use a separate disk or disk subsystem for log files because log files access information in a sequential manner, while data files are accessed randomly using separate disks to increase performance. Separating the log file also increases recoverability. Use different physical disks instead of partitions of the same disk.

• Use redundant array of inexpensive disks (RAID) technology to store database and log files. Primary and secondary database files are best stored in RAID 5 or RAID 10 disk subsystems. Log files are best stored in RAID 1 or RAID 10 disk subsystems.

• Spreading the database in multiple disk subsystems increases performance. Use as many disks as possible to distribute the database load. Performance is increased when using primary and secondary files, but not when using multiple log files.

Creating a Database

The following process describes how to create a database using Microsoft SQL Server Management Studio.

Using SQL Server Management Studio to Create a Database

1. From the Start menu, select Programs Microsoft SQL Server 2005 Sql Server Management Studio.

2. Connect to the database engine that will host the database.

3. In Object Explorer, right-click the Databases folder and choose New Database from the context menu.

4. When the New Database dialog box is displayed, name the database.

   

5. In the Database Files grid, scroll to the right to review the path of the file and, if necessary, change the path to support your storage design decisions.

6. If you want to use multiple data files, select the Add button to create secondary files.

7. If necessary, choose options in the Select A Page task pane on the left.

   

8. Configure the database options.

9. Click OK to create the database.

Using T-SQL to Create a Database

To create a database using T-SQL statements, use the following syntax.

CREATE DATABASE Tests
ON (NAME = Tests_dat
, FILENAME = 'C:\Data\TestDat.mdf'
, SIZE = 20 MB)
LOG
ON (NAME = Sales_log
, FILENAME = 'E:\Logs\TestLog.ldf'
, SIZE = 5MB);

Designing Database Schemas to Logically Group Database Objects

Schemas are namespaces that simplify database object management. By using schemas, you can organize tables and other database objects as well as make it easier to grant permissions. Use schemas to create categories of tables, help users and developers navigate your database, or create groups of tables that can be managed from a single point.

Using SQL Server Management Studio to Create Database Schemas

1. Open SQL Server Management Studio.

2. Connect to the database engine that hosts the database.

3. Expand the Databases folder, expand the database you will be working with, and then expand the Security folder.

4. Under the Security folder, right-click the Schemas folder and choose New Schema.

5. When the New Schema dialog box is displayed, name the schema.

   

6. Press OK to create the schema.

Using T-SQL to Create Database Schemas

To create a schema using T-SQL statements, use the following syntax:

CREATE SCHEMA Inventory

Creating Tables to Implement a Design

After you have designed your solution, you will begin the implementation of your solution by creating tables to store the data.

Using SQL Server Management Studio to Create Tables

1. Open SQL Server Management Studio.

2. Connect to the database engine that will host the database.

3. Expand the Databases folder, and expand the database you will be working with.

4. Right-click the Tables folder and choose New Table.

5. Add a column using the Columns grid, name the column, assign a datatype, and specify whether the column will allow NULL values.

6. Additional column configurations may be set in the Column Properties section.

7. Add all necessary columns.

   

Defining the Primary Key in a Table Using SQL Server Management Studio

1. In the Table Definition window (shown in Step 7 above), select the columns you want to configure as the primary key. (You can re-open this window for an existing table by right-clicking the table in Object Explorer and choosing Modify from the context menu.)

2. Click the Set Primary Key button on the toolbar, or right-click the selected columns and choose Set Primary Key from the context menu.

Defining a CHECK Constraint Using SQL Server Management Studio

1. In the Table Definition window, click the Manage Check Constraints button in the toolbar.

2. Select the Add button.

3. In the Expression property, specify the validation expression.

   

Using T-SQL to Create Tables

To create a table using T-SQL statements, use the following syntax.

CREATE TABLE Employee (EmployeeNumber       INT             NOT NULL
      PRIMARY KEY
      CHECK(EmployeeNumber>0)
   , EmployeeSSN        VARCHAR(15)     NOT NULL
      CHECK(EmployeeSSN
      LIKE
   '[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]')
      UNIQUE
   , HireDate           SMALLDATETIME   NOT NULL
      CHECK(HireDate>'19950101')
   , DepartmentCode  CHAR(3)         NOT NULL
      FOREIGN KEY REFERENCES Departments(DepartmentCode)
   , TerminationDateSMALLDATETIMENULL
   ,  CHECK(TerminationDate IS NULL OR TerminationDate>HireDate))