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: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

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 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

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 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

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 CH02_01 database:

USE [master];
GO

DROP DATABASE CH02_01;
GO

Create, modify, and drop databases with SSMS 2014

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.

Creating a database with SSMS 2014

Here are the steps for creating databases with SQL Server 2014 Management Studio:

  1. Launch SQL Server 2014 Management Studio.
  2. In Object Explorer, right-click on the Databases folder and select New Database from the context menu. This opens the New Database window.
  3. In the General page of the New Database window, type in CH02_04 in the Database name textbox.
  4. On the same page, select the owner of the database. By default, the user who creates the database is set as the owner of the database.
  5. In the Database files section, configure the data and transaction log file settings for this database. For the purposes of this demonstration, we will add a user-defined filegroup called CH02_04FG that contains one secondary data file named CH02_04Data02, as shown in the following screenshot:
    Creating a database with SSMS 2014
  6. To change database options, select Options. Change options as necessary and then click on OK to create a database, as shown in the following screenshot:
    Creating a database with SSMS 2014

Modifying a database with SSMS 2014

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:

  1. In Object Explorer, expand the Databases folder.
  2. Right-click on the CH02_04 database and select Properties.
  3. In there, click on the Add button to add another secondary data file to the user-defined filegroup of the CH02_04 database.
  4. Configure the settings for this secondary data file, as shown in the following screenshot:
    Modifying a database with SSMS 2014
  5. Once done, click on OK to add this secondary data file to the CH02_04 database user-defined filegroup.

Dropping a database with SSMS 2014

To drop a database, use the following steps:

  1. To drop a database, right-click on the name of the database you want to delete and then choose Delete from the shortcut menu. This opens the Delete Object window, as shown in the following screenshot:
    Dropping a database with SSMS 2014
  2. Click on OK to drop the database from the SQL Server instance.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.137.210.143