MOC 10776: Developing Microsoft SQL Server 2012 Databases

COURSE OUTLINE:

Description In this course, you will be introduced to SQL Server 2012, logical table design, indexing, and query plans. You will focus on creating database objects, including views, stored procedures, parameters, and functions. You will also learn procedure coding, such as transactions, concurrency, error handling, triggers, and SQL Common Language Runtime (CLR).

Audience
IT professionals who want to become skilled on SQL Server 2012 product features and technologies for implementing a database

Prerequisites

  • Knowledge of writing T-SQL queries
  • Knowledge of basic relationship database concepts
  • Querying Microsoft SQL Server 2012 (M10774)

Learning Objectives
Learn how to design and develop SQL Server 2012 databases.

  • 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