Developing Microsoft SQL Server 2014 Databases (M20464)

1. Database Development Introduction

• SQL Server Platform
• SQL Server Tools
• Configure SQL Server Services

2. Design and Implement Tables

• Design Tables
• Work with Schemas
• Create and Alter Tables
• Partition and Compress Data

3. Ensure Data Integrity through Constraints

• Data Integrity Enforcement
• Implement Domain Integrity
• Implement Entity and Referential Integrity

4. Introduction to Indexing

• Core Indexing Concepts
• Single Column and Composite Indexes
• SQL Server Table Structures and Clustered Indexes

5. Design Optimized Index Strategies

• Execution Plan Concepts and Elements
• INCLUDE Clause, Padding, Hints, and Statistics
• Design Effective Non-Clustered Indexes
• Performance Monitoring and the Database Engine Tuning Advisor

6. Columnstore Indexes

• Clustered and Non-Clustered Columnstore Indexes

7. Design and Implement Views

• Views
• Creating and Managing Views
• Performance Considerations for Views

8. Design and Implement Stored Procedures

• Stored Procedures
• Implement Parameterized Stored Procedures
• Control the Execution Context

9. Design and Implement User-Defined Functions

• Functions
• Design and Implement Scalar Functions and Table-Valued Functions
• Considerations for Implementing Functions
• Alternatives to Functions

10. Respond to Data Manipulation via Triggers

• Design and Implement DML Triggers
• Advanced Trigger Concepts

11. In-Memory Tables

• Memory-Optimized Tables
• Native Stored Procedures

12. Implement Managed Code in SQL Server 2014

• SQL CLR Integration and Implementation
• Import and Configure Assemblies
• Implement Objects created within .NET Assemblies

13. Store and Query XML Data in SQL Server

• XML and XML Schemas
• Store XML Data and Schemas in SQL Server
• Implement the XML Data Type within SQL Server
• Use the T-SQL for XML Statement
• XQuery Language
• Shred XML to a Relational Form

14. SQL Server 2014 Spatial Data

• Spatial Data Introduction
• SQL Server Spatial Data Types
• Spatial Data in Applications

• SQL Server platform tools including editions, versions, basics of network listeners, and concepts of services and service accounts
• Appropriate data types used when designing tables, convert data between data types, and create alias data types
• Design practices regarding SQL Server tables and create tables using T-SQL (partitioned tables not covered in this course)
• Implement PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK, and UNIQUE constraints
• Investigate cascading FOREIGN KEY constraints
• Appropriate single column and composite index strategies
• Create tables as heaps, tables with clustered indexes, and appropriate structure for table designs
• Common elements from execution plans
• Design effective non-clustered indexes
• Design and implement views and stored procedures
• Work with table types and table-valued parameters
• Use MERGE to create stored procedures that update data warehouses
• Design and implement scalar and table-valued functions
• Investigate deadlock situation and how transaction isolation levels affect application concurrency
• T-SQL error handling code and structured exception handling
• Design and implement data manipulation language (DML) triggers
• SQL CLR integration and implement existing .NET assembly within SQL Server
• Store Extensible Markup Language (XML) data and schemas in SQL Server
• Basic queries on XML data in SQL Server
• GEOGRAPHY and GEOMETRY data types
• Implement and query a full-text index

Lab 1: Database Development Introduction

• SQL Server Management Studio

Lab 2: Design and Implement Tables

• Table Designs
• Create a Schema and a Table

Lab 3: Ensure Data Integrity through Constraints

• Design and Test the Constraints

Lab 4: Create Indexes

• Tables with Clustered Indexes
• Performance Improvement through Non-clustered Indexes

Lab 5: Plan for SQL Server 2014 Indexing

• Index Statistics
• Create Covering Indexes

Lab 6: In-Memory Database Capabilities

• Columnstore Indexes

Lab 7: Design and Implement Views

• WebStock, Contacts, and Available Models Views

Lab 8: Design and Implement Stored Procedures

• Create Stored and Parameterized Stored Procedures
• Alter the Execution Context of Stored Procedures

Lab 9: Design and Implement User-Defined Functions

• Format Phone Numbers and Modify Existing Function
• Function-Related Performance Issue Resolutions

Lab 10: Respond to Data Manipulation via Triggers

Lab 11: In-Memory Database Capabilities

• Memory Optimized Tables
• Natively Compiled Stored Procedures

Lab 12: Implement Managed Code in SQL Server

• CLR Code
• Implement CLR Assembly
• Implement a CLR User-Defined Aggregate and CLR User-Defined Data Type

Lab 13: Store and Query XML Data in SQL Server

• XML Data Storage in Variables
• Information Retrieval about XML Schema Collections
• Query SQL Server Data as XML
• Write a Stored Procedure Returning XML

Lab 14: SQL Server Spatial Data

• Query the Geometry Data Type
• Add Spatial Data to an Existing Table

• Knowledge of writing T-SQL queries
• Knowledge of basic relational database concepts

• IT professionals who want to become skilled on SQL Server 2014 product features and technologies for implementing a database
• Developers from other product platforms or previous versions of SQL Server who want to become skilled in implementing a SQL Server 2014 database