A. Masood-Al-Farooq SQL Server 2014 Development Essentials

Design, implement, and deliver a successful database solution with Microsoft SQL Server 2014.
1. Auflage 2025
ISBN: 978-1-78217-256-7
Verlag: De Gruyter
Format: PDF
Kopierschutz: Adobe DRM (»Systemvoraussetzungen)

E-Book, Englisch, 214 Seiten

This book is an easy-to-follow, comprehensive guide that is full of hands-on examples, which you can follow to successfully design, build, and deploy mission-critical database applications with SQL Server 2014. If you are a database developer, architect, or administrator who wants to learn how to design, implement, and deliver a successful database solution with SQL Server 2014, then this book is for you. Existing users of Microsoft SQL Server will also benefit from this book as they will learn what's new in the latest version.

Autoren/Hrsg.

A. Masood-Al-Farooq, Basit

Fachgebiete

• Mathematik | Informatik EDV | Informatik Programmierung | Softwareentwicklung Software Engineering

Leseproben

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 statement to create a new database on SQL Server. The general syntax for the command is as follows:

CREATE DATABASE database_name [CONTAINMENT = {NONE | PARTIAL}] [ON [PRIMARY] [

The following are the arguments of the command:

• : This is the name of new SQL Server database. The database name must be unique with an instance of SQL Server.
• : This is used to specify the containment status of the database. Specify for non-contained databases, and for partially contained databases.
• : 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.
• : This is used to specify the location for the transaction log files.
• : The arguments are used to control file properties. This option is supported for both the data and transaction log file. The parameters include:
  • : This is the logical name of the database. We use this name in Transact-SQL statements to refer to the file.
  • : 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 article at http://msdn.microsoft.com/en-us/library/dn385720.aspx.

  • : This is the initial size of the database file. The value can be specified in KB, MB, GB, or TB.
  • : 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 .
  • : 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 (%).
• : 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 topic at http://msdn.microsoft.com/en-gb/library/ms184391(v=sql.120).aspx.
• : This is used to configure the following external excess options:

  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 script creates a database using the default parameters from the 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 script creates the 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 script creates the database on the following two filegroups:

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

The following code generates the 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 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 statement:

ALTER DATABASE database_name ADD FILE

The following are the arguments of the command:

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

Example – adding a secondary data file to an existing database

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

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 clause of the statement to change database options. For example, you can run the following command to set the recovery model of the database to :

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 statement to delete the database from SQL Server. The following is the basic syntax for :

DROP DATABASE database_name;

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

Über Autor(innen)

A. Masood-Al-Farooq Basit :

Basit A. Masood-Al-Farooq is an internationally known Lead SQL DBA, trainer, and technical author with twelve years' experience of the Microsoft technology stack. He is an accomplished development and production SQL Server DBA with a proven record of delivering major projects on time and within budget. He is an expert at evaluating the clients' needs against the capabilities of the SQL Server product set, with the objective of minimizing costs and maximizing functions by making innovative use of advance capabilities. Basit has authored numerous SQL Server technical articles on various SQL Server topics for different SQL Server community sites, which include SQLMag.com, MSSQLTips.com, SQLServerCentral.com, SSWUG.org, SQL-Server-Performance.com, and SearchSQLServer.com. He has also developed and implemented many successful database infrastructures, data warehouses, and business intelligence projects. He holds a Master's degree in Computer Science from London Metropolitan University and industry-standard certifications from Microsoft, Sun, Cisco, Brainbench, ProSoft, and APM, which include MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS.NET Framework 2.0 Web Applications. He also has a good understanding of ITIL principles. He can be reached via Twitter (@BasitAali), his blog (http://basitaalishan.com), or via LinkedIn (http://uk.linkedin.com/in/basitfarooq). He was a technical reviewer for SQL Server 2012 Reporting Services Blueprints, Marlon Ribunal and Mickey Stuewe, Packt Publishing and Reporting with Microsoft SQL Server 2012, James Serra and Bill Anton, Packt Publishing.