SQL Server
2000 for Developers Part 2 - SQD2
Description
ATS's SQL Server 2000 for Developers Part 2 training course 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.
Course Outline
Module 1
Section A: Introduction
Section B: Using the Query Analyzer Overview 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 Overview 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 Overview 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
Module 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 Overview 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? Overview Purpose of Views Security
of Views View Syntax View Options
Module 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 Overview Recovery Models Analyzing
Your Needs Backup User/System Databases How Database Backups Work
Database Backup Restore Database Section C: Database Maintenance
Plan Overview 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
Module 4
Section A: Advanced Stored Procedures Overview Temp Tables
Multiple Updates Execution Method Error Handling Return
Codes Messages Trapping Errors RAISERROR for Return
Verify Procedure
Section B: Using the T-SQL Debugger Overview Debug Procedure
Debug Features Nested Procedures Step Options
Section C: Building Triggers Overview 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
Module 5
Section A: Advanced Queries Overview Testing of Parameters
Execute Procedure Execution Options
Section B: T-SQL Cursors Overview 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
Section F: Understanding Roles Overview Fixed Server Roles
Role Properties Understanding Ownership
Section G: Assigning Permission Overview Permission Types
Permission Statements Granting Permissions Securing Data
Row-Level Permissions Procedure Permission
Module 6
Section A: Application Roles Overview Creating AppRoles
Grant Permissions Verify Permissions
Section B: Evaluating Performance Overview Monitoring Tools
Section C: SQL Server Profiler Overview Toolbar Options
Start a Trace Filter Options
Section D: Tuning Queries Overview Join Types Performance
Issues
Section E: Indexes Index Types Index Recommendations Optimization
Graphical Show Plan Section F: Indexed Views Overview Requirements
Create Indexed View
Section G: Index Tuning Wizard Overview Test for Recommendations
Check Results
Section H: Data Caching Overview Memory Usage Procedure
Cache
Section I: The System Monitor Overview Add Counters System
Monitor Counters Create Alert Logs
Price £1025 (Bundle of 6)
Complementary Courses
SQ2A - SQL Server 2000 Admin
SQ2D - SQL Server Implementing Data Base Design
SQD1 - SQL Server for Developers Part 1
SQD3 - SQL Server for Developers Part 3
SQ2D - SQL Server DTS Data Transformation Services
SXM2 - XML SQL Server 2000
<<Back
<<Contact Us
|