Creating and modifying databases

You can use either Transact-SQL DDL statements or SQL Server Management Studio to create and modify databases. In the following subsections, we will discuss these options.

Create, modify, and drop databases with T-SQL DDL statements

In this section, we will cover Transact-SQL DDL statements that are used to create, alter and modify SQL Server databases.

Creating a database with T-SQL DDL statements

We use the CREATE DATABASE statement to create a new database on SQL Server. The general syntax for the CREATE DATABASE command is as follows:

CREATE DATABASE database_name [CONTAINMENT = {NONE | PARTIAL}] [ON [PRIMARY] [<filespec> [,...n] [,<filegroup> [,...n]] [LOG ON <filespec> [,...n]]] [COLLATE collation_name] [WITH <option> [,...n]] [;]

The following are the arguments of the CREATE DATABASE command:

• database_name: This is the name of new SQL Server database. The database name must be unique with an instance of SQL Server.
• CONTAINMENT: This is used to specify the containment status of the database. Specify NONE for non-contained databases, and PARTIAL for partially contained databases.
• ON [PRIMARY]: This is used to specify the files in the primary filegroup. If this parameter is not specified, the first file in the list becomes the primary file for the database.
• LOG ON: This is used to specify the location for the transaction log files.
• filespec: The filespec arguments are used to control file properties. This option is supported for both the data and transaction log file. The filespec parameters include:
  • Name: This is the logical name of the database. We use this name in Transact-SQL statements to refer to the file.
  • FILENAME: This specifies the operating system name and file path.

    Note

    
    

    SQL Server 2014 Database Engine enables you to store SQL Server database files as Windows Azure Blobs Storage. This is one of the new features of SQL Server 2014. For more information about this feature, refer to the SQL Server Data Files in Windows Azure article at http://msdn.microsoft.com/en-us/library/dn385720.aspx.

  • Size: This is the initial size of the database file. The value can be specified in KB, MB, GB, or TB.
  • MAXSIZE: This is used to specify the maximum size limit for the database file. The value can be specified in KB, MB, GB, TB, or as UNLIMITED.
  • FILEGROWTH: This is used to specify the automatic growth increments for the database file. The value can be specified in KB, MB, GB, TB, or percentage (%).
• COLLATE: This specifies the default collation setting for the database. If not specified, the server default collation is used as the database collation. For more information about the Windows and SQL collation names, refer to the COLLATE (Transact-SQL) topic at http://msdn.microsoft.com/en-gb/library/ms184391(v=sql.120).aspx.
• WITH <option>: This is used to configure the following external excess options:
  • DEFAULT_FULLTEXT_LANGUAGE
  • DEFAULT_LANGUAGE
  • DB_CHAINING, TRUSTWORTHY
  • NESTED_TRIGGERS
  • TWO_DIGIT_YEAR_CUTOFF
  • TRANSFORM_NOISE_WORDS

  Note

  
  

  A detailed discussion about a database's external excess options is beyond the scope of this chapter. For help with this, refer to http://technet.microsoft.com/en-us/library/ms176061(v=sql.120).aspx.

Example 1 - creating a database based on a model database

The following CREATE DATABASE script creates a CH02_01 database using the default parameters from the model database:

USE [master]; GO CREATE DATABASE [CH02_01]; GO

Tip

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

Example 2 - creating a database that explicitly specifies the database data and the transaction log file's filespecs properties

The following CREATE DATABASE script creates the CH02_02 database by explicitly specifying data and the transaction log file's filespecs properties:

USE [master]; GO CREATE DATABASE CH02_02 ON PRIMARY (NAME='CH02_02_Data', FILENAME = 'C:\SQLDATA\CH02_02.mdf', SIZE=10MB, MAXSIZE=20, FILEGROWTH=10%) LOG ON (NAME='CH02_02_log', FILENAME = 'C:\SQLLog\CH02_02_log.ldf', SIZE=10MB, MAXSIZE=200, FILEGROWTH=20%); GO

Example 3 - creating a database on multiple filegroups

The following CREATE DATABASE script creates the CH02_03 database on the following two filegroups:

• The primary filegroup, which contains CH02_03DAT01 and CH02_02DAT02
• The user-defined filegroup, CH02_FG1, which only contains the database file, that is, CH02_03DAT03

The following code generates the CH02_03 database:

USE [master]; GO CREATE DATABASE [CH02_03] CONTAINMENT = NONE ON PRIMARY (NAME = N'CH02_03DAT01 ', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CH02_03DAT01.mdf', SIZE = 524288KB, FILEGROWTH = 102400KB), (NAME = N'CH02_03DAT02', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CH02_03DAT02.ndf', SIZE = 524288KB, FILEGROWTH = 102400KB), FILEGROUP [CH02_FG1] (NAME = N'CH02_03DAT03', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CH02_03DAT03.ndf', SIZE = 262144KB, FILEGROWTH = 102400KB) LOG ON (NAME = N'CH02_03_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CH02_03_log.ldf', SIZE = 262144KB, FILEGROWTH = 102400KB) GO

Modifying a database with T-SQL DDL statements

We use ALTER DATABASE to modify an existing SQL Server database. Some common situations for modifying an existing SQL Server database include:

• Adding or removing filegroups and database files to an existing database
• Adding or removing transaction log files to an existing database
• Manually expanding data and/or transaction log file sizes
• Changing data and/or transaction log file growth settings
• Setting database options
• Changing the database default collation

The following is the basic syntax for the ALTER DATABASE statement:

ALTER DATABASE database_name ADD FILE <filespec> [,...n] [TO FILEGROUP {filegroup_name | DEFAULT}] | ADD LOG FILE <filespec> [,...n] | REMOVE FILE logical_filename | MODIFY FILE filespec

The following are the arguments of the ALTER DATABASE command:

• database_name: This is the name of a new SQL Server database. The database name must be unique with an instance of SQL Server.
• ADD FILE: This argument adds a file to the database.
• TO FILEGROUP: This will be the name of the filegroup to which the specified file will be added.
• REMOVE FILE: This argument removes a file from the database.
• MODIFY FILE: This argument specifies the file that should be modified.

Example - adding a secondary data file to an existing database

The following example uses ALTER DATABASE to add a secondary data file to the CH2_03 database user-defined filegroup (CH02_FG1):

USE [master]; GO ALTER DATABASE [CH02_03] ADD FILE (NAME = N'CH02_03DAT04', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\CH02_03DAT04.ndf', SIZE = 524288KB, FILEGROWTH = 102400KB) TO FILEGROUP [CH02_FG1]; GO

You can also use the SET clause of the ALTER DATABASE statement to change database options. For example, you can run the following command to set the recovery model of the CH02_03 database to FULL:

USE [master]; GO ALTER DATABASE [CH02_03] SET RECOVERY FULL WITH NO_WAIT; GO

Dropping a database with T-SQL DDL statements

When you no longer need a database, you can use the DROP DATABASE statement to delete the database from SQL Server. The following is the basic syntax for DROP DATABASE:

DROP DATABASE database_name;

For example, you run the following command to drop the...