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.

Transact-SQL

As we have seen in the previous instalment of this tutorial, the SQL Server Management Studio (SSMS) graphical user interface can be used to create and configure new databases. This use of SSMS is ideal for development scenarios where full access to the server is available. However, in many real-life situations you may have no direct access to a SQL Server. For example, with off-the-shelf software you may have to rely upon executing commands from your script or program against a SQL Server installation that you have never seen.

To allow commands to be executed against a SQL Server instance or database, the Transact-SQL (T-SQL) language is used. This is Microsoft's variant of the structured query language (SQL). It contains textual commands, or statements, that are used to create databases and their constituent parts, to query tables and views and to manipulate data. All tasks that can be undertaken with SQL Server are controlled using T-SQL.

NB: When creating a database using a graphical user interface such as SSMS, the selections made are converted into an appropriate T-SQL statement for execution. In many cases, you will find a "Script" button on the screen that allows you to view the underlying T-SQL code.

Executing T-SQL in SQL Server Management Studio

There are many commercial and freeware tools available for purchase or download that can be used to issue T-SQL commands to a SQL Server instance. T-SQL statements may also be executed from program code, such as .NET framework-based software. However, for the purposes of this article and tutorial we will use either SQL Server Management Studio or the free SQL Server Management Studio Express software.

To run T-SQL commands in SSMS, a query window must be opened and connected to a SQL server instance and database. If you have not already done so, open SSMS and enter the details of the SQL Server that you are using for the tutorial's examples. You can now open a new query window by opening the File menu, selecting the New submenu and clicking the option, "Query with Current Connection". You can also open such a window by clicking the "New Query" toolbar button or by pressing Ctrl + N.

Windows in SSMS appear in a tabbed layout by default, allowing you to click between tabs to show the other open windows. The tab at the top of the query window shows the name of the current SQL server and the name of the database that has been selected automatically. The database chosen will vary according to your security privileges.

NB: It is very important to ensure that the database selected is the one that you want to execute commands against. If the database is incorrect, you can quickly change it using the drop-down list of databases in the toolbar.

Executing a Simple Command

Query windows allow T-SQL statements to be typed and executed. We can test this with a simple command that outputs some text. Type the following command into the new query window. Note the use of apostrophes around the literal text. This is T-SQL's way of declaring string data.

PRINT 'Hello world'

To execute the command, ensure that no text is selected and then choose Execute from the Query menu. You can also click the Execute button or press the F5 key to run a command. On completion, the query window will update to show the output of the print statement.

Executing a Multiple Line Command

T-SQL commands do not need to be run individually. A script of multiple statements can be created, simply by adding more commands to the query window. Try changing the query as follows and then hitting F5 to see the results. Again, ensure that no text is selected in the query window before executing.

PRINT 'Hello'
PRINT 'world'

Executing a Selection

In the last two examples no selection was made when the Execute command was given. When no text is selected, the entire content of the query window forms the script to be run. One very useful aspect of SSMS is the ability to select one or more lines, or even parts of lines, from a query window and only run that part. You can try this by selecting either of the two print statements and pressing F5. This is advantageous when you have multiple statements in a single window but want to run them individually.

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.

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!

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.

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