SQL 2005+

Creating SQL Server Databases Part 2 (2)

The fourth part of the SQL Server Programming Fundamentals tutorial continues the consideration of database creation in SQL Server. In this article we will investigate how to create new databases using Transact-SQL query language commands.

The CREATE DATABASE Command

Now that we can run statements, we can investigate the CREATE DATABASE command. This command allows a new database to be generated. In its simplest form, only a name for the new database is required with all settings and file locations automatically using their defaults.

To create a new database for the JoBS example, try executing the following statement. After execution, right-click the "Databases" branch of the object explorer tree and choose "Refresh" from the context-sensitive menu that appears to see the new database in the list.

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 entire tutorial.

CREATE DATABASE JoBS

If you have been following the tutorial examples so far, the above command will fail with an error explaining that the database already exists. A database can be deleted, or dropped, using the DROP DATABASE command. To drop the JoBS database, execute the following statement.

NB: If you are using a different name than "JoBS", replace this in the drop statement. Check this carefully to ensure that you do not delete the wrong database!

DROP DATABASE JoBS

You may receive an error when trying to drop a database if the database is in use. Any connection to the database, including having the database selected as current for any open query window, will prevent it from being deleted.

Files and Filegroups

In some cases creating a database using the default file names and locations is acceptable. However, often it is not and you must provide the names of the filegroups and files that you wish the database to use. This is achieved by providing file specifications and filegroups to the CREATE DATABASE command.

A file specification is defined with the minimum of a name and filename, surrounded by parentheses (). The following file specification represents a file named "DatabaseFile" located in the root of the C: drive. It is usual to use the MDF extension for data files and LDF for log files.

(NAME='DatabaseFile', FILENAME='C:\NewDatabase.mdf')

To provide names and file details for both the data file and the log file for a new database, two file specifications must be provided. The following sample shows this with the first file specification provided for the primary data file and the second for the log file. Executing this command will create both files in the root of C: and use non-default naming.

CREATE DATABASE JoBS
ON PRIMARY (NAME='JoBSData', FILENAME='c:\JoBS.mdf')
LOG ON (NAME='JoBSLog', FILENAME='c:\JoBS.ldf')

As described in the first article regarding database creation, multiple files can be created for both the data and log files. The information in the database or log is then spread amongst the available files. To add more files for either the data or the log, additional file specifications are included in the CREATE DATABASE command, each separated by a comma. The following example demonstrates this by using two data files and two logs files. The files are located on different disks for a performance improvement.

CREATE DATABASE JoBS
ON PRIMARY (NAME='JoBSData', FILENAME='c:\JoBS.mdf'),
(NAME='JoBSData2', FILENAME='d:\JoBS2.mdf')
LOG ON (NAME='JoBSLog', FILENAME='e:\JoBS.ldf'),
(NAME='JoBSLog2', FILENAME='f:\JoBS2.ldf')

The data files used for a new database may be arranged in filegroups. These are administrative containers for files that can aid performance as individual tables and indexes can be assigned to a filegroup, and therefore can be targeted at a specific disk drive or set of drives. A filegroup can be specified within the comma-separated of data files with subsequent files in the list linked to that group. Filegroups may not be specified for log files.

CREATE DATABASE JoBS
ON PRIMARY (NAME='JoBSData', FILENAME='c:\JoBS.mdf'),
FILEGROUP SECONDARY
(NAME='JoBSData2', FILENAME='c:\JoBS2.mdf')
LOG ON (NAME='JoBSLog', FILENAME='c:\JoBS.ldf')

When a new table or index is added to a database, the filegroup that it will be stored in is set to the default filegroup unless otherwise specified. The default filegroup is usually the primary group. However, this can be changed by adding the DEFAULT keyword when creating a filegroup, as shown below.

CREATE DATABASE JoBS
ON PRIMARY (NAME='JoBSData', FILENAME='c:\JoBS.mdf'),
FILEGROUP SECONDARY DEFAULT
(NAME='JoBSData2', FILENAME='c:\JoBS2.mdf')
LOG ON (NAME='JoBSLog', FILENAME='c:\JoBS.ldf')