This course focuses on designing a database structure using SQL Server 2014. You will learn how to translate business requirements into which SQL Server components are needed, how to design a physical database, and how to develop an index and partitioning strategy.
David Peter is a Microsoft Certified Master (MCM) and has over a decade of experience with database development and administration on SQL Server, the Microsoft Business Intelligence platform, and Big Data. He specializes in scalable architecture and performance tuning on large-scale data warehouses and business analytics solutions.
Why a Good Database Structure Matters Hi, my name is David Peter Hansen, and welcome to this course called "Design a Database Structure with SQL Server 2014. " This course is designed to help you study for the exam 70-465. Throughout this course, we're going to take a look at three main things. The first thing is, what is a database structure? What is it actually all about? We're going to take a look at what are some of the issues that we need to be aware of when we're designing a database structure? And last but not least, how can we build a really good database structure? This course is the first part of a number of courses that will help you study for exam 70-465, also known as Designing Databse Solutions for Microsoft SQL Server. The courses are, Design a Database Structure with SQL server 2014, Design a Database Environment, Design Database and Database Objects, Design Database Security, and Design a Troubleshooting and Optimization Solution with SQL server 2014. This is the first course, so let's get started.
Translating Business Requirements In this module, we are going to take a look at how we can translate the requirements of the business into a good structure for our database. Understanding the business requirements is key to make the right design choices and it is an essential part of the 70-465 certification. We're going to take a look at three things in this module. The first thing we need to understand is what type of application is the database going to be used for. Is it going to be used for an online transaction processing system or is it going to be used for business intelligence solution? These two type of application have significant different work loads, which we need to take into consideration when designing our database structure. The first step we need to take is to create a logical design which represents the business. Next, we will then take the logical design and create a physical design. The physical design is how we are actually going to be implementing the database. In the last part of this module, we will take a look at a method called normalization and how this can be used to create an optimal structure for our database design. We will also take a look at denormalization, which can help us improve query performance when reading a massive amount of data.
Designing Physical Database and Object Placement In this module we're going to take a look at how we can design the physical database structure and where we should place our database objects. Understanding this is an essential part of the 70-465 certification. We're going to take a look at three things in this module. The first thing we'll look at is how do we physically design our database. We will look at what are file groups and what kind of files can we have in our database. Then we're going to take a look at where should we place our database objects within these file groups. And last but not least, how should we configure our system databases.
Designing the Right Index Strategy In this module, we are going to take a look at how we can design a right index strategy for our database structure. Having the right index strategy is an essential part of gaining good performance. We're going to take a look at four things in this module. First, we will take a look at the difference between a heap and a clustered index. And we will take a look at the considerations we need to take into account when deciding a clustered index. Then we are going to take a look at nonclustered indexes. This is followed by columnstore indexes. And last but not least, we will take a look at XML indexes.
Designing the Right Partitioning Strategy In this module, we're going to take a look at how we can design a right partitioning strategy for our database structure. Having the right partitioning strategy will help us maintain our physical database structure, transfer and access subset of data faster, and could help improve query performance. If we have lost tables in our database, we should consider using partitioning. Let us take a look at three things in this module to better understand partitioning and how we can design the right partitioning strategy. First, we will take a look at the difference between horizontal and vertical partitioning. Then we're going to take a look at how table partitioning works. And last but not least, we will take a look at how we should store a partition table on our IO soft system.