Microsoft SQL Server Development & Performance

SELECT Fundamentals

The SELECT statement is very important in T-SQL. It allows to retrieve data from tables and views. But that data may need to be filtered, both in the rows and columns we return, and sorted. Another crucial concept discussed in this module is that of expressions, which allow us to combine multiple columns into one computed column before we return the result.

• Basic SELECT Statement
• T-SQL Expressions
• Formatting Result Sets
• The CASE Statement
• Fetching Data from Tables
• Filtering Data
• Sorting Data
• Combining Multiple Result Sets: UNION, EXCEPT and INTERSECT
• LAB: Selecting from the AdventureWorks Database

Aggregating Data

Often the data stored in the database is more detailed than the result we are interested in. This module shows how to aggregate multiple values into a group, and how to compute aggregated values on top of these groups. The following topics are covered in this module:

• Using Aggregation Functions
• GROUP BY Fundamentals
• Filtering on Groups Using the HAVING Clause
• Computing Subtotals Using the ROLLUP and CUBE Clauses
• LAB: Aggregating Data

Joining Multiple Tables

Most operational databases are normalized: the data is spread over multiple tables. But when building reports, we need to join these different tables to get the answer we need. That's why mastering the different techniques to combine tables in a single result is crucial. In this module we cover two important ways: via joins and via nested queries.

• Database Normalization
• Inner Join
• Left, Right and Full Outer Join
• Cross Join
• Joining a table with itself
• Creating Nested Queries
• LAB: Joining Contacts, Employees and More

Modifying Data

Sometimes there is the need to insert new data into the database. Or old data needs an update or has to be deleted. This module shows you the basics of how this can be done. The following topics are covered in this module:

• Understanding Transactions
• Inserting Data
• Updating Existing Records
• Deleting Data
• Truncating a Table
• Intro Into Constraints
• LAB: Updating Customers in AdventureWorks

Ensuring Data Integrity through Constraints

The quality of data in your database largely determines the usefulness and effectiveness of applications that rely on the success or failure of an organization or a business venture could depend on it. Ensuring data integrity is a critical step in maintaining high-quality data. You should enforce data integrity at all levels of an application from first entry or collection through storage. Microsoft SQL Server data management software provides a range of features to simplify the job.

• Enforcing Data Integrity
• Implementing Data Domain Integrity
• Implementing Entity and Referential Integrity
• Lab : Using Data Integrity Through Constraints

Introduction to Indexes

An index is a collection of pages associated with a table. Indexes are used to improve the performance of queries or enforce uniqueness. Before learning to implement indexes, it is helpful to understand how they work, how effective different data types are when used within indexes, and how indexes can be constructed from multiple columns. This module discusses table structures that do not have indexes, and the different index types available in Microsoft SQL Server.

• Core Indexing Concepts
• Data Types and Indexes
• Heaps, Clustered, and Nonclustered Indexes
• Single Column and Composite Indexes
• Lab : Implementing Indexes

Query execution and query plans

Having an index is one thing, using the index is another story: how can we see which indexes SQL Server uses and how it's using them? Execution plans are the answer to that question. We discuss in this part of the training how to get execution plans and how to analyze them. This is done using the traditional techniques we already have in SQL Server for many years as well as with the Query Store, which is new since SQL Server 2016 and is also available in Azure SQL Databases.

• Execution context
• Execution plans
• Plan cache and plan reuse
• Plan recompilation
• Parameterization
• Query store
• Adaptive Query Processing
• Automatic regressed plan detection
• LAB: Analyzing query plans

Designing and Implementing Views

This module describes the design and implementation of views. A view is a special type of query-one that is stored and can be used in other queries-just like a table. With a view, only the query definition is stored on disk; not the result set. The only exception to this is indexed views, when the result set is also stored on disk, just like a table. Views simplify the design of a database by providing a layer of abstraction, and hiding the complexity of table joins. Views are also a way of securing your data by giving users permissions to use a view, without giving them permissions to the underlying objects. This means data can be kept private, and can only be viewed by appropriate users.

• Introduction to Views
• Creating and Managing Views
• Performance Considerations for Views
• Lab : Designing and Implementing Views

Designing and Implementing Stored Procedures

This module describes the design and implementation of stored procedures.

• Introduction to Stored Procedures
• Working with Stored Procedures
• Implementing Parameterized Stored Procedures
• Controlling Execution Context
• Lab : Designing and Implementing Stored Procedures

SQL Server Concurrency

Concurrency control is a critical feature of multiuser database systems; it allows data to remain consistent when many users are modifying data at the same time. This module covers the implementation of concurrency in Microsoft SQL Server. You will learn about how SQL Server implements concurrency controls, and the different ways you can configure and work with concurrency settings.

• Concurrency and Transactions
• Locking Internals
• Lab : SQL Server Concurrency

Importing and Exporting Data

If you need to load large amounts of data from other systems, this module can help you identify the best options for doing so. Not only does it discuss different techniques (SSIS, BCP, ...), it also provides tricks to improve the performance of these data loads.

• Introduction to Transferring Data
• Importing and Exporting Table Data
• Copying or Moving a Database
• Deploy to DEV, TEST and PROD with DACPAC
• LAB: Importing and Exporting Data