SQL Server: Why Physical Database Design Matters

This course shows how you to choose the right data types applicable to SQL Server developers and anyone who is responsible for designing and creating SQL Server tables and indexes, from SQL Server 2005 onward
Course info
Rating
(497)
Level
Intermediate
Updated
Sep 27, 2013
Duration
3h 56m
Table of contents
Introduction
Data Types and Row Size
Data Types and Index Size
Data Types and Query Performance
Description
Course info
Rating
(497)
Level
Intermediate
Updated
Sep 27, 2013
Duration
3h 56m
Description

This course is about how your database’s physical design either takes advantage of or is hindered by the way that the SQL Server platform works; knowing this can give you better long-term scalability, availability, and performance. Choosing the right data types when you're designing your columns, tables, and indexes is really critical. Using the wrong data type can cause more space to be required, affecting data density in memory, database and backup size, transaction log efficiency, and more. It's even more important when you're choosing your clustering keys, as the wrong choice there can cause nonclustered index sizes to balloon dramatically. It can even affect the performance of queries, when incompatible data types are used in comparisons and cause very costly operations to take place. This course will show you how to make the right choices and avoid all of these problems. It starts by explaining the various data structures that are used to store columns and rows, and how they can be affected by data type choice. Then it shows how data type choice factors into clustered and nonclustered index key choice. Finally it describes the implicit conversion and probe residual problems that can occur from mismatched data types used in queries. Packed with a wealth of information and practical, easy-to-follow demonstrations, this course will show you how to make the RIGHT choices to make sure you avoid all these common problems. The course is applicable for all SQL Server versions from SQL Server 2005 onward, and for SQL Server developers and anyone responsible for designing and creating SQL Server tables and indexes, with any level of experience.

About the author
About the author

Kimberly is a SQL Server MVP, Microsoft Regional Director and President/Founder of SQLskills.com, which she now runs with her husband, Paul Randal. Kimberly’s areas of expertise focus on performance tuning through effective database design and architecture.

More from the author
SQL Server: Indexing for Performance
Beginner
7h 11m
15 Sep 2017
Section Introduction Transcripts
Section Introduction Transcripts

Data Types and Index Size
Hi this is Kimberly Tripp from SQLskills. com and I'm recording this course for Pluralsight. My course is focused on SQL and it's titled Why Physical Database Design Matters. You're watching Module 3: Data Types and Index Size. So does data type choice impact your indexes? It's another one of these questions that I feel people think, who cares disk space is cheap, it doesn't matter. There's really not that big of a difference between 4 bytes, 8 bytes, 16. And as I showed in the last module you can actually have a pretty profound effect on your performance and your structures, memory, the resources in general that you use by making bad decisions. Well what you're going to find with indexes, especially with certain types of indexes, is that design decision can actually become even worse. So, I want to give you some insight into the basic index structures so that you can see exactly how profound that affect can actually be. So we're going to look at different types of indexes, we're going to look at the clustered, we're going to look at the non-clustered. And I just want to take a moment to mention that SQL 2012 introduced a new type of index called a column store index that's really not going to be the focus of our session today, but I want to make a couple of points on that since that is a new index type. So, in this module what I'm going to be focusing on and demonstrating, I'm doing a lot of demos in this module, are the key considerations around your index structures, what the physical structures look like, how to analysis them, how SQL Server accesses the data based on the index type and the structure. And what the effect is going to be on performance for the different data types and decisions that you make when you create these indexes.