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.