SQL Server uses the model database as the prototype for new databases. If you want new databases to have a particular setup, first modify the model database, and then create the new databases. Otherwise, you will have to modify the settings of each new database manually.
The easiest way to create a new database is by using SQL Server Management Studio. You can also create databases using Transact-SQL.
In SQL Server Management Studio, you set database properties with buttons and input boxes and let SQL Server do all the SQL detail work. Create a database with the default options by completing these steps:
In SQL Server Management Studio, use Registered Servers view to select a type of server, such as Database Engine. If you need to expand a server group to see the servers available in a group, click the plus sign (+) next to the name of a group.
In Registered Servers view, select a server by double-clicking its name in the list. This connects you to the server in Object Explorer view.
Right-click the Databases folder, and then from the shortcut menu, choose New Database to display the dialog box shown in Figure 7-2.
On the General page, type a name for the database in the Database Name box. Although database names can have up to 128 characters, it is a good idea to give a new database a short but descriptive name to make it easier to track.
Note
The names of database objects are referred to as identifiers. Identifiers can contain from 1 to 128 characters (except for local temporary tables, which can have from 1 to 116 characters), and they must follow the specific naming conventions for the identifier class to which they belong. Generally, if the identifier name uses spaces or if it begins with a number, you must use brackets ([]) or double quotation marks (" ") to delimit the name when referencing it in Transact-SQL commands.
Click OK. SQL Server creates the database.
To customize the creation process, follow steps 1 through 4 (but not 5) in the previous example, and then continue with these steps:
On the General page, set the database owner by clicking the button to the right of the Owner box to display the Select Database Owner dialog box.
In the Select Owner dialog box, click Browse, and then in the Browse For Objects dialog box that opens, select the login that will be the owner of the database.
Click OK twice.
If the database will be used with full-text indexing, select Use Full-Text Indexing. You will then need to configure full-text indexing. (Refer to the section titled "Working with Full-Text Search" in Chapter 5.)
By default, SQL Server bases the data file name on the database name. For example, if you type Projects as the database name, the data file is named Projects. You can change the default name by typing a new value.
The File Group box shows which filegroup the data file belongs to. By default, all files are placed in the primary group. Although the primary data file must be in the primary group, you can create other data files and place them in different filegroups. Filegroups provide additional options for determining where data is stored and how it is used, as well as how data is backed up and restored.
Tip
Filegroups are designed primarily for large databases and advanced administration. If your database might grow to 1 GB or larger, consider using multiple filegroups. Otherwise, you really do not need to use multiple filegroups. The primary reason to use filegroups is to improve database response time. You do this by allowing database files to be created across multiple disks or to be accessed by multiple disk controllers, or both.
In the Initial Size box, type an initial size for the database in megabytes. Use a size that makes sense for the amount of data that the database will store. By default, new databases have the same size as the model database. The size range for databases is 1 MB to many terabytes.
Tip
Setting the initial database size to a reasonable value cuts down on the overhead that may be associated with growing the database. Whether you grow the database manually or SQL Server grows it automatically, the database is locked until the growth is complete. This can cause delays in processing queries and handling transactions.
Note
Keep in mind that you cannot shrink a database to make it smaller than it was when you created it. However, you can shrink individual data and log files to make them smaller than their original sizes by using the DBCC SHRINKFILE statement. With DBCC SHRINKFILE, you must shrink each file individually; you cannot shrink the entire database.
By default, new databases are set to auto grow each time a data file needs to be expanded. Click the button to the right of the Autogrowth box to adjust the settings. As Figure 7-3 shows, the Autogrowth feature can be set to grow using a percentage or an amount in megabytes, and you can either restrict the maximum file growth to a specific size or allow unrestricted file growth.
Real World
The Autogrowth feature is a good tool to use to ensure that databases do not run out of space. Be careful when configuring the database to enable growth by a certain percentage, however. Setting a 10 percent growth rate, for example, will cause a database that is 5 GB in size to grow by a whopping 500 MB each time a data file needs to be expanded, and a server with multiple databases may run out of space as a result of the growth factor. If you set the growth in megabytes, with 1 MB as a minimum growth size, you will know exactly how much the database will grow each time the data file expands. You may also want to configure an alert to notify you when the database grows to a certain size. You will learn how to configure an alert in Chapter 15.
In the Path box, type the full path to the data file. The primary data file name should end with the .mdf file extension. By default, SQL Server uses the default data location you selected when you installed the server. Click the button to the right of the Path box to find a new path, or you can enter a new path directly.
Secondary data files provide an additional location for data. If you want to configure secondary data files, click Add to start on a new line, and then repeat steps 5 through 9. Secondary data file names should end with the .ndf file extension.
Transaction logs are listed with the Log file type. After you configure data files, you can configure one or more transaction log files in much the same way that you configured the data files. Type the file name, filegroup, initial size, and path information. Configure Autogrowth as necessary. Be sure to name the log files with the .ldf file extension.
Note
Setting a size for the transaction log can be tricky. You do not want to rob the system of needed space for data, but you do want to avoid a situation in which the transaction logs are resized again and again because a file is locked when it is being expanded. I recommend 2 MB to 3 MB as a minimum for most databases and 25 percent of total data file size on a moderately active database. Note also that placing transaction logs on separate drives from data files can usually improve database performance.
On the Options page, use the Collation selection menu to choose a collation for the database. Microsoft Windows collation names have two components: a collation designator and a comparison style. The collation designator specifies the alphabet or language whose sorting rules are applied with dictionary sorting and the code page to use when storing non-Unicode character data. The comparison style specifies additional collation style as identified by the following abbreviations:
90. Code-point sorting (updated collation to include code-point sorting)
CI. Case insensitive
CS. Case sensitive
AI. Accent insensitive
AS. Accent sensitive
KS. Kanatype sensitive
WS. Width sensitive
BIN. Binary sort order
BIN2. Code-point binary sort (for pure code-point comparison collations)
Click OK to complete the creation process.
After you finish creating a new database, you should set options and permissions for that database. You will learn about setting options in the section titled "Setting Database Options in SQL Server Management Studio" later in this chapter. Setting permissions is covered in Chapter 8.
You can also create a database by using the CREATE DATABASE command. This command has options that are similar to those in the Database Properties tab, and the best way to learn how the command works is by creating databases in SQL Server Management Studio first and then trying the CREATE DATABASE command.
The syntax and usage for CREATE DATABASE are shown in Example 7-1.
Example 7-1. CREATE DATABASE Command Syntax and Usage
Syntax
CREATE DATABASE database_name
[ ON
[ <filespec> [ ,...n ] ]
[ , <filegroup> [ ,...n ] ]
]
[
[ LOG ON { <filespec> [ ,...n ] } ]
[ COLLATE collation_name ]
[ WITH <external_access_option> ]
]
[;]
<filespec> ::=
{
[ PRIMARY ]
( NAME = logical_file_name ,
FILENAME = "os_file_name"
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | % ] ]
) [ ,...n ] }
<filegroup> ::=
{ FILEGROUP filegroup_name [ DEFAULT ] <filespec> [ ,...n ] }
<external_access_option> ::=
{ DB_CHAINING { ON | OFF }
| TRUSTWORTHY { ON | OFF } }
Usage
USE MASTER
GO
CREATE DATABASE Sample
ON
PRIMARY
( NAME = Sample1,
FILENAME = "c:datasampledat1.mdf",
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%),
( NAME = Sample2,
FILENAME = "c:datasampledat2.ndf",
SIZE = 100MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%)
LOG ON
( NAME = SampleLog1,
FILENAME = "c:datasamplelog1.ldf",
SIZE = 3MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 5MB)
GO