TSQL 2008 - MSS350

A Tour of SQL Server 2008

• SQL Server 2008 Editions, Components, and Tools
• Editions
• Server Components
• Management Tools
• Documentation and Samples
• SQL Server 2008 Feature Pack
• Using SQL Server Management Studio (SSMS)
• Connecting Management Studio
• The Object Explorer
• Database Objects
• Monitoring Server Activity
• Connecting to Other Servers
• Customizing the User Interface
• Working with Tables and Queries
• Creating Tables
• Creating a View
• Generating Scripts
• Viewing Table Rows
• 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

Working with SQL Server Management Studio

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

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 TRY/CATCH Error Handling
• TRY/CATCH Overview
• Using TRY/CATCH in Stored Procedure
• Handling Uncommittable Transactions in XACT_STATE

Data Selection Queries

• Understanding Transact-SQL
• Schemas and Naming in SQL Server 2008
• 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 Column Data
• 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
• T-SQL 2008
• Join Notation
• Inner Joins
• Outer Joins
• Combining Inner and Outer Joins
• Self Joins

Advanced Data Types

• The HierarchyID Data Type
• Indexing a Hierarchy
• Manipulating Hierarchies
• Working with the Instructor Hierarchy
• Exploring and Manipulating a Hierarchy
• Sparse Columns and Column Sets
• Restrictions on Sparse Columns
• Column Sets
• Recommendations
• Working with Sparse Columns
• Working with Column Sets
• Filtered Indexes
• FILESTREAM Storage
• FILESTREAM Data Type
• FILESTREAM Data Access Methods
• Gotchas and Limitations
• Using FILESTREAM
• Spatial Data
• Geometry vs. Geography
• Spatial Data Standards
• Types of Spatial Data
• Using Spatial Data
• Working with Spatial Data
• Interactions Between Objects

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
• The RANK Function
• The DENSE_RANK Function
• The NTILE Function
• Writing Correlated Subqueries
• Subquery Basics
• What is a Correlated Subquery?
• Using the WHERE Clause
• Including the HAVING Clause
• Correlated Subqueries and Updates
• Comparing to Joins and Temp Tables
• Using Common Table Expressions
• When to Use CTEs
• CTE Syntax
• Recursive CTEs

Modifying Data

• Modifying Data with Rransact-SQL
• Inserting Data
• Inserting a Single Value
• Inserting Multiple Values
• Inserting Multiple Rows
• Creating a New Table with 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
• Blocking and Deadlocks
• Using Snapshot Isolation

Working with XML

• The xml Data Type
• Declaring XML Objects
• Loading Data into an XML Instance
• Indexing XML Columns
• XML Schema Collections
• Types and Untyped XML
• Schema Basics
• Greater Support in SQL Server 2008
• Registering Schemas
• Viewing Stored Schemas
• Querying XML
• XQuery
• For XML
• OPENXML
• Best Practices
• T-SQL 2008
• Relational vs. XML Data Model
• Storing XML in SQL Server 2008
• Reasons to Index XML Columns
• Querying

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 Common Table Expressions
• 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

Implementing SQL Server Partitions

• Overview of Table-Based Partitioning
• Why Partition?
• SQL Server Partitioning
• 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
• Creating User-Defined Functions
• User-Defined Function Overview
• Scalar Functions
• Scalar Function Syntax
• Inline Table-Valued Functions
• Inline Syntax
• Executing Inline 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

Creating Stored Procedures and Triggers

• Creating Stored Procedures
• Stored Procedure Features
• Stored Procedure Performance Benefits
• Reasons to Use Stored Procedures
• Stored Procedure Syntax
• Creating Stored Procedures in Management Studio
• Creating and Executing Stored Procedures
• Working with Parameters
• Testing and Debugging Stored Procedures
• Creating Triggers
• Uses for Triggers
• The Trouble with Triggers
• How Triggers Work
• Creating an AFTER Trigger
• Creating an INSTEAD OF Trigger on a View
• Creating a DDL Trigger to Restrict Table Creation

Advanced Query Techniques

• Full-Text Search
• Configuring Full-Text Search
• Writing Full-Text Queries
• The CONTAINS Predicate
• The FREETEXT Predicate
• T-SQL 2008
• Using CONTAINSTABLE and FREETEXTTABLE
• Using Full-Text DDL
• Advanced T-SQL Techniques
• Using APPLY
• Inserting and Updating Data with MERGE
• Creating Recursive Queries
• Grouping Data with Grouping Sets
• Creating Pivot Queries
• Executing Dynamic SQL
• Overcoming PIVOT Limitations
• Using Parameters with sp_executesql
• Using QUOTENAME()
• Using sp_executesql with Output Parameters
• Signing Stored Procedures with Certificates

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
• User-Defined Table-Typed Parameters
• Working with Complex Queries
• Using Implicit Transactions
• Keeping it Simple
• Maintaining Query Files
• Using Source Control and Versioning
• Using Visual SourceSafe