HP Education Services course data sheet
Developing Microsoft SQL Server
Databases (20464)
H8N64S
Course Overview
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.
Special Note
Elements of this syllabus are subject to change.
Audience
This course is intended for:
IT professionals who want to become skilled on SQL Server 2014 (includes SQL Server 2012) product features and technologies for implementing a database
Prerequisites
Knowledge of writing T-SQL queries
Knowledge of basic relational database concepts
Course H8N61S: Querying Microsoft SQL Server (20461)or equivalent skills and knowledge.
Course objectives
During this course, students will 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
Price USD $2,500 Links to local schedules, pricing and registration US/Canada Course title Developing Microsoft SQL Server Databases (20464) HP course # H8N64S
2
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
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
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
4
Labs
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
© Copyright 2015 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice. The only warranties for HP products and services are set forth in the express warranty statements accompanying such products and services. Nothing herein should be construed as constituting an additional warranty. HP shall not be liable for technical or editorial errors or omissions contained herein.
Microsoft and Windows are U.S. registered trademarks of the Microsoft group of companies.
Learn more at
hp.com/us/training/americas
Lab 14: SQL Server Spatial Data