OD20762: Developing SQL Databases

• Introduction to the SQL Server Platform
• SQL Server Database Development Tasks

Lab : SQL Server Database Development Tasks

After completing this module, you will be able to:

• Describe the SQL Server platform.
• Use SQL Server administration tools.

Module 2: Designing and Implementing TablesThis modules describes the design and implementation of tables. (Note: partitioned tables are not covered).Lessons

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

Lab : Designing and Implementing Tables

After completing this module, you will be able to:

• Design Tables
• Describe the various types of data
• Be able to work with schemas
• Be able to create and amend tables

Module 3: Advanced Table DesignsThis module describes more advanced table designs.Lessons

• Partitioning data
• Compressing Data
• Temporal Tables

Lab : Using Advanced Table Designs

After completing this module, you will be able to:

• Describe how data can be partitioned
• Describe how data can be compressed.
• Describe Temporal tables

Module 4: Ensuring Data Integrity through ConstraintsThis module describes the design and implementation of constraints.Lessons

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

Lab : Ensuring Data Integrity through Constraints

After completing this module, you will be able to:

• Describe how to enforce data integrity
• Describe how to implement domain integrity
• Describe how to implement entity and referential integrity

Module 5: Introduction to IndexesThis module describes the concept of an index and discusses selectivity, density and statistics. It covers appropriate data type choices and choices around composite index structures.Lessons

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

Lab : Implementing Indexes

After completing this module, you will be able to:

• Explain the need for indexes and describe the core concepts of index design
• Choose appropriate data types for indexes
• Design single column and composite indexes

Module 6: Designing Optimized Index StrategiesThis module includes covering indexes and the INCLUDE clause, hints, padding / fillfactor, statistics. It also execution plans and the DTE Lessons.Lessons

• Covering Indexes
• Managing Indexes
• Execution Plans
• Using the DTE

Lab : Designing Optimized Index Strategies

After completing this module, you will be able to:

• Choose an appropriate table structure
• Implement clustered indexes and heaps

Module 7: Columnstore IndexesThis module introduces Columnstore indexes.Lessons

• Introduction to Columnstore indexes
• Creating Columnstore indexes
• Working Columnstore indexes

Lab : Using Columnstore indexes

After completing this module, you will be able to:

• Describe suitable scenarios for Columnstore indexes
• Create clustered and non-clustered Columnstore indexes
• Describe considerations for updating non-clustered Columnstore indexed tables
•  

Module 8: Designing and Implementing ViewsThis module describes the design and implementation of views.Lessons

• Introduction to views
• Creating and managing views
• Performance considerations for views

Lab : Designing and Implementing Views

After completing this module, you will be able to:

• Explain the concept of views
• Design, Create and Alter Views
• Create Partitioned Views

Module 9: Designing and Implementing Stored ProceduresThis module describes the design and implementation of stored procedures.Lessons

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

Lab : Designing and Implementing Stored Procedures

After completing this module, you will be able to:

• Design, Create and Alter Stored Procedures
• Control the Execution Context of Stored Procedures
• Implement Stored Procedures that use Parameters

Module 10: Designing and Implementing User-Defined FunctionsThis module describes the design and implementation of functions, both scalar and table-valued. (Also discusses where they can lead to performance issues).Lessons

• 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

After completing this module, you will be able to:

• Design, create and alter scalar functions
• Design, create and alter table-valued functions
• Describe the performance impacts of functions

Module 11: Responding to Data Manipulation via TriggersThis module describes the design and implementation of triggers.Lessons

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

Lab : Responding to Data Manipulation via Triggers

After completing this module, you will be able to:

• Design DML triggers
• Implement DML triggers
• Work with Nested and Recursive Triggers

Module 12: Using In-Memory TablesThis module covers the creation of in-memory tables and native stored procedures. Furthermore, advantages of in-memory tables are discussed, for example the removal of transaction blocking.Lessons

• In-Memory tables
• Native Stored Procedures

Lab : In Memory OLTP

After completing this module, you will be able to:

•  Implement in-memory tables
• Implement native stored procedures

Module 13: Implementing Managed Code in SQL ServerThis module describes the implementation of and target use-cases for SQL CLR integration.Lessons

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

Lab : Implementing Managed Code in SQL Server

After completing this module, you will be able to:

• Describe SQL CLR Integration
• Detail appropriate use cases for SQL CLR Integration
• Implement SQL CLR code

Module 14: Storing and Querying XML Data in SQL ServerThis module covers the XML data type, schema collections, typed and un-typed columns and appropriate use cases for XML in SQL Server.Lessons

• Introduction to XML and XML Schemas
• Storing XML Data and Schemas in SQL Server
• Implementing the XML Data Type
• Using the T-SQL FOR XML Statement
• Getting Started with xQuery

Lab : Storing and Querying XML Data in SQL Server

After completing this module, you will be able to:

• Explain the purpose and structure of XML and XML schemas
• Describe how XML data and schemas can be stored in SQL Server
• Implement the XML data type

Module 15: Working with SQL Server Spatial DataThis module describes spatial data and how this data can be implemented within SQL Server.Lessons

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

Lab : Working with SQL Server Spatial Data

After completing this module, you will be able to:

• Describe how spatial data can be stored in SQL Server
• Use basic methods of the GEOMETRY and GEOGRAPHY data types
• Query databases containing spatial data

Module 16: Storing and Querying Blobs and Text Documents in SQL ServerThis module covers full text indexes and queries.Lessons

• Considerations for BLOB Data
• Working with FileStream
• Using Full-Text Search

Lab : Storing and Querying Blobs and Text Documents in SQL Server

After completing this module, you will be able to:

• Describe the need for full-text indexing in SQL Server
• Implement a full-text index
• Perform basic full-text queries