SQL Server 2000 For Developers Part 2

SQL Server 2000 For Developers Part 2

6 Sessions –
18 Hours of Interactive Training

LearnKey's SQL Server 2000 for Developers Part 2 builds on the development and support concepts to teach effective handling of data. Experts Mary Chipman and Andy Baron show you the requisites for using the Transact-SQL language and SQL Server's graphical tools to perform the tasks necessary for manipulating, securing, and maintaining efficient databases. At the conclusion of this course, you will have the knowledge and skills to make effective use of SQL Server 2000.

Prerequisites: Experience with any database product or some developer or programming experience.

Benefits

Increase your earning potential with technical mastery and proficiency.

Enhance employment opportunities with in-demand technical knowledge.

LearnKey courses let you move along at your own pace and gain new skills in a useful, productive manner.

Also Available:
· Study Guide

About The Author

Mary Chipman and Andy Baron are Senior Consultants with MCW Technologies, and both are Microsoft MVPs for 5 years running. Specializing in database application development and writing about SQL Server, Visual Basic, the .NET framework, and related technologies, they are co-authors of the Microsoft Access Developer's Guide to SQL Server, published by Sams, and Mary co-authored SQL Server 7.0 in Record Time, published by Sybex. Their combination of experience brings a unique programming and management view to the concepts of SQL development.

Session 1

Section A: Introduction

Section B: Using the Query Analyzer
· Customize Toolbar
· File Menu Items
· Goto & Bookmarks
· Templates
· Advanced
· Query Execution & Result Sets
· Object Browser
· Stored Procedures
· Object Search
· Manage Indexes
· Manage Statistics
· Options
· Connection Options
· Window & Help Menus

Section C: Programming with Transact-SQL
· Transact-SQL Batches
· DECLARE Variables
· Using CAST & CONVERT
· Using STR
· Using Built-in Functions
· Working with NULLS
· COALESCE
· ISNUMERIC
· RAND
· ROUND

Section D: More Built-in T-SQL Functions
· REPLACE & STUFF
· LEN, LEFT & RIGHT
· SUBSTRING
· CHARINDEX
· SPACE
· CHAR & ASCII
· LOWER & UPPER
· LTRIM & RTRIM
· Date & Time Functions
· DATEPART
· DATEFIRST
· DATEADD & DATEDIFF
· @@ROWCOUNT
· @@TRANCOUNT
· @@IDENTITY
· @@ERROR

Session 2

Section A: Flow Control & Error Handling
· IF…ELSE
· BEGIN & END
· GOTO, RETURN & Labels
· CASE
· Using CASE
· WHILE
· Error Handling
· Using @@ERROR
· Using RAISERROR

Section B: Transaction Processing
· Transactions Defined
· Transaction Types
· How Autocommit Works
· Locks Defined
· Lock Modes
· Row Level Locking

· Monitoring Processes & Locks
· Current SQL Users
· Current Issued Locks
· Transaction Isolation
· READ COMMITTED
· READ UNCOMMITTED
· REPEATABLE READ
· SERIALIZABLE · Committed vs. Uncommitted
· Using Repeatable Read
· Using Serializable
· Lock Hint
· Blocking & Deadlocks
· Set Deadlock Priority
· Avoidance

Section C: What is a View?
· Purpose of Views
· Security of Views
· View Syntax
· View Options

Session 3

Section A: Creating Views
· CREATE VIEW
· SELECT/FROM
· Working with Views
· Data Tools
· Group By & Criteria
· Order By & Icons
· Verify View
· Other Views
· Correlated Subquery
· JOIN
· Derived Table
· Update Views
· New Features

Section B: Creating Backup Strategy
· Recovery Models
· Analyzing Your Needs
· Backup User/System Databases
· How Database Backups Work
· Database Backup
· Restore Database

Section C: Database Maintenance Plan
· Select Databases
· Integrity Check
· Backup Plan
· Reports & Plan History
· Modify the Plan

Section D: Stored Procedures
· What is a Stored Procedure?
· Advantages
· Security & Data Integrity
· Create Stored Procedures
· Input Parameters
· Optional Parameters
· NULL Values
· Output Parameters
· Discover Parameters
· SET NOCOUNT ON

Session 4

Section A: Advanced Stored Procedures
· Temp Tables
· Multiple Updates
· Execution Method
· Error HandlingReturn Codes
· Messages
· Trapping Errors
· RAISERROR for Return
· Verify Procedure

Section B: Using the T-SQL Debugger
· Debug Procedure
· Debug Features
· Nested Procedures
· Step Options

Section C: Building Triggers
· Trigger Types
· Advantages
· Disadvantages
· Creating Triggers
· Test Scenario
· INSTEAD OF Triggers
· INSTEAD OF UPDATE
· Set Trigger Order

Section D: User-Defined Functions
· Function Types
· Scalar Functions
· Adding Parameters
· Inline Table-Valued Functions
· Inline Update Options
· Multi-Statement Table-Valued Functions
· Syntax & Example
· More Examples

Session 5

Section A: Advanced Queries
· Testing of Parameters
· Execute Procedure
· Execution Options

Section B: T-SQL Cursors
· Using Cursors
· Verify Results

Section C: Handling Large Data Sets
· Horizontally Partitioning Tables
· Horizontal Example
· Distributed Partitioned Views
· Distributed Example
· Vertically Partitioning Tables
· Text in Row

Section D: Distributed Queries
· Distributed Architecture
· Linked Servers
· Join Kinded to Local
· OPENROWSET & OPENQUERY
· OPEN…Examples

Section E: Authentication
· Security Overview
· Security Settings
· Adding Logins

SQL Server 2000 For Developers Part 2 continued

Section F: Understanding Roles
· Fixed Server Roles
· Role Properties
· Understanding Ownership

Section G: Assigning Permission
· Permission Types
· Permission Statements
· Granting Permissions
· Securing Data
· Row-Level Permissions
· Procedure Permissions

Session 6

Section A: Application Roles
· Creating AppRoles
· Grant Permissions
· Verify Permissions

Section B: Evaluating Performance
· Monitoring Tools

Section C: SQL Server Profiler
· Toolbar Options
· Start a Trace
· Filter Options

Section D: Tuning Queries
· Join Types
· Performance Issues

Section E: Indexes
· Index Types
· Index Recommendations
· Optimization
· Graphical Show Plan

Section F: Indexed Views
· Requirements
· Create Indexed View

Section G: Index Tuning Wizard
· Test for Recommendations
· Check Results

Section H: Data Caching
· Memory Usage
· Procedure Cache

Section I: : The System Monitor
· Add Counters
· System Monitor Counters
· Create Alert
· Logs