IT TRAINING

Microsoft 10776A – Developing Microsoft SQL Server 2012 Databases

In this 5 day course you will be introduced to Microsoft SQL Server 2012 as well as logical table design, indexing and query plans. Topics discussed in this course include creating database objects, views, stored procedures, parameters and functions. You will also cover procedure coding like transactions, concurrency, error handling, triggers and SQL Common Language Runtime.

Who needs to attend

Who needs to attend?
This course is aimed at anyone interested in becoming a skilled SQL Server 2012 user.

what you will learn

What you will learn

Upon completion students 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, converting data between data types, and creating alias data types
  • Design practices regarding SQL Server tables and create tables using T-SQL
  • Implement PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK, UNIQUE constraints, and investigate cascading FOREIGN KEY constraints
  • Appropriate single column and composite index strategies
  • Create table heaps, tables with clustered indexes, and table designs with appropriate structure
  • 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 and basic queries on XML data in SQL Server
  • Work with GEOGRAPHY and GEOMETRY data types
  • Implement and query a full-text index
Prerequisites

Prerequisites

Students need to have:

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

Course Outline

Module 01 – Introduction to SQL Server 2012 and its Toolset
Estimated Study Time: 2 hour(s) and 0 minute(s)
Introduction to the SQL Server Platform
Working with SQL Server Tools
Configuring SQL Server Services
Lab : Introduction to SQL Server and its Toolset
Module 02 – Working with Data Types
Estimated Study Time: 2 hour(s) and 20 minute(s)
Using Data Types
Working with Character Data
Converting Data Types
Specialized Data Types
Lab : Working with Data Types
Module 03 – Designing and Implementing Tables
Estimated Study Time: 2 hour(s) and 0 minute(s)
Designing Tables
Working with Schemas
Creating and Altering Tables
Lab : Designing and Implementing Tables
Module 04 – Ensuring Data Integrity through Constraints
Estimated Study Time: 2 hour(s) and 0 minute(s)
Enforcing Data Integrity
Implementing Domain Integrity
Implementing Entity and Referential Integrity
Lab : Ensuring Data Integrity through Constraints
Module 05 – Planning for SQL Server 2012 Indexing
Estimated Study Time: 2 hour(s) and 0 minute(s)
Core Indexing Concepts
Data Types and Indexes
Single Column and Composite Indexes
Lab : Planning for SQL Server Indexing
Module 06 – Implementing Table Structures in SQL Server 2012
Estimated Study Time: 2 hour(s) and 0 minute(s)
SQL Server Table Structures
Working with Clustered Indexes
Designing Effective Clustered Indexes
Lab : Implementing Table Structures in SQL Server
Module 07 – Reading SQL Server 2012 Execution Plans
Estimated Study Time: 2 hour(s) and 0 minute(s)
Execution Plan Core Concepts
Common Execution Plan Elements
Working with Execution Plans
Lab : Reading SQL Server Execution Plans
Module 08 – Improving Performance through Nonclustered Indexes
Estimated Study Time: 2 hour(s) and 0 minute(s)
Designing Effective Nonclustered Indexes
Implementing Nonclustered Indexes
Using the Database Engine Tuning Advisor
Lab : Improving Performance through Nonclustered Indexes
Module 09 – Designing and Implementing Views
Estimated Study Time: 2 hour(s) and 0 minute(s)
Introduction to Views
Creating and Managing Views
Performance Considerations for Views
Lab : Designing and Implementing Views
Module 10 – Designing and Implementing Stored Procedures
Estimated Study Time: 2 hour(s) and 20 minute(s)
Introduction to Stored Procedures
Working With Stored Procedures
Implementing Parameterized Stored Procedures
Controlling Execution Context
Lab : Designing and Implementing Stored Procedures
Module 11 – Merging Data and Passing Tables
Estimated Study Time: 2 hour(s) and 0 minute(s)
Using the MERGE Statement
Implementing Table Types
Using TABLE Types As Parameters
Lab : Passing Tables and Merging Data
Module 12 – Designing and Implementing User-Defined Functions
Estimated Study Time: 2 hour(s) and 40 minute(s)
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
Module 13 – Creating Highly Concurrent SQL Server 2012 Applications
Estimated Study Time: 2 hour(s) and 20 minute(s)
Introduction to Transactions
Introduction to Locks
Management of Locking
Transaction Isolation Levels
Lab : Creating Highly Concurrent SQL Server Applications
Module 14 – Handling Errors in T-SQL Code
Estimated Study Time: 2 hour(s) and 0 minute(s)
Understanding T-SQL Error Handling
Implementing T-SQL Error Handling
Implementing Structured Exception Handling
Lab : Handling Errors in T-SQL Code
Module 15 – Responding to Data Manipulation via Triggers
Estimated Study Time: 2 hour(s) and 0 minute(s)
Designing DML Triggers
Implementing DML Triggers
Advanced Trigger Concepts
Lab : Responding to Data Manipulation via Triggers
Module 16 – Implementing Managed Code in SQL Server 2012
Estimated Study Time: 2 hour(s) and 0 minute(s)
Introduction to SQL CLR Integration
Importing and Configuring Assemblies
Implementing SQL CLR Integration
Lab : Designing and Implementing Views
Module 17 – Storing XML Data in SQL Server 2012
Estimated Study Time: 2 hour(s) and 0 minute(s)
Introduction to XML and XML Schemas
Storing XML Data and Schemas in SQL Server
Implementing the XML Data Type
Lab : Storing XML Data in SQL Server
Module 18 – Querying XML Data in SQL Server
Estimated Study Time: 2 hour(s) and 0 minute(s)
Using the T-SQL FOR XML Statement
Getting Started with XQuery
Shredding XML
Lab : Querying XML Data in SQL Server
Module 19 – Working with SQL Server 2012 Spatial Data
Estimated Study Time: 2 hour(s) and 0 minute(s)
Introduction to Spatial Data
Working with SQL Server Spatial Data Types
Using Spatial Data in Applications
Lab : Working with SQL Server Spatial Data
Module 20 – Working with Full-Text Indexes and Queries
Estimated Study Time: 2 hour(s) and 0 minute(s)
Introduction to Full-Text Indexing
Implementing Full-Text Indexes in SQL Server
Working with Full-Text Queries
Lab : Working with Full-Text Indexes and Queries

Follow on
There are no follow-ons for this course.

Certification programs
There are no certifications associated with this course.