SQL Training Overview
In this course, you'll see an overview tour of SQL Server 2008 including its editions, components, and tools. Then you will learn about the steps to install, and/or upgrade to SQL Server 2008 and configuring the server. You will see how the principals of database design are used in creating of databases and then how to apply server tables to them. The course continues with SQL Server Management Studio and how to build effective views, stored procedures, triggers, and user-defined functions using Transact-SQL. Learn how to make your databases more scalable through partitioning and how to use .NET languages like Visual C# and Visual Basic to build database objects. SQL Server 2008 includes a rich set of tools that go beyond the basics of querying and manipulating data. You will learn how to take advantage of the user-friendly management console that integrates both authoring and administrative tasks. You will learn how to take advantage of SQL Server's tools for analyzing and tuning your databases. You'll also learn about integration servers, implementing security, and Microsoft's new Business Intelligence (BI) suite.
SQL Training Audience:
Database developers interested in gaining a more thorough knowledge of SQL Server 2008’s features.
SQL Training Prerequisites
A solid understanding of relational databases and the concepts of SQL Server is recommended. No particular programming experience is required, but the course is taught from a developer's perspective.
SQL Training Course duration
5 days
SQL Training Course outline
A Tour of SQL Server 2005
SQL Server 2005 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)
Installing SQL Server 2005
Preparing for Installation
Hardware and Software Requirements
Setting up the Service Accounts
Multiple Instances
Upgrading an Earlier Version
Considerations
Upgrade Advisor
Installation Steps
Beginning the Installation
Customizing the Installation
Performing the Installation
Configuring the Server
SQL Server Configuration Manager
Server Configuration
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 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
Join Notation
Inner Joins
Outer Joins
Combining Inner and Outer Joins
Self Joins
Modifying Data
Modifying Data with Transact-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 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
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 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
Understanding and Implementing Security
Security Overview
The Threats
SQL Server 2008 Security Design Philosophy
The Two Stages of Security
Authentication
Windows Integrated Authentication
SQL Server Authentication
Authorization
Principals
Securable Objects
Permissions
Permission Types
Permission Statements
Granting Permissions
Roles, Permissions, and Schemas
Execution Context
Metadata Security
Data Encryption
Encryption Keys
Encrypting Data
Security Epilog
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
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 in Management Studio
Creating and Executing Stored Procedures
Working with Parameters
Testing and Debugging Stored Procedures
Creating Triggers
Uses for Triggers
How Triggers Work
Creating an 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 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
Transaction 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 a Stored Procedure
Handling Uncommittable Transactions with XACT_STATE
Using .NET Code in SQL Server
Ways to Use SQLCLR
Enabling SQLCLR
Writing SQLCLR Code
Creating SQLCLR Code Manually
Creating SQLCLR Code Using Visual Studio 2008
SQL Data Types
Accessing Local Data
Returning Results
SQLCLR Code Modules
Triggers
User-Defined Functions
SQLCLR Security
Code Access Security
SQLCLR Permission Sets
T-SQL vs. .NET Code
Advanced SQLCLR Code Techniques
Advanced SQLCLR Code Modules
Aggregates
Large Aggregates
Multiparameter Aggregates
User-Defined Types
Ordered Table-Valued Functions
Managing Code Modules
System Catalogs
Troubleshooting SQLCLR Code
Advanced Query Techniques
Full-Text Search
Configuring Full-Text Search
Writing Full-Text Queries
The CONTAINS Predicate
The FREETEXT Predicate
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 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 Spars 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
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 of 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
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
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
Design and Deployment with Visual Studio
Team System for Database Professionals
Database Development
Visual Studio Integration
Security
Creating Databases and Managing Projects
Creating and Configuring a Database Project
Generating Test Data
Database and Project Management
Creating Database Unit Tests
Changing the Schema
Comparing Database Schemas
Comparing Database Data
Creating Partial Database Projects
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
OPEN XML
Best Practices
Relational vs. XML Data Model
Storing XML in DQL Server 2008
Reasons to Index XML Columns
Querying
Business Intelligence Services
Introduction to Business Intelligence Services
Using Integration Services
What is SSIS?
Importing and Exporting Data
Integration Services Tools
SQL Server Business Intelligence Development Studio (BIDS)
BIDS Design Surfaces
Execute Package Utility
What Else is There?
Using Analysis Services
OLAP Terminology
Cubes, Dimensions, and Measures
Cube Storage
The Unified Dimensional Model
Creating a Unified Dimensional Model
Creating a Data Source View
Creating a Dimension
Creating a Cube
Browsing and Enhancing the Cube
What Else is There?
Using Reporting Services
Configuring Reporting Services
Building a Simple Report
What Else is There?
|
