10776 Developing Microsoft SQL Server 2012 Databases

Learning 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.
• Determine appropriate data types to be used when designing tables, convert data between data types, and create alias data types.
• 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.

Course Outline

1 – Introduction to SQL Server 2012 and its Toolset

• Introduction to the SQL Server Platform
• Working with SQL Server Tools
• Configuring SQL Server Services
• Lab : Introduction to SQL Server and its Toolset

2 – Working with Data Types

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

3 – Designing and Implementing Tables

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

4 – Ensuring Data Integrity through Constraints

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

5 – Planning for SQL Server 2012 Indexing

• Core Indexing Concepts
• Data Types and Indexes
• Single Column and Composite Indexes
• Lab : Planning for SQL Server Indexing

6 – Implementing Table Structures in SQL Server 2012

• SQL Server Table Structures
• Working with Clustered Indexes
• Designing Effective Clustered Indexes
• Lab : Implementing Table Structures in SQL Server

7 – Reading SQL Server 2012 Execution Plans

• Execution Plan Core Concepts
• Common Execution Plan Elements
• Working with Execution Plans
• Lab : Reading SQL Server Execution Plans

8 – Improving Performance through Nonclustered Indexes

• Designing Effective Nonclustered Indexes
• Implementing Nonclustered Indexes
• Using the Database Engine Tuning Advisor
• Lab : Improving Performance through Nonclustered Indexes

9 – Designing and Implementing Views

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

10 – 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

11 – Merging Data and Passing Tables

• Using the MERGE Statement
• Implementing Table Types
• Using TABLE Types As Parameters
• Lab : Passing Tables and Merging Data

12 – 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

13 – Creating Highly Concurrent SQL Server 2012 Applications

• Introduction to Transactions
• Introduction to Locks
• Management of Locking
• Transaction Isolation Levels
• Lab : Creating Highly Concurrent SQL Server Applications

14 – Handling Errors in T-SQL Code

• Understanding T-SQL Error Handling
• Implementing T-SQL Error Handling
• Implementing Structured Exception Handling
• Lab : Handling Errors in T-SQL Code

15 – Responding to Data Manipulation via Triggers

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

16 – Implementing Managed Code in SQL Server 2012

• Introduction to SQL CLR Integration
• Importing and Configuring Assemblies
• Implementing SQL CLR Integration
• Lab : Designing and Implementing Views

17 – Storing XML Data in SQL Server 2012

• 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

18 – Querying XML Data in SQL Server

• Using the T-SQL FOR XML Statement
• Getting Started with XQuery
• Shredding XML
• Lab : Querying XML Data in SQL Server

19 – Working with SQL Server 2012 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

20 – Working with Full-Text Indexes and Queries

• 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