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