20464 - Developing Microsoft SQL Server Databases (2012/2014)

20464 - Developing Microsoft SQL Server Databases (2012/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).

This course incorporates material from the Official Microsoft Learning Product 20464: Developing Microsoft SQL Server Databases. It covers the skills and knowledge measured by Exam 70-464 and along with on-the-job experience, helps you prepare for the exam.

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 (includes SQL Server 2012) product features and technologies for implementing a database


  • Knowledge of writing T-SQL queries
  • Knowledge of basic relational database concepts
  • Querying Microsoft SQL Server (M20461)

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
    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. Advanced Indexing
    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
    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 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