![]()
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...
