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.
In this section, we will cover Transact-SQL DDL statements that are used to create, alter and modify SQL Server databases.
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.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
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.
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
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.
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:SQLDATACH02_02.mdf', SIZE=10MB, MAXSIZE=20, FILEGROWTH=10%) LOG ON (NAME='CH02_02_log', FILENAME = 'C:SQLLogCH02_02_log.ldf', SIZE=10MB, MAXSIZE=200, FILEGROWTH=20%); GO
The following CREATE DATABASE
script creates the CH02_03
database on the following two filegroups:
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 FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATACH02_03DAT01.mdf', SIZE = 524288KB, FILEGROWTH = 102400KB), (NAME = N'CH02_03DAT02', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATACH02_03DAT02.ndf', SIZE = 524288KB, FILEGROWTH = 102400KB), FILEGROUP [CH02_FG1] (NAME = N'CH02_03DAT03', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATACH02_03DAT03.ndf', SIZE = 262144KB, FILEGROWTH = 102400KB) LOG ON (NAME = N'CH02_03_log', FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATACH02_03_log.ldf', SIZE = 262144KB, FILEGROWTH = 102400KB) GO
We use ALTER DATABASE
to modify an existing SQL Server database. Some common situations for modifying an existing SQL Server database include:
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.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 FilesMicrosoft SQL ServerMSSQL12.MSSQLSERVERMSSQLDATACH02_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
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 CH02_01
database:
USE [master]; GO DROP DATABASE CH02_01; GO
You can also use SQL Server 2014 Management Studio to create, modify, and drop SQL Server databases. In this section, we will cover this GUI tool.
Here are the steps for creating databases with SQL Server 2014 Management Studio:
Databases
folder and select New Database from the context menu. This opens the New Database window.CH02_04
in the Database name textbox.CH02_04FG
that contains one secondary data file named CH02_04Data02
, as shown in the following screenshot:This section illustrates how you can modify an existing SQL Server database using SQL Server 2014 Management Studio. Here are the steps for modifying an existing SQL Server database using SQL Server 2014 Management Studio:
Databases
folder.CH02_04
database and select Properties.CH02_04
database.CH02_04
database user-defined filegroup.3.137.210.143