SQL Server 2014 Development Essentials电子书

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 UPDATE statement examples

Example 1 – updating a single row

Example 2 – updating multiple rows

Deleting data from SQL Server database tables

The DELETE statement examples

Example 1 – deleting a single row

Example 2 – deleting all rows

Using the MERGE statement

The MERGE statement examples

The TRUNCATE TABLE statement

The SELECT INTO statement

Summary

5. Understanding Advanced Database Programming Objects and Error Handling

Creating and using variables

Creating a local variable

Creating the cursor variable

Creating the table variable

Control-of-flow keywords

BEGIN…END keywords

The IF…ELSE expression

A CASE statement

WHILE, BREAK, and CONTINUE statements

RETURN, GOTO, and WAITFOR statements

Creating and using views

Creating views with Transact-SQL and SSMS 2014

Creating, altering, and dropping views with Transact-SQL DDL statements

The CREATE VIEW statement

The ALTER VIEW statement

The DROP VIEW statement

Creating, altering, and dropping views with SSMS 2014

Creating views with SSMS 2014

Altering and dropping views with SSMS 2014

Indexed views

Indexed view example

Creating and using stored procedures

Creating a stored procedure

Modifying a stored procedure

Dropping a stored procedure

Viewing stored procedures

Executing stored procedures

Creating and using user-defined functions

Creating user-defined functions

Creating a user-defined scalar function

Using a user-defined scalar function

Creating a user-defined table-valued function

Inline table-valued function example

Multistatement table-valued function example

Modifying user-defined functions

Using a user-defined table-valued function

Dropping user-defined functions

Viewing user-defined functions

Creating and using triggers

Nested triggers

Recursive triggers

DML triggers

Inserted and deleted logical tables

Creating DML triggers

Modifying a DML trigger

Dropping a DML trigger

Data Definition Language (DDL) triggers

The EVENTDATA function

Creating a DDL trigger

Modifying a DDL trigger

Dropping a DDL trigger

Disabling and enabling triggers

Viewing triggers

Handling Transact-SQL errors

An example of TRY...CATCH

An example of TRY...CATCH with THROW

An example of TRY...CATCH with RAISERROR

Summary

6. Performance Basics

Components of SQL Server Database Engine

The SQL Server Relational Engine architecture

Parsing and binding

Query optimization

Query execution and plan caching

Query plan aging

The improved design in SQL Server 2014 for the cardinality estimation

Optimizing SQL Server for ad hoc workloads

Manually clearing the plan cache

The SQL Server 2014 in-memory OLTP engine

The limitations of memory-optimized tables

Indexes

The cost associated with indexes

How SQL Server uses indexes

Access without an index

Access with an index

The structure of indexes

Index types

Clustered indexes

When should you have a clustered index on a table?

Nonclustered indexes

Single-column indexes

Composite indexes

Covering indexes

Unique indexes

Spatial indexes

Partitioned indexes

Filtered indexes

Full-text indexes

XML indexes

Memory-optimized indexes

Columnstore indexes

The architecture of columnstore indexes

Creating and managing columnstore indexes

Guidelines for designing and optimizing indexes

Avoid overindexing tables

Create a clustered index before creating nonclustered indexes when using clustered indexes

Index columns used in foreign keys

Index columns frequently used in joins

Use composite indexes and covering indexes to give the query optimizer greater flexibility

Limit key columns to columns with a high level of selectability

Pad indexes and specify the fill factor to reduce page splits

Rebuild indexes based on the fragmentation level

Query optimization statistics

Database-wide statistics options in SQL Server to automatically create and update statistics

Manually create and update statistics

Determine the date when the statistics were last updated

Using the DBCC SHOW_STATISTICS command

Using the sys.stats catalog view with the STATS_DATE() function

The fundamentals of transactions

Transaction modes

Implementing transactions

BEGIN TRANSACTION

COMMIT TRANSACTION

ROLLBACK TRANSACTION

SAVE TRANSACTION

An overview of locking

Basic locks

Optimistic and pessimistic locking

Transaction isolation

SQL Server 2014 tools for monitoring and troubleshooting SQL Server performance

Activity Monitor

The SQLServer:Locks performance object

Dynamic Management Views

SQL Server Profiler

The sp_who and sp_who2 system stored procedures

SQL Server Extended Events

Summary

Index