NewEra Technology Facebook
20464 Developing Microsoft SQL Server Databases - Course Information
PRINT
SEND TO A FRIEND
Name: 20464 Developing Microsoft SQL Server Databases
Category: Microsoft Official Curriculum Courses
Length (Days): 5
Course Price (CAD): 2995
Description:
TRAGET AUDIENCE

The primary audience for this course is IT Professionals who want to become skilled on SQL Server 2014 product features and technologies for implementing a database.

COURSE OBJECTIVES

After completing this course, students will be able to:

• Introduce 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.
• Be aware of 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.
• Determine 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
• Design and implement stored procedures.
• Work with table types, table valued parameters and use the MERGE statement to create stored procedures that update data warehouses.
• Design and implement functions, both scalar and table-valued. (Also describe where they can lead to performance issues).
• Perform basic investigation of a deadlock situation and learn how transaction isolation levels affect application concurrency.
• Use both traditional T-SQL error handling code and structured exception handling.
• Design and implement DML triggers
• Learn appropriate uses for SQL CLR integration and implement an existing .NET assembly within SQL Server.
• Store XML data and schemas in SQL Server.
• Perform basic queries on XML data in SQL Server.
• Work with the GEOGRAPHY and GEOMETRY data types
• Implement and query a full-text index.
Prerequisites:
This course requires that you meet the following prerequisites:
Knowledge of writing T-SQL queries.
Knowledge of basic relational database concepts.
Outline:
1 - INTRODUCTION TO DATABASE DEVELOPMENT

Introduction to the SQL Server Platform
Working with SQL Server Tools
Configuring SQL Server Services
Lab : Introduction to Database Development

2 - DESIGNING AND IMPLEMENTING TABLES

Designing Tables
Working with Schemas
Creating and Altering Tables
Lab : Designing and Implementing Tables

3 - ENSURING DATA INTEGRITY THROUGH CONSTRAINTS

Enforcing Data Integrity
Implementing Domain Integrity
Implementing Entity and Referential Integrity
Lab : Ensuring Data Integrity through Constraints

4 - INTRODUCTION TO INDEXING

Core Indexing Concepts
Single Column and Composite Indexes
SQL Server Table Structures
Working with Clustered Indexes

5 - ADVANCED INDEXING

Execution Plan Core Concepts
Common Execution Plan Elements
Working with Execution Plans
Designing Effective Nonclustered Indexes
Performance Monitoring
Lab : Planning for SQL Server 2014 Indexing

6 - COLUMNSTORE INDEXES

Columnstore Indexes
Best Practices for Columnstore Indexes
Lab : Using In-Memory Database Capabilities

7 - DESIGNING AND IMPLEMENTING VIEWS

Introduction to Views
Creating and Managing Views
Performance Considerations for Views
Lab : Designing and Implementing Views

8 - DESIGNING AND IMPLEMENTING STORED PROCEDURES

Introduction to Stored Procedures
Working With Stored Procedures
Implementing Parameterized Stored Procedures
Controlling Execution Context
Lab : Designing and Implementing Stored Procedures

9 - 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
Lab : Designing and Implementing User-Defined Functions

10 - RESPONDING TO DATA MANIPULATION VIA TRIGGERS

Designing DML Triggers
Implementing DML Triggers
Advanced Trigger Concepts
Lab : Responding to Data Manipulation via Triggers

11 - USING IN-MEMORY TABLES

Memory-Optimized Tables
Native Stored Procedures
Lab : Using In-Memory Database Capabilities

12 - IMPLEMENTING MANAGED CODE IN SQL SERVER

Introduction to SQL CLR Integration
Importing and Configuring Assemblies
Implementing SQL CLR Integration
Lab : Implementing Managed Code in SQL Server

13 - 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
Lab : Storing and Querying XML Data in SQL Server

14 - WORKING WITH SQL SERVER SPATIAL DATA

Introduction to Spatial Data
Working with SQL Server Spatial Data Types
Using Spatial Data in Applications
Lab : Working with SQL Server Spatial Data

Actual course outline may vary depending on offering center. Contact your sales representative for more information.
Guaranteed to run courses