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 1

The third part of the SQL Server Programming Fundamentals tutorial investigates the creation of new databases. This first task in any new database project requires the careful consideration of several key configuration options.

Database Name

The first page of the New Database dialog box allows the new database to be named. In the simplest scenarios, this is actually the only piece of information that must be provided. All other configuration options can be left at their default settings.

The name of a database can contain letters, numbers, spaces and many symbols. This gives a great deal of flexibility to the name but can also lead to difficult-to-read names and names that cannot be used without adding delimiters. It is usual, therefore, to use only letters and numbers and no spaces. A good convention is to use Pascal case when joining multiple words and capitalisation for abbreviations, as in the example database.

To set the name of the database, type JoBS into the Database name text box.

Database Owner

All databases are assigned an owner. This is a user that has full rights to modify and maintain the new database and may be any Windows account or SQL Server login with the rights to create databases. For new databases, the Owner textbox shows <default>. This indicates that the owner will be the current user, as used to log in to the database. The owner can be changed by typing a new name or searching for a suitable login using the ellipsis button.

For the JoBS database, use the default option.

Full-Text Indexing

SQL Server includes a facility known as full-text indexing. This allows the construction of special indexes that permit searching of textual data. This can be useful when holding large sections of text that need to be read by a search engine similar to Google, for example.

In this tutorial we will not be examining full-text indexing so leave this checkbox blank.

Database Files

All databases use two files by default. These are the primary data file and the log file. The primary data file keeps track of all other data files and holds some or all of the information that is stored in the database. The log file contains details of transactions that have occurred against the database and allows those transactions to be recovered in the event of a disaster.

Each file is listed in a table in the dialog box. The table shows seven columns:

  • Logical Name. This is the name of the file that SQL Server will use. By default, the name is based upon the database name.
  • File Type. The file type is either "Data" or "Log", depending upon its purpose.
  • Filegroup. The name of the filegroup that the file will be created within. See below for more information on filegroups.
  • Initial Size. SQL Server files do not grow continuously in the same manner as files in applications such as those in Microsoft Office. Instead, the files are created with a specific size and grow in increments when the available space is exhausted. This provides improved performance, which is critical in database systems. The Initial Size column shows the starting size of each file in megabytes.
  • Autogrowth. When a data or log file becomes full, the autogrowth settings determine the action that is taken by the database engine. If autogrowth is disabled, the database simply raises an error to indicate that the file is full. When enabled, the data file can be set to increase by a specific size in megabytes or by a percentage of the current file size. Each file may be set to grow indefinitely or can be limited to a specific maximum size.
  • Path. This column shows the path to the folder in which the file will be created.
  • File. The last column in the table shows the name of the database file. For new databases, this appears blank.

Additional files can be added to a database. These may be secondary data files or additional log files. When extra data files are added, the information held in the database is distributed amongst all of the available files. This allows files to be spread over a series of disks for maximum performance.

For the JoBS database, use the default settings for the data and log files.

Filegroup Options

Filegroups are simply administrative containers for database files. Every data file created is allocated to a single filegroup. In many databases, the default "PRIMARY" filegroup is the only one in use.

Filegroups are useful because individual tables and indexes within a database can be allocated to a particular filegroup. Using this facility, the disk location of groups of tables can be determined by the database administrator (DBA). This allows configurations where highly utilised data can be placed in a group of files that is using very fast disks whilst archive data is held in a filegroup using slower hardware.

New filegroups can be added to a database by clicking on the Filegroups option at the left of the dialog box. Additional filegroups can be created by clicking the Add button and providing a name. One filegroup can be marked as the default. This is the one that will be used by new tables and indexes where a filegroup is not specified.

For the JoBS database, we will use the single, default filegroup.

Database Options

The final set of database options can be viewed and modified by clicking "Options" at the left of the dialog box. This page shows various general configuration options that may be applied to the new database.

Collation

The collation setting determines how information in the database is compared and sorted by SQL Server. This is important as different languages and cultures use different rules for comparing and sorting text. The collation also determines whether capital letters and lower-case letters are considered to be equivalent.

When installing SQL Server, a default collation is required. If a collation is not specified for each new database, the default collation is used instead.

For the JoBS database, use the default collation for the SQL Server instance.

NB: The JoBS database in the tutorial examples uses the Latin1_General_CI_AS collation. If your collation is set differently, the results of comparisons and sort operations may vary from those shown.

8 June 2008