SQL Server 2005 includes a rich set of tools that go beyond the basics of querying and manipulating data. You'll learn how to take advantage of the new, user-friendly management console that integrates both authoring and administrative tasks. You'll learn how to take advantage of SQL Server's tools for analyzing and tuning your databases. You'll also learn about integration services, implementing security, and Microsoft's new Business Intelligence (BI) suite.
SQL Training Audience:
sql Programmers who wish to use WCF to create and consume web services.
SQL Training Prerequisites
A solid understanding of relational databases is recommended
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
- 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 a View
- 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)
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
- Installing Prerequisites
- System Configuration Checks
- Customizing the Installation
- Performing the Installation
- Configuring the Server
- SQL Server Configuration Manager
- SQL Server Surface Area Configuration Tool
- Server Configuration
Designing and Creating a Database
- Relational Database Design Principles
- The Origins of Relational Design
- Data Normalization
- Understanding the 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 Expression
- The GROUP BY Clause
- Aggregate Functions
- Counting Rows
- Counting Columns
- Counting with a WHERE Clause
- Using GROUP BY
- Using GROUP 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
- Configuring SSMS Options
- 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?
- Advantage of Views
- Views and Security
- Creating Views
- View Rules
- View Syntax and Options
- Tools for Creating Views
- View Examples
- 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 Stores 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 an AFTER Trigger
- Creating an INSTEAD OF Trigger on a View
- Creating a DDL Trigger to Restrict Table Creating
Creating User-Defined Functions
- User-Defined Function Overview
- Scalar Functions
- Scalar Function Syntax
- Scalar Function Examples
- Inline Table-Valued Functions
- Inline Syntax
- Inline Function Example
- Executing Inline Table-Valued Functions
- Inline Functions with Parameters
- Updating Data with Inline Functions
- Multi-Statement Table-Valued Functions
- Multi-Statement Function Syntax
- Multi-Statement Function Example
- 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 sql code in SQL Server 2005
- Ways to Use SQLCLR
- Enabling SQLCLR
- Writing SQL CLR Code
- Creating 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. sql 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 Headings
- Executing Dynamic SQL
- Overcoming PIVOT Limitations
- Using Parameters with sp_executesql
- Using QUOTENAME()
- Using sp_executesql with Output Parameters
- Signing Stored Procedures with Certificates
Understanding and Implementing Security
- Security Overview
- The Threats
- SQL Server 2005 Design Philosophy
- The Two Stage of Security
- Authentication
- Configuring SQL Server Security Settings
- 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
Analyzing and Tuning Performance
- Evaluating Performance
- Define Monitoring Goals
- SQL Server Monitoring Tools
- Monitoring with SQL Server Profiler
- Profiler Terminology
- Getting Started with the Profiler
- Tuning Queries
- How Join Types Affect Performance
- Join Hints
- Performance Issues with Joins and Subqueries
- Avoid Cursors
- Indexes and Partitions
- What Is an Index?
- What Is a Partition?
- Using the Database Engine Tuning Advisor
- Capturing a Workload
- Analyzing the Workload
- Providing an Indexing and Partition Recommendation
- Understanding SQL Server Performance Problems
- Slow Queries
- CPU Bottlenecks
- Memory Bottlenecks
- Troubleshooting with Dynamic Management Views
Automating Administrative Tasks
- SQL Server Agent
- Starting Agent
- Setting Agent Properties
- Operators
- Jobs
- Alerts
Maintenance Plans
- Maintenance Plan Wizard
- Maintenance Plan Designer
SQL Management Objects (SMO)
- SMO Object Model
- Accessing and Managing a Server
Programming Replication
- Overview of SQL Server Replication
- Publishing Model
- Replication Types
- Agents and Metadata
- Replication Programming Interfaces
- Replication Stored Procedures
- Replication Management Objects
- Agent Executables and ActiveX Controls
- Configuring Replication
- Publishing Data
- Generating Replication Scripts
- Running the Sample Application
- Creating Subscriptions
- Synchronizing Data
- Starting a Synchronization
- Showing Synchronizations Status
- Subscription Status
- Reinitializing a Subscription
Using Integration Services
- What is SSIS?
- When to Use SSIS
- SSIS Architecture
- Importing and Exporting Data
- Integration Services Tools
- SQL Server Business Intelligence Development Studio (BIDS)
- BIDS Design Surfaces
- Execute Package Utility
- Building a Package
- Creating the Package
- Creating the Data Source Connections
- Creating the Control and Data Flows
- Adding Control Flow Tasks
- Adding Data Flows
- Troubleshooting the Package
- Logging
- Visual Tools
- Data Views
- Breakpoints
- Other Debugging Tools
Analysis Services
- Understanding Analysis Services
- OLAP Terminology
- Cubes, Dimensions, and Measures
- Cube Storage
- The Unified Dimensional Model
- Creating a Unified Dimensional Model
- The AdventureWorksDW Sample
- Creating a Data Source View
- Creating a Cube
- Browsing and Enhancing the Cube
- Data Mining
- Microsoft Data Mining Algorithms
- Creating a Data Mining Structure
- Processing and Viewing a Data Mining Model
Introduction to Reporting Services
- Understanding Reporting Services
- Server Components
- Client Components
- Configuring Reporting Services
- Installation
- Building a Simple Report
- The Report Wizard
- Creating, Publishing, and Viewing Reports
- Creating a Report Manually
- Creating a Data Source
- Adding Data to the Report
- Previewing the Report
- Publishing the Report
- Using and Managing Published Reports
