Advanced SQL for Microsoft SQL Server 2000 by tkq39202

VIEWS: 0 PAGES: 11

									Advanced SQL for Microsoft SQL Server 2000


Duration: 5 days                                                   Course Number: ISI-MSS-02


Description
In this class students learn to:

•    Master the SQL Language.
•    Create queries that are not performance hits.
•    Debug and trouble shoot queries.
•    Learn how to optimize the SQL Server 2000 database to have a smooth running database
     engine.

A series of written and lab exercises will be used to reinforce the classroom education.


Audience
Database administrators and system administrators.


Prerequisites
•    Basic knowledge of relational database theory.
•    Introduction to SQL, or equivalent experience.
•    SQL Server 2000 Systems Administration, or equivalent experience.




Phone:      888-811-0204                E-mail: ISI@sencoinc.com             1516 N. Elmhurst Road #168
Cellular:   916-549-6306                                                     Mt. Prospect, IL 60056
Fax:        419-844-5564                        1 of 11
Advanced SQL for Microsoft SQL Server 2000


Course Agenda
      Fundamentals of SQL Server 2000 Query Processing
      • Query Processing Steps
      • Sending Queries to SQL Server 2000
      • Query Processing
      • Returning Result Rows
      • Potential Bottlenecks

      Presentation of Class Participants' Problem Queries

      Datatype Conversions and Conflicts
      • SQL Server 2000 Datatypes
      • SQL Server 2000 Datatype Conversions
      • SQL Server Datatype Hierarchy
      • Implicit Datatype Conversion and Query Optimization
      • Convert Function

      Update Performance
      • Deferred Updates
      • Direct Updates
      • Checking Direct vs. Deferred Updates
      • Direct vs. Deferred
      • Implications of Modifications
      • Effect of Fill Factor on Update

      SQL Statement Processing
      • General Statement Processing Steps
      • Query Optimization Steps
      • SQL Statements
      • Select <column list> and *
      • Select Distinct <column list> and Distinct *
      • Query Tracking: Distinct
      • Select with Aggregate Functions
      • Select with Sum




Phone:      888-811-0204                E-mail: ISI@sencoinc.com   1516 N. Elmhurst Road #168
Cellular:   916-549-6306                                           Mt. Prospect, IL 60056
Fax:        419-844-5564                        2 of 11
Advanced SQL for Microsoft SQL Server 2000


Course Agenda (continued)
      SQL Statement Processing
      • SQL Aggregate Functions
      • Min
      • Max
      • Count
      • Where Clause Processing
      • Where with Search Arguments
      • Query Tracking: SARGs
      • Details of SARGs
      • Clustered Index Seek
      • Select Statement
      • Bookmark Lookup
      • Where with Non-SARGs
      • Filter
      • Table Scan
      • Where with NO SARGs
      • Query Tracking Non-SARGs
      • Filter
      • Table Scan
      • Non-SARG Using a Clustered Index
      • Order By Processing
      • Query Tracking: Order By Using an Index
      • Order By Not Using an Index
      • Group by and Aggregate Function Processing
      • Query Tracking: Group By
      • Having Clauses with Group By
      • Where vs. Having
      • Grouping and Worktables
      • Role of the Worktable
      • How Does SQL Server 2000 Process Queries Involving a Worktable?
      • Understanding Worktables




Phone:      888-811-0204            E-mail: ISI@sencoinc.com         1516 N. Elmhurst Road #168
Cellular:   916-549-6306                                             Mt. Prospect, IL 60056
Fax:        419-844-5564                    3 of 11
Advanced SQL for Microsoft SQL Server 2000


Course Agenda (continued)
      Session Configuration
      • Set Statement
      • Set Datefirst
      • Set Dateformat
      • Set Deadlock Priority
      • Set Lock_Timeout
      • Set Concat_Null_Yields_Null
      • Set Cursor_Close_On_Commit
      • Set Fips_Flagger
      • Set Identity_Insert
      • Set Language
      • Set Quoted Identifier
      • Set Arithabort
      • Set Arithignore
      • Set Fmtonly
      • Set Nocount
      • Set Noexec
      • Set Numeric_Roundabort
      • Set Parseonly
      • Set Query_Governor_Cost_limit
      • Set Rowcount
      • Set Textsize
      • Set Ansi_defaults
      • Set_Ansi Padding
      • Set Ansi_Warnings
      • Set Forceplan
      • Set Showplan_All
      • Set Showplan_Text
      • Set Statistics IO
      • Set Statistics Time
      • Set Impilicit_Transactions
      • Set Remote_Proc_Transactions




Phone:      888-811-0204           E-mail: ISI@sencoinc.com   1516 N. Elmhurst Road #168
Cellular:   916-549-6306                                      Mt. Prospect, IL 60056
Fax:        419-844-5564                   4 of 11
Advanced SQL for Microsoft SQL Server 2000


Course Agenda (continued)
      Session Configuration
      • Set Transaction Isolation Level
      • Set Xact_Abort
      • Viewing User Configuration Options
      • DBCC Useroptions
      • @@options
      • Default User Configuration Options

      Subqueries
      • Defining Subqueries
      • Where Subqueries Can Be Used
      • Subquery Restriction
      • Categorizing Subqueries
      • Expression Subqueries
      • Quantified Predicate Subqueries
      • Correlated Subqueries
      • Subqueries vs. Joins
      • Internal Subquery Processing
      • Subquery Materialization
      • Materialization: Rewrite
      • Subquery Flattening
      • Correlated Subquery
      • Using Any and All
      • Using In/Not In
      • Using Exists/Not Exists

      Views
      • What is a View?
      • View Syntax
      • Views and Performance
      • View Query Execution Plan
      • Using Views to Simplify SQL
      • Usefulness of the View




Phone:      888-811-0204              E-mail: ISI@sencoinc.com   1516 N. Elmhurst Road #168
Cellular:   916-549-6306                                         Mt. Prospect, IL 60056
Fax:        419-844-5564                      5 of 11
Advanced SQL for Microsoft SQL Server 2000


Course Agenda (continued)
      Views
      • Views for Security
      • Vertical Security
      • Horizontal Security
      • Modifying Data Through a View
      • Views with Check Option

      Single – Table Optimization
      • SQL Server 2000 Search Techniques
      • Table Scans
      • Index Selection
      • Optimizer Selection Criteria
      • SARG Matching
      • Using Indexes
      • SAER Matching
      • CI vs. NCI
      • Or Indexing
      • Or Clauses
      • Or Strategy
      • Or Query Plan
      • Index Selection and Select List
      • Index Covering
      • Composite Indexes
      • Composite Indexes vs. Many Indexes
      • Index Usefulness
      • Data Distribution
      • Index Statistics
      • Distribution Steps
      • Viewing Statistics
      • DBCC Show Statistics
      • Estimating Logical Page I/O
      • How to Force Index Selection




Phone:      888-811-0204            E-mail: ISI@sencoinc.com   1516 N. Elmhurst Road #168
Cellular:   916-549-6306                                       Mt. Prospect, IL 60056
Fax:        419-844-5564                    6 of 11
Advanced SQL for Microsoft SQL Server 2000


Course Agenda (continued)
      Stored Procedures
      • Coding Standards and Conventions
      • Structured Error Handling in Stored Procedures
      • Returning Procedure Status
      • Return Status Example
      • SQL Server 2000 Status Codes
      • Default Parameters
      • Stored Procedure Recompilation
      • Alternatives to Recompile
      • Calling Stored Procedures from Transactions
      • Stored Procedures and Transactions
      • Called or Stand-Alone Procedure Template
      • Calling Batch or Procedure template
      • Using Temporary Tables in Stored Procedures
      • Temporary Tables Performance Tips
      • Debugging Techniques
      • Procedure Optimization

      SQL Server 2000 Join Processing
      • Joins Described
      • Join Optimization
      • Optimizer Limitations
      • Join Order
      • Optimizer Tips
      • Join Resolution
      • Nested Iteration
      • Cost Of Nested Iteration
      • Hash Join
      • Hash Join Query Plan
      • Merge Joins
      • Or with Joins
      • Overriding the Optimizer




Phone:      888-811-0204                E-mail: ISI@sencoinc.com   1516 N. Elmhurst Road #168
Cellular:   916-549-6306                                           Mt. Prospect, IL 60056
Fax:        419-844-5564                        7 of 11
Advanced SQL for Microsoft SQL Server 2000


Course Agenda (continued)
      SQL Server 2000 Join Processing
      • Forcing Join Order
      • Force Plan
      • Self-Joins (Parent/Child)
      • Using Temporary Tables to Eliminate Self-Joins
      • Outer Joins (Parent/Child)
      • Full-Outer Joins
      • Problems with TSQL Outer Joins
      • Outer Join Optimization
      • Right Outer Join

      Using Cursors
      • Row Processing vs. Set Processing
      • Cursors
      • Cursor Types
      • Cursor Steps
      • TSQL Cursor Types
      • TSQL Cursor Usage
      • Declaring Cursors
      • Opening Cursors
      • Fetching Rows
      • Modification with Cursors
      • Closing the Cursor
      • Deallocating the Cursor
      • Scope of Cursors and Name Conflicts
      • Cursor Variables
      • Cursor Options
      • Cursor Locks Granularity
      • Cursors and Locking
      • Cursor Pros and Cons
      • When to Use a Cursor
      • Why is Cursor Performance Worse than Set SQL?
      • Cursor Concurrency
      • Acquiring Locks


Phone:      888-811-0204              E-mail: ISI@sencoinc.com   1516 N. Elmhurst Road #168
Cellular:   916-549-6306                                         Mt. Prospect, IL 60056
Fax:        419-844-5564                      8 of 11
Advanced SQL for Microsoft SQL Server 2000


Course Agenda (continued)
      Using Cursors
      • Optimizing Cursors
      • Multi-table Cursor Can Improve Performance

      Union Queries
      • Standard Union Query Processing
      • Union Features
      • Union Restrictions
      • Showplan Output for Union
      • Hash Match/Union Operator
      • Concatenation Operator
      • Merger Join Union
      • Why US Union?

      Using Binary Large Object Datatypes
      • Text and Image Data
      • Defining Text and Image Columns
      • Writetext
      • Readtext and Updatetext
      • Text Pointers
      • Using Text Pointers
      • BLOBs and Network Packet Size
      • Drawbacks of BLOBs
      • Alternatives to BLOBs

      Full Text Search
      • Definition
      • Configuring a Database for a Full Text Search
      • Creating the Full Text Catalog
      • Registering Tables for Full Text Search
      • Registering columns for Full-Text Search
      • Activation of the Full Text Index
      • Starting the Index




Phone:      888-811-0204              E-mail: ISI@sencoinc.com   1516 N. Elmhurst Road #168
Cellular:   916-549-6306                                         Mt. Prospect, IL 60056
Fax:        419-844-5564                      9 of 11
Advanced SQL for Microsoft SQL Server 2000


Course Agenda (continued)
      Full Text Search
      • Verifying the Fulltext Index
      • Full-Text Query
      • Proximity Search
      • Variation Search
      • Weighted Search

      Query Troubleshooting Tools and Approaches
      • Tools to Examine SQL Server Query Processing
      • Showplan_All and Showplan_Text
      • Set Showplan_All
      • Interpreting Showplan_All and Showplan_text
      • Logical and Physical Design Operators
      • Set Statistics IO
      • Set Statistics Time
      • DBCC Trace Flags
      • The Query Analyzer
      • View Query Plans with the Query Analyzer
      • Query Analyzer Icons
      • Query Analyzer Index Analysis
      • What to Look For in Problematic Queries
      • Troubleshooting Queries

      Nulls
      • Definition
      • Null Truth Tables
      • Issues with Nulls
      • Nulls and Aggregates
      • Four Value Logic




Phone:      888-811-0204               E-mail: ISI@sencoinc.com   1516 N. Elmhurst Road #168
Cellular:   916-549-6306                                          Mt. Prospect, IL 60056
Fax:        419-844-5564                      10 of 11
Advanced SQL for Microsoft SQL Server 2000


Course Agenda (continued)
      Advanced Select Options
      • Case
      • Cube and Rollup
      • Cube vs. Rollup
      • The Top Clause

      Specific SQL Problems and Solutions
      • Writing Cross-Tab Reports
      • Approaches
      • Get Grouping Working
      • Determinant Functions
      • Date Functions
      • Building the Determinant Functions
      • Writing the Query
      • Using Vector Data
      • Vertical Vector Data
      • Comparison of Vertical and Horizontal Implementations
      • How to Retrieve Results from Vector Data
      • Retrieving Awkward Results
      • Fast Random Data Population Techniques
      • Insert Performance
      • Distributing Data and Datatypes




Phone:      888-811-0204             E-mail: ISI@sencoinc.com   1516 N. Elmhurst Road #168
Cellular:   916-549-6306                                        Mt. Prospect, IL 60056
Fax:        419-844-5564                    11 of 11

								
To top