Email Us   Phone : 503-259-0312   
  Home    |    Instructor-led Training    |    Self-Paced Learning    |    Online Training     


Contact Us   -   Why Choose Wintrac   -   Clients    

Courses
ADA
Adobe
Agile
AJAX
Android
Apache
AutoCAD
Big Data
Business Analysis
Business Intelligence
Business Objects
Business Skills
C++ programming
Cisco
Citrix
Cloud Computing
COBOL
Cognos
ColdFusion
COM/COM+
CompTIA
CORBA
CRM
Crystal Reports
Datawarehousing
DB2
Desktop Application Software
DNS
Embedded Systems
Google Web Toolkit (GWT)
IPhone
ITIL
Java
JBoss
LDAP
Leadership Development
Lotus
Machine learning/AI
Macintosh
Mainframe programming
Microsoft technologies
Mobile
MultiMedia and design
.NET
NetApp
Networking
New Manager Development
Object oriented analysis and design
OpenVMS
Oracle
Oracle VM
Perl
PHP
PowerBuilder
Professional Soft Skills Workshops
Project Management
Rational
Ruby
Sales Performance
SAP
SAS
Security
SharePoint
SOA
Software quality and tools
SQL Server
Sybase
Symantec
Telecommunications
Teradata
Tivoli
Tomcat
Unix/Linux/Solaris/AIX/
HP-UX
Visual Basic
Visual Foxpro
VMware
Web Development
WebLogic
WebSphere
Websphere MQ (MQSeries)
Windows programming
XML
XML Web Services
Other
T-SQL 2005 WITH ADVANCED TOPICS
SQL Training Overview

In this course, you'll start by learning about the basic features that are available in SQL Server, including how to design and create a database and how to build queries using Transact-SQL, the language of SQL Server. Then, you'll learn how to build effective views, stored procedures, triggers, and user-defined functions using Transact-SQL. You'll learn about the new enhancements to the Transact-SQL programming language including improved support for error handling and hierarchical queries, and how programmers can now use .NET languages like C# and Visual Basic to build database objects. You'll also learn about enhancements made to SQL Server 2005 for handling complex data structures, using partitioning, and working with XML.

SQL Training Audience:

Database developers and SQL Server administrators who need to understand the enhancements made to the Transact-SQL programming language

SQL Training Prerequisites

A good working knowledge of SQL Server 2000 or 2005, basic relational database concepts, XML, Transact-SQL, and a fundamental understanding of networking and security concepts is recommended.


SQL Training Course duration

4 days

SQL Training Course outline

A Tour of SQL Server 2005

  • SQL Server 2005 Editions, Components, and Tools
  • Editions
  • Server Components
  • Management Tools
  • Documentation and Samples
  • Using SQL Server Management Studio (SSMS)
  • Connecting Management Studio
  • The Object Explorer
  • Exploring the Sample Databases
  • Database Objects
  • Working with Tables and Queries
  • Creating Tables
  • Creating Views
  • Generating Scripts
  • New Transact-SQL Features
  • Common Table Expressions (CTEs)
  • Ranking Functions
  • TOP(n) and APPLY
  • PIVOT
  • FOR XML PATH
  • Try/Catch Error Handling
  • Using EXECUTE AS
  • Using Snapshot Isolation
  • Running CLR Code in SQL Server 2005
  • Business Intelligence Services
  • SQL Server Integration Services (SSIS)
  • SQL Server Business Intelligence Development Studio (BIDS)
  • Reporting Services
  • Analysis Services (SSAS)
Designing and Creating a Database
  • Relational Database Design Principles
  • The Origins of Relational Design
  • Data Normalization
  • Understanding Referential Integrity
  • Beyond Normalization
  • Implementing the Design
  • Database Storage
  • Creating Databases
  • Modifying Database Options
  • Creating Tables
  • Creating Constraints
  • Triggers
  • Creating Indexes
  • Using Database Diagrams
Data Selection Queries
  • Understanding Transact-SQL
  • Schemas and Naming in SQL Server 2005
  • The SELECT Statement
  • Selecting All Columns
  • Concatenating Columns
  • Naming Columns
  • Using DISTINCT to Limit Values
  • The WHERE Clause
  • Transact-SQL Comparison Operators
  • The LIKE Operator
  • The BETWEEN Operator
  • Using IS NULL to Test for Nulls
  • Multiple Conditions with AND, OR, and NOT
  • Operator Precedence
  • Using the IN Operator
  • Using ORDER BY to Sort Data
  • Sorting on a Single Column
  • Sorting by Multiple Columns
  • Sorting with Expressions
  • The GROUP BY Clause
  • Aggregate Functions
  • Counting Rows
  • Counting Columns
  • Counting with a WHERE Clause
  • Using GROUP BY
  • Using ORDER BY with GROUP BY
  • Using HAVING with GROUP BY
  • TOP Values Queries
  • Joining Tables
  • Cross Joins (Cartesian Products)
  • The Use of Keys in Joining
  • Join Notation
  • Inner Joins
  • Outer Joins
  • Self Joins
Modifying Data
  • Modifying Data
  • Inserting Data
  • Inserting a Single Value
  • Inserting Multiple Values
  • Inserting Multiple Rows
  • Creating a New Table using SELECT INTO
  • Temporary Tables
  • Using Bulk Copy to Insert Data
  • Updating Data
  • Updating a Single Row
  • Updating Multiple Rows and Columns
  • Updating from Another Table
  • Updating with TOP
  • Updating Large Value Types with UPDATE.WRITE
  • Deleting Data
  • Deleting a Single Row
  • Deleting Multiple Rows
  • Understanding Transaction Isolation
  • Isolation Levels Explained
  • Blocking and Deadlocks
  • Using Snapshot Isolation
Working with SQL Server Management Studio
  • Getting Started with SSMS
  • Connecting to Management Studio
  • SSMS Toolbars
  • Overview of SSMS Menu Options
  • Configuring SSMS Windows
  • Exploring the Object Explorer
  • Right-Click Menu Options
  • Server and Database Objects
  • Displaying and Filtering Objects
  • Finding Objects
  • Editing Database Objects in the SSMS Designers
  • Working with the Query Editor
  • Displaying Multiple Code Windows
  • Formatting and Editing Code
  • Scripting with SSMS
  • Executing Queries
  • Creating Projects and Solutions
  • Working Offline
  • Using SQL Server Books Online
  • Getting Help in SSMS
Transact-SQL Programming
  • Overview of Transact-SQL
  • Transact-SQL Extensions
  • Batches and Scripts
  • Variables
  • Delimiters and Operators
  • Transact-SQL and Data Types
  • Using Built-In Functions
  • Working with Nulls
  • Handling Numbers
  • Manipulating Strings
  • Working with Date and Time Values
  • Using the @@ Functions
  • Controlling Flow
  • IF…ELSE
  • BEGIN…END
  • GOTO, RETURN, and Labels
  • CASE
  • WHILE
  • WAITFOR
  • Ranking Results
Transactions and Error Handling
  • Transaction Concepts
  • Passing the ACID Test
  • Transaction Types
  • Avoiding Blocked Transactions
  • Working around Deadlocks
  • Applications and Transactions
  • Designing Transactional Support
  • Understanding Compile and Runtime Errors
  • Creating Explicit Transactions
  • Explicit Transaction Syntax
  • Transact-SQL Error Handling in Transactions
  • Using RAISERROR
  • Using TRY/CATCH Error Handling
  • TRY/CATCH Overview
  • Using TRY/CATCH in a Stored Procedure
  • Handling Uncommittable Transactions with XACT_STATE
Creating Views
  • What is a View?
  • Advantages of Views
  • Views and Security
  • Creating Views
  • View Rules
  • View Syntax and Options
  • Tools for Creating Views
  • Nesting Views, Derived Tables, and CTEs
  • Encrypting View Definitions
  • Updating Data Using a View
  • Updating Rules
  • Updating Behavior
  • Using Computed Columns
  • Creating a Computed Column
  • Indexed Views
  • How Indexed Views Work
  • Partitioned Views
Creating Stored Procedures and Triggers
  • Creating Stored Procedures
  • Stored Procedure Features
  • Stored Procedures Performance Benefits
  • Reasons to Use Stored Procedures
  • Stored Procedure Syntax
  • Creating Stored Procedures Using SSMS
  • Creating and Executing Stored Procedures
  • Working with Parameters
  • Testing and Debugging Stored Procedures
  • Creating Triggers
  • Uses for Triggers
  • How Triggers Work
  • Creating and AFTER Trigger
  • Creating an INSTEAD OF Trigger on a View
  • Creating a DDL Trigger to Restrict Table Creation
Creating User-Defined Functions
  • User-Defined Function Overview
  • Scalar Functions
  • Scalar Function Syntax
  • Inline Table-Valued Functions
  • Inline Syntax
  • Executing Inline Table-Valued Functions
  • Inline Functions with Parameters
  • Updating Data with Inline Functions
  • Multi-Statement Table-Valued Functions
  • Multi-Statement Function Syntax
  • Executing Multi-Statement Functions
  • Joining to Functions
  • Using Functions, Views, and Stored Procedures
  • Using Scalar Functions
  • Using Table-Valued Functions
  • Choosing Between Functions, Views, and Stored Procedures
Using .NET Code in SQL Server 2005
  • Introduction
  • Ways to Use SQLCLR
  • Enabling SQLCLR
  • Writing SQLCLR Code Manually
  • Creating SQLCLR Code Using Visual Studio 2005
  • SQL Data Types
  • Accessing Local Data
  • Returning Results
  • SQLCLR Code Modules
  • Stored Procedures and Triggers
  • User-Defined Functions
  • Aggregates
  • Managing Code Modules
  • System Catalogs
  • Troubleshooting
  • SQLCLR Security
  • Code Access Security (CAS)
  • SQLCLR Permission Sets
  • T-SQL vs. .NET Code
Advanced Query Techniques
  • Full-Text Search
  • SQL Server 2005 Enhancements
  • Configuring Full-Text Search
  • Writing Full-Text Queries
  • The CONTAINS Predicate
  • The FREETEXT Predicate
  • Using CONTAINSTABLE and FREETEXTTABLE
  • Using Full-Text DDL
  • Generating XML with FOR XML
  • Using AUTO Mode
  • Using RAW Mode
  • Using EXPLICIT Mode
  • Using PATH Mode
  • Using APPLY
  • Comparing APPLY to Using Joins
  • Using APPLY with Table-Valued Functions
  • Combining APPLY with FOR XML PATH
  • Creating Recursive Queries
  • Using a Common Table Expression (CTE)
  • Creating Pivot Queries
  • Rotating Column Data into Column Heading
  • Executing Dynamic SQL
  • Overcoming PIVOT Limitations
  • Using Parameters with sp_executesql
  • Using QUOTENAME()
  • Using sp_executesql with Output Parameters
  • Signing Stored Procedures with Certificates
Implementing SQL Server Partitions
  • Overview of Table-Based Partitioning
  • Why Partition?
  • What’s New in SQL Server 2005?
  • Range Partitions
  • Partition Key and Number of Partitions
  • Using Multiple Filegroups
  • Index Partitioning
  • Creating Partitioned Tables
  • Creating a Partition Function
  • Creating a Partition Scheme
  • Partitioning Tables and Indexes
  • Querying Partitions
  • Using the $PARTITION Function
  • Catalog Views
  • Managing Partitions
  • Modifying Partitioned Tables or Indexes
  • Modifying a Partition Function
  • Modifying a Partition Scheme
  • Backing Up Partitions
  • Performance Considerations
Complex Querying
  • Working with NULL Values
  • SqlTypes and CLR Types
  • About the ANSI_NULLS Option
  • NULLs and SqlBoolean
  • Assigning Null Values
  • Ranking Grouped Data
  • The ROW_NUMBER Function
  • Using the RANK Function
  • Introducing the DENSE_RANK Function
  • The NTILE Function
  • Writing Correlated Subqueries
  • Subquery Basics
  • What is a Correlated Subquery?
  • Using the WHERE Clause
  • Correlated Subqueries and Updates
  • Comparing to Joins and Temp Tables
  • Using Common Table Expressions
  • When to Use CTEs
  • CTE Syntax
  • Recursive CTEs
Advanced Techniques
  • Complex Data and Structures
  • Issues with Data Types
  • Working with Multinational Data
  • Working with Hierarchical Data
  • Writing More Efficient Queries
  • The EXISTS Clause
  • Joins vs. Subqueries
  • One-Pass Queries
  • Using Temporary Tables
  • Table Variables
  • User-Defined Table-Valued Functions
  • Common Table Expressions
  • Worktables
  • Working wit Complex Queries
  • Using Implicit Transactions
  • Keeping it Simple
  • Maintaining Query Files
  • Using Source Control and Versioning
  • Using Visual SourceSafe
Working with XML
  • The xml Data Type
  • Declaring XML Objects
  • Loading Data into an XML Instance
  • Indexing XML Columns
  • XML Schema Collections
  • Typed and Untyped XML
  • Schema Basics
  • Registering Schemas
  • Viewing Stored Schemas
  • Querying XML
  • XQuery
  • FOR XML
  • OPENXML
  • Best Practices
  • Relational vs. XML Data Model
  • Storing XML in SQL Server 2005
  • Reasons to Index XML Columns
  • Querying

 
About us
Contact us
Careers at Wintrac
Our Clients
Why Wintrac


Register for a free training CD-ROM drawing
Refer a client or instructor and earn $$$


Wintrac Inc.
16523 SW McGwire Ct.
Beaverton OR 97007
 
© Wintrac, Inc. All rights reserved.                                                                               Site Map   |   Terms of Use   |   Privacy Policy