SQL Server 2012 for Developers

Course:  SSDEV
Duration:  5 Days
Level:  I
Course Summary

This course teaches developers how to use SQL Server 2012 and how to create logical table designs, indexes and query plans. The creation of database objects including views, stored procedures, along with parameters, and functions is included. Transactions, concurrency, error handling, triggers, and SQL CLR are also covered in this course.

New features of SQL Server included:

  • Use of SC collations
  • Use of TRY_PARSE, TRY_CONVERT
  • Detailed discussion on IDENTITY constraints
  • Working with SEQUENCES
  • Use of THROW and comparisons to RAISERROR
  • Full-globe and arc support in spatial
  • Customizable nearness in full-text
« Hide The Details
Topics Covered In This Course

Introduction to SQL Server 2012 and its Toolset

  • Introduction to the SQL Server Platform
  • Working with SQL Server Tools
  • Configuring SQL Server Services
  • Verifying SQL Server Component Installation
  • Altering Service Accounts for New Instance
  • Enabling Named Pipes Protocol for Both Instances
  • Creating an Alias for AdvDev
  • Ensuring SQL Browser is Disabled

Working with Data Types

  • Using Data Types
  • Working with Character Data
  • Converting Data Types
  • Specialized Data Types
  • 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

Ensuring Data Integrity through Constraints

  • Enforcing Data Integrity
  • Implementing Domain Integrity
  • Implementing Entity and Referential Integrity
  • Designing Constraints
  • Testing the constraints

Indexing

  • Core Indexing Concepts
  • Data Types and Indexes
  • Single Column and Composite Indexes
  • Exploring existing index statistics
  • Designing column orders for indexes

Implementing Table Structures

  • SQL Server Table Structures
  • Working with Clustered Indexes
  • Designing Effective Clustered Indexes
  • Creating Tables as Heaps
  • Creating Tables with Clustered Indexes
  • Comparing the Performance of Clustered Indexes vs. Heaps

Execution Plans

  • Execution Plan Core Concepts
  • Common Execution Plan Elements
  • Working with Execution Plans
  • Actual vs. Estimated Plans
  • Identifying Common Plan Elements
  • Querying Cost Comparison

Improving Performance through Nonclustered Indexes

  • Designing Effective Nonclustered Indexes
  • Implementing Nonclustered Indexes
  • Using the Database Engine Tuning Advisor
  • Working with SQL Server Profiler and Database Engine Tuning Advisor

Views

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

Stored Procedures

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

Merging Data and Passing Tables

  • Using the MERGE Statement
  • Implementing Table Types
  • Using TABLE Types as Parameters
  • Creating a Table Type
  • Using a Table Type Parameter
  • Using a Table Type with MERGE

User-Defined Functions

  • Overview of Functions
  • Designing and Implementing Scalar Functions
  • Designing and Implementing Table-Valued Functions
  • Implementation Considerations for Functions
  • Alternatives to Functions
  • Performance Considerations

Concurrent Applications

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

Handling Errors in T-SQL Code

  • Understanding T-SQL Error Handling
  • Implementing T-SQL Error Handling
  • Implementing Structured Exception Handling
  • Replacing @@ERROR based error handling with structured exception handling
  • Adding deadlock retry logic to the stored procedure

Triggers

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

Managed Code

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

Storing XML Data

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

Querying XML Data

  • Using the T-SQL FOR XML Statement
  • Getting Started with XQuery
  • Shredding XML
  • Writing a stored procedure returning XML
  • Writing a stored procedure that updates using XML

Spatial Data

  • Introduction to Spatial Data
  • Working with SQL Server Spatial Data Types
  • Using Spatial Data in Applications
  • Geometry Data Type
  • Adding Spatial Data to an Existing Table

Full-Text Indexes and Queries

  • Introduction to Full-Text Indexing
  • Implementing Full-Text Indexes in SQL Server
  • Working with Full-Text Queries
  • Implementing a stoplist
What You Can Expect

At the end of this course, students will be able to:

  • Understand the entire SQL Server platform and its major tools.
  • Determine appropriate data types to be used when designing tables, converting data between data types, and creating alias data types.
  • Recognize good design practices regarding SQL Server tables and be able to create tables using T-SQL, including partitioned tables.
  • Implement PRIMARY KEY, FOREIGN KEY, DEFAULT, CHECK and UNIQUE constraints, and investigate cascading FOREIGN KEY constraints.
  • Determine appropriate single column and composite indexes strategies.
  • Create tables as heaps and tables with clustered indexes.
  • Read and interpret details of common elements from execution plans.
  • Design effective non-clustered indexes.
  • Design and implement views
  • Design and implement stored procedures.
  • Work with table types, table valued parameters and use the MERGE statement to create stored procedures that update data warehouses.
  • Design and implement functions, both scalar and table-valued.
  • Perform basic investigation of a deadlock situation.
  • Use both traditional T-SQL error handling code and structured exception handling.
  • Design and implement DML triggers
  • Determine appropriate uses for SQL CLR integration and implement an existing .NET.
Who Should Take This Course

IT Professionals who want to become skilled on SQL Server 2012 product features and technologies for implementing a database.

Training Style

Instructor led with 50% lecture and 50% lab.

« Hide The Details
Related Courses
Code Course Title Duration Level
SSQRY
Querying Microsoft SQL Server 2012
5 Days
I
Details

Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.

You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.

Schedule For This Course
There are currently no public sessions scheduled for this course. We can schedule a private class for your organization just a couple of weeks from now. Or we can let you know the next time we do schedule a public session.
Notify me the next time this course is confirmed!
Can't find the course you want?
Call us at 800.533.3893, or
email us at info@verhoef.com