Course Content
Introduction to SQL Server and its Toolset
- Introduction to SQL Server Platform
- Working with SQL Server Tools
- Configuring SQL Server Services
Lab
- Verifying SQL Server Component Installation
- Altering Service Accounts for New Instance
- Enabling Named Pipes Protocol for Both Instances
- Creating Aliases for AdventureWorks and Proseware
- Ensuring SQL Browser is Disabled and Configure a Fixed TCP/IP Port
Working with Data Types
- Using Data Types
- Working with Character Data
- Converting Data Types
- Working with Specialized Data Types
Lab
- Choosing Appropriate Data Types
- Writing Queries With Data Type Conversions
- Designing and Creating Alias Data Types
Designing and Implementing Tables
- Designing Tables
- Working with Schemas
- Creating and Altering Tables
Lab
- Improving the Design of Tables
- Creating a Schema
- Creating the Tables
Designing and Implementing Views
- Introduction to Views
- Creating and Managing Views
- Performance Considerations for Views
Lab
- Designing, Implementing and Testing the WebStock Views
- Designing and Implementing the Contacts View
- Modifying the AvailableModels View
Planning for SQL Server Indexing
- Core Indexing Concepts
- Data Types and Indexes
- Single Column and Composite Indexes
Lab
- Exploring Existing Index Statistics
- Designing Column Orders for Indexes
Implementing Table Structures in SQL Server
- SQL Server Table Structures
- Working with Clustered Indexes
- Designing Effective Clustered Indexes
Lab
- Creating Tables as Heaps
- Creating Tables with Clustered Indexes
- Comparing the Performance of Clustered Indexes vs. Heaps
Reading SQL Server Execution Plans
- Execution Plan Core Concepts
- Common Execution Plan Elements
- Working with Execution Plans
Lab
- Actual vs. Estimated Plans
- Identify Common Plan Elements
- Query Cost Comparison
Improving Performance through Nonclustered Indexes
- Designing Effective Nonclustered Indexes
- Implementing Nonclustered Indexes
- Using the Database Engine Tuning Advisor
Lab
- Reviewing Nonclustered Index Usage
- Improving Nonclustered Index Designs
- Using SQL Server Profiler and Database Engine Tuning Advisor
- Nonclustered Index Design
Designing and Implementing Stored Procedures
- Introduction to Stored Procedures
- Working With Stored Procedures
- Implementing Parameterized Stored Procedures
- Controlling Execution Context
Lab
- Creating Stored Procedures
- Creating a Parameterized Stored Procedure
- Altering the Execution Context of Stored Procedures
Merging Data and Passing Tables
- Using the MERGE Statement
- Implementing Table Types
- Using Table Types as Parameters
Lab
- Creating a Table Type
- Using a Table Type Parameter
- Using a Table Type with MERGE
Creating Highly Concurrent SQL Server Applications
- Introduction to Transactions
- Introduction to Locks
- Management of Locking
- Transaction Isolation Levels
Lab
- Detecting Deadlocks
- Investigating Transaction Isolation Levels
Handling Errors in T-SQL Code
- Designing T-SQL Error Handling
- Implementing T-SQL Error Handling
- Implementing Structured Exception Handling
Lab
- Replacing @@ERROR Based Error Handling With Structured Exception Handling
- Adding Deadlock Retry Logic to the Stored Procedure
Designing and Implementing User-Defined Functions
- Designing and Implementing Scalar Functions
- Designing and Implementing Table-valued Functions
- Implementation Considerations for Functions
- Alternatives To Functions
Lab
- Formatting Phone Numbers
- Modifying an Existing Function
- Resolving a Function-related Performance Issue
Ensuring Data Integrity through Constraints
- Enforcing Data Integrity
- Implementing Domain Integrity
- Implementing Entity and Referential Integrity
Lab
- Designing Constraint
- Testing the Constraints
Responding to Data Manipulation via Triggers
- Designing DML Triggers
- Implementing DML Triggers
- Advanced Trigger Concepts
Lab
- Creating the Audit Trigger
- Improving the Audit Trigger
Implementing Managed Code in SQL Server
- Introduction to SQL CLR Integration
- Importing and Configuring Assemblies
- Implementing SQL CLR Integration
Lab
- Assessing Proposed CLR Code
- Implementing a CLR Assembly
- Implementing a CLR User-defined Aggregate and User-defined Type
Storing XML Data in SQL Server
- Introduction to XML and XML Schemas
- Storing XML Data and Schemas in SQL Server
- Implementing the XML Data Type
Lab
- 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
Querying XML Data in SQL Server
- Using the T-SQL FOR XML Statement
- Getting Started with XQuery
- Shredding XML
Lab
- Learning to Query SQL Server Data as XML
- Writing a Stored Procedure Returning XML
- Writing a Stored Procedure that Updates Using XML
Working with SQL Server Spatial Data
- Introduction to Spatial Data
- Working with SQL Server Spatial Data Types
- Using Spatial Data in Applications
Lab
- Familiarity With Geometry Data Type
- Adding Spatial Data to an Existing Table
- Business Application of Spatial Data
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
- Implementing a Full-Text Index
- Implementing a Stoplist
- Creating a Stored Procedure to Implement a Full-Text Search
