SQL Server 2014 Development Essentials

Basit A. Masood-Al-Farooq

• Publisher: Packt Publishing
• Year: 2014
• eBook ISBN: 9781782172550
• Edition: 1
• Topic: Informatique
• Subtopic: Applications de l'entreprise

In Detail

SQL Server 2014 Development Essentials is an easy-to-follow yet comprehensive guide that is full of hands-on examples. With Microsoft SQL Server 2014, you can design, build, and deploy mission-critical database applications. The variety of new in-memory features enable you to design high performance database applications that can improve the performance of your applications, making them as much as ten times more efficient in some cases.

Whether you are thinking about becoming a database developer, architect, or administrator, or you are a seasoned database expert, this book will provide you with all the skills you need to successfully create, design, and deploy databases using SQL Server 2014. You will also learn how to add, modify, and delete data stored within a database. You will use Transact-SQL statements to create and manage advanced database objects that include scalar and table-valued functions, views, stored procedures, and triggers. Finally, you will learn about how SQL Server works, how indexes and statistics improve query performance, and the new SQL Server 2014 in-memory technologies.

Approach

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.

Who this book is for

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.

SQL Server 2014 Development Essentials

---

Table of Contents

SQL Server 2014 Development Essentials

Credits

About the Author

Acknowledgments

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers, and more

Why subscribe?

Free access for Packt account holders

Instant updates on new Packt books

Preface

What this book covers

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. Microsoft SQL Server Database Design Principles

Database design

The requirement collection and analysis phase

The conceptual design phase

The logical design phase

The physical design phase

The implementation and loading phase

The testing and evaluation phase

The database design life cycle recap

Table design

Tables

Entities

Attributes

Relationships

A one-to-one relationship

A one-to-many relationship

A many-to-many relationship

Data integrity

The basics of data normalization

The normal forms

The first normal form (1NF)

The second normal form (2NF)

The third normal form (3NF)

Denormalization

The SQL Server database architecture

Pages

Extents

The transaction log file architecture

The operation and workings of a transaction log

Filegroups

The importance of choosing the appropriate data type

SQL Server 2014 system data types

Alias data types

Creating and dropping alias data types with SSMS 2014

Creating and dropping alias data types using the Transact-SQL DDL statement

Creating an alias data type using CREATE TYPE

Dropping an alias data type using DROP TYPE

CLR user-defined types

Summary

2. Understanding DDL and DCL Statements in SQL Server

Understanding the DDL, DCL, and DML language elements

Data Definition Language (DDL) statements

Data Manipulation Language (DML) statements

Data Control Language (DCL) statements

Understanding the purpose of SQL Server 2014 system databases

SQL Server 2014 system databases

The master database

The model database

The msdb database

The tempdb database

The resource database

The distribution database

An overview of database recovery models

The simple recovery model

The bulk-logged recovery model

Full recovery

Creating and modifying databases

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

Creating a database with T-SQL DDL statements

Example 1 – creating a database based on a model database

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

Example 3 – creating a database on multiple filegroups

Modifying a database with T-SQL DDL statements

Example – adding a secondary data file to an existing database

Dropping a database with T-SQL DDL statements

Create, modify, and drop databases with SSMS 2014

Creating a database with SSMS 2014

Modifying a database with SSMS 2014

Dropping a database with SSMS 2014

Creating and managing database schemas

Managing schemas using T-SQL DDL statements

Managing schemas using SSMS 2014

Creating and managing tables

Creating and modifying tables

Creating and modifying tables with T-SQL DDL statements

Creating a table with T-SQL DDL statements

Modifying a table with T-SQL DDL statements

Dropping a table with T-SQL DDL statements

Creating and modifying tables with SSMS 2014

Creating a table with SSMS 2014

Modifying a table with SSMS 2014

Deleting a table with SSMS 2014

Grant, deny, and revoke permissions to securables

Grant, deny, and revoke permissions to securables with T-SQL DCL statements

Granting permissions to securables with T-SQL DCL statements

Denying permissions to securables with T-SQL DCL statements

Revoking permissions to securables with T-SQL DCL statements

Managing permissions using SSMS 2014

Summary

3. Data Retrieval Using Transact-SQL Statements

Understanding Transact-SQL SELECT, FROM, and WHERE clauses

The SELECT statement

The FROM clause

The WHERE clause

Using T-SQL functions in the query

Aggregate functions

Configuration functions

Cursor functions

Date and time functions

Mathematical functions

Metadata functions

Rowset functions

Security functions

String functions

System statistical functions

Multiple table queries using UNION, EXCEPT, INTERSECT, and JOINs

The UNION operator

The EXCEPT operator

The INTERSECT operator

The JOIN operator

Using INNER JOIN

Using outer joins

Using LEFT OUTER JOIN

Using RIGHT OUTER JOIN

Using FULL OUTER JOIN

Using CROSS JOIN

Using self joins

Subqueries

Examples of subqueries

Common Table Expressions

Organizing and grouping data

The ORDER BY clause

The GROUP BY clause

The HAVING clause

The TOP clause

The DISTINCT clause

Pivoting and unpivoting data

Using the Transact-SQL analytic window functions

Ranking functions

PERCENT RANK

CUME_DIST

PERCENTILE_CONT and PERCENTILE_DISC

LEAD and LAG

FIRST_VALUE and LAST_VALUE

Summary

4. Data Modification with SQL Server Transact-SQL Statements

Inserting data into SQL Server database tables

The INSERT examples

Example 1 – insert a single row into a SQL Server database table

Example 2 – INSERT with the SELECT statement

Example 3 – INSERT with the EXEC statement

Example 4 – explicitly inserting data into the IDENTITY column

Updating data in SQL Server database tables

The UPDA...

Table of contents

• SQL Server 2014 Development Essentials