Pro T-SQL 2019 : Toward Speed, Scalability, and Standardization for SQL Server Developers

Design and write simple and efficient T-SQL code in SQL Server 2019 and beyond. Writing T-SQL that pulls back correct results can be challenging. This book provides the help you need in writing T-SQL that performs fast and is easy to maintain. You also will learn how to implement version control, testing, and deployment strategies. Hands-on examples show modern T-SQL practices and provide straightforward explanations. Attention is given to selecting the right data types and objects when designing T-SQL solutions. Author Elizabeth Noble teaches you how to improve your T-SQL performance through good design practices that benefit programmers and ultimately the users of the applications. You will know the common pitfalls of writing T-SQL and how to avoid those pitfalls going forward. What You Will Learn Choose correct data types and database objects when designing T-SQL Write T-SQL that searches data efficiently and uses hardware effectively Implement source control and testing methods to streamline the deployment process Design T-SQL that can be enhanced or modified with less effort Plan for long-term data management and storage Who This Book Is For Database developers who want to improve the efficiency of their applications, and developers who want to solve complex query and data problems more easily by writing T-SQL that performs well, brings back correct results, and is easy for other developers to understand and maintain Table of Contents About the Author About the Technical Reviewer Acknowledgments Introduction Part I: Building Understandable T-SQL Chapter 1: Data Types Number Data Types Exact Number Data Types BIT TINYINT, SMALLINT, INT, BIGINT DECIMAL/NUMERIC SMALLMONEY, MONEY Approximate Number Data Types Converting Number Data Types String Data Types Character String Data Types CHAR and VARCHAR TEXT Unicode String Data Types NCHAR and NVARCHAR NTEXT Binary String Data Types BINARY and VARBINARY IMAGE Date and Time Data Types DATE TIME SMALLDATETIME, DATETIME, DATETIME2, DATETIMEOFFSET Other Data Types UNIQUEIDENTIFIER XML Spatial Geometry Types Spatial Geography Types SQL_VARIANT Rowversion HIERARCHYID Table Cursor Chapter 2: Database Objects Views User-Defined Views Indexed View Functions Scalar Functions Table-Valued Functions Inline Table-Valued Functions Multi-statement Table-Valued Functions Other User-Defined Objects User-Defined Table Types Table-Valued Parameters Common Table Expressions Temporary Objects Temporary Tables Local Temporary Tables Global Temporary Tables Persistent Temporary Table Table Variables Temporary Stored Procedures Triggers Logon Triggers Data Definition Language (DDL) Triggers Data Manipulation Language (DML) Triggers Cursors Forward-Only Cursors Static Cursors Keyset Cursors Dynamic Cursors Chapter 3: Standardizing T-SQL Formatting T-SQL Naming T-SQL Commenting T-SQL Chapter 4: Designing T-SQL Using Stored Procedures Using Parameters Using Complex Logic Part II: Building Performant T-SQL Chapter 5: Set-Based Design Introduction to Set-Based Design Thinking in Data Sets Writing Code for Data Sets Chapter 6: Hardware Usage Considering Memory with T-SQL Design Considering Storage with T-SQL Design Considering CPU with T-SQL Design Chapter 7: Execution Plans Reading Execution Plans Index Usage in Execution Plans Logical Join Types in Execution Plans Chapter 8: Optimize T-SQL Optimizing Logical Reads Optimizing Duration Automatic Database Tuning Query Store Automatic Plan Correction Automatic Index Management Intelligent Query Processing Memory Grant Feedback Batch Mode on Rowstore Adaptive Joins Part III: Building Manageable T-SQL Chapter 9: Coding Standards Why Use Coding Standards What to Include in Coding Standards T-SQL Design ANSI STANDARD Normal Form Table Size Name Value Pair Primary Key Foreign Key Non-clustered Index Constraint Definition T-SQL Performance Select Necessary Data Sargeable Implicit Conversion SET NOCOUNT ON NULL Values NOLOCK RECOMPILE T-SQL Usability Linked Server Column Definition BETWEEN Stored Procedure Parameters UNION CAST Cursor ORDER BY Case Statement TRY... CATCH Chapter 10: Source Control Why Use Source Control How to Use Source Control Setting Up Source Control Chapter 11: Testing Unit Testing Integration Testing Load Testing Static Code Analysis Chapter 12: Deployment Feature Flag Methodology Automated Deployment Part IV: Building Maintainable T-SQL Chapter 13: Functional Design Inserting and Updating Data Disable Functionality Support Legacy Code Reporting on Transactional Data Dynamic SQL Chapter 14: Logging Data Modification Error Handling Chapter 15: Managing Data Growth Partitioning Partitioned Tables Partitioned Views Hybrid Workloads Index Untitled