Microsoft SQL Server 2000 Professional Training via CD-ROM

Level 2

Microsoft SQL Server 2000 CD-ROMs

Cost Level 2: $595 + GST

Set of 6 CD's

Cost All 3 Levels: $1,350.00 + GST

Set of 14 CD's

Level 2 assumes that you already know how to create a well-designed database and how to write simple SQL queries for retrieving and manipulating data. The course shows you how to build effective views, stored procedures, triggers, and user-defined functions, using Transact-SQL (T-SQL), the programming language of SQL Server. You'll also learn how to take advantage of SQL Server's tools for maintaining, securing, and tuning your databases.

Level 2

What's new in the Query Analyzer
  • Object Browser
  • Transact-SQL Debugger
  • Query Analyzer Features
  • Query Plans and Index Tuning
  • Manage Indexes and Statistics
  • Options and Customization

Programming with T-SQL

  • Overview of Transact-SQL
  • Transact-SQL Extensions
  • Batches, Variables
  • Delimiters and Operators
  • Data Types
  • Built-in Functions
  • System Functions
    @@IDENTITY
    @@ERROR, etc.
  • Error Handling
  • Flow of Control

Transaction Processing

  • Implicit Transactions
  • Explicit Transactions
  • Working with Locks
  • Monitoring Processes and Locks
  • Isolation Levels
  • Blocking and Deadlocks
  • Setting Deadlock Priority

Building Views

  • Complex Joins
  • Pre-Aggregate Data
  • Indexed Views
  • Partitioned Views
Maintaining and Backing up SQL Server
  • Backup and Restore
  • Creating a Backup Strategy
  • Implementing Backups
  • Database Maintenance Plan Wizard
  • Restoring from a Backup

Building Stored Procedures and Triggers

  • Building Stored Procedures
     Parameters
     Temp tables
     Cursors
  • Error Handling in Stored Procedues
  • Debugging Stored Procedures
  • Building Triggers
     Enforce Business Rules
     Send Alerts
     AFTER Triggers
     INSTEAD OF Triggers
Building User-Defined Functions
  • Scalar functions
  • Inline table-valued functions
  • Multi-statement table-valued functions
  • The TABLE Data Type
  • Putting it All Together
    When to use a view, function, stored procedur    e

Advanced Queries/Linked Servers

  • Advanced Queries
    Handling Text & Image Datatypes
  • EXECUTE, EXECUTE() and sp_executeSQL
  • Dynamic SQL and Permissions
  • When and How to Use Cursors
  • Handling Large Data Sets
  • Partitioning Tables
  • Distributed Queries-Linked Servers
    OPENROWSET
    OPENQUERY

Security

  • NT 4.0 and Windows 2000
  • SQL Server Authentication
  • Configuring SQL Server Security Settings
  • Understanding Roles, Database, Server and User-Defined Roles
  • Assigning and Granting Permissions
  • Application Roles
  • Securing Data
  • Windows 2000 / Kerberos

Monitoring, Tuning and Configuration

  • SQL Server Monitoring Tools
  • SQL Server Profiler
  • Tuning Queries (Joins and Subqueries)
  • Clustered and Non-Clustered Index Recommendations
  • Graphical Showplan
  • Indexing tools and recommendations
  • Using System Monitor
  • Factors for a Well-Tuned Application
  • Understanding Caching, Data Cache, Procedure Cache