Search for Training

Implementing a Microsoft SQL Server Database

Course Overview


Implementing a Microsoft SQL Server Database training course is intended for Microsoft SQL Server database developers who are responsible for implementing a database on SQL Server 2008 R2. This course, participants learn the skills and best practices on how to use SQL Server 2008 R2 product features and tools related to implementing a database server.

Course Content


Introduction to SQL Server and its Toolset

  • Introduction to SQL Server Platform
  • Working with SQL Server Tools
  • Configuring SQL Server Services

Lab

  • Verifying SQL Server Component Installation
  • Altering Service Accounts for New Instance
  • Enabling Named Pipes Protocol for Both Instances
  • Creating Aliases for AdventureWorks and Proseware
  • Ensuring SQL Browser is Disabled and Configure a Fixed TCP/IP Port

Working with Data Types

  • Using Data Types
  • Working with Character Data
  • Converting Data Types
  • Working with Specialized Data Types

Lab

  • Choosing Appropriate Data Types
  • Writing Queries With Data Type Conversions
  • Designing and Creating Alias Data Types

Designing and Implementing Tables

  • Designing Tables
  • Working with Schemas
  • Creating and Altering Tables

Lab

  • Improving the Design of Tables
  • Creating a Schema
  • Creating the Tables

Designing and Implementing Views

  • Introduction to Views
  • Creating and Managing Views
  • Performance Considerations for Views

Lab

  • Designing, Implementing and Testing the WebStock Views
  • Designing and Implementing the Contacts View
  • Modifying the AvailableModels View

Planning for SQL Server Indexing

  • Core Indexing Concepts
  • Data Types and Indexes
  • Single Column and Composite Indexes

Lab

  • Exploring Existing Index Statistics
  • Designing Column Orders for Indexes

Implementing Table Structures in SQL Server

  • SQL Server Table Structures
  • Working with Clustered Indexes
  • Designing Effective Clustered Indexes

Lab

  • Creating Tables as Heaps
  • Creating Tables with Clustered Indexes
  • Comparing the Performance of Clustered Indexes vs. Heaps

Reading SQL Server Execution Plans

  • Execution Plan Core Concepts
  • Common Execution Plan Elements
  • Working with Execution Plans

Lab

  • Actual vs. Estimated Plans
  • Identify Common Plan Elements
  • Query Cost Comparison

Improving Performance through Nonclustered Indexes

  • Designing Effective Nonclustered Indexes
  • Implementing Nonclustered Indexes
  • Using the Database Engine Tuning Advisor

Lab

  • Reviewing Nonclustered Index Usage
  • Improving Nonclustered Index Designs
  • Using SQL Server Profiler and Database Engine Tuning Advisor
  • Nonclustered Index Design

Designing and Implementing Stored Procedures

  • Introduction to Stored Procedures
  • Working With Stored Procedures
  • Implementing Parameterized Stored Procedures
  • Controlling Execution Context

Lab

  • Creating Stored Procedures
  • Creating a Parameterized Stored Procedure
  • Altering the Execution Context of Stored Procedures

Merging Data and Passing Tables

  • Using the MERGE Statement
  • Implementing Table Types
  • Using Table Types as Parameters

Lab

  • Creating a Table Type
  • Using a Table Type Parameter
  • Using a Table Type with MERGE

Creating Highly Concurrent SQL Server Applications

  • Introduction to Transactions
  • Introduction to Locks
  • Management of Locking
  • Transaction Isolation Levels

Lab

  • Detecting Deadlocks
  • Investigating Transaction Isolation Levels

Handling Errors in T-SQL Code

  • Designing T-SQL Error Handling
  • Implementing T-SQL Error Handling
  • Implementing Structured Exception Handling

Lab

  • Replacing @@ERROR Based Error Handling With Structured Exception Handling
  • Adding Deadlock Retry Logic to the Stored Procedure

Designing and Implementing User-Defined Functions

  • Designing and Implementing Scalar Functions
  • Designing and Implementing Table-valued Functions
  • Implementation Considerations for Functions
  • Alternatives To Functions

Lab

  • Formatting Phone Numbers
  • Modifying an Existing Function
  • Resolving a Function-related Performance Issue

Ensuring Data Integrity through Constraints

  • Enforcing Data Integrity
  • Implementing Domain Integrity
  • Implementing Entity and Referential Integrity

Lab

  • Designing Constraint
  • Testing the Constraints

Responding to Data Manipulation via Triggers

  • Designing DML Triggers
  • Implementing DML Triggers
  • Advanced Trigger Concepts

Lab

  • Creating the Audit Trigger
  • Improving the Audit Trigger

Implementing Managed Code in SQL Server

  • Introduction to SQL CLR Integration
  • Importing and Configuring Assemblies
  • Implementing SQL CLR Integration

Lab

  • Assessing Proposed CLR Code
  • Implementing a CLR Assembly
  • Implementing a CLR User-defined Aggregate and User-defined Type

Storing XML Data in SQL Server

  • Introduction to XML and XML Schemas
  • Storing XML Data and Schemas in SQL Server
  • Implementing the XML Data Type

Lab

  • Appropriate Usage of XML Data Storage in SQL Server
  • Investigating the Storage of XML Data in Variables
  • Investigating the Use of XML Schema Collections
  • Investigating the Creation of Database Columns Based on XML

Querying XML Data in SQL Server

  • Using the T-SQL FOR XML Statement
  • Getting Started with XQuery
  • Shredding XML

Lab

  • Learning to Query SQL Server Data as XML
  • Writing a Stored Procedure Returning XML
  • Writing a Stored Procedure that Updates Using XML

Working with SQL Server Spatial Data

  • Introduction to Spatial Data
  • Working with SQL Server Spatial Data Types
  • Using Spatial Data in Applications

Lab

  • Familiarity With Geometry Data Type
  • Adding Spatial Data to an Existing Table
  • Business Application of Spatial Data

Working with Full-Text Indexes and Queries

  • Introduction to Full-Text Indexing
  • Implementing Full-Text Indexes in SQL Server
  • Working with Full-Text Queries

Lab

  • Implementing a Full-Text Index
  • Implementing a Stoplist
  • Creating a Stored Procedure to Implement a Full-Text Search

Customer Reviews


Thanks to Xpertised and the tutor who walked me through all the topics with Practical exposure which is helping me in my current project.
-Waseem

Course was quite helpful in terms of understanding of concepts and practicality. Its really a very friendly environment to learn. The timing were mutually chosen, as we both are working professional. I am quite satisfied with the course.
-Tanmoy

...more
Share:

For Batch Details
Call us at: +91 7259222234

Not sure? Consult Our Experts

What is the sum of 3 + 9? (security question)

Looking for a Training for

Myself

My Team/Organization

I agree to be contacted over mail or phone

or
Call us at: +91 7259222234

Subscribe to our weekly newsletter