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