Microsoft 20464 – Developing Microsoft SQL Server Databases

In this course you will learn about SQL Server, logical table design, indexing, query plans and data and domain integiryt. You will also discuss about creating database objects, including views, stored procedures, parameters and functions. Other topics discussed in this course include procedure coding like indexes, concurrency, error handling, triggers and SQL Common Language Runtime.

Who needs to attend

Who needs to attend?
This course is aimed at IT professionals that want to learn more about SQL Server 2014 product features and technologies for implementing a database.

what you will learn

What you will learn

Upon completion you will know how to:

  • 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

What you will learn

Students need to have:

  • Knowledge of writing T-SQL queries
  • Knowledge of basic relational database concepts
Course outline

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