پرو T-SQL 2022: به سوی سرعت، مقیاس‌پذیری و استانداردسازی برای توسعه‌دهندگان SQL Server

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 about T-SQL so they can be prepared to solve complex problems more easily; data professionals who want to write high-quality T-SQL that performs well, is flexible to changing needs, and is easy for others to understand Table of Contents 5 About the Author 11 About the Technical Reviewer 12 Acknowledgments 13 Introduction 14 Chapter 1: Data Types 15 Number Data Types 15 Exact Number Data Types 16 BIT 16 TINYINT, SMALLINT, INT, and BIGINT 17 DECIMAL/NUMERIC 19 SMALLMONEY and MONEY 20 Approximate Number Data Types 20 Converting Number Data Types 21 String Data Types 22 Character String Data Types 23 CHAR and VARCHAR 23 TEXT 24 Unicode String Data Types 24 NCHAR and NVARCHAR 24 NTEXT 25 Binary String Data Types 25 BINARY and VARBINARY 25 IMAGE 26 Collation for Data Types 26 Date and Time Data Types 26 DATE 27 TIME 28 SMALLDATETIME, DATETIME, DATETIME2, and DATETIMEOFFSET 28 Other Data Types 31 UNIQUEIDENTIFIER 32 XML 32 Spatial Geometry Types 33 Spatial Geography Types 33 SQL_VARIANT 34 ROWVERSION 34 HIERARCHYID 35 Table 35 Cursor 36 Chapter 2: Database Objects 38 Views 38 User-Defined Views 39 Indexed View 47 Functions 49 Scalar Functions 49 Table-Valued Functions 54 Inline Table-Valued Functions 54 Multi-Statement Table-Valued Functions 57 Other User-Defined Objects 66 User-Defined Table Types 66 Table-Valued Parameters 67 Common Table Expressions 69 Temporary Objects 73 Temporary Tables 73 Local Temporary Tables 74 Global Temporary Tables 77 Persistent Temporary Table 77 Table Variables 79 Temporary Stored Procedures 81 Triggers 81 Logon Triggers 82 Data Definition Language Triggers 82 Data Manipulation Language Triggers 83 Cursors 86 Forward-Only Cursors 88 Static Cursors 91 Keyset Cursors 91 Dynamic Cursors 91 Chapter 3: Standardizing T-SQL 94 Formatting T-SQL 94 Naming T-SQL 107 Commenting T-SQL 113 Chapter 4: Designing T-SQL 118 Using Stored Procedures 118 Using Parameters 124 Using Complex Logic 133 Chapter 5: Set-Based Design 144 Introducing Set-Based Design 144 Understanding Data Retrieval 148 Thinking in Data Sets 150 Identifying Data Sets 153 Using Data Sets 156 Writing Code for Data Sets 160 Chapter 6: Hardware Usage 173 Considering Memory with T-SQL Design 173 Considering Storage with T-SQL Design 180 Considering CPU with T-SQL Design 185 Chapter 7: Execution Plans 192 Introducing Execution Plans 192 Reading Execution Plans 193 Index Usage in Execution Plans 208 Logical Join Types in Execution Plans 217 Chapter 8: Optimizing Databases for T-SQL 229 Using Query Store 229 Optimizing Logical Reads 230 Optimizing Duration 238 Optimizing Indexes 244 Automatic Database Tuning 245 Automatic Plan Correction 246 Automatic Index Management 247 Intelligent Query Processing 247 Memory Grant Feedback 248 Batch Mode on Rowstore 248 Adaptive Joins 249 Chapter 9: Coding Standards 257 Why Use Coding Standards 257 What to Include in Coding Standards 260 T-SQL Design 260 ANSI STANDARD 260 Normal Form 260 Table Size 261 Name Value Pair 261 Primary Key 261 Foreign Key 262 Non-Clustered Index 262 Constraint Definition 262 T-SQL Performance 263 Selecting Necessary Data 263 Sargeable 264 Implicit Conversion 269 SET NOCOUNT ON 269 NULL Values 270 NOLOCK 271 RECOMPILE 273 T-SQL Usability 274 Linked Server 275 Column Definition 275 BETWEEN 275 Stored Procedure Parameters 276 UNION 276 CAST or CONVERT 276 Cursors 277 ORDER BY 277 Case Statements 278 TRY... CATCH 278 Chapter 10: Source Control 280 Reasons to Use Source Control 280 How to Use Source Control 283 Rollback Changes 285 Setting Up Source Control 287 Chapter 11: Testing 301 Unit Testing 301 Integration Testing 315 Load Testing 325 Static Code Analysis 326 Chapter 12: Deployment 328 Methodology 328 Types of Deployment 329 Migration- Based Deployments 330 State-Based Migrations 334 Styles of Development 335 Kanban 336 Scrum 336 Feature Flags 337 Automated Deployment 344 Chapter 13: Functionality 353 Inserting and Updating Data 353 Disable Functionality 359 Supporting Legacy Code 366 Reporting on Transactional Data 372 Dynamic SQL 379 Chapter 14: Logging 383 Data Modification 383 Error Handling 395 Chapter 15: Enhancement 404 Adding New Functionality 404 Application Rules 404 Feature Flags 410 Phasing Out Old Technology 415 Chapter 16: Managing Data Growth 416 Partitioning 416 Partition Foundation 417 Partitioned Tables 425 Partitioned Views 438 Hybrid Workloads 445 Chapter 17: Managing Data Long-Term 457 Data Retention and Archival 457 Switching Partitions 460 Sliding Window Partition 471 Chapter 18: Implementing Security Features 488 Data Discovery and Classification 488 Data Discovery and Classification in SQL Server 489 Data Discovery and Classification in Azure 499 Dynamic Data Masking 501 Row-Level Security 509 Ledger 516 Chapter 19: Implementing Encryption 525 Always Encrypted 525 Transparent Data Encryption 538 Index 545