20464 - Developing Microsoft SQL Server Databases 2014

20464 - Developing Microsoft SQL Server Databases 2014

Length : 5 days

In this course, you will be introduced to SQL Server, logical table design, indexing, query plans, and data and domain integrity. You will focus on creating database objects, including views, stored procedures, parameters, and functions. You will also learn procedure coding, such as indexes, concurrency, error handling, triggers, and SQL Common Language Runtime (CLR).

What You'll Learn

  • 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

Who Needs to Attend

  • 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 databasee


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

Course Outline

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
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
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


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

Class Dates:

Classes being scheduled,
call 1-800-755-0142 or

Contact Us with your request.

On Site Class Available

ExpertONE Learning Solutions Provider Microsoft Certified Partner Logo GSA Advantage