SQL Server 2008 for Developers - MSS300

In this course, you will see an overview tour of SQL Server 2008 including its editions, components, and tools. Then you will learn about the steps to install, and/or upgrade to SQL Server 2008 and configuring the server. You will see how the principals of database design are used in creating of databases and then how to apply server tables to them. The course continues with SQL Server Management Studio and how to build effective views, stored procedures, triggers, and user-defined functions using Transact-SQL. Learn 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 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 tools for analyzing and tuning your databases. You will also learn about integration servers, implementing security, and the Microsoft Business Intelligence (BI) suite.

Course ID: MSS300
Duration: 5 days
Audience: Database developers
Prerequisites: None
Topics:

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)

Installing SQL Server 2008

  • Preparing for Installation
  • Hardware and Software Requirements
  • Setting up the Service Accounts
  • Multiple Instances
  • Upgrading an Earlier Version
  • Considerations
  • Upgrade Advisor
  • Installation Steps
  • Beginning the Installation
  • Customizing the Installation
  • Performing the Installation
  • Configuring the Server
  • SQL Server Configuration Manager
  • Server Configuration
  • Microsoft SQL Server 2008for Developers
  • Microsoft SQL Server 2008 for Developers

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 2008
  • 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

Modifying Data

  • Modifying Data with Transact-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 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
  • Microsoft SQL Server 2008 for Developers
  • 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
  • IF ELSE
  • BEGIN END
  • GOTO, RETURN, and Labels
  • CASE
  • WHILE
  • WAITFOR
  • Ranking Results

Understanding and Implementing Security

  • Security Overview
  • The Threats
  • SQL Server 2008 Security Design Philosophy
  • The Two Stages of Security
  • Authentication
  • 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
  • Encrypting Data
  • Security Epilog

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

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 in Management Studio
  • Creating and Executing Stored Procedures
  • Working with Parameters
  • Testing and Debugging Stored Procedures
  • Microsoft SQL Server 2008 for Developers
  • 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 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

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 a Stored Procedure
  • Handling Uncommittable Transactions with XACT_STATE

Using .NET Code in SQL Server

  • Ways to Use SQLCLR
  • Enabling SQLCLR
  • Writing SQLCLR Code
  • Creating SQLCLR Code Manually
  • Creating SQLCLR Code Using Visual Studio 2008
  • SQL Data Types
  • Accessing Local Data
  • Returning Results
  • SQLCLR Code Modules
  • Triggers
  • User-Defined Functions
  • SQLCLR Security
  • Code Access Security
  • SQLCLR Permission Sets
  • T-SQL vs. .NET Code

Advanced SQLCLR Code Techniques

  • Advanced SQLCLR Code Modules
  • Aggregates
  • Large Aggregates
  • Multiparameter Aggregates
  • User-Defined Types
  • Ordered Table-Valued Functions
  • Managing Code Modules
  • System Catalogs
  • Troubleshooting SQLCLR Code

Advanced Query Techniques

  • Full-Text Search
  • Configuring Full-Text Search
  • Writing Full-Text Queries
  • The CONTAINS Predicate
  • The FREETEXT Predicate
  • Using CONTAINSTABLE and FREETEXTTABLE
  • 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
  • Microsoft SQL Server 2008 for Developers
  • Overcoming PIVOT Limitations
  • Using Parameters with sp_executesql
  • Using QUOTENAME()
  • Using sp_executesql with Output Parameters
  • Signing Stored Procedures with Certificates

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 Spars 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
  • Using FILESTREAM
  • Spatial Data
  • Geometry vs. Geography
  • Spatial Data Standards
  • Types of Spatial Data
  • Using Spatial Data
  • Working with Spatial Data
  • Interactions Between Objects

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 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
  • 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

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
  • Microsoft SQL Server 2008 for Developers
  • Keeping it Simple
  • Maintaining Query Files
  • Using Source Control and Versioning
  • Using Visual SourceSafe

Design and Deployment with Visual Studio

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 2008
  • Reasons to Index XML Columns
  • Querying

Business Intelligence Services

  • Introduction to Business Intelligence Services
  • Using Integration Services
  • What is SSIS?
  • Importing and Exporting Data
 

Call Toll-free 877-245-6815 or 303-416-3056 to set up your IT Training today.