Querying Microsoft SQL Server 2005 with Transact – SQL
Duration
Three Days
Description
In this course, you’ll learn how to build basic 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 programmers can now use .NET languages like C# and Visual Basic to build database objects.
In this course, you will
- Understand basic database design principles and how to implement them in SQL Server.
- Learn the fundamentals of writing Transact-SQL queries that retrieve or modify data.
- Understand the differences between view, stored procedures, triggers, and user-defined functions.
- Use the new Common Table Expressions for creating hierarchical queries.
- Perform calculations with Visual Basic and Visual C# code rather than T-SQL.
Pre-requisites
This course is designed for database developers and SQL Server administrators who need to understand Transact-SQL. To get the most out of this course, you should have a solid understanding of relational databases. No particular programming experience is required, but the course is taught from a developer's perspective.
Course Content
A Tour of SQL Server 2005
- SQL Server 2005 Editions, Components, and Tools
- Using SQL Server Management Studio (SSMS)
- Working with Tables and Queries
- New Transact-SQL Features
- Business Intelligence Services
Designing and Creating a Database
- Relational Database Design Principles
- Implementing the Design
Data Selection Queries
- Understanding Transact-SQL
- The SELECT Statement
- The WHERE Clause
- Using ORDER BY to Sort Data
- The GROUP BY Clause
- Joining Tables
Modifying Data
- Modifying Data
- Inserting Data
- Updating Data
- Deleting Data
- Understanding Transaction Isolation
Working with SQL Server Management Studio
- Getting Started with SSMS
- Exploring the Object Explorer
- Working with the Query Editor
- Using SQL Server Books Online
Transact-SQL Programming
- Overview of Transact-SQL
- Using Built-In Functions
- Controlling Flow
- Ranking Results
Transactions and Error Handling
- Transaction Concepts
- Applications and Transactions
- Creating Explicit Transactions
- Using TRY/CATCH Error Handling
Creating Views
- What Is a View?
- Creating Views
- Updating Data Using a View
- Using Computed Columns
- Indexed Views
- Partitioned Views
Creating Stored Procedures and Triggers
- Creating Stored Procedures
- Creating Triggers
Creating User-Defined Functions
- User-Defined Function Overview
- Scalar Functions
- Inline Table-Valued Functions
- Multi-Statement Table-Valued Functions
- Using Functions, Views, and Stored Procedures
Using .NET Code in SQL Server 2005
- Writing SQLCLR Code
- SQLCLR Code Modules
- Managing Code Modules
- SQLCLR Security
- T-SQL vs. .NET Code
Advanced Query Techniques
- Full-Text Search
- Generating XML with FOR XML
- Using APPLY
- Creating Recursive Queries
- Creating Pivot Queries
- Executing Dynamic SQL
