Overview
This 5-day course introduces SQL Server 2014 and describes logical table design, indexing and query plans. It also focuses on the creation of database objects including views, stored procedures, along with parameters, and functions. Other common aspects of procedure coding, such as indexes, concurrency, error handling, and trigger are also covered in this course. This course also helps participants to prepare for 70-464 Exam.
Prerequisites
Participants who wish to take up 20464 Developing Microsoft SQL Server Databases should meet the following prerequisites:
- Knowledge of writing T-SQL queries
- Knowledge of basic relational database concepts
Who Should Attend?
This course is recommended for IT professionals who want to be proficient on SQL Server 2014 product features and technologies for implementing a database. This course is also tailored for individuals who are developers from other product platforms or previous versions of SQL Server looking to become skilled in the implementation of an SQL Server 2014 database.
Course Outline
- Introduction to the SQL Server Platform
- SQL Server Database Development Tasks
Lab: Introduction to Database Development
- Designing Tables
- Data Types
- Working with Schemas
- Creating and Altering Tables
- Partitioning Data
- Compressing Data
Lab: Designing and Implementing Tables
- Enforcing Data Integrity
- Implementing Domain Integrity
- Implementing Entity and Referential Integrity
Lab: Ensuing Data Integrity through Constraints
- Core Indexing Concepts
- Data Types and Indexes
- Single Column and Composite Indexes
Lab: Implementing Indexes
- Covering Indexes
- Managing Indexes
- Working with Execution Plans
- Using the DTE
Lab: Designing Optimized Index Strategies
- Introduction to Columnstore indexes
- Creating Columnstore Indexes
- Working with Columnstore Indexes
Lab: Using Columnstore Indexes
- Introduction to Views
- Creating and Managing Views
- Performance Considerations for Views
Lab: Designing and Implementing Views
- Introduction to Stored Procedures
- Working with Stored Procedures
- Implementing Parameterized Stored Procedures
- Controlling Execution Context
Lab: Designing and Implementing Stored Procedures
- Overview of Functions
- Designing and Implementing Scalar Functions
- Designing and Implementing Table-Valued Functions
- Implementation Considerations for Functions
- Alternatives to Functions
Lab: Designing and Implementing User-Defined Functions
- Designing DML Triggers
- Implementing DML Triggers
- Advanced Trigger Concepts
Lab: Responding to Data Manipulation via Triggers
- In-Memory Tables
- Native Stored Procedures
Lab: Using In-Memory OLTP
- Introduction to SQL CLR Integration
- Importing and Configuring Assemblies
- Implementing SQL CLR Integration
Lab: Implementing Managed Code in SQL Server
- Introduction to XML and XML Schemas
- Storing XML Data and Schemas in SQL Server
- Implementing the XML Data Type
- Using the T-SQL FOR XML Statement
- Getting Started with XQuery
Lab: Storing and Querying XML Data in SQL Server
- Introduction to Spatial Data
- Working with SQL Server Spatial Data Types
- Using Spatial Data in Applications
Lab: Working with SQL Server Spatial Data
- Querying Data with Stored Procedures
- Implementing FILESTREAM and File Tables
- Searching Data Files
Lab: Implementing a Solution for Storing Data Files
