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

Contact Us   -   Why Choose Wintrac   -   Clients    

Big Data
Business Analysis
Business Intelligence
Business Objects
Business Skills
C/C++/Go programming
Cloud Computing
Crystal Reports
Data Science
Desktop Application Software
Embedded Systems
Google Web Toolkit (GWT)
Leadership Development
Machine learning/AI
Mainframe programming
Microsoft technologies
MultiMedia and design
New Manager Development
Object oriented analysis and design
Oracle VM
Professional Soft Skills Workshops
Project Management
Sales Performance
Software quality and tools
SQL Server
Visual Basic
Visual Foxpro
Web Development
Websphere MQ (MQSeries)
Windows programming
XML Web Services
T-SQL 2008
SQL Training Overview

In this course, you will learn about the features that are available in SQL Server like how to design and create a database, and how to build basic queries using Transact-SQL, the language of SQL Server. Then you will learn how to build effective views, stored procedures, triggers, and user-defined functions using Transact-SQL. You will learn how to use the Transact-SQL programming language for error handling and hierarchical queries, dealing with complex data structures and processes, and how to make your databases more scalable through partitioning. SQL Server 2008 includes a rich set of tools that go beyond the basics of querying and manipulating data. You will learn how to take advantage of the user-friendly management console that integrates both authoring and administrative tasks. You will learn how to take advantage of SQL Server's tools for analyzing and tuning your databases.

SQL Training Audience:

Database developers and SQL Server administrators who need to understand Transact-SQL.

SQL Training Prerequisites

A solid understanding of relational databases is recommended. No particular programming experience is required, but the course is taught from a developer's perspective.

SQL Training Course duration

4 days

SQL Training Course outline

A Tour of SQL Server 2008

  • SQL Server 2008 Editions, Components, and Tools
  • Editions
  • Server Components
  • Management Tools
  • Documentation and Samples
  • SQL Server 2008 Feature Pack
  • Using SQL Server Management Studio (SSMS)
  • Connecting Management Studio
  • The Object Explorer
  • Database Objects
  • Monitoring Server Activity
  • Connecting to Other Servers
  • Customizing the User Interface
  • Working with Tables and Queries
  • Creating Tables
  • Creating a View
  • Generating Scripts
  • Viewing Table Rows
  • 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
  • Working with SQL Server Management Studio
  • Getting Started with Management Studio
  • Connecting to a Server from Management Studio
  • Configuring Management Studio Options
  • Management Studio Toolbars
  • Overview of Management Studio Menu Options
  • Configuring Management Studio Windows
  • Exploring the Object Explorer
  • Right-Click Menu Options
  • Server and Database Objects
  • Displaying and Filtering Objects
  • Finding Objects
  • Editing Database Objects in the Management Studio Designers
  • Working with the Query Editor
  • Displaying Multiple Code Windows
  • Formatting and Editing Code
  • Scripting with Management Studio
  • Executing Queries
  • Creating Projects and Solutions
  • Working Offline
  • Using SQL Server Books Online
  • Getting Help in Management Studio
  • 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
  • GOTO, RETURN, and Labels
  • CASE
  • 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 TRY/CATCH Error Handling
  • TRY/CATCH Overview
  • Using TRY/CATCH in Stored Procedure
  • Handling Uncommittable Transactions in XACT_STATE
  • Data Selection Queries
  • Understanding Transact-SQL
  • Schemas and Naming in SQL Server 1008
  • 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 Column Data
  • 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
  • Combining Inner and Outer Joins
  • Self Joins
  • Advanced Data Types
  • The HierarchyID Data Type
  • Indexing a Hierarchy
  • Manipulating Hierarchies
  • Working with the Instructor Hierarchy
  • Exploring and Manipulating a Hierarchy
  • Sparse Columns and Column Sets
  • Restrictions on Sparse Columns
  • Column Sets
  • Recommendations
  • Working with Sparse Columns
  • Working with Column Sets
  • Filtered Indexes
  • FILESTREAM Storage
  • FILESTREAM Data Type
  • FILESTREAM Data Access Methods
  • Gotchas and Limitations
  • Spatial Data
  • Geometry vs. Geography
  • Spatial Data Standards
  • Types of Spatial Data
  • Using Spatial Data
  • Working with Spatial Data
  • Interactions Between Objects
  • 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
  • The RANK Function
  • The DENSE_RANK Function
  • The NTILE Function
  • Writing Correlated Subqueries
  • Subquery Basics
  • What is a Correlated Subquery?
  • Using the WHERE Clause
  • Including the HAVING Clause
  • Correlated Subqueries and Updates
  • Comparing to Joins and Temp Tables
  • Using Common Table Expressions
  • When to Use CTEs
  • CTE Syntax
  • Recursive CTEs
  • Modifying Data
  • Modifying Data with Rransact-SQL
  • Inserting Data
  • Inserting a Single Value
  • Inserting Multiple Values
  • Inserting Multiple Rows
  • Creating a New Table with 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
  • Blocking and Deadlocks
  • Using Snapshot Isolation
  • Working with XML
  • The xml Data Type
  • Declaring XML Objects
  • Loading Data into an XML Instance
  • Indexing XML Columns
  • XML Schema Collections
  • Types and Untyped XML
  • Schema Basics
  • Greater Support in SQL Server 2008
  • Registering Schemas
  • Viewing Stored Schemas
  • Querying XML
  • XQuery
  • For XML
  • Best Practices
  • Relational vs. XML Data Model
  • Storing XML in SQL Server 2008
  • Reasons to Index XML Columns
  • Querying
  • 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 Common Table Expressions
  • 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
  • Implementing SQL Server Partitions
  • Overview of Table-Based Partitioning
  • Why Partition?
  • SQL Server Partitioning
  • 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
  • Creating User-Defined Functions
  • User-Defined Function Overview
  • Scalar Functions
  • Scalar Function Syntax
  • Inline Table-Valued Functions
  • Inline Syntax
  • Executing Inline 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
  • Creating Stored Procedures and Triggers
  • Creating Stored Procedures
  • Stored Procedure Features
  • Stored Procedure Performance Benefits
  • Reasons to Use Stored Procedures
  • Stored Procedure Syntax
  • Creating Stored Procedures in Management Studio
  • Creating and Executing Stored Procedures
  • Working with Parameters
  • Testing and Debugging Stored Procedures
  • Creating Triggers
  • Uses for Triggers
  • The Trouble with Triggers
  • How Triggers Work
  • Creating an AFTER Trigger
  • Creating an INSTEAD OF Trigger on a View
  • Creating a DDL Trigger to Restrict Table Creation
  • Advanced Query Techniques
  • Full-Text Search
  • Configuring Full-Text Search
  • Writing Full-Text Queries
  • The CONTAINS Predicate
  • The FREETEXT Predicate
  • Using Full-Text DDL
  • Advanced T-SQL Techniques
  • Using APPLY
  • Inserting and Updating Data with MERGE
  • Creating Recursive Queries
  • Grouping Data with Grouping Sets
  • Creating Pivot Queries
  • Executing Dynamic SQL
  • Overcoming PIVOT Limitations
  • Using Parameters with sp_executesql
  • Using QUOTENAME()
  • Using sp_executesql with Output Parameters
  • Signing Stored Procedures with Certificates
  • 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
  • User-Defined Table-Typed Parameters
  • Working with Complex Queries
  • Using Implicit Transactions
  • Keeping it Simple
  • Maintaining Query Files
  • Using Source Control and Versioning
  • Using Visual SourceSafe

    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