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.
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.
SQL Training Course duration
3 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
- Database Objects
- Working with Tables and Queries
- Creating Tables
- Creating a View
- 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 Value 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
- Configuring SSMS Options
- 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 Stored Procedure
- Handling Uncommittable Transactions in 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 an 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
- Writing SQLCLR Code
- Creating 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
- Creating Pivot Queries
- Rotating Column Data in Column Headings
Executing Dynamic SQL
- Overcoming PIVOT Limitations
- Using Parameters with sp_executesql
- Using QUOTENAME()
- Using sp_executesql with Output Parameters
- Signing Stored Procedures with Certificates
