Creating Database Objects / Create a Database

The organization of a database involves many different objects. All objects of a database are either physical or logical. The physical objects are related to the organization of the data on the physical device (disk). The Database Engine’s physical objects are files and filegroups. Logical objects represent a user’s view of a database. Databases, tables, columns, and views (virtual tables) are examples of logical objects.

The first database object that has to be created is a database itself. The Database Engine manages both system and user databases. An authorized user can create user databases, while system databases are generated during the installation of the database system.

This chapter describes the creation, alteration, and removal of user databases.

Creating a Database

Two basic methods are used to create a database. The first method involves using Object Explorer in SQL Server Management Studio. The second method involves using the Transact-SQL statement CREATE DATABASE. This statement has the following general form, the details of which are discussed next:

CREATE DATABASE db_name
	[ON [PRIMARY] { file_spec1} ,...]
	[LOG ON {file_spec2} ,...]
		[COLLATE collation_name]
	[FOR {ATTACH | ATTACH_REBUILD_LOG } ]

Note – For the syntax of the Transact-SQL statements, the conventions used are those described in the section “Syntax Conventions“. According to the conventions, optional items appear in brackets, [ ]. Items written in braces, { }, and followed by “…” are items that can be repeated any number of times.

db_name is the name of the database. The maximum size of a database name is 128 characters. (The rules for identifiers described in Chapter “SQL Components” apply to database names.) The maximum number of databases managed by a single system is 32,767.

All databases are stored in files. These files can be explicitly specified by the system administrator or implicitly provided by the system. If the ON option exists in the CREATE DATABASE statement, all files containing the data of a database are explicitly specified.

Note – The Database Engine uses disk files to store data. Each disk file contains data of a single database. Files themselves can be organized into filegroups. Filegroups provide the ability to distribute data over different disk drives and to back up and restore subsets of the database (useful for very large databases).

file_spec1 represents a file specification, which includes further options such as the logical name of the file, the physical name, and the size. The PRIMARY option specifies the first (and most important) file that contains system tables and other important internal information concerning the database. If the PRIMARY option is omitted, the first file listed in the specification is used as the primary file.

A login account of the Database Engine that is used to create a database is called a database owner. A database can have one owner, who always corresponds to a login account name. The login account, which is the database owner, has the special name dbo. This name is always used in relation to a database it owns.

dbo uses the LOG ON option to define one or more files as the physical destination of the transaction log of the database. If the LOG ON option is not specified, the transaction log of the database will still be created because every database must have at least one transaction log file. (The Database Engine keeps a record of each change it makes to the database. The system keeps all those records, in particular before and after values, in one or more files called the transaction log. Each database of the system has its own transaction log.)

With the COLLATE option, you can specify the default collation for the database. If the COLLATE option is not specified, the database is assigned the default collation of the model database, which is the same as the default collation of the database system.

The FOR ATTACH option specifies that the database is created by attaching an existing set of files. If this option is used, you have to explicitly specify the first primary file. The FOR ATTACH_REBUILD_LOG option specifies that the database is created by attaching an existing set of operating system files. (Attaching and detaching a database is described later in this chapter.)

During the creation of a new database, the Database Engine uses the model database as a template. The properties of the model database can be changed to suit the personal conception of the system administrator.

Note – If you have a database object that should exist in each user database, you should create that object in the model database first.

Example 3.1 creates a simple database without any further specifications. To execute this statement, type it in the Query Editor window of SQL Server Management Studio and press f5.

Example 3.1

USE master;
CREATE DATABASE sample;Code language: PHP (php)

Example 3.1 creates a database named sample. This concise form of the CREATE DATABASE statement is possible because almost all options of that statement have default values. The system creates, by default, two files. The logical name of the data file is sample and its original size is 2MB. Similarly, the logical name of the transaction log is sample_log and its original size is 1MB. (Both size values, as well as other properties of the new database, depend on corresponding specifications in the model database.)

Example 3.2 creates a database with explicit specifications for database and transaction log files.

Example 3.2

USE master;
CREATE DATABASE projects
	ON (NAME=projects_dat,
		FILENAME = 'C:\projects.mdf',
		SIZE = 10,
		MAXSIZE = 100,
		FILEGROWTH = 5)
LOG ON
	(NAME=projects_log,
		FILENAME = 'C:\projects.ldf',
		SIZE = 40,
		MAXSIZE = 100,
		FILEGROWTH = 10);Code language: PHP (php)

Example 3.2 creates a database called projects. Because the PRIMARY option is omitted, the first file is assumed to be the primary file. This file has the logical name projects_dat and is stored in the file projects.mdf. The original size of this file is 10MB. Additional portions of 5MB of disk storage are allocated by the system, if needed. If the MAXSIZE option is not specified or is set to UNLIMITED, the file will grow until the disk is full. (The KB, TB, and MB suffixes can be used to specify kilobytes, terabytes, or megabytes, respectively—the default is MB.)

There is also a single transaction log file with the logical name projects_log and the physical name projects.ldf. All options of the file specification for the transaction log have the same name and meaning as the corresponding options of the file specification for the data file.

Using the Transact-SQL language, you can apply the USE statement to change the database context to the specified database. (The alternative way is to select the database name in the Database pull-down menu in the toolbar of SQL Server Management Studio.)

The system administrator can assign a default database to a user by using the CREATE LOGIN statement or the ALTER LOGIN statement. In this case, the users do not need to execute the USE statement if they want to use their default database.

Creating a Database Snapshot

The CREATE DATABASE statement can also be used to create a database snapshot of an existing database (source database). A database snapshot is transactionally consistent with the source database as it existed at the time of the snapshot’s creation.

The syntax for the creation of a snapshot is

CREATE DATABASE database_snapshot_name
	ON (NAME = logical_file_name,
		FILENAME = 'os_file_name') [ ,...n ]
		AS SNAPSHOT OF source_database_nameCode language: PHP (php)

As you can see, if you want to create a database snapshot, you have to add the AS SNAPSHOT OF clause in the CREATE DATABASE statement. Example 3.3 creates a snapshot of the AdventureWorks database and stores it in the C:\temp data directory. (You must create this directory before you start the following example. Also you have to download and create the AdventureWorks database, if this database does not exist on your system.) The AdventureWorks database is a sample database of SQL Server and can be downloaded from Microsoft’s Codeplex page.

Example 3.3

USE master;
CREATE DATABASE AdventurWorks_snapshot
	ON (NAME = 'AdventureWorks_Data' ,
	FILENAME = 'C:\temp\snapshot_DB.mdf')
	AS SNAPSHOT OF AdventureWorks;Code language: PHP (php)

An existing database snapshot is a read-only copy of the corresponding database that reflects the point in time when the database is copied. (For this reason, you can have multiple snapshots for an existing database.) The snapshot file (in Example 3.3, ‘C:\temp\snapshot_DB.mdf’) contains only the modified data that has changed from the source database. Therefore, the process of creating a database snapshot must include the logical name of each data file from the source database as well as new corresponding physical names (see Example 3.3).

While the snapshot contains only modified data, the disk space needed for each snapshot is just a small part of the overall space required for the corresponding source database.

Note – To create snapshots of a database, you need NTFS disk volumes, because only such volumes support the sparse file technology that is used for storing snapshots.

Database snapshots are usually used as a mechanism to protect data against user errors.

Attaching and Detaching Databases

All data of a database can be detached and then attached to the same or another database server. Detaching and attaching a database should be done if you want to move the database.

You can detach a database from a database server by using the sp_detach_db system procedure. (The detached database must be in the single-user mode.)

To attach a database, use the CREATE DATABASE statement with the FOR ATTACH clause. When you attach a database, all data files must be available. If any data file has a different path from when the database was first created, you must specify the file’s current path.

Creating a Database

Creating a Database Snapshot

Attaching and Detaching Databases

Related Lessons: