Previous Next

Pro T-SQL 2022, Second Edition Toward Speed, Scalability, and Standardization for SQL Server Developers (Elizabeth Noble) (z-library.sk, 1lib.sk, z-lib.sk)

Author: Elizabeth Noble

SQL

Learn how to write and design simple and efficient T-SQL code. This is a hands-on book that teaches you how to write better T-SQL with examples and straightforward explanations. New in this edition is coverage of Parameter Sensitive Plan (PSP) optimization, Azure Synapse Link for real-time analytics, integration with S3-compatible object storage, and SQL Ledger tables based on blockchain. You’ll learn to improve your T-SQL design in ways that provide faster performance, stronger security, and easier comprehension. The book identifies common anti-patterns in writing T-SQL and instructs you on how to change your code so you can be more confident in writing T-SQL code that is reliable and performs well. Pro T-SQL 2022 is focused on the problems that developers are faced with and must solve in delivering successful applications. The content begins from a foundation of writing T-SQL that is clear and understandable. The book then takes you through writing SQL that performs well, is easy to manage through coding standards and version control, can be maintained and modified as business requirements change over time, scales as your business grows, and takes advantage of the full range of security features built into the SQL Server and Azure SQL database engines. What You Will Learn • Choose the correct data types and database objects when designing your T-SQL • Write T-SQL that accesses your data efficiently and uses hardware effectively • Implement source control and testing methods to streamline your deployment process. • Design T-SQL that is flexible and can be enhanced or modified with little effort • Plan for long-term data management, including growth and purging of older data • Enhance database security through features such as encryption, data masking, row-level security, and the SQL Ledger blockchain table type Who This Book Is For Database developers who want to improve the efficiency of their applications with better T-SQL; developers who want to know more

📄 File Format: PDF
💾 File Size: 17.2 MB
15
Views
0
Downloads
0.00
Total Donations

📄 Text Preview (First 20 pages)

ℹ️

Registered users can read the full content for free

Register as a Gaohf Library member to read the complete e-book online for free and enjoy a better reading experience.

📄 Page 1
Pro T-SQL 2022 Toward Speed, Scalability, and Standardization for SQL Server Developers — Second Edition — Elizabeth Noble
📄 Page 2
Pro T-SQL 2022 Toward Speed, Scalability, and Standardization for SQL Server Developers Second Edition Elizabeth Noble
📄 Page 3
Pro T-SQL 2022: Toward Speed, Scalability, and Standardization for SQL Server Developers ISBN-13 (pbk): 978-1-4842-9255-6 ISBN-13 (electronic): 978-1-4842-9256-3 https://doi.org/10.1007/978-1-4842-9256-3 Copyright © 2023 by Elizabeth Noble This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director, Apress Media LLC: Welmoed Spahr Acquisitions Editor: Jonathan Gennick Development Editor: Laura Berendson Editorial Project Manager: Shaul Elson Copy Editor: Mary Behr Cover image by Solen Feyissa from Unsplash Distributed to the book trade worldwide by Springer Science+Business Media New York, 1 New York Plaza, Suite 4600, New York, NY 10004-1562, USA. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@ springer-sbm.com, or visit www.springeronline.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail booktranslations@springernature.com; for reprint, paperback, or audio rights, please e-mail bookpermissions@springernature.com. Apress titles may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Print and eBook Bulk Sales web page at www.apress.com/bulk-sales. Any source code or other supplementary material referenced by the author in this book is available to readers on GitHub. For more detailed information, please visit www.apress.com/source-code. Printed on acid-free paper Elizabeth Noble Roswell, GA, USA
📄 Page 4
To my best girl, Khari. I miss you oodles, especially you letting me know it was time to quit working or to go to bed. Now I've been working on figuring that out myself.
📄 Page 5
v Table of Contents About the Author ����������������������������������������������������������������������������������������������������� xi About the Technical Reviewer ������������������������������������������������������������������������������� xiii Acknowledgments ���������������������������������������������������������������������������������������������������xv Introduction �����������������������������������������������������������������������������������������������������������xvii Chapter 1: Data Types ����������������������������������������������������������������������������������������������� 1 Number Data Types ����������������������������������������������������������������������������������������������������������������������� 1 Exact Number Data Types �������������������������������������������������������������������������������������������������������� 2 Approximate Number Data Types �������������������������������������������������������������������������������������������� 6 Converting Number Data Types ����������������������������������������������������������������������������������������������� 7 String Data Types �������������������������������������������������������������������������������������������������������������������������� 8 Character String Data Types ���������������������������������������������������������������������������������������������������� 9 Unicode String Data Types ����������������������������������������������������������������������������������������������������� 10 Binary String Data Types ������������������������������������������������������������������������������������������������������� 11 Collation for Data Types ��������������������������������������������������������������������������������������������������������� 12 Date and Time Data Types ����������������������������������������������������������������������������������������������������������� 12 DATE �������������������������������������������������������������������������������������������������������������������������������������� 13 TIME ��������������������������������������������������������������������������������������������������������������������������������������� 14 SMALLDATETIME, DATETIME, DATETIME2, and DATETIMEOFFSET ���������������������������������������� 14 Other Data Types ������������������������������������������������������������������������������������������������������������������������� 17 UNIQUEIDENTIFIER ����������������������������������������������������������������������������������������������������������������� 18 XML���������������������������������������������������������������������������������������������������������������������������������������� 18 Spatial Geometry Types ��������������������������������������������������������������������������������������������������������� 19 Spatial Geography Types ������������������������������������������������������������������������������������������������������� 19 SQL_VARIANT ������������������������������������������������������������������������������������������������������������������������ 20 ROWVERSION ������������������������������������������������������������������������������������������������������������������������� 20
📄 Page 6
vi HIERARCHYID ������������������������������������������������������������������������������������������������������������������������� 21 Table �������������������������������������������������������������������������������������������������������������������������������������� 21 Cursor ������������������������������������������������������������������������������������������������������������������������������������ 22 Chapter 2: Database Objects ���������������������������������������������������������������������������������� 25 Views ������������������������������������������������������������������������������������������������������������������������������������������ 25 User-Defined Views ��������������������������������������������������������������������������������������������������������������� 26 Indexed View ������������������������������������������������������������������������������������������������������������������������� 34 Functions ������������������������������������������������������������������������������������������������������������������������������������ 36 Scalar Functions �������������������������������������������������������������������������������������������������������������������� 36 Table-Valued Functions ��������������������������������������������������������������������������������������������������������� 41 Other User-Defined Objects��������������������������������������������������������������������������������������������������������� 53 User-Defined Table Types ������������������������������������������������������������������������������������������������������ 53 Table-Valued Parameters ������������������������������������������������������������������������������������������������������ 54 Common Table Expressions ��������������������������������������������������������������������������������������������������� 56 Temporary Objects ���������������������������������������������������������������������������������������������������������������������� 60 Temporary Tables ������������������������������������������������������������������������������������������������������������������ 60 Table Variables����������������������������������������������������������������������������������������������������������������������� 66 Temporary Stored Procedures ����������������������������������������������������������������������������������������������� 68 Triggers ��������������������������������������������������������������������������������������������������������������������������������������� 68 Logon Triggers ����������������������������������������������������������������������������������������������������������������������� 69 Data Definition Language Triggers ���������������������������������������������������������������������������������������� 69 Data Manipulation Language Triggers ����������������������������������������������������������������������������������� 70 Cursors ���������������������������������������������������������������������������������������������������������������������������������������� 73 Forward-Only Cursors ����������������������������������������������������������������������������������������������������������� 75 Static Cursors ������������������������������������������������������������������������������������������������������������������������ 78 Keyset Cursors ���������������������������������������������������������������������������������������������������������������������� 78 Dynamic Cursors ������������������������������������������������������������������������������������������������������������������� 78 Table of ConTenTs
📄 Page 7
vii Chapter 3: Standardizing T-SQL ������������������������������������������������������������������������������ 81 Formatting T-SQL ������������������������������������������������������������������������������������������������������������������������ 81 Naming T-SQL ����������������������������������������������������������������������������������������������������������������������������� 94 Commenting T-SQL ������������������������������������������������������������������������������������������������������������������� 100 Chapter 4: Designing T-SQL ���������������������������������������������������������������������������������� 105 Using Stored Procedures ���������������������������������������������������������������������������������������������������������� 105 Using Parameters ���������������������������������������������������������������������������������������������������������������������� 111 Using Complex Logic ����������������������������������������������������������������������������������������������������������������� 120 Chapter 5: Set-Based Design �������������������������������������������������������������������������������� 131 Introducing Set-Based Design �������������������������������������������������������������������������������������������������� 131 Understanding Data Retrieval ���������������������������������������������������������������������������������������������� 135 Thinking in Data Sets ���������������������������������������������������������������������������������������������������������� 137 Identifying Data Sets ����������������������������������������������������������������������������������������������������������� 140 Using Data Sets ������������������������������������������������������������������������������������������������������������������� 143 Writing Code for Data Sets �������������������������������������������������������������������������������������������������������� 147 Chapter 6: Hardware Usage ���������������������������������������������������������������������������������� 161 Considering Memory with T-SQL Design ����������������������������������������������������������������������������������� 161 Considering Storage with T-SQL Design ����������������������������������������������������������������������������������� 168 Considering CPU with T-SQL Design ����������������������������������������������������������������������������������������� 173 Chapter 7: Execution Plans ����������������������������������������������������������������������������������� 181 Introducing Execution Plans������������������������������������������������������������������������������������������������������ 181 Reading Execution Plans ����������������������������������������������������������������������������������������������������������� 182 Index Usage in Execution Plans ������������������������������������������������������������������������������������������������ 197 Logical Join Types in Execution Plans ��������������������������������������������������������������������������������������� 206 Chapter 8: Optimizing Databases for T-SQL ���������������������������������������������������������� 219 Using Query Store ��������������������������������������������������������������������������������������������������������������������� 219 Optimizing Logical Reads ���������������������������������������������������������������������������������������������������������� 220 Optimizing Duration ������������������������������������������������������������������������������������������������������������������ 228 Optimizing Indexes �������������������������������������������������������������������������������������������������������������������� 234 Table of ConTenTs
📄 Page 8
viii Automatic Database Tuning ������������������������������������������������������������������������������������������������������ 235 Automatic Plan Correction ��������������������������������������������������������������������������������������������������� 236 Automatic Index Management ��������������������������������������������������������������������������������������������� 237 Intelligent Query Processing ����������������������������������������������������������������������������������������������������� 237 Memory Grant Feedback ����������������������������������������������������������������������������������������������������� 238 Batch Mode on Rowstore ���������������������������������������������������������������������������������������������������� 238 Adaptive Joins ��������������������������������������������������������������������������������������������������������������������� 239 Chapter 9: Coding Standards �������������������������������������������������������������������������������� 247 Why Use Coding Standards ������������������������������������������������������������������������������������������������������� 247 What to Include in Coding Standards ���������������������������������������������������������������������������������������� 250 T-SQL Design ����������������������������������������������������������������������������������������������������������������������� 250 T-SQL Performance ������������������������������������������������������������������������������������������������������������� 253 T-SQL Usability �������������������������������������������������������������������������������������������������������������������� 264 Chapter 10: Source Control����������������������������������������������������������������������������������� 271 Reasons to Use Source Control ������������������������������������������������������������������������������������������������� 271 How to Use Source Control ������������������������������������������������������������������������������������������������������� 274 Rollback Changes ���������������������������������������������������������������������������������������������������������������� 276 Setting Up Source Control ��������������������������������������������������������������������������������������������������������� 278 Chapter 11: Testing ����������������������������������������������������������������������������������������������� 293 Unit Testing ������������������������������������������������������������������������������������������������������������������������������� 293 Integration Testing �������������������������������������������������������������������������������������������������������������������� 307 Load Testing ������������������������������������������������������������������������������������������������������������������������������ 317 Static Code Analysis ������������������������������������������������������������������������������������������������������������������ 318 Chapter 12: Deployment ��������������������������������������������������������������������������������������� 321 Methodology ����������������������������������������������������������������������������������������������������������������������������� 321 Types of Deployment ����������������������������������������������������������������������������������������������������������� 322 Styles of Development ��������������������������������������������������������������������������������������������������������� 328 Feature Flags ���������������������������������������������������������������������������������������������������������������������������� 330 Automated Deployment ������������������������������������������������������������������������������������������������������������� 337 Table of ConTenTs
📄 Page 9
ix Chapter 13: Functionality ������������������������������������������������������������������������������������� 347 Inserting and Updating Data ����������������������������������������������������������������������������������������������������� 347 Disable Functionality ����������������������������������������������������������������������������������������������������������������� 353 Supporting Legacy Code ����������������������������������������������������������������������������������������������������������� 360 Reporting on Transactional Data ����������������������������������������������������������������������������������������������� 366 Dynamic SQL ����������������������������������������������������������������������������������������������������������������������������� 373 Chapter 14: Logging ��������������������������������������������������������������������������������������������� 377 Data Modification ���������������������������������������������������������������������������������������������������������������������� 377 Error Handling ��������������������������������������������������������������������������������������������������������������������������� 389 Chapter 15: Enhancement ������������������������������������������������������������������������������������ 399 Adding New Functionality ��������������������������������������������������������������������������������������������������������� 399 Application Rules ����������������������������������������������������������������������������������������������������������������� 399 Feature Flags ����������������������������������������������������������������������������������������������������������������������� 405 Phasing Out Old Technology ������������������������������������������������������������������������������������������������������ 410 Chapter 16: Managing Data Growth ��������������������������������������������������������������������� 411 Partitioning �������������������������������������������������������������������������������������������������������������������������������� 411 Partition Foundation ������������������������������������������������������������������������������������������������������������ 412 Partitioned Tables ���������������������������������������������������������������������������������������������������������������� 420 Partitioned Views ����������������������������������������������������������������������������������������������������������������� 433 Hybrid Workloads ���������������������������������������������������������������������������������������������������������������������� 440 Chapter 17: Managing Data Long-Term ���������������������������������������������������������������� 453 Data Retention and Archival ������������������������������������������������������������������������������������������������������ 453 Switching Partitions ������������������������������������������������������������������������������������������������������������������ 456 Sliding Window Partition ����������������������������������������������������������������������������������������������������������� 467 Chapter 18: Implementing Security Features ������������������������������������������������������� 485 Data Discovery and Classification ��������������������������������������������������������������������������������������������� 485 Data Discovery and Classification in SQL Server ���������������������������������������������������������������� 486 Data Discovery and Classification in Azure ������������������������������������������������������������������������� 496 Table of ConTenTs
📄 Page 10
x Dynamic Data Masking ������������������������������������������������������������������������������������������������������������� 498 Row-Level Security ������������������������������������������������������������������������������������������������������������������� 506 Ledger ��������������������������������������������������������������������������������������������������������������������������������������� 513 Chapter 19: Implementing Encryption ������������������������������������������������������������������ 523 Always Encrypted ��������������������������������������������������������������������������������������������������������������������� 523 Transparent Data Encryption ����������������������������������������������������������������������������������������������������� 536 Index ��������������������������������������������������������������������������������������������������������������������� 543 Table of ConTenTs
📄 Page 11
xi About the Author Elizabeth Noble is a Microsoft Data Platform MVP and Director of Database Development in the metro Atlanta area. When ze was introduced to hir first database over 10 years ago, ze found the career ze wanted to pursue. Hir passion is to help others improve the quality and speed of deploying database changes through automation. When ze is not trying to automate database development, ze can be found spending time with zir dogs, painting, paddle boarding, or throwing axes.
📄 Page 12
xiii About the Technical Reviewer Eric Blinn has been an SQL Server professional for 20 years, holding the role of DBA, developer, and architect at that time. He has learned a lot over those years, from how to write DTS packages in SQL Server 2000 to implementing read-scale availability groups in SQL Server 2022. He loves to help others on their journey by writing about and presenting on SQL Server topics. You can read his blog on MSSQLTips.com. Eric was born and raised in Ohio where he lives with his wife, Kate, and four children, Jack, Lucy, Felix, and Max.
📄 Page 13
xv Acknowledgments This book would truly not be possible without the technical editing of Eric Blinn. He challenged me to make this book more organized and structured. I also want to thank the entire Apress team including Shaul Elson, Shonmirin PA, and Laura Berendson for their patience while I worked to complete this book. I also would like to acknowledge the continued support of Mind, Eve, Danny, Mom, Dad, Kaiya, and Ares. You all played your part in helping me either destress or take a break or focus to get this book finished. I want to thank my team at work for their support including Anthony, Helen, and Jude. I also want to thank the SQL community and the data community for being a part of this journey, and I hope you enjoy this book!
📄 Page 14
xvii Introduction This book was written with the intention of encouraging others to get to the next level of their T-SQL development. The goal is to not only get a better understanding of how to write better queries but also an idea of why some methods can be better than others. There is a great deal of flexibility when creating T-SQL statements. In many cases, there is more than one way to achieve the same outcome from your T-SQL. This book is designed for database developers and data professionals who have a general knowledge of T-SQL but are looking to improve their overall code quality. You should understand the T-SQL syntax and know how to write SELECT, INSERT, UPDATE, or DELETE statements before beginning this book. Pro T-SQL 2022 will prepare you to write consistent code with improved performance. You will also learn how to protect your T-SQL code by using source control and improving your database deployment pipeline. Overall, the goal of this book is to provide you with a framework to write better T-SQL code. As data professionals, we can find ourselves in scenarios where there are high demands or short deadlines. Pro T-SQL 2022 was written to help you write code that can save you time and energy in the future. There are five sections in this book. The first section covers how to improve the readability of your T-SQL code. There is an overview of the various data types of T-SQL along with guidance on how to best use these data types. This first section explains the benefits and disadvantages of the various database objects in SQL Server. Additional chapters discuss standardizing and designing T-SQL code. The second section explains how to write T-SQL code that is efficient. This section includes using set-based design to write T-SQL code and how to understand the relationship between hardware and T-SQL design. You will also learn how to use execution plans and new features in SQL Server 2019 to improve the performance of your T-SQL code. The third section discusses how to manage your T-SQL code. The chapters included in this section cover developing coding standards and using source control to store your code. To further manage your T-SQL code, you will also learn some methods to test and deploy your database code. The fourth section addresses how to write T-SQL code so that it is sustainable over time. The last section gives an overview of the different options you can implement to help make your databases more secure. These chapters include methods to safely add new functionality, to log changes to data within your databases, and to manage data growth over time.
📄 Page 15
1 CHAPTER 1 Data Types Data types are key to writing efficient and performant T-SQL. While many data types can be a number, string, or date/time, there are also a variety of data types that do not fit into any of these categories. When selecting a data type, it is important to understand what the data type is and when best to use it. The most obvious use for data types is to allow users to know what type of information is stored in each column. There is also a difference in how SQL Server stores different data types, and data types can affect how SQL Server generates execution plans. In addition, columns all have a storage cost that is based on the data type and row count of the column. The effects of these storage requirements will be discussed further in Chapter 7. As you’ll find later in this chapter, data types can also affect the cost of performing calculations as well as the results of those calculations. Number Data Types While numbers may all seem to be the same, T-SQL segments numeric data types into several different categories of data types. These segments can include whole numbers or numbers with decimal places. Numbers are also categorized by either being exact or approximate. When saving numbers there is also a variation in data types based on the range. Understanding how to work with various number data types when performing mathematical calculations is critical to ensuring that applications handle data as expected. While it may be easiest to pick the most common data types from each category, there are times where it is best to analyze the data that will be stored and search for a more appropriate option. When choosing data types, there are various factors to consider. The most important step is to figure out what kind of data will be stored. The next logical step is to consider how the data will be used. In addition, it is important to understand how T-SQL handles calculations involving various data types. If you want to © Elizabeth Noble 2023 E. Noble, Pro T-SQL 2022, https://doi.org/10.1007/978-1-4842-9256-3_1
📄 Page 16
2 store the day of the year, you should use a whole number that allows you to use a value between 1 and 366. When storing revenue for a large company, you need a number with multiple digits and two decimal places. However, if you need to store the price for a gallon of gas, you want a number with two digits and several decimal places. All of these situations are defined differently in SQL Server. Exact Number Data Types There are situations when the value of a number is definite and known. These types of numbers can be referred to as exact numbers. Some examples include 1 or 0 for true or false, quantity of units sold, discount percent, or dollars and cents. When working to select the best data type, there are often several similar data types that seem to do the job. In some cases, the categories have more than one data type available. When considering what data type to use, you want to consider the purpose for this data type. This provides better clarity when determining which data type should be used. You want to consider both the benefits and drawbacks associated with each data type. You also want to consider if SQL Server will have to perform any implicit conversions as a result of using this data type in calculations with other data types. The final piece is to consider how the data type is stored in SQL Server. Here are a series of exact number data types. For each type, you will find some benefits, drawbacks, and common use cases. BIT The BIT data type is used to store a binary digit of either 1 or 0. In T-SQL, the data types can also be nullable, which indicates that the BIT data type can also store a NULL value. With only three possible values, this data type has the smallest number of values possible. Due to the small variety of values that can be stored in this data type, the BIT data type also requires the smallest amount of storage on disk. As there are 8 bits in a byte, the same holds true for storing the BIT data type in a database. The BIT data type is great for data types where “either X or Y” is applicable. The information stored can be interpreted as true or false, on or off, and yes or no. In the case of true or false, this BIT type can be used to indicate if a data record was translated successfully. A common use of indicating on or off with the BIT data type involves indicating if a certain feature is enabled. One example of a yes or no value is recording a customer’s decision to opt in to receive marketing information from a company. Chapter 1 Data types
📄 Page 17
3 One of the challenges with the BIT data type is making sure to use it in a way that promotes good database design. This means there are times when you need to consider the overall purpose when selecting the BIT data type. For instance, it may seem like indicating whether an item has specific characteristics may be a good use of the BIT data type. An example is to add a column in the Product table IsWood to indicate if a part is made of wood. However, it may be better to consider redesigning the database to record those attributes in another table. A BIT can be used to indicate a successful status for a transaction. However, it is often better to record the state of the transaction over a period of time. If recording status changes over time is important, then using a BIT to record if a transaction is successful may not be the best option. While BIT is small and is great for yes or no values, sometimes you need more options for a column than just yes or no, so you need to consider a larger data type. TINYINT, SMALLINT, INT, and BIGINT SQL Server also allows you to store whole numbers, which are numbers that do not have decimals or fractional values. These numbers are known as integers. One example of data stored as integers is quantities of a given item. There are several types of integer values that can be stored within SQL Server. The first integer type is TINYINT. The TINYINT value can contain any integer value between 0 and 255. Due to the limited size of this data type, this may be useful for limited configuration types or number of locations. This data type is like the BIT data type, but this data type has a slightly wider range. This data type is also useful to configure the types of statuses in a system or categories of objects. TINYINT is good for storing these status types as many applications do not need more than 256 statuses. Now that we’ve covered TINYINT, let’s discuss the possibilities of SMALLINT. The range of SMALLINT covers approximately 70,000 possible values. With this range available, you want to consider what sorts of values you need to have between 256 and 65,435 unique values. The range for the SMALLINT starts at -32,768 and ends at 32,767. This data type would not be useful for a table that logs every single activity that happens. Many databases or data tables may have more than 70,000 transactions or records over the course of several years. This may cause this data type to be unsuitable for those tables. However, there may be other tables where the SMALLINT data type is ideal. Chapter 1 Data types
📄 Page 18
4 If you have data tables that do not experience high transactional activity but will be growing for some time, the SMALLINT data type may be beneficial. Understanding your business will help you determine if SMALLINT is the correct data type for that value being stored. If you want to create a table to continue to add functionality to your applications, you may want to store a record indicating each new piece of functionality in your application. An example of this is feature flags. Your application will likely have more than 256 enhancements over the life of the application. You may also want to store configuration values for the application. Storing these configuration values in a table may benefit from the SMALLINT data type. INT is the most frequent use of the whole number data types. Many databases use this type exclusively for any sort of whole number that is being tracked, which is not recommend. One of the reasons for this is the entire range covers about 5.3 billion records. The INT data type covers the range from −2,147,483,648 to 2,147,483,647. However, when many data tables are created, their identity column is often started, or seeded, at the integer 1. This causes the table to be limited to approximately 2.15 billion unique identities. If you believe that your table will need more than 2.15 billion unique identity records, you may want to start the identity with the lowest number possible, –2,147,483,648, or try using BIGINT. There are very few situations where you would need a value larger than INT, and I speculate there are many times the INT data type is used when TINYINT or SMALLINT would be a better choice. The BIGINT data table can store values from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,808 and takes twice the storage space of INT. This is often where you may need to perform mathematical calculations. Some businesses process a couple hundred transactions per second. Other businesses process upward of 10,000 or 20,000 transactions per second. In both cases, it’s important to consider what kind of growth will be expected in the tables holding this transactional information. If your application has hundreds of transactions per second over several years, the number of records stored will be much smaller than if the application has tens of thousands of transactions per second for the same time period. Relational databases almost always have more whole number data types than any other data type. These whole numbers do everything from incrementing tables and creating relationships between tables to storing information about various aspects of the business. One of the common temptations is to treat all whole numbers as INT, but some care should be used when selecting which data type to use. Chapter 1 Data types
📄 Page 19
5 As an example, imagine a database for the trucking industry. If you want to save the number of kilometers driven in a day, you don’t need a number larger than 2,880 (120 kilometers per hour times 24 hours in a day). Storing the number of lines on an invoice will be less than 256 for most businesses. There may also be some part of the business that is storing the location of a delivery vehicle every couple of minutes. If there are 5,000 trucks updating that table every two to three minutes, over three years that table could grow to three billion records. In that case, you want to choose a BIGINT, possibly seeded at -9,223,372,036,854,775,808 instead of choosing INT seeded at 1, which would only allow you to save 2,147,483,647 records. DECIMAL/NUMERIC Now that we have discussed various integers, let’s consider what to do with numbers that require decimals. There are various cases where you are going to want decimal places. There is no difference between DECIMAL and NUMERIC. They are the same data type in SQL Server. Some of these cases involve using dollars and cents, and other times you need decimal places for precision in measurements. There are a couple of options available in these scenarios. First, there is the option for DECIMAL (alternatively, it is called a NUMERIC data type). This value does not save any currency information with it; however, it does record decimal places. These decimal places can be specified by indicating both the total number of digits that should be stored and the number of digits to the right of the decimal point. For instance, a DECIMAL data type of DECIMAL (10,4) indicates that you can store a number with 6, or 10 minus 4, digits to the left of the decimal point and digits to the right, such as 999999.9999. If you instead created a data type of NUMERIC (9,2), you can store numbers from -9999999.99 to 999999.99. You will find that the DECIMAL or NUMERIC data type is acceptable for almost all data types involving numbers. This includes general-purpose numbers, decimals, measurements, and money values. Considering that the DECIMAL type can represent multiple different types of numbers, let’s examine this data type. There are some specific terms for the DECIMAL data type. The values that make up a DECIMAL data type are precision and scale. Precision relates to the total number of digits that are saved in a DECIMAL data type. Scale refers to the number of digits that are stored to the right of the decimal point. Referring back to the previous examples with the trucking industry, if you want to store kilometers per liter, it may make sense to use the data type DECIMAL(3,1) as you are not likely to have kilometers per liter of 99.9. Chapter 1 Data types
📄 Page 20
6 SMALLMONEY and MONEY The next data types to discuss are MONEY and SMALLMONEY. The MONEY and SMALLMONEY data types are like the DECIMAL or NUMERIC data type. The SMALLMONEY and MONEY data types can also be used to store values for currency. SQL Server saves the numeric value and excludes the type of currency associated with the value saved. The largest difference between the MONEY and SMALLMONEY data types is the size and amount of storage space required. The SMALLMONEY data type covers a range from –214,000 to positive 214,000 and uses half the storage as the MONEY type, which covers a range from –922 billion to 922 billion. The MONEY data type is accurate to store up to four decimal places. The limitation on decimal places limits the accuracy to ten thousandths of the monetary value stored. The MONEY data type saves all values to four decimal places. The fixed number of decimal places impacts how rounding affects calculations involving the MONEY data type. This is because SQL Server saves the MONEY data type as BIGINT. Similarly, the SMALLMONEY data type is saved as an INT. This explains why you may have rounding issues with MONEY or SMALLMONEY. Choosing MONEY or DECIMAL (9,4) is mostly a matter of preference as long as you are aware of the impact the MONEY data type can have on calculations and plan accordingly. The main goal is consistency with how you are storing data relating to currency so that DBAs and developers alike can quickly understand the purpose and implications of the data field in question. While this data type will save space over a DECIMAL, there are other ways your database administrator can save space that do not involve changing the data type. Approximate Number Data Types Next, let’s analyze the differences between exact and approximate numbers. Exact numbers exist for things that you know the exact quantity of, such as how many items you bought at the store or the exact amount in dollars and cents. Approximate numbers exist for scenarios where the measurements may not be exact. Approximate numbers can be used to store very large or very small numbers. You may also find that your application is recording a measurement that is not exact. You may cut a length of fabric that is close to but not exactly a specific value. For example, the length of fabric may be around 12 inches. Storing the value of 12 inches would be an approximate value. This measurement of 12 inches may be so close that it would be difficult to tell that the length of fabric was not exactly 12 inches. Chapter 1 Data types
The above is a preview of the first 20 pages. Register to read the complete e-book.

💝 Support Author

0.00
Total Amount (¥)
0
Donation Count

Login to support the author

Login Now

Recommended for You

Loading recommended books...
Failed to load, please try again later
Back to List