Target Audience
The course is intended for database developers who need to participate in the implementation anddevelopment activities of SQL Server databases. Students should be familiar with relational
database concepts, basic programming and the syntax of Transact-SQL.
Objectives
- Be familiar with SQL Server architecture and objects
- Be able to design and implement efficient and reliable databases
- Learn to work with SQL Server client tools
Introduction to Microsoft SQL Server
1) Databases – Relational and Others
2) The Database as Part of the System Architecture
3) The Role of the Database Administrator (DBA)
4) SQL Server Versions and Editions
5) SQL Server Services and Components
6) SQL Server Client Tools
All about Databases
1) Overview of System Databases
2) Filegroups and Files
3) Introducing the Transaction Log
4) Database Configuration Options
5) Creating a Database
6) Database Snapshots
7) Schemas and Database Objects
All about Tables
1) System Tables and System Views
2) System Data Types
3) The Use of NULL
4) Creating a Table
5) Special Data Types
6) User-Defined Data Types
7) Table @Variables vs. #Temporary Tables
8) Table Types
9) Sparse Columns
10) Using FILESTREAM and FileTables
11) Working with Spatial Data
Indexes and Statistics
1) Introduction to Indexes
2) Physical Structures
3) Special Types of Indexes
4) Creating an Index
5) Guidelines for Planning Indexes
6) The Database Engine Tuning Advisor
7) Introducing Statistics
8) Understanding Execution Plans
9) Guidelines for Writing Efficient Queries
10) Indexes and Statistics Maintenance
Table Constraints
1) Data Integrity Overview
2) Primary Key and Unique Constraint
3) Foreign Key
4) Check and Default Constraints
5) Creating Constraints
6) Disabling Constraints
Partitioning
1) Introduction to Partitioning
2) Partitioning Setup
3) Operations on a Partitioned Table
4) Partitioned Indexes
Transactions and Locks
1) Transactions Overview
2) Lock Types
3) Concurrency Issues
4) Transaction Isolation Levels
5) Locking Hints
6) Deadlocks
7) Nesting Transactions
Views
1) Introduction to Views
2) Creating a View
3) Modifying Data through a View
4) Indexed Views
5) Partitioned Views
User-Defined Functions
1) Introduction to User-Defined Functions
2) Scalar Functions
3) Table-Valued Functions
4) Joining with APPLY
Stored Procedures
1) Introduction to Stored Procedures
2) Stored Procedure Interfaces
3) Creating and Executing Stored Procedures
4) Compilation Sequence
Triggers
1) Introduction to Triggers
2) The INSERTED and DELETED Tables
3) Creating a Trigger
4) Nested and Recursive Triggers
5) Triggers Pros and Cons
6) Scenarios for Using Triggers
XML
1) Introduction to XML
2) XPath and XQuery
3) The XML System Data Type
4) XML Schema Collections
5) XML Indexes
6) Converting Relational Data to XML
7) Converting XML to Relational Data
