Course Length: 4 days

Course Description: In this course, you'll start by learning about the basic features that are available in SQL Server, including how to design and create a database and how to build queries using Transact-SQL, the language of SQL Server. Then, you'll learn how to build effective views, stored procedures, triggers, and user-defined functions using Transact-SQL. You'll learn about the new enhancements to the Transact-SQL programming language including improved support for error handling and hierarchical queries, and how programmers can now use .NET languages like C# and Visual Basic to build database objects. You'll also learn about enhancements made to SQL Server 2005 for handling complex data structures, using partitioning, and working with XML.

Audience: Database developers and SQL Server administrators who need to understand the enhancements made to the Transact-SQL programming language

Prerequisites: A good working knowledge of SQL Server 2000 or 2005, basic relational database concepts, XML, Transact-SQL, and a fundamental understanding of networking and security concepts is recommended.

 

See Dates for this class

 

A Tour of SQL Server 2005

·  SQL Server 2005 Editions, Components, and Tools
·  Editions
·  Server Components
·  Management Tools
·  Documentation and Samples
·  Using SQL Server Management Studio (SSMS)
·  Connecting Management Studio
·  The Object Explorer
·  Exploring the Sample Databases
·  Database Objects
·  Working with Tables and Queries
·  Creating Tables
·  Creating Views
·  Generating Scripts
·  New Transact-SQL Features
·  Common Table Expressions (CTEs)
·  Ranking Functions
·  TOP(n) and APPLY
·  PIVOT
·  FOR XML PATH
·  Try/Catch Error Handling
·  Using EXECUTE AS
·  Using Snapshot Isolation
·  Running CLR Code in SQL Server 2005
·  Business Intelligence Services
·  SQL Server Integration Services (SSIS)
·  SQL Server Business Intelligence Development Studio (BIDS)
·  Reporting Services
·  Analysis Services (SSAS)

Designing and Creating a Database

·  Relational Database Design Principles
·  The Origins of Relational Design
·  Data Normalization
·  Understanding Referential Integrity
·  Beyond Normalization
·  Implementing the Design
·  Database Storage
·  Creating Databases
·  Modifying Database Options
·  Creating Tables
·  Creating Constraints
·  Triggers
·  Creating Indexes
·  Using Database Diagrams

Data Selection Queries

·  Understanding Transact-SQL
·  Schemas and Naming in SQL Server 2005
·  The SELECT Statement
·  Selecting All Columns
·  Concatenating Columns
·  Naming Columns
·  Using DISTINCT to Limit Values
·  The WHERE Clause
·  Transact-SQL Comparison Operators
·  The LIKE Operator
·  The BETWEEN Operator
·  Using IS NULL to Test for Nulls
·  Multiple Conditions with AND, OR, and NOT
·  Operator Precedence
·  Using the IN Operator
·  Using ORDER BY to Sort Data
·  Sorting on a Single Column
·  Sorting by Multiple Columns
·  Sorting with Expressions
·  The GROUP BY Clause
·  Aggregate Functions
·  Counting Rows
·  Counting Columns
·  Counting with a WHERE Clause
·  Using GROUP BY
·  Using ORDER BY with GROUP BY
·  Using HAVING with GROUP BY
·  TOP Values Queries
·  Joining Tables
·  Cross Joins (Cartesian Products)
·  The Use of Keys in Joining
·  Join Notation
·  Inner Joins
·  Outer Joins
·  Self Joins

Modifying Data

·  Modifying Data
·  Inserting Data
·  Inserting a Single Value
·  Inserting Multiple Values
·  Inserting Multiple Rows
·  Creating a New Table using SELECT INTO
·  Temporary Tables
·  Using Bulk Copy to Insert Data
·  Updating Data
·  Updating a Single Row
·  Updating Multiple Rows and Columns
·  Updating from Another Table
·  Updating with TOP
·  Updating Large Value Types with UPDATE.WRITE
·  Deleting Data
·  Deleting a Single Row
·  Deleting Multiple Rows
·  Understanding Transaction Isolation
·  Isolation Levels Explained
·  Blocking and Deadlocks
·  Using Snapshot Isolation

Working with SQL Server Management Studio

·  Getting Started with SSMS
·  Connecting to Management Studio
·  SSMS Toolbars
·  Overview of SSMS Menu Options
·  Configuring SSMS Windows
·  Exploring the Object Explorer
·  Right-Click Menu Options
·  Server and Database Objects
·  Displaying and Filtering Objects
·  Finding Objects
·  Editing Database Objects in the SSMS Designers
·  Working with the Query Editor
·  Displaying Multiple Code Windows
·  Formatting and Editing Code
·  Scripting with SSMS
·  Executing Queries
·  Creating Projects and Solutions
·  Working Offline
·  Using SQL Server Books Online
·  Getting Help in SSMS

Transact-SQL Programming

·  Overview of Transact-SQL
·  Transact-SQL Extensions
·  Batches and Scripts
·  Variables
·  Delimiters and Operators
·  Transact-SQL and Data Types
·  Using Built-In Functions
·  Working with Nulls
·  Handling Numbers
·  Manipulating Strings
·  Working with Date and Time Values
·  Using the @@ Functions
·  Controlling Flow
·  IF…ELSE
·  BEGIN…END
·  GOTO, RETURN, and Labels
·  CASE
·  WHILE
·  WAITFOR
·  Ranking Results

Transactions and Error Handling

·  Transaction Concepts
·  Passing the ACID Test
·  Transaction Types
·  Avoiding Blocked Transactions
·  Working around Deadlocks
·  Applications and Transactions
·  Designing Transactional Support
·  Understanding Compile and Runtime Errors
·  Creating Explicit Transactions
·  Explicit Transaction Syntax
·  Transact-SQL Error Handling in Transactions
·  Using RAISERROR
·  Using TRY/CATCH Error Handling
·  TRY/CATCH Overview
·  Using TRY/CATCH in a Stored Procedure
·  Handling Uncommittable Transactions with XACT_STATE

Creating Views

·  What is a View?
·  Advantages of Views
·  Views and Security
·  Creating Views
·  View Rules
·  View Syntax and Options
·  Tools for Creating Views
·  Nesting Views, Derived Tables, and CTEs
·  Encrypting View Definitions
·  Updating Data Using a View
·  Updating Rules
·  Updating Behavior
·  Using Computed Columns
·  Creating a Computed Column
·  Indexed Views
·  How Indexed Views Work
·  Partitioned Views

 

 

 

       

 

 

Creating Stored Procedures and Triggers

·  Creating Stored Procedures
·  Stored Procedure Features
·  Stored Procedures Performance Benefits
·  Reasons to Use Stored Procedures
·  Stored Procedure Syntax
·  Creating Stored Procedures Using SSMS
·  Creating and Executing Stored Procedures
·  Working with Parameters
·  Testing and Debugging Stored Procedures
·  Creating Triggers
·  Uses for Triggers
·  How Triggers Work
·  Creating and AFTER Trigger
·  Creating an INSTEAD OF Trigger on a View
·  Creating a DDL Trigger to Restrict Table Creation

Creating User-Defined Functions

·  User-Defined Function Overview
·  Scalar Functions          
·  Scalar Function Syntax
·  Inline Table-Valued Functions
·  Inline Syntax
·  Executing Inline Table-Valued Functions
·  Inline Functions with Parameters
·  Updating Data with Inline Functions
·  Multi-Statement Table-Valued Functions
·  Multi-Statement Function Syntax
·  Executing Multi-Statement Functions
·  Joining to Functions
·  Using Functions, Views, and Stored Procedures
·  Using Scalar Functions
·  Using Table-Valued Functions
·  Choosing Between Functions, Views, and Stored Procedures

Using .NET Code in SQL Server 2005

·  Introduction
·  Ways to Use SQLCLR
·  Enabling SQLCLR
·  Writing SQLCLR Code Manually
·  Creating SQLCLR Code Using Visual Studio 2005
·  SQL Data Types
·  Accessing Local Data
·  Returning Results
·  SQLCLR Code Modules
·  Stored Procedures and Triggers
·  User-Defined Functions
·  Aggregates
·  Managing Code Modules
·  System Catalogs
·  Troubleshooting
·  SQLCLR Security
·  Code Access Security (CAS)
·  SQLCLR Permission Sets
·  T-SQL vs. .NET Code

Advanced Query Techniques

·  Full-Text Search
·  SQL Server 2005 Enhancements
·  Configuring Full-Text Search
·  Writing Full-Text Queries
·  The CONTAINS Predicate
·  The FREETEXT Predicate
·  Using CONTAINSTABLE and FREETEXTTABLE
·  Using Full-Text DDL
·  Generating XML with FOR XML
·  Using AUTO Mode
·  Using RAW Mode
·  Using EXPLICIT Mode
·  Using PATH Mode
·  Using APPLY
·  Comparing APPLY to Using Joins
·  Using APPLY with Table-Valued Functions
·  Combining APPLY with FOR XML PATH
·  Creating Recursive Queries
·  Using a Common Table Expression (CTE)
·  Creating Pivot Queries
·  Rotating Column Data into Column Heading
·  Executing Dynamic SQL
·  Overcoming PIVOT Limitations
·  Using Parameters with sp_executesql
·  Using QUOTENAME()
·  Using sp_executesql with Output Parameters
·  Signing Stored Procedures with Certificates

Implementing SQL Server Partitions

·  Overview of Table-Based Partitioning
·  Why Partition?
·  What’s New in SQL Server 2005?
·  Range Partitions
·  Partition Key and Number of Partitions
·  Using Multiple Filegroups
·  Index Partitioning
·  Creating Partitioned Tables
·  Creating a Partition Function
·  Creating a Partition Scheme
·  Partitioning Tables and Indexes
·  Querying Partitions
·  Using the $PARTITION Function
·  Catalog Views
·  Managing Partitions
·  Modifying Partitioned Tables or Indexes
·  Modifying a Partition Function
·  Modifying a Partition Scheme
·  Backing Up Partitions
·  Performance Considerations

Complex Querying

·  Working with NULL Values
·  SqlTypes and CLR Types
·  About the ANSI_NULLS Option
·  NULLs and SqlBoolean
·  Assigning Null Values
·  Ranking Grouped Data
·  The ROW_NUMBER Function
·  Using the RANK Function
·  Introducing the DENSE_RANK Function
·  The NTILE Function
·  Writing Correlated Subqueries
·  Subquery Basics
·  What is a Correlated Subquery?
·  Using the WHERE Clause
·  Correlated Subqueries and Updates
·  Comparing to Joins and Temp Tables
·  Using Common Table Expressions
·  When to Use CTEs
·  CTE Syntax
·  Recursive CTEs

Advanced Techniques

·  Complex Data and Structures
·  Issues with Data Types
·  Working with Multinational Data
·  Working with Hierarchical Data
·  Writing More Efficient Queries
·  The EXISTS Clause
·  Joins vs. Subqueries
·  One-Pass Queries
·  Using Temporary Tables
·  Table Variables
·  User-Defined Table-Valued Functions
·  Common Table Expressions
·  Worktables
·  Working wit Complex Queries
·  Using Implicit Transactions
·  Keeping it Simple
·  Maintaining Query Files
·  Using Source Control and Versioning
·  Using Visual SourceSafe

Working with XML

·  The xml Data Type
·  Declaring XML Objects
·  Loading Data into an XML Instance
·  Indexing XML Columns
·  XML Schema Collections
·  Typed and Untyped XML
·  Schema Basics
·  Registering Schemas
·  Viewing Stored Schemas
·  Querying XML
·  XQuery
·  FOR XML
·  OPENXML
·  Best Practices
·  Relational vs. XML Data Model
·  Storing XML in SQL Server 2005
·  Reasons to Index XML Columns
·  Querying