> > > 10776 Detailed outline

Developing Microsoft SQL Server 2012 Databases (10776)

Course Description Schedule Course Outline

Detailed Course Outline

Module 1: Introduction to SQL Server 2012 and its Toolset

Lessons

  • Introduction to the SQL Server Platform
  • Working with SQL Server Tools
  • Configuring SQL Server Services

Lab: Introduction to SQL Server and its Toolset

  • Verifying SQL Server Component Installation
  • Altering Service Accounts for New Instance
  • Enabling Named Pipes Protocol for Both Instances
  • Creating an Alias for AdvDev
  • Ensuring SQL Browser is Disabled and Configure a Fixed TCP/IP Port (Only if time permits

Module 2: Working with Data Types

Lessons

  • Using Data Types
  • Working with Character Data
  • Converting Data Types
  • Specialized Data Types

Lab: Working with Data Types

  • Choosing Appropriate Data Types
  • Writing Queries With Data Type Conversions
  • Designing and Creating Alias Data Types

Module 3: Designing and Implementing Tables

Lessons

  • Designing Tables
  • Working with Schemas
  • Creating and Altering Tables

Lab: Designing and Implementing Tables

  • Improving the Design of Tables
  • Creating a Schema
  • Creating the Tables

Module 4: Ensuring Data Integrity through Constraints

Lessons

  • Enforcing Data Integrity
  • Implementing Domain Integrity
  • Implementing Entity and Referential Integrity

Lab: Ensuring Data Integrity through Constraints

  • Designing Constraints
  • Testing the constraints

Module 5: Planning for SQL Server 2012 Indexing

Lessons

  • Core Indexing Concepts
  • Data Types and Indexes
  • Single Column and Composite Indexes

Lab: Planning for SQL Server Indexing

  • Exploring existing index statistics
  • Designing column orders for indexes

Module 6: Implementing Table Structures in SQL Server 2012

Lessons

  • SQL Server Table Structures
  • Working with Clustered Indexes
  • Designing Effective Clustered Indexes

Lab: Implementing Table Structures in SQL Server

  • Creating Tables as Heaps
  • Creating Tables with Clustered Indexes
  • Comparing the Performance of Clustered Indexes vs. Heaps

Module 7: Reading SQL Server 2012 Execution Plans

Lessons

  • Execution Plan Core Concepts
  • Common Execution Plan Elements
  • Working with Execution Plans

Lab: Reading SQL Server Execution Plans

  • Actual vs. Estimated Plans
  • Identifying Common Plan Elements
  • Querying Cost Comparison

Module 8: Improving Performance through Nonclustered Indexes

Lessons

  • Designing Effective Nonclustered Indexes
  • Implementing Nonclustered Indexes
  • Using the Database Engine Tuning Advisor

Lab: Improving Performance through Nonclustered Indexes

  • Nonclustered index usage review
  • Improving nonclustered index designs
  • Working with SQL Server Profiler and Database Engine Tuning Advisor
  • Designing nonclustered index

Module 9: Designing and Implementing Views

Lessons

  • Introduction to Views
  • Creating and Managing Views
  • Performance Considerations for Views

Lab: Designing and Implementing Views

  • Designing, Implementing and Testing the WebStock Views
  • Designing and Implementing the Contacts View
  • Modifying the AvailableModels View

Module 10: Designing and Implementing Stored Procedures

Lessons

  • Introduction to Stored Procedures
  • Working With Stored Procedures
  • Implementing Parameterized Stored Procedures
  • Controlling Execution Context

Lab: Designing and Implementing Stored Procedures

  • Creating stored procedures
  • Creating a parameterized stored procedure
  • Altering the execution context of stored procedures

Module 11: Merging Data and Passing Tables

Lessons

  • Using the MERGE Statement
  • Implementing Table Types
  • Using TABLE Types As Parameters

Lab: Passing Tables and Merging Data

  • Creating a Table Type
  • Using a Table Type Parameter
  • Using a Table Type with MERGE

Module 12: Designing and Implementing User-Defined Functions

Lessons

  • 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

  • Formatting Phone Numbers
  • Modifying an Existing Function
  • Resolve a Function-related Performance Issue

Module 13: Creating Highly Concurrent SQL Server 2012 Applications

Lessons

  • Introduction to Transactions
  • Introduction to Locks
  • Management of Locking
  • Transaction Isolation Levels

Lab: Creating Highly Concurrent SQL Server Applications

  • Detecting Deadlocks
  • Investigating Transaction Isolation Levels

Module 14: Handling Errors in T-SQL Code

Lessons

  • Understanding T-SQL Error Handling
  • Implementing T-SQL Error Handling
  • Implementing Structured Exception Handling

Lab: Handling Errors in T-SQL Code

  • Replacing @@ERROR based error handling with structured exception handling
  • Adding deadlock retry logic to the stored procedure

Module 15: Responding to Data Manipulation via Triggers

Lessons

  • Designing DML Triggers
  • Implementing DML Triggers
  • Advanced Trigger Concepts

Lab: Responding to Data Manipulation via Triggers

  • Creating and Testing the Audit Trigger
  • Improving the Audit Trigger

Module 16: Implementing Managed Code in SQL Server 2012

Lessons

  • Introduction to SQL CLR Integration
  • Importing and Configuring Assemblies
  • Implementing SQL CLR Integration

Lab: Designing and Implementing Views

  • Assessing Proposed CLR Code
  • Implementing a CLR Assembly
  • Implementing a CLR User-defined Aggregate and CLR User-defined Data Type

Module 17: Storing XML Data in SQL Server 2012

Lessons

  • 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

  • Appropriate Usage of XML Data Storage in SQL Server
  • Investigating the Storage of XML Data in Variables
  • Investigating the use of XML Schema Collections
  • Investigating the Creation of Database Columns Based on XML

Module 18: Querying XML Data in SQL Server

Lessons

  • Using the T-SQL FOR XML Statement
  • Getting Started with XQuery
  • Shredding XML

Lab: Querying XML Data in SQL Server

  • Learning to query SQL Server data as XML
  • Writing a stored procedure returning XML
  • Writing a stored procedure that updates using XML

Module 19: Working with SQL Server 2012 Spatial Data

Lessons

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

Lab: Working with SQL Server Spatial Data

  • Familiarity With Geometry Data Type
  • Adding Spatial Data to an Existing Table
  • Business Application of Spatial Data

Module 20: Working with Full-Text Indexes and Queries

Lessons

  • 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

  • Implementing a full-text index
  • Implementing a stop list
  • Creating a stored procedure to implement a full-txt search
 

Accessing our website tells us you are happy to receive all our cookies. However you can change your cookie settings at any time. Find out more.   Got it!