Course Description
Developing Microsoft SQL Server Databases – 2014 Edition is designed for IT Professionals who want to become skilled and certified on SQL Server 2014 product features and technologies for implementing a database. It is also appropriate for developers from other product platforms or individuals with experience on earlier versions of SQL server who are looking to become skilled in the implementation of an SQL Server 2014 database.
This course covers SQL Server 2014, logical table design, indexing and query plans. You will learn how to create database objects including views, stored procedures, parameters, and functions. You will also learn procedure coding features such as indexes, concurrency, error handling, and triggers. This course will help to prepare you for the Oracle exam 70-464.
In This Course You Will Learn:
- Explain the entire SQL Server platform and its major tools. It will cover editions, versions, basics of network listeners, and concepts of services and service accounts.
- Identify appropriate data types to be used when designing tables, convert data between data types, and create alias data types.
- List good design practices regarding SQL Server tables and be able to create tables using T-SQL. (Note: partitioned tables are not covered).
- Implement PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK and UNIQUE constraints, and investigate cascading FOREIGN KEY constraints.
- Identify appropriate single column and composite indexes strategies.
- Create tables as heaps and tables with clustered indexes. Also consider the design of a table and suggest an appropriate structure.
- Read and interpret details of common elements from execution plans.
- Design effective non-clustered indexes.
- Design and implement views
Prerequisites
Students should have previous experience in writing T-SQL queries, and basic relational database concepts or have taken the following GogoTraining Courses:
Outline
Module 00: Course Introduction: Developing Microsoft SQL Server 2014 Databases
Module 01: Installing and Configuring SQL Server 2014
- Planning SQL Server Installation
- Installing SQL Server 2014
- Post-Installation Configuration
Module 02: Lab Exercise: Installing SQL Server 2014
Module 03: Introduction to Database Development
- Introduction to the SQL Server Platform
- Working with SQL Server Tools
- Configuring SQL Server Services
Module 04: Lab Exercise: Introduction to Database Development
Module 05: Designing Tables
- Designing Tables
- Table Datatypes
- Column Attributes
Module 06: Implementing Tables
- Working with Schemas
- Creating and Altering Tables
Module 07: Lab Exercise: Designing and Implementing Tables
Module 08: Ensuring Data Integrity through Constraints
- Enforcing Data Integrity
- Implementing Domain Integrity
- Implementing Entity and Referential Integrity
Module 09: Lab Exercise: Ensuring Data Integrity through Constraints
Module 10: Introduction to Indexing
- Core Indexing Concepts
- Single Column and Composite Indexes
- SQL Server Table Structures
- Working with Clustered Indexes
Module 11: Lab Exercise: Creating Indexes
Module 12: Execution Plan Concepts
- Execution Plan Core Concepts
- Common Execution Plan Elements
- Working with Execution Plans
Module 13: Advanced Indexing
- Execution Plan Terms
- Designing Effective Non-clustered Indexes
- Performance Monitoring
Module 14: Lab Exercise: Planning for SQL Server 2014 Indexing
Module 15: Columnstore Indexes
- Columnstore Indexes
- Best Practices for Columnstore Indexes
Module 16: Lab Exercise: Using In-Memory Database Capabilities
Module 17: Designing and Implementing Views
- Introduction to Views
- Creating and Managing Views
- Performance Considerations for Views
Module 18: Lab Exercise: Designing and Implementing Views
Module 19: Designing and Implementing Stored Procedures
- Introduction to Stored Procedures
- Working With Stored Procedures
- Implementing Parameterized Stored Procedures
- Controlling Execution Context
Module 20: Lab Exercise: Designing and Implementing Stored Procedures
Module 21: Designing and Implementing User-Defined Functions
- Overview of Functions
- Designing and Implementing Scalar Functions
- Designing and Implementing Table-Valued Functions
- Implementation Considerations for Functions
- Alternatives to Functions
Module 22: Lab Exercise: Designing and Implementing User-Defined Functions
Module 23: Responding to Data Manipulation via Triggers
- Designing DML Triggers
- Implementing DML Triggers
- Advanced Trigger Concepts
Module 24: Lab Exercise: Responding to Data Manipulation via Triggers
Module 25: Using In-Memory Tables
- Memory-Optimized Tables
- Native Stored Procedures
Module 26: Lab Exercise: Using In-Memory Database Capabilities
Module 27: Implementing Managed Code in SQL Server
- Introduction to SQL CLR Integration
- Importing and Configuring Assemblies
- Implementing SQL CLR Integration
Module 28: Lab Exercise: Implementing Managed Code in SQL Server
Module 29: Storing and Querying XML Data 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
- Shredding XML
Module 30: Lab Exercise: Storing and Querying XML Data in SQL Server
Module 31: Working with SQL Server Spatial Data
- Introduction to Spatial Data
- Working with SQL Server Spatial Data Types
- Using Spatial Data in Applications
Module 32: Lab Exercise: Working with SQL Server Spatial Data
Module 33: Where Do You Go from Here?
Certification
This course helps you to prepare for Microsoft Exam 70-464 – Developing Microsoft SQL Server Databases. The exam is intended for database professionals who build and implement databases across organizations and who ensure high levels of data availability. Individuals in this capacity create database file, data types, and tables; plan, create and optimize indexes; ensure data integrity, implement views, store procedures, and functions; and manage transactions and locks.
As of February 18, 2016, this exam includes content covering both SQL Server 2012 and 2014. The exam does not include questions on features or capabilities that are present only in the SQL Server 2012 product. The exam covers the following topics: Implement database objects (30 – 35%); implement programming objects (20 – 25%); Design database objects (20 – 25%); and Optimize and troubleshoot queries (20-25%).
Successfully passing Exam 70-464 will also assist you in working toward your Microsoft Certified Solutions Expert (MCSE) Certification. To learn more about the MCSE Certifications, click here.