Designing and Implementing SQL Server Tables and Views

Every great database starts with a great design and tables and views are the bedrock of that design. This course will show you how to create tables and views that use the right data types, constraints, and indexes to meet all business requirements.
Course info
Rating
(12)
Level
Intermediate
Updated
Mar 7, 2019
Duration
2h 25m
Table of contents
Course Overview
Introducing Tables and Views
Designing and Implementing Tables
Improving Table Design Through Normalization
Ensuring Data Integrity with Constraints
Designing View to Meet Business Requirements
Implementing Indexed Views
Implementing Partitioned Views
Summary
Description
Course info
Rating
(12)
Level
Intermediate
Updated
Mar 7, 2019
Duration
2h 25m
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.

About the author
About the author

Gerald is a multiple-year of the Microsoft MVP award, Gerald has led introductory classes in Python and SQL for industry-sponsored events at Ryerson University, Toronto and the University of Toronto (his alma mater). 

More from the author
Managing SQL Server Database Concurrency
Intermediate
1h 45m
Jun 7, 2019
Getting Started with Python on SQL Server
Intermediate
1h 25m
Apr 18, 2018
More courses by Gerald Britton
Section Introduction Transcripts
Section Introduction Transcripts

Course Overview
Hi everyone, my name is Gerald Britton, Welcome to my course, Designing Tables and Views in SQL Server. I'm a Senior Solutions Designer specializing in SQL Server database technologies. Did you know that at any given moment, there are more than 20, 000 openings for SQL Server developers and that many of those jobs pay over $100, 000 per year? And that's just in North America. Does that sound like a field you'd like to get into. In this course, we're going to work on fundamental skills needed by everyone who wants to get ready for prime time when it comes to database design and development. We'll do it all in the context of a growing business struggling with managing customer orders. Some of the major topics that we will cover include matching datatypes to usage with an eye on storage utilization, normalizing tables to eliminate redundancy and improve integrity, leveraging the power of data constraints to keep data clean, creating higher-level objects called views to improve reusability, and building high-performance views for growing data. By the end of this course, you'll know how to design the tables and views for a new database, as well as spot problems and improvement opportunities in the databases you may already be using. Before beginning this course, you should be familiar with writing simple database queries using SQL, the structured query language. When you are done, you should feel comfortable diving into advanced database design with courses on indexing, stored procedures, functions, and triggers, and performance analysis and troubleshooting. I hope you'll join me on this journey to learn database design with the Designing Tables and Views in SQL Server course at Pluralsight.

Introducing Tables and Views
Hello. Welcome to the course, Designing and Implementing Tables and Views in SQL Server. My name is Gerald Britton. I'm a Senior IT Solutions Designer, SQL Server Specialist, and Pluralsight Author. This course is packed with essential information to get you started designing tables and views, and not just for SQL Server, although that will be our environment. There are differences between SQL dialects to be sure, but most share the same core concepts. In this introductory module, I'll take a few moments to review the background of relational table design and how that has developed since first introduced. I'll also kick the tires of Azure Data Studio, the IDE I'll be using throughout this course. Then, I'll open up a common business problem that we will use throughout the course to develop a good set of tables and views that follow industry best practices. Finally, I'll give you a brief overview of the modules that follow so that you can easily find your way.

Improving Table Design Through Normalization
Hello. Welcome back to the course, Designing and Implementing Tables and Views in SQL Server. My name is Gerald Britton. In the previous module, we created a table to hold order data for Bob's Shoes. We covered a lot. Still, I have the feeling that there are some issues with this table that you'll see in a moment. Fortunately, there are standard methods to fix these. That approach is called normalization. Here I'm not talking about the common, everyday idea of being normal, whatever that is. In the world of relational database systems, normalization is a rigorous mathematical concept that when applied correctly can reduce redundancy and improve integrity. So in this module, I'm going to be talking about normalization. Okay, you knew that. So what will we cover? First off, I'll talk about the motivation for normalization, and I'll do that by revisiting the table design from the previous module and note some problems with it. Then I'll give a formal definition of normalization and briefly talk about its history. The bulk of this module will be talking about the various normal forms available. As I describe each one, I'll change the design for Bob's Shoes to match. As you'll soon see, applying normalization can reduce duplication but also introduce a way to enforce referential integrity. I'll implement that using FOREIGN KEY constraints. Now let's revisit our table design so you can see a problem or two that I want to address.

Ensuring Data Integrity with Constraints
Hi. Welcome back to the course, Designing and Implementing Tables and Views in SQL Server. My name is Gerald Britton. Throughout this course, we've been refining the design for a database to hold an order processing system for Bob's shoe company. I started off with a naïve design with everything in one table. That meant a lot of repetition of things like names, addresses, and basic order information. And you also learned about the NULL constraint. In the previous module on normalization, we transfer on the design into third normal form or 3NF. To do that, I introduced two other constraints, the PRIMARY and FOREIGN KEY constraints. In this module, I'm going to explore these constraints in more detail and introduce three more constraints, the UNIQUE, CHECK, and DEFAULT constraints. Here's what's ahead. First, I'll revisit the NULL constraint, when to use it, and a little bit about an ongoing controversy about the nulls. The NOT NULL constraint should be paired with a default option, so that will come next. I'll revisit primary keys and go a little deeper into two ways of defining them and how to select good ones. What happens with FOREIGN KEY constraints when you update rows or delete them? I'll show you how to set up your foreign keys to handle that in a process known as cascading updates. In addition to primary and foreign keys, there is a third type of key constraint you need to know about, the UNIQUE constraint. And I'll finish up this module by talking about CHECK constraints. As I've been doing all along, I'll continue to modify the design of Bob's order system adding appropriate constraints as I go.

Designing View to Meet Business Requirements
Hello again. Welcome back to the course, Designing and Implementing Tables and Views in SQL Server. My name is Gerald Britton. In the previous three modules, I showed you the basics of designing tables, normalizing them up to the third normal form, and applying constraints to enforce data and referential integrity. At this point, we have a good set of tables to operate Bob's Shoes order system. One of the effects of normalizing tables is that it takes one or more joints to reconstruct the original, un-normalized View. Customer data goes to the Customers table, CityState data to the City table, inventory to the Stock table, and the orders themselves to the Orders and OrderItems tables. Correctly writing joins every time to get the right data the right way is a bit of an art. Give two developers the same task of joining these tables together to produce a report or view it in a browser, and you'll likely get two different solutions. That is at least a maintenance problem, at worst one of the joints may work most of the time but fail with some edge cases. Wouldn't it be nice if you could somehow take a best-of-breed query to join up these tables and encapsulate it so that everyone could use it, and you only have one thing to maintain. Well, that's exactly what views are for. In this module, you'll learn the basics of creating views to encapsulate queries and begin to discover other great uses for views.

Implementing Partitioned Views
Hello. Welcome back to the course, Designing and Implementing Tables and Views in SQL Server. My name is Gerald Britton. During this course, we've been building a solution to Bob's Shoes' expanding business. Now it's time to think about the long-term. Specifically, what happens with old orders several years into the new system? Normally you don't want to just throw that data away. At the same time, keeping years' old data in the current table will inevitably make the system run slower for some operations, especially maintenance operations like backup and CHECKDB. Also keep in mind that storage can be expensive. I'm not talking about drives you can buy at your local computer store or online. This is about server class storage to build a business on. Many enterprises use storage area networks or SANs to hold their persistent data. That's a good solution that scales well, but it can be expensive. On the other hand, storage does not have to be homogeneous. You can store current data on high-speed, solid-state drives and older data on cheaper spinning media. Still, you want to have the data available and queryable when the auditor comes knocking. Partition views offer a solution to this kind of problem. In this module, you'll learn how to create partition views and the requirements the tables must have to be part of a partitioned view.

Summary
Congratulations! You made it through the course, Designing and Implementing Tables and Views in SQL Server. The purpose of this course was to give you the essential tools you need to succeed as a database designer and developer. If you have mastered the material here, you are well on your way. In this final module, I want to summarize the topics that I've covered and point out some directions for future study. If you haven't already guessed it, I only just scratched the surface on designing tables. There is a lot left to explore. Plus, with every release of SQL Server, more features are added to fit the needs from small to large to world-spanning, big data projects. Before I get ahead of myself, though, here is what we covered.