Merchant Services

Training

 

SQL Server 2005 T-SQL[ Administrator Track ]

SQL Server 2005 includes a rich set of tools that go beyond the basics of querying and manipulating data. You'll learn how to take advantage of the new, user-friendly management console that integrates both authoring and administrative tasks. You'll learn how to take advantage of SQL Server's tools for analyzing and tuning your databases. You'll also learn about integration services, implementing security, and Microsoft's new Business Intelligence (BI) suite.

 

Audience: Database developers and administrators.

Prerequisites: A solid understanding of relational databases is recommended.

Course Contents

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 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) Installing SQL Server 2005 Preparing for Installation Hardware and Software Requirements Setting Up the Service Accounts Multiple Instances Upgrading an Earlier Version Considerations Upgrade Advisor Installation Steps Installing Prerequisites System Configuration Checks Customizing the Installation Performing the Installation Configuring the Server SQL Server Configuration Manager SQL Server Surface Area Configuration Tool Server Configuration Designing and Creating a Database Relational Database Design Principles The Origins of Relational Design Data Normalization Understanding the 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 Expression The GROUP BY Clause Aggregate Functions Counting Rows Counting Columns Counting with a WHERE Clause Using GROUP BY Using GROUP 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 w/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 a Stored Procedure Handling Uncommittable Transactions with XACT_STATE Creating Views What is a View? Advantage of Views Views and Security Creating Views View Rules View Syntax and Options Tools for Creating Views View Examples 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 & Triggers Creating Stored Procedures Stored Procedure Features Stored Procedures Performance Benefits Reasons to Use Stored procedures Stored Procedure Syntax Creating Stores 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 Creating   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 Understanding and Implementing Security Security Overview Authentication Authorization Permissions Data Encryption Security Epilog Analyzing and Tuning Performance Evaluating Performance Monitoring with SQL Server Profiler Tuning Queries Indexes and Partitions Using the Database Engine Tuning Advisor Understanding SQL Server Performance Problems Automating Administrative Tasks SQL Server Agent Maintenance Plans SQL Management Objects (SMO) Programming Replication Overview of SQL Server Replication Replication Programming Interfaces Configuring Replication Synchronizing Data Using Integration Services Importing and Exporting Data Integration Services Tools Building a Package Troubleshooting a Package Analysis Services Understanding Analysis Services Creating a Unified Dimensional Model Data Mining Introduction to Reporting Services Understanding Reporting Services Configuring Reporting Services Building a Simple Report Creating, Publishing, and Viewing Reports Using and Managing Published Reports

Top of page

---

 

SQL Server 2005 for Administrators [ Administrator Track ]

In this course, you'll learn about available features for the administration of SQL Server, and how to design, create, modify, analyze and tune your databases. You will learn how to build basic queries using Transact-SQL, how to take advantage of the new, user-friendly management console, and how to use SQL Server's tools for configuring, tuning and monitoring database activity. This course also covers critical topics like integration services, implementing security, disaster prevention and recovery, failover clustering, database mirroring and more.

Audience: Database administrators interested in gaining a more thorough knowledge of SQL Server 2005’s features.

Prerequisites: A working knowledge of SQL Server 2000 or 2005, basic relational database concepts, XML, Transact-SQL, and networking and security concepts.

Course Contents

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) Installing SQL Server 2005 Preparing for Installation Hardware and Software Requirements Setting up the Service Accounts Multiple Instances Upgrading an Earlier Version Considerations Upgrade Advisor Installation Steps Installing Prerequisites System Configuration Checks Customizing the Installation Performing the Installation Configuring the Server SQL Server Configuration Manager SQL Server Surface Area Configuration Tool Server Configuration 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 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 Understanding & Implementing Security Security Overview The Threats SQL Server 2005 Security Design Philosophy The Two Stages of Security Authentication Configuring SQL Server Security Settings Windows Integrated Authentication SQL Server Authentication Authorization Principals Securable Objects Permissions Permission Types Permission Statements Granting Permissions Roles, Permissions, and Schemas Execution Context Metadata Security Data Encryption Encryption Keys Encryption Data Analyzing and Tuning Performance Evaluating Performance Define Monitoring Goals SQL Server Monitoring Tools Monitoring with SQL Server Profiler Profiler Terminology Getting Started with the Profiler Tuning Queries How Join Types Affect Performance Join Hints Performance Issues with Joins and Subqueries Avoid Cursors Indexes and Partitions What is an Index? What is a Partition? Using the Database Engine Tuning Advisor Capturing a Workload Analyzing the Workload Providing an Indexing and Partition Recommendation Understanding SQL Server Performance Problems Slow Queries CPU Bottlenecks Memory Bottlenecks Troubleshooting with Dynamic Management Views Automatic Administrative Tasks SQL Server Agent Starting Agent Setting Agent Properties Operators Jobs Alerts Maintenance Plans Maintenance Plan Wizard Maintenance Plan Designer SQL Management Objects (SMO) SMO Object Model Accessing and Managing a Server Programming Replication Overview of SQL Server Replication Publishing Model Replication Types Agents and Metadata Replication Programming Interfaces Replication Stored Procedures Replication Management Objects Agent Executables and ActiveX Controls Configuring Replication Publishing Data Generating Replication Scripts Creating Subscriptions Synchronizing Data Starting a Synchronization Showing Synchronization Status Subscription Status Reinitializing a Subscription

Disaster Recovery Planning a Backup Strategy Your Business Needs and Capabilities Considering Data Recovery Needs Availability Demands Data Usage Size of Database(s) Database Server Configuration Disaster Recovery Testing Recovery Time Considerations Backing Up Databases Backup Devices Creating Backups Recovery Models Types of Backup Scheduling your Backups Scheduling System Database Backups Scheduling Simple Backups Scheduling Full and Bulk-Logged Backups Backing Up Related Databases Verifying Backup Media Using Checksums Using RESOTRE VERIFYONLY Restoring Databases Roll Forward Set Restoring with the Simple Recovery Model Restoring with Full/Bulk-Logged Recovery Models Online Restore Using Database Snapshots Creating a Database Snapshot Reverting to a Database Snapshot Deleting a Database Snapshot Failover Clustering High Availability Overview Levels of Availability and Downtime Estimating Availability Requirements SQL Server 2005 Failover Cluster Preparing a Server Cluster Hardware and Software Requirements Special Considerations Creating a New Cluster Set Up Domain Service Accounts Prepare Static IP Addresses Configure Computer Servers Prepare Shared Disks Create the New Cluster Server Configure the Cluster Add the Distributed Transaction Coordinator Installing SQL Server 2005 Failover What to Expect Before Installing SQL Server Cluster Running SQL Server Setup System Configuration Checks After Installing SQL Server Adding SQL Server Nodes Add a New Node to the Cluster Add a Node to the SQL Server Virtual Server Cluster Remove a SQL Server Virtual Server Node Database Mirroring Understanding Mirroring How Mirroring Works The mirroring Sessions Database Mirroring Operating Modes Role Switching (Failover) Other Mirroring Details Database Mirroring Endpoints Client-Side Redirection Setting Up Database Mirroring Prerequisites Configuration Recommendations Configuring the Recovery Model Configuring Mirroring with SQL Server Management Studio and Transact-SQL Configuring the Endpoints Configuring the Operating Mode Setting up the Mirror Server Database Backing up the Principal Restoring Full Backup on the Mirror Restoring Transaction Log Backups on the Mirror Starting the Mirroring Sessions Working with a Mirror Session Accessing Mirror Data with Snapshots Monitoring Database Mirroring Viewing the Mirror, Witness, and Endpoints Log Shipping Log Shipping Requirements Security Requirements Configuring Log Shipping Transaction Log Shipping Property Page Configuring the Primary Database Configuring the Secondary Database Configuring the Monitor Server Saving the Log Shipping Configuration Removing Log Shipping Monitoring Log Shipping Jobs Viewing the Agent Job History Transaction Log Shipping Status Report Querying Log Shipping System Tables Querying with Log Shipping Stored Procedures Configuring Log Shipping in Transact-SQL Configuring the Primary Database Configuring the Primary Backup Job Configuring the Secondary Database Configuring the Secondary Copy Job Configuring the Secondary Restore Job Removing Log Shipping Peer-to-Peer Replication High Availability Requirements and Limitations Protecting Data with Partitioning Special Identity Columns handling Configuring Peer-to-Peer Replication Security Planning Configuring the Distributor Configure Distribution Wizard Adding a Publisher Server Changing a Distributor’s Properties Securing the Distributor Creating the Publisher Specify the Distributor Publication Database Publication Type Articles Filter Table Rows and Snapshot Agent Agent Security Wizard Actions Complete the Wizard Creating Publication Allowing Peer-to-Peer Subscriptions Securing the Publisher The Replication Monitor Confirugint Peer-to-Peer Subscribers Initializing the Subscriber Database Peer Distributor Preparation Securing the Distributor Configuring Peer-to-Peer Topology Publication Specification Peers Log Reader Agent Security Distribution Agent Security New Peer Initialization Completing the Wizard Securing the Publisher Configuring the PAL Identity Columns Adding a Node to an Existing Topology Well-Connected Topology Monitoring and Troubleshooting Diagnosing and Resolving Database Server Errors Using the Dedicated Administrator Connection (DAC) SQL Server Error Logs Error Messages in Event Logs Setting up Alerts in SQL Server Agent Advantages of Using Alerts Configuring the Alert Monitoring SQL Server Agent Job History Job Activity Monitor Gathering Performance and Optimization Data Using DMVs DMV Permission Requirements Inspecting a Server with DMVs Troubleshooting Bottlenecks Collecting Information using DMVs SQL Server 2005 Performance Dashboard Reports DMVStats Implementing Service Brokers Understanding Service Brokers Service Broker Advantages Service Broker Applications Service Broker Messaging Service Broker Uses Designing Service Broker Applications Message Types Service Broker Activation Transaction Life Cycle Handling Errors Handling Poison Messages Service Broker Security Programming Service Broker Applications Enabling Service Broker Creating Message Types Creating Contracts Creating the Queue Creating the Service Creating Routes Creating Service Broker Endpoints Implementing Applications

Top of page

---

 

SQL Server 2005: Additional Topics [ Administrator Track ]

Developing a database, getting the database to work with your applications, and getting it to production is just the beginning, but can often be where the story ends. This course examines other SQL Server 2005 essential application, support, and maintenance topics— features you can use for high-availability, stability, reliability, and scalability of databases, plus enhancements for processing large amounts of data, handling complex data structures, implementing asynchronous messaging, and working with XML.

Audience: Database developers and administrators.

Prerequisites: A basic understanding of Windows operating systems, security, networking technologies, SQL Server, T-SQL, Visual Studio.NET, and XML.

Course Contents

Disaster Recovery Planning a Backup Strategy Your Business Needs and Capabilities Considering Data Recovery Needs Availability Demands Data Usage Size of Database(s) Database Server Configuration Disaster Recovery Testing Recovery Time Considerations Backing Up Databases Backup Devices Creating Backups Recovery Models Types of Backup Scheduling Your Backups Scheduling System Database Backups Scheduling Simple Backups Scheduling Full and Bulk-Logged Backups Backing Up Related Databases Verifying Backup Media Using Checksums Using RESTORE VERIFYONLY Restoring Databases Roll Forward Set Restoring with the Simple Recovery Model Restoring with Full/Bulk-Logged Recovery Models Online Restore Using Database Snapshots Creating a Database Snapshot Reverting to a Database Snapshot Deleting a Database Snapshot Failover Clustering High Availability Overview Levels of Availability and Downtime Estimating Availability Requirements SQL Server 2005 Failover Cluster Preparing a Server Cluster Hardware and Software Requirements Special Considerations Creating a New Cluster Set Up Domain Service Accounts Prepare Static IP Addresses Configure Computer Servers Prepare Shared Disks Create the New Cluster Server Configure the Cluster Add the Distributed Transaction Coordinator Installing SQL Server 2005 Failover What to Expect Before Installing SQL Server Setup System Configuration Checks After Installing SQL Server Adding SQL Server Nodes Add a New Node to the Cluster Add a Node to the SQL Server Virtual Server Cluster Remove a SQL Server Virtual Server Node Database Mirroring Understanding Mirroring How Mirroring Works The Mirroring Sessions Database Mirroring Operating Modes Role Switching (Failover) Other Mirroring Details Database Mirroring Endpoints Client-Side Redirections Setting Up Database Mirroring Prerequisites Configuration Recommendations Configuring the Recovery Model Configuring Mirroring with SQL Server Management Studio and Transact-SQL Configuring the Endpoints Configuring the Operating Mode Setting Up the Mirror Server Database Backing Up the Principal Restoring Full Backup on the Mirror Restoring Transaction Log Backups on the Mirror Starting the Mirroring Session Working with a Mirror Session Accessing Mirror Data with Snapshots Monitoring Database Mirroring Viewing the Mirror, Witness, and Endpoints Log Shipping Overview Log Shipping Requirements Security Requirements Configuring Log Shipping Transaction Log Shipping Property Page Configuring the Primary Database Configuring the Secondary Database Configuring the Monitor Server Saving the Log Shipping Configurations Removing Log Shipping Monitoring Log Shipping Jobs Viewing the Agent Job History Transaction Log Shipping Status Report Querying Log Shipping System Tables Querying with Log Shipping Stored Procedures Configuring Log Shipping in Transact-SQL Configuring the Primary Database Configuring the Primary Backup Job Configuring the Secondary Database Configuring the Secondary Copy Job Configuring the Secondary Restore Job Removing Log Shipping Peer-to-Peer Replication High-Availability Requirements and Limitations Protecting Data with Partitioning Special Identity Columns Handling Configuring Peer-to-Peer Replication Security Planning Configuring the Distributor Configure Distribution Wizard Adding a Publisher Server Changing a Distributor’s Properties Securing the Distributor Creating the Publisher Specify the Distributor Publication Database Publication Type Articles Filter Table Rows and Snapshot Agent Agent Security Wizard Actions Complete the Wizard Creating Publication Allowing Peer-to-Peer Subscriptions Securing the Publisher The Replication Monitor Configuring Peer-to-Peer Subscribers Initializing the Subscriber Database Peer Distributor Preparation Securing the Distributor Configuring Peer-to-Peer Topology Publication Specification Peers Log Reader Agent Security Distribution Agent Security New Peer Initialization Completing the Wizard Securing the Publisher Configuring the PAL Identity Columns Adding a Node to an Existing Topology Well-Connected Topology

Monitoring and Troubleshooting Diagnosing and Resolving Database Server Errors Using the Dedicated Administrator Connection (DAC) SQL Server Error Logs Setting Up Alerts in SQL Server Agent Advantages of Using Alerts Configuring the Alert Monitoring SQL Server Agent Job History Job Activity Monitor Gathering Performance and Optimization Data Using DMVs DMV Permission Requirements Inspecting a Server with DMVs Troubleshooting Bottlenecks Collecting Information Using DMVs SQL Server 2005 Performance Dashboard Reports DMVStats Implementing Service Brokers Understanding Service Brokers Service Broker Advantages Service Broker Applications Service Broker Messaging Service Broker Uses Designing Service Broker Applications Message Types Service Broker Activation Transaction Life Cycle Handling Errors Handling Poison Messages Service Broker Security Programming Service Broker Applications Enabling Service Broker Creating Message Types Creating Contracts Creating the Queue Creating the Service Creating Routes Creating Service Broker Endpoints Implementing Applications 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 Ranked 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 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 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 with Complex Queries Keeping it Simple Maintaining Query Files Using Source Control and Versioning Using Visual SoruceSafe 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

Top of page

---

 

Exploring SQL Server 2008  [ Developer Track ]

This course looks at many of the new features within SQL Server 2008 and will give you a first look at the new powerful database server. You will learn about new T-SQL data types and syntax enhancements, see how to store and manipulate unstructured data, and learn about the SQL CLR and new advances in client data access. You’ll also take a first look at the new Visual Studio Team System Edition 2008 for Database Professional, and check out the new and improved administrative features in PowerShell, security, FullText search, and many more.

Audience: Database developers.

Prerequisites: No particular programming experience is required, but the course is taught from a developer's perspective.

 

Course Contents

You will learn to: Use the new features in Management Studio for administering a database, creating objects, and writing and debugging code. Apply what's new in Integration Services, Reporting Services, Service Broker and Analysis Services. Install or upgrade from an earlier version of SQL Server. Use the new data types, including spatial data and heirarchyID. Apply Sparse columns and column sets to effect databases size. Exploit Table-valued parameters. Use Grouping sets to streamline complex GROUP BY clauses. Utilize the MERGE statement to perform multiple operations in a single statement. Make use of enhancements to SQLCLR code. Utilize Visual Studio 2008 Team System for Database Professionals, or Data Dude, to build database projects. Operate newly integrated Full-Text Search. Introduction to SQL Server 2008 Overview New Features Across the Server Deprecated and Discontinued Features SQL Server 2008 Feature Pack Installing SQL Server 2008 T-SQL Enhancements New T-SQL Data Types T-SQL Syntax Sparse Columns and Column Sets Table-Valued Parameters Grouping Sets MERGE Statement Storing and Manipulating Unstructured Data T-SQL Enhancements New T-SQL Data Types T-SQL Syntax Sparse Columns and Column Sets Table-Valued Parameters Grouping Sets MERGE Statement Storing and Manipulating Unstructured Data Programmability SQL CLR Client Data Access Client Programming Occasionally Connected Applications Team System Edition for Database Professionals Administrator Features for Developers Indexed Search Across Rich Data Types Integrated FullText Search PowerShell Security Others

Top of page

---

 

SQL Server 2008 for Developers, plus Installing, Querying, and
Security [ Developer Track ]

In this course, you'll learn about the features that are available in SQL Server, how to design and create a database, and 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 how to use the Transact-SQL programming language for error handling and hierarchical queries, dealing with complex data structures and processes, how to make your databases more scalable through partitioning, and how to use .NET languages like Visual C# and Visual Basic to build database objects.
SQL Server 2008 includes a rich set of tools that go beyond the basics of querying and manipulating data. You'll learn how to take advantage of the user-friendly management console that integrates both authoring and administrative tasks. You'll learn how to take advantage of SQL Server's tools for analyzing and tuning your databases. You'll also learn about integration services, implementing security, and the new Business Intelligence (BI) suite.

In the second half of this course, you'll learn Installing, Querying and Security: You'll learn about the components that make up the comprehensive SQL Server 2008 product and the various editions that they make available, so that you can select the right one. You'll learn about installation and upgrade issues, about some of the many configuration choices available to customize it to your needs and environment, as well as how to tweak it after installation. Then you'll learn how to work with database objects and data, using Management Studio and other tools, as well as Transact SQL (T-SQL), the programming language of SQL server. You'll learn about the features in T-SQL for querying, inserting, updating, and deleting data, as well as the rich support in T-SQL for various data-based operations. The course finishes up with a comprehensive look at security, both to help you secure your server and data from unauthorized use as well as to avoid security hurdles as you administer and develop applications that use the server and its data.

Audience: Database developers.

Prerequisites: To get the most out of this course, you should have a solid understanding of relational databases and the concepts in the SQL Server 2008 Installing, Querying, and Security course. No particular programming experience is required, but the course is taught from a developer's perspective.

 

Course Contents

You will learn to: Understand the differences between views, stored procedures, triggers, and user-defined functions. How to write and use stored procedures to provide a safe, controlled, efficient way to execute Transact-SQL code to access and update data and database objects. Explore the various types of user-defined functions and how to support reusable database code. Discover how transactions can protect the integrity of your data and ways to handle errors that occur when code executes. Learn why using .NET code, using either Visual C# or Visual Basic, in the database overcomes many Transact-SQL limitations, and how SQL Server protects itself from misbehaving code. Use advanced querying techniques for grouping aggregated data, executing multiple actions with the MERGE statement, and others. Understand the complex data types in SQL Server, such as for spatial and hierarchical data, and how they support complex data operations. How to partition data to store data in ways that improve the efficiency of queries. See how the Visual Studio Team System for Database Professionals edition supports designing, creating, and deploying databases in a collaborative environment. Investigate the XML data type and its properties and methods, and how it supports both free form and relational data. Understand Business Intelligence to add reporting and analysis capabilities to your databases and applications. 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 Functions Overview Scalar Functions Inline Table-Valued Functions Multi-Statement Table-Valued Functions Using Functions, Views, and Stored Procedures Transactions and Error Handling Transaction Concepts Applications and Transactions Creating Explicit Transactions Using TRY/CATCH Error Handling

Using .NET Code in SQL Server Introduction Writing SQLCLR Code SQLCLR Code Modules SQLCLR Security T-SQL vs .NET Code Advanced SQLCLR Code Techniques Advanced SQLCLR Code Modules Managing Code Modules Advanced Query Techniques Full-Text Search Advanced T-SQL Techniques Executing Dynamic SQL Advanced Data Types Introduction The HierarchyID Data Type Sparse Columns and Column Sets FILESTREAM Storage Spatial Data Implementing Partitions SQL Server Partitions Creating Partitioned Tables Querying Patitions Managing Partitions Complex Querying Complex Queries Ranking Correlated SubQueries Common Table Expressions Advanced Techniques Complex Data and Structures Efficient Queries Working with Complex Queries Maintaining Query Files Design and Deployment with Visual Studio Team System for Database Professionals Creating Databases and Managing Projects Database and Project Management Working with XML XML Data Type XML Schema Collections Querying XML Best Practices Business Intelligence Services Introduction to Business Intelligence Services Using Integration Services Using Analysis Services Using Reporting Services

A Tour of SQL Server 2008 SQL Server 2008 Editions, Components, and Tools Using SQL Server Management Studio (SSMS) Working with Tables, Queries, Views Business Intelligence Services Installing SQL Server 2008 Preparing for Installation Upgrading an Earlier Version Installation Steps Configuring the Server Designing and Creating a Database Relational Database Design Principles Implementing the Design Create a SQL Server Database Create SQL Server Tables Creating Relationships Using a Database Diagram

Data Selection 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 with Transact-SQL Inserting Data Updating Data Understanding Transaction Isolation Working with SQL Server Management Studio Getting Started with Management Studio 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 Using Control of Flow Constructs Understanding and Implementing Security Security Overview Authentication Authorization Data Encryption Security Epilog

Top of page

---

 

SQL Transact-SQL Server 2008 [ Developer Track ]

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.

Audience: Database developers.

Prerequisites: To get the most out of the Microsoft T-SQL Server 2008 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 Contents

You will learn to: Understand the differences between the available editions of SQL Server 2008. Create a SQL Server database based on sound design principles. Create constraints, triggers, and indexes. Use Transact-SQL INSERT, UPDATE and DELETE statements. Configure and use SQL Server Management Studio (SSMS). Use Transact-SQL with data types, delimiters and variables. Understand the differences between views, stored procedures, triggers, and user-defined functions. How to write and use stored procedures to provide a safe, controlled, efficient way to execute Transact-SQL code to access and update data and database objects. Discover how transactions can protect the integrity of your data and ways to handle errors that occur when code executes. Understand the complex data types in SQL Server, such as for spatial and hierarchical data, and how they support complex data operations. How to partition data to store data in ways that improves the efficiency of queries. Investigate the XML data type and its properties and methods, and how it supports both free form and relational data. A Tour of SQL Server 2008 SQL Server 2008 Editions, Components, and Tools Using SQL Server Management Studio (SSMS) Working with Tables, Queries, and Views Business Intelligence Services Designing and Creating a Database Relational Database Design Principles Implementing the Design Working with SQL Server Management Studio Getting Started with Management Studio 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 Data Selection Queries The SELECT Statement The WHERE Clause Using ORDER BY to Sort Data The GROUP BY Clause Joining Tables

Advanced Data Types Introduction The HierarchyID Data Type Sparse Columns and Column Sets FILESTREAM Storage Spatial Data Complex Querying Working with NULL Values Ranking Grouped Data Writing Correlated Subqueries Using Common Table Expressions Modifying Data Modifying Data with Transact-SQL Inserting Data Updating Data Deleting Data Understanding Transaction Isolation Working with XML The XML Data Type XML Schema Collections Querying XML Best Practices Creating Views What Is a View? Creating Views Updating Data Using a View Using Computed Columns Indexed Views Partitioned Views Implementing SQL Server Partitions Overview of Table-Based Partitions Creating Partitioned Tables Querying Partitions Managing Partitions 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 Creating Stored Procedures and Triggers Creating Stored Procedures Creating Triggers Advanced Query Techniques Full-Text Search Advanced T-SQL Techniques Executing Dynamic SQL Advanced Techniques Complex Data and Structures Writing More Efficient Queries Working with Complex Queries Maintaining Query Files

Top of page

---

 

 

SQL Server 2005  [ Developer Track ]

In this course, you’ll learn about the features that are available in SQL Server; how to design and create a database; and 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.

Audience: Database developers.

Prerequisites: To get the most out of this SQL Server 2005 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 Contents

You will learn to: Install and configure Microsoft SQL Server 2005. Understand basic database design principles and how to implement them in SQL Server. The fundamentals of writing Transact-SQL queries that retrieve or modify data. Understand the differences between view, stored procedures, triggers, and user-defined functions. Perform calculations with Visual Basic and Visual C# code rather than T-SQL. Use the new Common Table Expressions for creating hierarchical queries. Understand the SQL Server security model. Configure and tune SQL Server, and monitor database activity. Support distributed users by implementing SQL Server Replication. Publish data to the Web, and take advantage of SQL Server’s rich support for XML. Understand Business Intelligence to add reporting and analysis capabilities. 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 Installing SQL Server 2005 Preparing for Installation Upgrading an Earlier Version Installation Steps Configuring the Server 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 Understanding and Implementing Security Security Overview Authentication Authorization Permissions Data Encryption Security Epilog Analyzing and Tuning Performance Evaluating Performance Monitoring with SQL Server Profiler Tuning Queries Indexes and Partitions Using the Database Engine Tuning Advisor Understanding SQL Server Performance Problems Automating Administrative Tasks SQL Server Agent Maintenance Plans SQL Management Objects (SMO) Programming Replication Overview of SQL Server Replication Replication Programming Interfaces Configuring Replication Synchronizing Data Using Integration Services Importing and Exporting Data Integration Services Tools Building a Package Troubleshooting a Package Analysis Services Understanding Analysis Services Creating a Unified Dimensional Model Data Mining Introduction to Reporting Services Understanding Reporting Services Configuring Reporting Services Building a Simple Report Creating, Publishing, and Viewing Reports Using and Managing Published Reports

Top of page

 

---

 

SQL Server 2005 for Developers  [ Developer Track ]

In this course, you'll learn how to develop projects for SQL Server. This includes how to design, create, modify, analyze and tune your databases. You will learn how to build basic queries using Transact-SQL and how to program using Transact-SQL, the language of SQL Server. 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. This course examines other SQL Server 2005 essential application, and support. Features you can use for high-availability, stability, reliability, and scalability of databases, plus enhancements for processing large amounts of data, handling complex data structures, and working with XML.

Audience: Database developers interested in gaining a more thorough knowledge of SQL Server 2005’s features.

Prerequisites: A working knowledge of SQL Server 2000 or 2005.

Course Contents

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) Installing SQL Server 2005 Preparing for Installation Hardware and Software Requirements Setting up the Service Accounts Multiple Instances Upgrading an Earlier Version Considerations Upgrade Advisor Installation Steps Installing Prerequisites System Configuration Checks Customizing the Installation Performing the Installation Configuring the Server SQL Server Configuration Manager SQL Server Surface Area Configuration Tool Server Configuration 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 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 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 an AFTER Trigger Creating an INSTEAD OF Trigger on a View Creating a DDL Trigger to Restrict Table Creation

Creating User-Defined Functions 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 Ways to Use SQLCLR Enabling SQLCLR 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 APLLY with FOR XML PATH Creating Recursive Queries Using a Common Table Expression (CTE) Creating Pivot Queries Rotating Column Data into 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 Using Integration Services What is SSIS? When to Use SSIS SSIS Architecture Importing and Exporting Data Integration Services Tools SQL Server Business Intelligence Development Studio (BIDS) BIDS Design Surfaces Execute Package Utility Building a Package Creating the Package Creating the Data Source connections Creating the Control and Data Flows Adding Control Flow Tasks Adding Data Flows Troubleshooting a Package Logging Visual Tools Data Views Breakpoints Other Debugging Tools Analysis Services Understanding Analysis Services OLAP Terminology Cubes, Dimensions, and Measures Cube Storage The Unified Dimensional Model Creating a Unified Dimensional Model Creating a Data Source View Creating a Cube Browsing and Enhancing the Cube Data Mining Microsoft Data Mining Algorithms Creating a Data Mining Structure Processing and Viewing a Data Mining Model Introduction to Reporting Services Understanding Reporting Services Server Components Client Components Configuring Reporting Services Installation Building a Simple Report The Report Wizard Creating, Publishing, and Viewing Reports Creating a Report Manually Creating a Data Source Adding Data to the Report Previewing the Report Publishing the Report Using and Managing Published Reports 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 of 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_RAND Function The NTILE Function Writing Correlated Subqueries Subquery Basics What is a Correlated Subquery? Using the WHERE Clause Introducing 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 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 with 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 OPEN XML Best Practices Relational vs. XML Data Model Storing XML in DQL Server 2005 Reasons to Index XML Columns Querying

Top of page

---

 

SQL Server 2005 Reporting Services [ Developer Track ]

In this course, you will learn how to use SQL Server 2005 Reporting Services to create, execute, and manage reports. You will learn how to create tabular, matrix (cross-tab), and chart reports using Visual Studio 2005 and SQL Server 2005 Business Intelligence Studio. You'll explore creating reports with groups, expressions, conditional formatting, and parameters. The reports you create in the course will employ shared data sources, interactive sorting, and drill-down capabilities. You'll explore the deployment and printing of reports and the export of reports to PDF, Excel, and other formats. You'll gain a thorough understanding of Reporting Services security, report snapshots, subscriptions, and the use of custom assemblies. You'll also discover how to integrate reports into your ASP.NET and WinForms applications using URL access and the Reporting Services Web Service API, without requiring users to use Internet Explorer. Finally, you'll learn how to create ad-hoc reports using Report Builder and Report Model projects.

Audience: SQL Server Users.

Prerequisites: Familiarity with databases and the SQL language. Knowledge of SQL Server, Visual Studio .NET/2005, C# or VB helpful, but not required.

Course Contents

Introducing SQL Server Reporting Services Tour of Features Reporting Services Architecture and Terminology Reporting Services Editions Taking Reporting Services for a Spin Creating Reports Deconstructing Reporting Services Shared Data Sources Creating Reports from Scratch Calculations and Formatting Creating Expressions Using the Global Collections Formatting Items Conditional Formatting Grouping and Sorting Creating Groups Calculating Totals and Percentages Interactive Sorting Creating Drill-Down Reportss Report Parameters Creating Report Parameters Creating Drop-Down Parameters Multi-Valued Parameters Debugging Parameter Issues Creating Matrix Reports and Charts Creating a Basic Matrix Report Matrix Subtotals Creating a Basic Chart Exploring the Charting Possibilities

Managing Reporting Services Deploying Reports and Data Sources Exporting Reports and Printing Using the Web-Based Report Manager Using SQL Server Management Studio-based Reporting Services Manager Reporting Services Security Two Parts to Reporting Services Security Securing Access to Reports Data Source Security Programming Reporting Services The Many Reporting Services APIs Integrating Reporting Services into Applications using URL Access Using the Report Viewer Controls Advanced Reporting Services Programming Using the Reporting Services Web Service Working with Custom Assemblies Snapshots and Subscriptions Caching and Snapshots Creating Standard Subscriptions Creating Data-Driven Subscriptions Managing Subscriptions Ad-Hoc Reporting with Report Builder The Report Builder Architecture Creating Report Model Projects Running Report Builder

Top of page

 

---

 

SQL Server 2005 T-SQL with Advanced Topics [ Developer Track ]

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.

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

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.

Course Contents

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

Top of page