BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

Creating SQL Server Databases Part 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.

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')

Setting File Sizes and Growth Options

The files created by the above scripts use the default settings for their original size and the manner in which they grow. You can determine an initial size for each file using the SIZE clause within the file specification. The size must be specified in kilobytes, megabytes, gigabytes or terabytes by providing an integer with a suffix of KB, MB, GB or TB respectively.

The following statement creates the JoBS database with a data file of one gigabyte and a log file of 512 megabytes. If you use Windows Explorer to view the generated files' properties you will be able to see that these file sizes have been pre-allocated.

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

A maximum file size may be set within a file specification. This uses a similar syntax to assign a value to the MAXSIZE option. If the value is set to UNLIMITED, the file will grow as required until the disk is full.

CREATE DATABASE JoBS
ON PRIMARY (NAME='JoBSData', FILENAME='c:\JoBS.mdf', SIZE=1GB, MAXSIZE=UNLIMITED)
LOG ON (NAME='JoBSLog', FILENAME='c:\JoBS.ldf', SIZE=512MB, MAXSIZE=1GB)

Finally, the growth options can be changed for each file specification individually. The FILEGROWTH option determines the automatic size increase of a file when it becomes full. The value can be set as a specific increment using a suffix of KB, MB, GB or TB. Alternatively, the file can be configured to increase by a percentage value when required. The following example shows a percentage increment for the data file and a specific value for the log file.

CREATE DATABASE JoBS
ON PRIMARY
(NAME='JoBSData', FILENAME='c:\JoBS.mdf', SIZE=1GB, MAXSIZE=4GB, FILEGROWTH=15%)
LOG ON
(NAME='JoBSLog', FILENAME='c:\JoBS.ldf', SIZE=512MB, MAXSIZE=1GB, FILEGROWTH=64MB)

Setting a Collation

The final element of the CREATE DATABASE command that we will consider in this article is the ability to set the collation. This is achieved using the COLLATE clause. This clause is appended to the command with the name of a valid SQL collation or Windows collation. The selected collation name determines how data is ordered and compared. The following example uses the "Latin1_General_CI_AS" collation, which is case-insensitive and accent-sensitive.

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