SQLServer BOOTCAMP ®

Printer Friendly Version

Course Name: SQLServer BOOTCAMP using Visual Basic

Brief Course Description: This is an intensive course on writing applications using Transact-SQL for the Microsoft platform. Learn to apply T-SQL performance and tuning techniques that result in dramatic throughput improvements.

Duration: 4 Full Days, hands-on (3 days if the VB and ASP programming with ADO is removed)

Format: Instructor-led, hands-on

Prerequisites: Solid knowledge of Visual Basic and relational concepts (database normalization) is assumed. Student should already have done some self-study and be able to write simple SELECT statements.

Recommended Reading: Students should be comfortable in advance of attendance relational database concepts and intermediate-level VB programming. The following books will be helpful:

Beginning SQL Programming, Kauffman, Matsik, Spenser; publisher Wrox Press

VB6 BOOTCAMP Certification Exam Guide, Mezick; publisher McGraw-Hill

Assumed Audience: Developers new to SQL development for the Microsoft platform. Instructor assumes T-SQL development work post-class will be done primarily in Visual Basic with ADO.

Course Materials: 400+ page SQLServer Textbook, NewTech SQL Server Resource Pack with CD.

Resource pack contains the following:

1. Source code listings
2. Book lists with commentary and reviews
3. Terminology grid, listing all pertinent terms in alphabetical order. All terms are linked, i.e., a term that refers to another term in the list is a link, allowing the user to view related terms in real time
4. List of 3rd party add-ons and libraries that aid in building systems with this technology
5. Web site listings related to this topic
6. White papers written by NewTech consulting staff on this topic
7. CD-ROM with additional resources for the developer using this technology

Course Description Summary: Course Description Summary: This comprehensive course on writing applications using Transact-SQL for the Microsoft platform is based upon the experience of NewTech’s consultant’s in the field. This course covers ‘core’ Transact-SQL techniques and also covers the SQL Server environment, Query Analyzer, using ADO with T-SQL from Visual Basic, and other topics pertinent to developing good all-around T-SQL skills.

This course clearly introduces students to common pitfalls in T-SQL and the associated tricks and tips. Like almost all programming, knowing what to avoid is very as important as knowing what to use and Transact-SQL is no exception.

The Outline

1. PROGRAMMING IN TRANSACT-SQL
1. Introduction to Transact-SQL
1. SQL and the Introduction of Transact-SQL
2. The Relational Database Model
3. Normalization
4. Row Processing Versus Set Processing
5. The History of SQL Server
6. What is Transact-SQL?
7. Summary
2. Database Analysis and Design
1. The Need for Adequate Analysis and Design
2. The Systems Life Cycle
3. Overview of Analysis
4. Overview of Design
5. Putting it Together with currently available CASE Technology (use cases)
6. Summary
3. SQL Primer
1. SQL Versus Transact-SQL
2. Data Definition Language
3. Data Manipulation Language
4. Summary
5. EXERCISE
4. Transact-SQL Fundamentals
1. Server Character Sets
2. Server Sort Orders
3. Identifiers
4. Foundation Units
5. Executing Transact-SQL
6. The GO Statement
7. Transact-SQL Batches
8. Summary
9. EXERCISE
5. Format and Style
1. Why Bother with Good Formatting?
2. What Is a Good Coding Style?
3. Case of Keywords and User-Defined Names
4. Naming Conventions and Abbreviations
5. Standard Error Handling
6. Style Odds and Ends
7. Formatting DML and DDL Statements
2. T-SQL CONCEPTS AND FACILITIES
1. Datatypes and Variables
1. Datatypes
2. User-Defined Datatypes
3. Processing Nulls
4. Variables
5. Parameters
6. Summary
2. Conditional Processing
1. BEGIN…END Constructs
2. IF Constructs
3. GOTO Branching and Error Handling
4. WAITFOR Constructs
5. WHILE Loops
6. RETURN Statement
7. Summary
3. Row Processing with Cursors
1. Cursor Fundamentals
2. Declaring a Cursor
3. Opening a Cursor
4. Asynchronous Keysets
5. Fetching Data
6. Closing and Deallocating a Cursor
7. Working with Cursors
8. Performance Issues for Cursors
9. Summary
10. EXERCISE
4. Error Handling
1. Code Processing and Error Handling: Basic Concepts
2. Displaying an Error Message with PRINT
3. Raising and Error with RAISERROR
4. Finding and Error Conditions with Global Variables
5. Handling Errors with GOTO
6. Creating Specialized Error Messages
7. Finding Error Messages in Operating System Files
8. Summary
9. EXERCISE
5. Temporary Objects
1. Creating and Manipulating Temporary Objects
2. The tempdb Database
3. Classes of Temporary Objects
4. Query Optimization with Temporary Tables
5. Management of the tempdb Database
6. Summary
7. EXERCISE
6. Transactions and Locking
1. About Transactions
2. Locking
3. Transactions and the Database
4. BEGIN, COMMIT, ROLLBACK TRANSACTION explained
5. BEGIN DISTRIBUTED TRANSACTION explained
6. Transactions and ADO
7. Summary
8. EXERCISE
3. ADVANCED TOPICS
1. Functions
1. Aggregate Functions
2. Mathematical Functions
3. String Functions
4. Date Functions
5. Text and Image Functions
6. Type Conversion Functions
7. System Functions
8. Summary
9. EXERCISE
2. CASE Expressions and Transact-SQL Extensions
1. COMPUTE
2. CUBE and ROLLUP
3. Summary
4. Stored Procedures and Modular Design
5. About Stored Procedures
6. CREATE PROCEDURES
7. Types of Stored Procedures
8. Advantages of Stores Procedures
3. Stored Procedures Versus other objects
1. Executing Stored Procedures
2. Remote Procedure Calls (RPCs)
3. Stored Procedure Design
4. Conditional Data Retrieval
5. Data Formatting
6. Data Security and Encryption
7. Performance Issues
8. Summary
9. EXERCISE
4. Triggers and Constraints
1. Triggers
2. Interaction of Triggers and Constraints
3. Summary
5. Views
1. About Views
2. Managing Views
3. Querying Views
4. View Dependencies, Permissions, and Data Manipulations
5. Limitation Views
6. View Overhead
7. Summary
6. System and Extended Stored Procedures
1. System Stored Procedures
2. Common System Stored Procedures
3. Extended Store Procedures
4. Summary
5. EXERCISE
4. PERFORMANCE AND TUNING
1. Transact-SQL Code Design
1. Coding for Reuse
2. Categorization Schemas
3. RETURN
4. RAISERROR
5. Temporary Data Storage
6. Using Parameters Effectively
7. Help
8. Summary
2. Code Maintenance in SQL Server
1. Using SQL Server System Tables
2. Interdependence of Server-Side Objects
3. Source Code Management
4. Code Maintenance
5. Related Topics
6. Summary
3. Transact-SQL Optimization and Tuning
1. About Optimization
2. Efficient Physical Database Design
3. Indexing strategies
4. SQL Server Query Optimizer
5. SHOWPLAN Output (very cool!)
6. Optimizer Hints
7. Query Analysis/Query Analyzer
8. Optimization Tips and Techniques
9. Summary
10. EXERCISE
4. Debugging Transact-SQL Programs
1. Testing Versus Debugging
2. Successful Debugging
3. Specific Debugging Techniques
4. The SET Command
5. Debugging Utilities
6. Summary
7. EXERCISE
5. ADO and T-SQL using Visual Basic
6. ADO and T-SQL using Active Server Pages
7. SQL Enterprise Manager
1. Server Administration
2. Database Administration
3. Security Administration
4. Utilities
1. Performance Monitor
2. Security Manager
3. Service Manager
4. SQL Trace
5. Query Analyzer

Upon Completion of this course: Students will exit the class ready to tackle larger Transact-SQL programming projects from the developer point of view. Students will be familiar with Transact-SQL syntax, concepts and facilities, and specific programming techniques. Students will be comfortable examining T-SQL embedded in ADO calls from Visual Basic and Active Server pages.

Click here to contact us for more information.