Developing SQL Server Databases

Paths

Developing SQL Server Databases

Authors: Gerald Britton, Gail Shaw, Mike McQuillan, Ryan Booz, Pinal Dave, Viktor Suha, Hugo Barona

Microsoft SQL Server is a relational database management system. It's main purpose is to store and retrieve data as needed by other software applications. This skill will teach... Read more

What You Will Learn

  • Designing and implementing tables, views, and other database objects
  • Implementing indexes
  • Concurrency management
  • Basic Optimization
  • Basic performance tuning and query analysis

Pre-requisites

  • Relational database literacy
  • Querying Data from SQL Server with T-SQL

Beginner

Design and implement database tables, constraints, views, and indexes.

Designing and Implementing SQL Server Tables and Views

by Gerald Britton

Mar 7, 2019 / 2h 25m

2h 25m

Start Course
Description

In this course, Designing and Implementing SQL Server Tables and Views, you will gain the foundational knowledge you need to match business requirements to the objects to be created in your database. First, you will learn about data types and which ones to use for your data. Next, you will discover data normalization and how to transform a basic design into third normal form. Following that, you will learn how to maximize data integrity by using constraints. Finally, you will explore three types of views and when to use each one. When you're finished with this course, you will have the skills and knowledge of table and view design needed to create great databases.

Table of contents
  1. Course Overview
  2. Introducing Tables and Views
  3. Designing and Implementing Tables
  4. Improving Table Design Through Normalization
  5. Ensuring Data Integrity with Constraints
  6. Designing View to Meet Business Requirements
  7. Implementing Indexed Views
  8. Implementing Partitioned Views
  9. Summary

Designing and Implementing SQL Server Database Indexes

by Gail Shaw

Aug 13, 2019 / 2h 11m

2h 11m

Start Course
Description

Poor database performance is a problem for applications and annoys users. Poor performance often comes down to poor indexing of critical tables. In this course, Designing and Implementing SQL Server Database Indexes, you'll gain knowledge on how to avoid these issues through the effective use of SQL Server database indexes. First, you'll discover the main index types that SQL Server has. Next, you'll explore considerations for their use. Finally, you'll learn how to choose indexes and index keys to best support your applications. When you've finished this course, you'll be in a good position to optimize all of your SQL Server databases and improve your applications' speed and throughput.

Table of contents
  1. Course Overview
  2. General Indexing Guidelines
  3. Understanding Basic Index Architecture and Index Usage
  4. Designing Indexes to Organize Tables
  5. Designing Indexes to Improve Query Performance: Part 1
  6. Designing Indexes to Improve Query Performance: Part 2
  7. Designing Indexed Views
  8. Designing Columnstore Indexes for Analytic Queries
  9. Summary and Further Reading

Intermediate

Program stored procedures, functions, and triggers. Manage concurrent transaction activity.

Programming SQL Server Database Stored Procedures

by Mike McQuillan

Mar 29, 2019 / 2h 1m

2h 1m

Start Course
Description

Ever wondered how to create repeatable, consistent, secure code in SQL Server? If you have, it sounds like you need to learn about stored procedures. In this course, Programming SQL Server Database Stored Procedures, you will gain the ability to create and manage stored procedures in Microsoft SQL Server. First, you will learn what a stored procedure is and how to create and manage simple procedures using SQL Server Management Studio. Next, you will discover how powerful stored procedures can be when you use parameters. Stored procedures support simple parameters like strings and integers, but you’ll also see how you can use your own custom data types as parameters with a stored procedure. Then, you will return data from stored procedures, using record sets and output parameters. Finally, you will explore how to debug your stored procedures as you write them, starting off with the basic PRINT statement before moving on to stepping through code using SQL Server Management Studio. When you’re finished with this course, you will have the skills and knowledge of SQL Server needed to create and manage your own stored procedures. Software required: SQL Server Development Edition, SQL Server Management Studio

Table of contents
  1. Course Overview
  2. Creating Your First Stored Procedure
  3. Creating Stored Procedures and Using Parameters
  4. Table-valued Parameters and Refactoring
  5. Debugging and Troubleshooting Stored Procedures

Programming SQL Server Database Triggers and Functions

by Ryan Booz

Jun 17, 2019 / 3h 20m

3h 20m

Start Course
Description

As a SQL Server Database Developer, it is often necessary to have business-layer logic that exists within the database to ensure clean, consistent data. Although constraints and foreign key relationships can go a long way to provide the essential relational guarantees, Triggers provide the ability to validate and act on data at the time of modification, while Functions help you provide consistent methods for accessing the data across applications.

In this course, Programming SQL Server Triggers and Functions, you will learn foundational knowledge to better administer and develop efficient, high-performing Triggers and Functions. First, you will learn how to develop DML, DDL, and LOGON Triggers, and how to avoid common mistakes. Next, you will discover the differences between Multi-Statement and Single-Statement functions and how to write the most efficient functions possible. Finally, you will explore some lesser-known features and upcoming improvements in newer versions of SQL Server that can improve your overall experience with Triggers and Functions. When you are finished with this course you will have the skills and knowledge needed to administer and develop efficient, high-performing Triggers and Functions.

Table of contents
  1. Course Overview
  2. Validating and Modifying Data with DML Triggers
  3. Protecting the Database with DDL and Logon Triggers
  4. Working Smarter with Triggers
  5. Reusing Code with Functions
  6. Scaler and Table-valued Functions
  7. Improving Function Performance with Inline Table-valued Functions

Managing SQL Server Database Concurrency

by Gerald Britton

Jun 7, 2019 / 1h 45m

1h 45m

Start Course
Description

Are you sometimes unsure about what type of database transaction or what isolation level makes sense for your application? Have you ever run into blocking problems or the dreaded deadlock? In this course, Managing SQL Server Database Concurrency, you will learn foundational knowledge of how SQL Server maximises the shared use of a database system while safeguarding the integrity of the data under its care and quickly delivering results to all applications that use it. First, you will learn how transactions are used and the various levels of isolation available. Next, you will discover how SQL Server uses locking to enable concurrency without affecting data integrity. Finally, you will explore how to analyze concurrency-affecting queries, including those that deadlock, and some techniques you can use to keep to avoid such issues. When you are finished with this course, you will have the skills and knowledge of concurrency management needed to explain database behavior, debug and fix blocking and deadlocking queries, and design queries that can do their jobs efficiently while minimizing their effects on other work being done simultaneously.

Table of contents
  1. Course Overview
  2. Introducing SQL Server Concurrency Control
  3. Understanding Transactions
  4. Managing Basic Isolation Levels
  5. Implementing Snapshot Isolation Levels
  6. Locking in the SQL Server Database Engine
  7. Optimizing Concurrency and Locking Behavior
  8. Summary

Using Memory-optimized Tables and Native Stored Procedures

by Pinal Dave

Jul 10, 2019 / 1h 58m

1h 58m

Start Course
Description

The In-Memory OLTP feature adds a new memory optimized RDBMS engine and a native compiled stored procedure. This new addition adds higher concurrency to the SQL Server and enables it to process a much higher amount of the workload than before. In this course, we will learn about how to get started with the memory optimized tables and build a use case around its effectiveness. Additionally, we will also understand how the efficiency of the stored procedure is increased when we use Native Stored Procedure along with Memory Optimized Tables.

Table of contents
  1. Course Overview
  2. Creating Memory Optimized Tables
  3. Optimizing Performance of In-Memory Tables
  4. Creating Natively Compiled Stored Procedures
  5. Collecting Execution Statistics for Natively Compiled Stored Procedure
  6. Summary

Advanced

Evaluate and optimize basic SQL Server performance.

Optimizing SQL Server Statistics and Indexes

by Gail Shaw

Apr 8, 2020 / 1h 49m

1h 49m

Start Course
Description

Index maintenance is a complex topic, and one that's crucial for a well-performing database. In this course, Optimizing SQL Server Statistics and Indexes, you will learn foundational knowledge on how to keep your database and indexes well-maintained. First, you will explore missing and unused indexes, looking at how the indexes used by the system change over time. Next, you will discover why indexes, rowstore, and columnstore need maintenance and how to best perform that maintenance. Finally, you will learn how to perform statistics maintenance, allowing you to ensure that the Query Optimizer gets the information it needs to generate good execution plans. When you are finished with this course, you will have the skills and knowledge of index maintenance needed to keep your database running smoothly.

Table of contents
  1. Course Overview
  2. Overview of Index Maintenance
  3. Review Index Usage and Identify Potential Missing Indexes
  4. Maintaining Rowstore Indexes
  5. Maintaining Columnstore Indexes
  6. Maintaining Statistics
  7. Summary and Further Reading

Analyzing SQL Server Query Plans

by Pinal Dave

Oct 17, 2019 / 2h 30m

2h 30m

Start Course
Description

Every database server runs different workloads and queries, and it's important for a SQL Server Performance Tuning Expert to identify queries that are negatively impacting performance. In this course, Analyzing SQL Server Query Plans, you will gain the ability to identify performance bottlenecks on your database. First, you will learn to analyze query plans. Next, you will discover and understand query execution plans. Finally, you will explore how to resolve the performance bottleneck. When you are finished with this course, you will have the skills and knowledge of analyzing the query plans needed to troubleshoot SQL Server performance problems.

Table of contents
  1. Course Overview
  2. Capture Query Plans Using Extended Events and Traces
  3. Identify Poorly Performing Query Plan Operators
  4. Create Efficient Query Plans Using Query Store
  5. Compare Estimated and Actual Query Plans and Related Metadata
  6. Configure Azure SQL Database Performance Insight
  7. Summary

Managing SQL Server Database Performance

by Viktor Suha

Jul 30, 2019 / 2h 50m

2h 50m

Start Course
Description

SQL Server performance troubleshooting and optimization are often overwhelming, complex, and take a very long time in production environments. However, this should not be the case. By understanding the exact problem with proper communication and having the best methods and tools to address the problems, the efforts and resources invested into the entire process could be cut significantly or could even be fully prevented. In this course, Managing SQL Server Database Performance, you’ll gain a better understanding and address some of the key factors that can contribute to or impact database performance adversely, beyond the workload you run. First, you’ll explore how to approach performance and scalability issues in general and what methods are preferred for efficiency. Next, you’ll discover a few important SQL Server concepts to help you understand how things work under the hood, along with optimization techniques for server memory and tempdb configuration. Finally, you’ll learn what factors like sizing and choosing the proper service tier, you need to watch out for in Microsoft Azure. When you’re finished with this course, you’ll have the skills and knowledge to prevent major problems, troubleshoot, and optimize your SQL Server environment more efficiently to better manage your SQL Server database workloads.

Table of contents
  1. Course Overview
  2. Aiming for Performance and Scalability
  3. Understanding Key SQL Server Concepts
  4. Optimizing SQL Server Instance and Memory Configuration
  5. Optimizing Tempdb and User Database File Configuration
  6. Configuring SQL Server in Azure
  7. Troubleshooting and Baselining the Environment

Managing Azure SQL Server Database Performance

by Hugo Barona

Dec 11, 2019 / 1h 31m

1h 31m

Start Course
Description

When we talk about managing and tuning database performance, we know that these tasks used to require expertise and used to be time-consuming. Sometimes we would not achieve the best results in our first, or even second, or third attempt to fix the performance issue. In this course, Managing Azure SQL Server Database Performance, you will gain the ability to leverage Azure services and features to help you to manage and tune the performance of your Azure SQL databases without requiring expertise and significant efforts from you. These services and features are leveraging Azure Engineers team and artificial intelligence to perform these tasks for you and remove the responsibility from you to perform them. First, you will discover the different hosting options you have available in Azure to host your SQL databases. Next, you will learn the different services and features you have available to allow you to monitor and troubleshoot issues in your databases. Finally, you will explore how to use features, such as Performance Recommendations and Automatic Tuning. When you’re finished with this course, you will have the skills and knowledge of using Azure services and features to manage and tune the performance of your Azure SQL databases.

Table of contents
  1. Course Overview
  2. Hosting SQL Server Databases in Azure
  3. Monitoring and Troubleshooting Database Performance
  4. Tuning Azure SQL Performance Manually
  5. Improving the Performance of Azure SQL Databases with Automatic Tuning
Offer Code *
Email * First name * Last name *
Company
Title
Phone
Country *

* Required field

Opt in for the latest promotions and events. You may unsubscribe at any time. Privacy Policy

By providing my phone number to Pluralsight and toggling this feature on, I agree and acknowledge that Pluralsight may use that number to contact me for marketing purposes, including using autodialed or pre-recorded calls and text messages. I understand that consent is not required as a condition of purchase from Pluralsight.

By activating this benefit, you agree to abide by Pluralsight's terms of use and privacy policy.

I agree, activate benefit