Database Development 20464-Developing Microsoft SQL Server 2014 Databases

20464-Developing Microsoft SQL Server 2014 Databases


Date of Class:


Last Day To Enroll:

2975.00 2,975.00

  • Course Delivery: Virtual Classroom
  • Language: English

Chat Live | Contact Us | Toll Free: (888) 360-8764



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.

Course Objectives

  • 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


Topics Covered

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



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


Subject Matter Expert

Productivity Point Global is led by a team of individuals with world-class experience in professional development skills and leading edge technologies. These established professionals collaborate to drive PPG's business forward through entrepreneurial innovation, strategic partnerships, brand management and sales growth. Productivity Point Global, previously ExecuTrain of Florida, began in 2003 and evolved out of a desire to increase our outreach both nationally and internationally. Utilizing the highest standard in customer service combined with our subject matter experts and high-end training venues, PPG has carved a niche by consistently expanding our corporate and government client base, originally hosted in the southeastern region of the US. By consistently setting quality as the benchmark of our value system, PPG combines strategic partnerships and cutting edge technology with our determined efforts to branch out into other regions. PPG's combination of technology and professional skills training, IT outsourcing, and event hosting enables us to place a focused emphasis on the diversified business needs of our clients. For more info please visit:

Got questions? Contact us below or call 877-881-2235

Why Choose

  • Fast and easy courses completion
  • Get an education faster than at traditional colleges!
  • 100% online - No classroom attendance required.
  • Unlimited 24x7 online customer support
  • Over 500,000+ certified nationwide.