SQL Server 2005 Database Design and Programming
Course Description
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.
<BR>
<BR>
<a href='/it_training/database_microsoft_sql.htm'>
Click here for other MS SQL Courses</a>
<BR>
5 days
Contact us for pricing
Prerequisites
A solid understanding of relational databases is recommended.A Tour of SQL Server 2005
SQL Server 2005 Editions, Components, and ToolsEditions
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 InstallationHardware 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 PrinciplesThe 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-SQLSchemas 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 DataInserting 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 SSMSConnecting 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-SQLTransact-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 ConceptsPassing 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 ProceduresStored 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 OverviewScalar 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 .NET code in SQL Server 2005
Ways to Use SQLCLREnabling 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. .NET Code
Advanced Query Techniques
Full-Text SearchSQL 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 OverviewThe 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 PerformanceDefine 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 AgentStarting Agent
Setting Agent Properties
Operators
Jobs
Alerts
Maintenance Plans
Maintenance Plan WizardMaintenance Plan Designer
SQL Management Objects (SMO
SMO Object ModelAccessing and Managing a Server
Programming Replication
Overview of SQL Server ReplicationPublishing 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 ServicesOLAP 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 ServicesServer 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
SQL ServerSQL Server 2005