Course Description
Students will learn logical table design, indexing and query plans, as well as the creation of database objects including views, and stored procedures, along with parameters, and functions. Also covered is procedure coding.
After completing this course, students will be able to:- Introduce the entire SQL Server platform and its major tools. It will cover editions, versions, basics of network listeners, and concepts of services and service accounts.- Determine appropriate data types to be used when designing tables, convert data between data types, and create alias data types.- Be aware of good design practices regarding SQL Server tables and be able to create tables using T-SQL. (Note: partitioned tables are not covered).- 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. Also consider the design of a table and suggest an appropriate structure.- 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. (Also describe where they can lead to performance issues).- Perform basic investigation of a deadlock situation and learn how transaction isolation levels affect application concurrency.- Use both traditional T-SQL error handling code and structured exception handling.- Design and implement DML triggers- Learn appropriate uses for SQL CLR integration and implement an existing .NET assembly within SQL Server.- Store XML data and schemas in SQL Server.- Perform basic queries on XML data in SQL Server.- Work with the GEOGRAPHY and GEOMETRY data types- Implement and query a full-text index.
The primary audience for this course is IT Professionals who want to become skilled on SQL Server 2012 product features and technologies for implementing a database.
Agenda
Introduction to SQL Server 2012 and its Toolset
- Introduction to the SQL Server Platform
- Working with SQL Server Tools
- Configuring SQL Server Services
- Lab : Introduction to SQL Server and its Toolset
Working with Data Types
- Using Data Types
- Working with Character Data
- Converting Data Types
- Specialized Data Types
- Lab : Working with Data Types
Designing and Implementing Tables
- Designing Tables
- Working with Schemas
- Creating and Altering Tables
- Lab : Designing and Implementing Tables
Ensuring Data Integrity through Constraints
- Enforcing Data Integrity
- Implementing Domain Integrity
- Implementing Entity and Referential Integrity
- Lab : Ensuring Data Integrity through Constraints
Planning for SQL Server 2012 Indexing
- Core Indexing Concepts
- Data Types and Indexes
- Single Column and Composite Indexes
- Lab : Planning for SQL Server Indexing
Implementing Table Structures in SQL Server 2012
- SQL Server Table Structures
- Working with Clustered Indexes
- Designing Effective Clustered Indexes
- Lab : Implementing Table Structures in SQL Server
Reading SQL Server 2012 Execution Plans
- Execution Plan Core Concepts
- Common Execution Plan Elements
- Working with Execution Plans
- Lab : Reading SQL Server Execution Plans
Improving Performance through Nonclustered Indexes
- Designing Effective Nonclustered Indexes
- Implementing Nonclustered Indexes
- Using the Database Engine Tuning Advisor
- Lab : Improving Performance through Nonclustered Indexes
Designing and Implementing Views
- Introduction to Views
- Creating and Managing Views
- Performance Considerations for Views
- Lab : Designing and Implementing Views
Designing and Implementing Stored Procedures
- Introduction to Stored Procedures
- Working With Stored Procedures
- Implementing Parameterized Stored Procedures
- Controlling Execution Context
- Lab : Designing and Implementing Stored Procedures
Merging Data and Passing Tables
- Using the MERGE Statement
- Implementing Table Types
- Using TABLE Types As Parameters
- Lab : Passing Tables and Merging Data
Designing and Implementing User-Defined Functions
- Overview of Functions
- Designing and Implementing Scalar Functions
- Designing and Implementing Table-Valued Functions
- Implementation Considerations for Functions
- Alternatives to Functions
- Lab : Designing and Implementing User-Defined Functions
Creating Highly Concurrent SQL Server 2012 Applications
- Introduction to Transactions
- Introduction to Locks
- Management of Locking
- Transaction Isolation Levels
- Lab : Creating Highly Concurrent SQL Server Applications
Handling Errors in T-SQL Code
- Understanding T-SQL Error Handling
- Implementing T-SQL Error Handling
- Implementing Structured Exception Handling
- Lab : Handling Errors in T-SQL Code
Responding to Data Manipulation via Triggers
- Designing DML Triggers
- Implementing DML Triggers
- Advanced Trigger Concepts
- Lab : Responding to Data Manipulation via Triggers
Implementing Managed Code in SQL Server 2012
- Introduction to SQL CLR Integration
- Importing and Configuring Assemblies
- Implementing SQL CLR Integration
- Lab : Designing and Implementing Views
Storing XML Data in SQL Server 2012
- Introduction to XML and XML Schemas
- Storing XML Data and Schemas in SQL Server
- Impleme
Comments
Virtual 10776 Developing Microsoft SQL Server 2012 Databases Online Live training option
Imagine an industry-leading classroom training experience from wherever you have access to the Internet.
The Virtual Online Live events listed here gives you an effective and proven online virtual learning experience with the freedom to attend virtually from anywhere.
There is also an option of experiencing Online Live from one of the many national partner centers where a computer and headset will be provided for you.
This rich and engaging virtual classroom environment lets you conveniently interact with instructors and other students.
You will hear and see your instructor as they teach the course and answer your questions via voice or text.
Choose the online live virtual training option and you can access the recorded version even after the class has finished.
The onDemand labs give you access to the same client and server technologies covered in class so you can test, apply and hone your skills by accessing the labs before and even after the class.
Technical Requirements: a computer or laptop with a modern browser and high speed internet access along with one or two monitors.