Creating a SQL Server Database | Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features

Creating a database in SQL Server is as easy as cutting butter on hot corn. Many database solutions, however, call for specialized configurations that cater to VLDBs, replication, federations, and so on. So before you start creating away, there are a number of considerations to keep in mind with respect to the actual files:

First, if you simply make the data files as large as possible (taking up most of the space on a disk), you need to be certain that the disk is going to be dedicated to the large database and nothing else. This practice can come back to haunt you in the future when database size priorities change and you can’t do anything else on the disk until the database file size is reduced. Make the database file only as large as you truly expect it to grow.
Second, allow the data files to grow automatically but place a ceiling on the file growth so that the files don’t suck up every drop of storage juice on the server and explode. This practice allows you to monitor the files carefully and take appropriate actions (such as installing additional storage). Alternatively, as discussed, you can add more filegroups to secondary hard disks.
If you do not want the database to grow beyond its initial size, set the maximum growth size to zero. This parameter prevents the file from growing beyond your initial settings, which is useful in turnkey situations, such as a voice mail system that gets shipped preconfigured to only store 100MB of voice messages. You can then sell additional storage quotas as needed.
Watch for file fragmentation. Autogrowing files can become fragmented to a point where performance begins to degrade. Again, creating files on multiple disks helps keep the fragmentation in check.

There are several methods you can use to create a SQL Server database:

T-SQL’s CREATE DATABASE statement
SQL Server Management Studio (New Database, Copy Database)
The SQL-SMO object model
SQL Server Integration Services (SSIS)

No matter the option that you use to create a database, SQL Server implements the creation of databases in two steps:

First, SQL Server initializes the new database using metadata derived from the model database to initialize the database and its metadata. To change defaults to suit the environment, you would need to alter the model database (such as by specifying collations other than the default).
Next, SQL Server fills the database with empty pages. The database, however, also stores the initial data that records how the space is to be used in the database.

If you created or altered objects in the model database, then these are exactly copied to all newly created databases. Whatever you add to model, such as tables, views, stored procedures, and data types, all are also included in all new databases. This is very convenient for turnkey systems or when you need to be creating new databases often, because it obviates the need to run a complex script every time you create a new database. In fact, after you’ve fine-tuned all the objects inside model, just executing CREATE DATABASE datdbase_name would be sufficient and the database would be ready for users.

As highlighted in the CREATE DATABASE syntax coming up, the new database inherits the database option settings from the model database (unless you are using the FOR ATTACH switch that attaches an existing database).

For example, if the recovery model is set to FULL in model, the recovery model will be set to FULL in all the new databases you create. Any properties and options changed using the ALTER DATABASE statement on model are also used for new databases you create. If FOR ATTACH is specified on the CREATE DATABASE statement, the new database inherits the database option settings of the original database.

There are three types of files used to store a database (SQL Server does not enforce the file extensions shown here):

First, there is the primary file (.mdf), which contains the startup information for the database. After the initial startup data, SQL Server uses the rest of the primary files to store user data. A SQL Server database cannot exist without its primary file.
When the primary fills to capacity, secondary files (.ndf) are used to hold the additional data. The secondary file is optional and may not be necessary if the primary is large enough or has enough hard disk accommodation to store all the data in the database. Secondary files can be installed to separate disks, or spread across multiple disks.
Finally, there are the transaction log files (.ldf). These files hold the transactional information used to recover the database. You cannot operate a SQL Server database without a transaction log file, but you can specify more than one. The minimum size for a transaction log file is typically 1MB.

Tip

Back up the master database and the files for mssqlsystemresource whenever a new user database is created or altered.

Let’s explore the T-SQL CREATE DATABASE statement first, before looking into the interactive procedures using Management Studio. This will actually make it easier for you to understand what the GUI tools are doing.

CREATE DATABASE

For many DBAs, the T-SQL CREATE statement is convenient because it means that you can create a T-SQL script and run the script with a new database name whenever you need. Creating such scripts is obviously ideal for large requirements and typically suits a DBA or some process that needs to create databases regularly (such as turnkey systems where the script is run as part of system setup).

Note

You need to be a member of the sysadmin or dbcreator fixed server role or be given the appropriate permission to create and alter databases (see Chapter 5).

Setting up databases for e-commerce hosting sites is a good example of where the T-SQL script is useful. When a new client needs a database, it becomes a matter of running the script against the target server and instance from one of the command-line utilities or a query window in Management Studio.

The simplest T-SQL CREATE DATABASE syntax is as follows:

 CREATE DATABASE database_name     [ ON         [ PRIMARY ] [ <filespec> [ ,...n ]         [ , <filegroup> [ ,...n ] ]     [ LOG ON { <filespec> [ ,...n ] } ] ]     [ COLLATE collation_name ]     [ WITH <external_access_option> ]] [;]

To attach a database,

 CREATE DATABASE database_name     ON <filespec> [ ,...n ]     FOR { ATTACH [ WITH <service_broker_option> ]         | ATTACH_REBUILD_LOG } [;] <filespec> : : = { (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 | GB | TB | % ] ] ) [ ,...n ]  <filegroup> ::= {FILEGROUP filegroup_name [ DEFAULT ]     <filespec> [ ,...n ] } <external_access_option> ::= {   DB_CHAINING {ON | OFF}   | TRUSTWORTHY {ON | OFF} } <service_broker_option> ::= {     ENABLE_BROKER | NEW_BROKER | ERROR_BROKER_CONVERSATIONS

To create a database snapshot,

 CREATE DATABASE database_snapshot_name     ON         (         NAME = logical_file_name,         FILENAME = 'os_file_name'         ) [ ,...n ]     AS SNAPSHOT OF source_database_name [;]

These statements can be daunting and confusing, so let’s go through simpler versions of them, making notes as we go along that will help us create a suitable database plan for our project.

Database Name

The database names must be unique within a server and conform to the rules for identifiers. The placeholder database_name can be a maximum of 128 characters. Typically SQL Server gives the log file the same name as the database name and provides the extension .ldf. You can specify your own log filename and path, a topic we will get to shortly. The following code, for example, creates a database named papazulu and assumes all the defaults derived from the model database:

 CREATE DATABASE papazulu

In this case papazulu.mdf and papazulu_log.ldf are created in the default path and the files are entrusted with the default settings for file growth and permissions (the user that created the database is the owner). Conversely, the following code makes the databases go away as quickly as they came:

 DROP DATABASE papazulu

Both the database and the log files are removed.

Note

The “user” that is allowed to create the database becomes the “legal” owner of the database, through a default schema. There may be circumstances, however, that dictate you need to change the owner (such as when the database is put into production). To change the owner, you can use the sp_changedbowner system stored procedure (see Appendix).

ON

Now let’s get a little more creative and install our database to a specific path. Naturally you need to be sure the path, server, and instance exist, or you could be trying to create into thin air. You also need to be sure you have domain permission and rights to create the remote database. ON is just a keyword that specifies that the disk files used to store the data portions of the database, the data files, are defined explicitly. It has nothing to do with making sure the database is switched on (which is what one newbie once asked me).

The ON keyword is followed by a comma-separated list represented by the <filespec> placeholder, which represents the items defining the data files for the primary filegroup (we will look into filegroups again later). The list of files in the primary filegroup can be followed by an optional, comma-separated list of <filegroup> items defining user filegroups and their files. The following code places the papazulu database on the C drive drive:

 ON (NAME = papazulu, FILENAME = 'C:\databases\papazulu.mdf',  SIZE = 4,  MAXSIZE = 10,  FILEGROWTH = 1)

The n placeholder indicates that multiple files can be specified for the database.

If an object is created with an ON filegroup clause specifying a user-defined filegroup, then all the pages for the object are allocated from the specified filegroup. The pages for all user objects created without an ON filegroup clause, or with an ON DEFAULT clause, are allocated from the default filegroup. When a database is first created, the primary filegroup is the default filegroup. You can specify a user-defined filegroup as the default filegroup using ALTER DATABASE as discussed later in this chapter.

LOG ON

This LOG ON argument specifies that the disk files used to store the database log (log files) are explicitly defined. If you use LOG ON, SQL Server will expect you to provide the name and path to the log file. The keyword is also followed by a comma-separated list of <filespec> items defining the log files. If you do not specify LOG ON, then a single log file is automatically created with a system-generated name (the same as the database name) and a size that is 25 percent of the sum of the sizes of all the data files for the database. The following code specifies the path for the papazulu log file:

 LOG ON (Name = 'papazulus_log',   FILENAME = 'D:\data\logfiles\papazulu_log. 1df',   SIZE = 5MB,   MAXSIZE = 25MB, FILEGROWTH = 5MB)

FOR ATTACH

The FOR ATTACH is built into the CREATE DATABASE statement, but it allows you to attach a database that was detached earlier or from somewhere else. You should use the sp_ attach_db system stored procedure instead of using CREATE DATABASE FOR ATTACH directly (see Appendix for usage). Use CREATE DATABASE FOR ATTACH only when you must specify more than 16 <filespec> items.

Naturally, this is easier in Management Studio but not always possible. You need to have the <filespec> entry to specify the first primary file. The only other <filespec> entries needed are those for any files that have a different path from when the database was first created or last attached. A <filespec> is needed for all files that need to be included in the attach process.

The statement is useful for some processes that require you to detach and attach in code. The database you attach, however, must have been created using the same code page and sort order as the SQL Server instance you are trying to attach it to.

You will also need to remove any replication objects installed to the database using the sp_removedbreplication system stored procedure if you plan to attach a database to a server other than the server from which the database was detached.

Collation Name

The collation_name placeholder specifies the default collation for the database. The collation name can be either a Windows collation name or a SQL collation name. If none is specified, the database is assigned the default collation of the SQL Server instance that you are creating the database on. (Collations are discussed in Chapter 10.)

PRIMARY

The PRIMARY keyword specifies that the associated <filespec> list defines the primary file. As you know, the primary filegroup contains all of the new database’s system tables. It also contains all objects not assigned to any user-defined filegroups. The first <filespec> entry in the primary filegroup becomes the primary file, which is the file containing the logical start of the database and its system tables (in other words, all other files are secondary because the database can have only one primary file). If you do not specify the PRIMARY file, the first file listed in the CREATE DATABASE statement becomes the primary file.

NAME

NAME specifies the logical name for the file defined by the <filespec>. The NAME parameter is not required when FOR ATTACH is specified. The following line from the earlier example specifies the logical filename:

 NAME = 'papazulu',

The name must be unique in the database and conform to the rules for identifiers. The name can be a character or Unicode constant, or a regular or delimited identifier.

FILENAME

The FILENAME specifies the file system filename for the file defined by the <filespec>. The placeholder os_file_name is the path and filename used by the operating system when it creates the physical file defined by the <filespec>. The path in os_file_name must specify a directory on an instance of SQL Server. Also, if you try to specify the os_file_name on a compressed file system, the statement will fail.

You can also install a file to a raw partition, which is a hard-disk partition that has not been formatted for any file system (such as FAT or NTFS). If you create the file on a raw partition, the os_file_name must specify only the hard-disk letter of an existing raw partition; no path information can be used. And you can only create one file on each raw partition. Files on raw partitions do not autogrow, so the MAXSIZE and FILEGROWTH parameters are not needed when you specify the os_file_name for a raw partition (I will go over raw partitions a little later in this chapter).

SIZE

The SIZE keyword specifies the size of the file defined in the <filespec>. When a size parameter is not supplied in the <filespec> for a primary file, SQL Server takes the size of the primary file in the model database. When a SIZE parameter is not specified in the <filespec> for a secondary or log file, SQL Server makes the file between 512KB and 1MB.

When you specify size, you can use the kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes. If you leave out the suffix, the default is MB. The number should not be fractional or include any decimal notation, such as 5.5MB. If you must create a database and a half or some similar fraction, move down to the next measurement. For example 5.5MB would be 5632KB. The minimum value for the size parameter is 512KB. The size specified for the primary file must be at least as large as the primary file of the model database.

MAXSIZE

The MAXSIZE option specifies the maximum size to which the file defined in the <filespec> can grow. The max_size parameter is specified as explained in the size parameter. When you specify size, you can use the kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffix. If you leave out the suffix, the default is MB. The number should not be fractional or include any decimal notation, such as 5,500MB. It is important to know in advance what the max_size will be because if it is not specified, the file will keep growing until the disk is full.

UNLIMITED

This is the converse of the maxsize parameter and specifies that the file defined in the <filespec> can grow until the disk is full.

FILEGROWTH

This argument specifies the growth increment of the file defined in the <filespec>. The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting. What FILEGROWTH means is that every time space is needed, the file is expanded by the value defined here. As discussed earlier, you need to specify a whole number and no decimals. A value of zero (0) specifies no growth.

The FILEGROWTH value can be specified in MB, KB, GB, TB, or percent (%). If a number is specified without an MB, KB, or % suffix, SQL Server assumes a default in MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. If FILEGROWTH is left out completely, the default value is 10 percent and the minimum value is 64KB. The size specified is rounded to the nearest 64KB.

Note

After the database has been created, or at any time for that matter, you can display a report on a database, or on all the databases for an instance of SQL Server, by executing the system stored procedure sp_helpdb. To get information about the space used in a database, use sp_ spaceused. You can also use sp_helpfile for a report of the files in a database. See Appendix for details about the results returned when you execute these system procs.

Examples

The following script creates the papazulu database against the instance you are connected to. You do not need to specifically include USE MASTER at the start of the script, because SQL Server 2005 knows that anyway. Also, depending on the tool you are using, the use of GO might not be necessary (keep the code simple).

Caution

Do not create user objects, such as tables, views, stored procedures, or triggers, in the master database.

 CREATE DATABASE papazulu ON (NAME = papazulu,    FILENAME = 'c:\program files\microsoft      sql server\mssq1.1\data\papazulu.mdf',

    SIZE = 10,    MAXSIZE = 50,    FILEGROWTH=5) LOG ON ( NAME = 'papazulu_log',    FILENAME = 'c:\program files\microsoft     sql server\mssql.1\data\papazulu_log.Idf'    SIZE = 5MB,    MAXSIZE = 25MB,    FILEGROWTH = 5MB)

ALTER DATABASE

The ALTER DATABASE statement is a little more complex than the CREATE statement. This code essentially adds or removes files and filegroups from the database. You can also use it to modify the attributes of files and filegroups, such as by changing the name or size of a file. ALTER DATABASE provides the capability to change the database name, filegroup names, and logical names of data files and log files.

The ALTER DATABASE syntax is as follows:

 ALTER DATABASE database__name {     <add_or_modify_files>   | <add_or_modify_filegroups>   | <set_database_options>   | MODIFY NAME=new_database_name   | COLLATE collation_name }[;] <add_or_modify_files> : : = {     ADD FILE <filespec> [ , ...n]         [ TO FILEGROUP { filegroup_name | DEFAULT } ]   | ADD LOG FILE <filespec> [ , ...n]   | REMOVE FILE logical_file_name   | MODIFY FILE <filespec> } <filespec>::= (     NAME = logical_file_name     [ , NEWNAME = new_logical_name]     [ , FILENAME = 'os_file_name']     [ , SIZE = size [ KB | MB | GB | TB]]     [ , MAXSIZE = {max_size [ KB | MB | GB | TB] | UNLIMITED } ]     [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]     [ , OFFLINE] )

 <add_or_modify_filegroups>::= {     | ADD FILEGROUP filegroup_name     | REMOVE FILEGROUP filegroup_name     | MODIFY FILEGROUP filegroup_name         { <filegroup_updatability_option>         | DEFAULT         | NAME=new_filegroup_name         } } <filegroup_updatability_option>::= {     { READONLY | READWRITE }     | { READ_ONLY | READ_WRITE } } <set_database_options>::= SET {     { <optionspec> [ ,...n ] [ WITH <termination> ] } } <optionspec>::= {     <db_state_option>   | <db_user_access_option>   | <db_update_option>   | <external_access_option>   | <cursor_option>   | <auto_option>   | <sql_option>   | <recovery_option>   | <database_mirroring_option>   | <service_broker_option>   | <date_correlation_optimization_option>   | <parameterization_option> } <db_state_option> ::=     { ONLINE | OFFLINE | EMERGENCY} <db_user_access_option> ::=     { SINGLE_USER | RESTRICTED_USER | MULTI_USER} <db_update_option> ::=     { READ_ONLY | READ_WRITE} <external_access_option> ::= {     DB_CHAINING {ON | OFF}   | TRUSTWORTHY {ON | OFF} }

 <cursor_option> ::= {     CURSOR_CLOSE_ON_COMMIT { ON | OFF }   | CURSOR_DEFAULT { LOCAL | GLOBAL } } <auto_option> ::= {     AUTO_CLOSE { ON | OFF }   | AUTO_CREATE_STATISTICS { ON | OFF }   | AUTO_SHRINK { ON | OFF }   | AUTO_UPDATE_STATISTICS { ON | OFF }   | AUTO_UPDATE_STATISTICS_ASYNC { ON | OFF } } <sql_option> ::= {     ANSI_NULL_DEFAULT { ON | OFF }   | ANSI_NULLS { ON | OFF }   | ANSI_PADDING { ON | OFF }   | ANSI_WARNINGS { ON | OFF }   | ARITHABORT { ON | OFF }   | CONCAT_NULL_YIELDS_NULL { ON | OFF }   | NUMERIC_ROUNDABORT { ON | OFF }   | QUOTED_IDENTIFIER { ON | OFF }   | RECURSIVE_TRIGGERS { ON | OFF } } <recovery_option> ::= {     RECOVERY {FULL | BULK_LOGGED | SIMPLE }   | TORN_PAGE_DETECTION {ON | OFF }   | PAGE_VERIFY {CHECKSUM | TORN_PAGE_DETECTION | NONE } } <database_mirroring_option> ::= { <partner_option> | <witness_option>}         <partner_option> ::=     PARTNER { = 'partner_server'             | FAILOVER             | FORCE_SERVICE_ALLOW_DATA_LOSS             | OFF             | RESUME             | SAFETY { FULL | OFF }             | SUSPEND             | TIMEOUT integer            } <witness_option> ::=     WITNESS { = 'witness_server'             | OFF             }

 <service_broker_option> ::= {     ENABLE_BROKER   | DISABLE_BROKER   | NEW_BROKER   | ERROR BROKER CONVERSATIONS } <date_correlation_optimization_option> ::= {     DATE_CORRELATION_OPTIMIZATION { ON | OFF } } <parameterization_option> ::= {     PARAMETERIZATION {SIMPLE | FORCED} } <snapshot_option> ::= {     ALLOW_SNAPSHOT_ISOLATION {ON | OFF}   | READ_COMMITTED_SNAPSHOT {ON | OFF} } <termination> ::= {     ROLLBACK AFTER integer [SECONDS]   | ROLLBACK IMMEDIATE   | NO_WAIT }

Some of the arguments are explained in the text that follows.

Database

This is the name of the database (database) to be changed.

Add File

The ADD FILE argument specifies that a file is to be added to the database.

To Filegroup

The TO FILEGROUP argument specifies the filegroup, in the filegroup_name, to which to add the specified file.

Add Log File

The ADD LOG FILE argument specifies that a log file is to be added to the specified database.

Remove File

The REMOVE FILE argument removes the file description from the database system tables and deletes the physical file. The file cannot be removed unless empty.

Add Filegroup

The ADD FILEGROUP argument specifies that a filegroup is to be added. You also need to specify the name in the filegroup_name placeholder.

Remove Filegroup

The REMOVE FILEGROUP argument is specified to remove the filegroup from the database and delete all the files in the filegroup. The filegroup cannot be removed unless it is empty, not even from Enterprise Manager.

Modify File

The MODIFY FILE argument specifies the given file that should be modified, including the FILENAME, SIZE, FILEGROWTH, and MAXSIZE options. Only one of these properties can be changed at a time. NAME must be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size. FILENAME can be specified only for files in the tempdb database, and the new name does not take effect until Microsoft SQL Server is restarted.

To modify the logical name of a data file or log file, specify in NAME the logical filename to be renamed, and specify for NEWNAME the new logical name for the file.

For example, MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_ name...). For optimum performance during multiple modify-file operations, several ALTER DATABASE database MODIFY FILE statements can be run concurrently.

Compressed read-only filegroups prevent updates and save disk space because you can put the files on a volume that has data compression enabled. Perform DBCC CHECK on these files to make sure the files are not risking corruption. Remember, disk compression is also CPU intensive, so you need to think about this before you start moving all your read-only files to compressed volumes (remember, disks are getting cheaper by the nanosecond).

Modify Name

This argument allows you to rename the database. The new name is inserted in the = new_ dbname placeholder.

Modify Filegroup

This argument lets you specify the filegroup to be modified. The information is required in filegroup_name {filegroup_property | NAME = new_filegroup_name}. If filegroup_name and NAME = new_filegroup_name are specified, these parameters change the filegroup name. See the discussion on filegroups earlier in this chapter.

With

The WITH <termination> argument specifies when to roll back incomplete transactions when the database is transitioned from one state to another. Only one termination clause can be specified, and it follows the SET clauses. ROLLBACK AFTER integer [SECONDS] | ROLLBACK IMMEDIATE specifies whether to roll back after the specified number of seconds or immediately. If the termination clause is omitted, transactions are allowed to commit or roll back on their own.

No Wait

The NO_WAIT argument specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.

Collate

See the section in CREATE DATABASE discussed earlier.

Filespec

The <filespec> section controls the file properties where the NAME argument specifies the logical name for the file.

The logical_file_name is the name used by SQL Server when referencing the file. The name must be unique within the database and conform to the rules for identifiers. The name can be a character or Unicode constant, a regular identifier, or a delimited identifier (see Identifiers in the Chapter 10).

Filename

The FILENAME argument specifies an operating system filename. When used with MODIFY FILE, FILENAME can be specified only for files in the tempdb database. The new tempdb filename takes effect only after SQL Server is stopped and restarted.

The os_file_name refers to the path and filename used by the operating system for the file. The file must reside in the server in which SQL Server is installed. Data and log files should not be placed on compressed file systems.

If the file is on a raw partition, os_file_name must specify only the drive letter of an existing raw partition. Only one file can be placed on each raw partition. Files on raw partitions do not autogrow; therefore, the MAXSIZE and FILEGROWTH parameters are not needed when os_file_name specifies a raw partition.

Size

The SIZE argument specifies the file size. The placeholder size is the size of the file. The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. The default is MB. Specify a whole number; do not include a decimal. The minimum value for size is 512KB, and the default if size is not specified is 1MB. When specified with ADD FILE, size is the initial size for the file. When specified with MODIFY FILE, size is the new size for the file and must be larger than the current file size.

Maxsize

The MAXSIZE parameter specifies the maximum file size, represented by the placeholder max_size. The KB, MB, GB, and TB suffixes can be used to specify kilobytes, megabytes, gigabytes, or terabytes. The default is MB. Specify a whole number; do not include a decimal. If max_size is not specified, the file size can increase until the disk is full.

Unlimited

This argument specifies that the file can increase in size until the disk is full.

Filegrowth

The FILEGROWTH argument specifies a file increase increment. The placeholder growth_ increment is the amount of space added to the file each time new space is needed. A value of 0 indicates no increase. The value can be specified in MB, KB, GB, TB, or %. Specify a whole number; do not include a decimal. When % is specified, the increment size is the specified percentage of the file size at the time the increment occurs. If a number is specified without an MB, KB, or % suffix, the default is MB. The default value if FILEGROWTH is not specified is 10%, and the minimum value is 64KB. The size specified is rounded to the nearest 64KB.

The db_state_option

This <db_state_option> section controls the online state of the database; allowing you to place it online, offline or emergency.

OFFLINE | ONLINE | EMERGENCY Controls whether the database is offline or online.

The db_user_access_option

SINGLE_USER | RESTRICTED_USER | MULTI_USER Controls which users may access the database. When SINGLE_USER is specified, only one user at a time can access the database. When RESTRICTED_USER is specified, only members of the db_owner, dbcreator, or sysadmin roles can use the database. MULTI_USER returns the database to its normal operating state.

The db_update_option

READ_ONLY | READ_WRITE Specifies whether the database is in read-only mode. In read-only mode, users can read data from the database but not modify it. The database cannot be in use when READ_ONLY is specified. The master database is the exception, and only the system administrator can use master while READ_ONLY is set. READ_WRITE returns the database to read/write operations.

The Cursor_option

This section controls cursor options.

CURSOR_CLOSE_ON_COMMIT ON | OFF If ON is specified, any cursors open when a transaction is committed or rolled back are closed. If OFF is specified, such cursors remain open when a transaction is committed; rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.
CURSOR_DEFAULT LOCAL | GLOBAL Controls whether cursor scope defaults to LOCAL or GLOBAL.

The Auto_option

This section controls automatic options.

AUTO_CLOSE ON | OFF If ON is specified, the database is shut down cleanly and its resources are freed after the last user exits. If OFF is specified, the database remains open after the last user exits.
AUTO_CREATE_STATISTICS ON | OFF If ON is specified, any missing statistics needed by a query for optimization are automatically built during optimization.
AUTO_SHRINK ON | OFF If ON is specified, the database files are candidates for automatic periodic shrinking.
AUTO_UPDATE_STATISTICS ON | OFF If ON is specified, any out-of-date statistics required by a query for optimization are automatically built during optimization. If OFF is specified, statistics must be updated manually.
AUTO_UPDATE_STATISTICS_ASYNC ON | OFF If ON, queries that initiate an automatic update of out-of-date statistics will not wait for the statistics to be updated before compiling. Subsequent queries will use the updated statistics when they are available. If OFF, queries that initiate an automatic update of out-of-date statistics wait until the updated statistics can be used in the query optimization plan. Setting this option to ON has no effect unless AUTO_UPDATE_STATISTICS is set to ON.

The Sql_option

The sql_option section controls the ANSI compliance options.

ANSI_NULL_DEFAULT ON | OFF If ON is specified, CREATE TABLE follows SQL-92 rules to determine whether a column allows null values.
ANSI_NULLS ON | OFF If ON is specified, all comparisons to a null value evaluate to UNKNOWN. If OFF is specified, comparisons of non-Unicode values to a null value evaluate to TRUE if both values are NULL.
ANSI_PADDING ON | OFF If ON is specified, strings are padded to the same length before comparison or insert. If OFF is specified, strings are not padded.
ANSI_WARNINGS ON | OFF If ON is specified, errors or warnings are issued when conditions such as divide-by-zero occur.
ARITHABORT ON | OFF If ON is specified, a query is terminated when an overflow or divide-by-zero error occurs during query execution.
CONCAT_NULL_YIELDS_NULL ON | OFF If ON is specified, the result of a concatenation operation is NULL when either operand is NULL. If OFF is specified, the null value is treated as an empty character string. The default is OFF.
QUOTED_IDENTIFIER ON | OFF If ON is specified, double quotation marks can be used to enclose delimited identifiers.
NUMERIC_ROUNDABORT ON | OFF If ON is specified, an error is generated when loss of precision occurs in an expression.
RECURSIVE_TRIGGERS ON | OFF If ON is specified, recursive firing of triggers is allowed. RECURSIVE_TRIGGERS OFF, the default, prevents direct recursion only. To disable indirect recursion as well, set the nested triggers server option to 0 using sp_configure.

The recovery_option

This section controls database recovery options.

RECOVERY FULL | BULK_LOGGED | SIMPLE If FULL is specified, complete protection against media failure is provided. If a data file is damaged, media recovery can restore all committed transactions.

If BULK_LOGGED is specified, protection against media failure is combined with the best performance and least amount of log memory usage for certain large-scale or bulk operations. These operations include SELECT INTO, bulk load operations (BCP and BULK INSERT), CREATE INDEX, and text and image operations (WRITETEXT and UPDATETEXT).

Under the bulk-logged recovery model, logging for the entire class is minimal and cannot be controlled on an operation-by-operation basis.

If SIMPLE is specified, a simple backup strategy that uses minimal log space is provided. Log space can be automatically reused when no longer needed for server failure recovery.

Note

The simple recovery model is easier to manage than the other two models but at the expense of higher data loss exposure if a data file is damaged. All changes since the most recent database or differential database backup are lost and must be reentered manually. Check out recovery models in Chapter 7.

The default recovery model is determined by the recovery model of the model database. To change the default for new databases, use ALTER DATABASE to set the recovery option of the model database.

TORN_PAGE_DETECTION ON | OFF If ON is specified, incomplete pages can be detected. The default is ON.
PAGE_VERIFY CHECKSUM | TORN_PAGE_DETECTION | NONE Discovers damaged database pages caused by disk I/O path errors. Disk I/O path errors can be the cause of database corruption problems and are generally caused by power failures or disk hardware failures that occur at the time the page is being written to disk.

The checksum is stored in page headers that already exist, and thus there is no extra storage cost associated with this feature. While you might see a slight performance loss during the calculation of the checksum, the benefit far outweighs the cost in additional CPU cycles to help preserve data integrity on valuable and large databases.

Creating a Database Using Management Studio

If you do not need to be scripting to create a database, then using Management Studio makes perfect sense. In addition to creating the database interactively, you can also manage or alter the database there. For example, you can manipulate the file sizes, filegroups, and so on. And you can attach and detach databases (which is a very convenient method of making copies of your database for distribution, provided you are sure the target is compatible).

To create a database interactively, do the following:

Connect to the specific server and SQL Server instance in which you want to create a new database. Expand the server node so that the node “Databases” is accessible. Select Databases and right-click. Now select New Database. The dialog box illustrated in Figure 6–1 loads.

Figure 6–1: Creating a database interactively
On the General page, enter the database name of the database, enter the name of the primary data file, and specify the file path for both the database and transaction logs. The values for the properties follow the same recommendations discussed in the CREATE DATABASE section earlier.
On the Options page you can select various options and behaviors of the database, such as its recovery model and database state.
On the Filegroups page you can add more filegroups as needed or configure the primary filegroup.

After creating the new database and before you use it, you should back up master, which will now contain definitions and specifics related to the new database.

Creating a Database Using the Copy Database Wizard

To create a database using the Copy Database Wizard, you need to do the following:

Open the target server in Object Explorer (in Solution Manager) and then expand down to the Databases node. Expand the Databases node and select a database. Right-click the database and select Tasks | Copy Database. The Copy Database Wizard appears. Click Next.
First choose the source server for the copy and authentication. This is shown in Figure 6–2. Click Next to arrive at the destination server option. Again you will be required to enter credentials to connect to the target server. Click Next.

Figure 6–2: Choosing a source server for the copy
The next screen prompts you for the transfer method. You can specify that the source database be first detached before the copy is done. This is obviously the faster of the options, but you must be able to log into the source and destination servers as the SSIS proxy account, as shown in Figure 6–3. The drawback is that the database cannot be in use. The slower option allows the database to stay online and uses the SQL Management Object API to perform the move.

Figure 6–3: Choosing the transfer method
After choosing the transfer method, you will arrive at a screen that will ask you for the name of the database to either copy or move. This is shown in Figure 6–4. Make the choice and click Next.

Figure 6–4: Choosing the database to copy
The next couple of screens are straightforward. You get to configure the database files in a similar interface to the one shown in Figure 6–1. The remaining screens involve saving the job as a DTS package (as in SQL Server 2000). You can schedule the job to run at a later time or immediately.

There you have all the methods at your disposal to create a database. We will return to database performance in Chapters 16 and 17. Next we look to database internals, tables and indexes.