Creating Databases

Database creation is a relatively straightforward operation that can be performed using T-SQL statements or the Enterprise Manager. Because the data and log files are created at the time the database is created, the time it takes for the database to be created will be dependent on the size and number of files specified when you create the database. If the disk space is insufficient to create any of the files specified, SQL Server will return an error and none of the files will be created.

Using the Create Database Wizard

The Create Database Wizard will take you step by step through the database creation process. This wizard can be accessed through the "magic wand" icon on the Enterprise Manager toolbar, from the Wizards menu option in the Tools menu, or from the Database Taskpad view. Figure 11.2 shows the first page of the wizard.

Figure 11.2. Creating a database with the Create Database Wizard.

Each page in the wizard provides input fields for the various options, such as database name , file location, filename, initial file size, file growth, and so on. While easy to use, this is one of my least favorite SQL Server Wizards because it takes several pages to do what you can do in three tabs or less with the Enterprise Manager Database Properties dialog box. Wizards are great for technically complex tasks , but the process of creating a database is technically quite simple. The conceptual design of file locations is the hard part, and unfortunately the wizard can't help with that.

Using Enterprise Manager

Although the Enterprise Manager Database Properties dialog box is no better than the wizard for helping with the layout of your data files, it does present more functionality in an easy-to-use format. To access the dialog box, right-click the Databases folder in Enterprise Manager and select New Database. The General tab of the Create Database dialog box is shown in Figure 11.3.

Figure 11.3. Creating a database with Enterprise Manager.

The General tab allows you to input a database name and select a database collation (character set and sort order). The Data Files tab allows you to input the logical filenames, the physical path and filename, the filegroup (except for the primary file), and file growth parameters. Each of these is automatically populated with defaults. For example, the filename is generated as databasename_data , and the path is the server default file path. The Transaction Log tab is identical to the Data Files tab except that a Filegroup column does not exist because filegroups are not valid for the log file. Figure 11.4 shows the Data Files tab.

Figure 11.4. The Data Files tab.

NOTE

Although SQL Server provides default values for database options such as file size, file path, and growth increments , these values might not be appropriate for your installation. It is up to you to decide the location of data and log files, file growth parameters, and initial file size based on application requirements and hardware configuration.

Using T-SQL

Whether using the wizard or Enterprise Manager as in the previous sections, SQL Server is simply generating T-SQL statements behind the scenes. If you prefer to use T-SQL rather than the GUI, databases can be created with the CREATE DATABASE statement, giving you the ability to save a script for the database creation and access fully all CREATE DATABASE options. Listing 11.1 shows an example script to create a database called Big_DB.

Listing 11.1 Using T-SQL to Create a Database

 CREATE DATABASE Big_DB ON PRIMARY ( NAME = Big_DB_dat,    FILENAME = 'd:\data\Big_DB.mdf',    SIZE = 10MB,    MAXSIZE = 50MB,    FILEGROWTH = 15% ), FILEGROUP Big_DB_Data ( NAME = Big_DB_Data_dat,    FILENAME = 'e:\data\Big_DB_Data.ndf',    SIZE = 50GB,    MAXSIZE = 100GB,    FILEGROWTH = 10GB ) LOG ON ( NAME = 'Big_DB_log',    FILENAME = 'f:\log\Big_DB_log.ldf',    SIZE = 50MB,    MAXSIZE = 100MB,    FILEGROWTH = 10MB ) COLLATE Latin1_General_CI_AI GO 

In this case, the database will be created with two filegroups: the primary filegroup and a second filegroup called Big_DB_Data. The log file is created with the LOG ON clause. The COLLATE clause specifies the collation, which determines the default Code Page (Character Set) and the sort order for the database. Note the file specification clause for each of the three files created in this example. The NAME parameter specifies a logical name for the file, which can be used within SQL Server to reference the file in future statements. The FILENAME is the actual physical filename, including the full path. The next three parameters are used to regulate the size of the file. All three parameters can be entered as KB , MB , GB , and TB , with MB the default if nothing is specified. The initial size of the file is controlled by SIZE . The MAXSIZE parameter controls the maximum size to which the file can grow. If MAXSIZE is omitted, then file growth is limited only by the available disk space. The final file parameter FILEGROWTH indicates the increment by which a file will increase in size if it becomes full, given available disk space, and provided the upper limit of MAXSIZE has not been reached; optionally , FILEGROWTH can also be specified as a percentage of the file size.