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.
Author(s): Elizabeth Noble
Edition: 2
Publisher: Apress
Year: 2023
Language: English
Pages: 555
Chapter 1: Data Types
Number Data Types
Exact Number Data Types
Approximate Number Data Types
Converting Number Data Types
String Data Types
Character String Data Types
Unicode String Data Types
Binary String Data Types
Collation for Data Types
Date and Time Data Types
DATE
TIME
SMALLDATETIME, DATETIME, DATETIME2, and 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
Other User-Defined Objects
User-Defined Table Types
Table-Valued Parameters
Common Table Expressions
Temporary Objects
Temporary Tables
Table Variables
Temporary Stored Procedures
Triggers
Logon Triggers
Data Definition Language Triggers
Data Manipulation Language 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
Chapter 5: Set-Based Design
Introducing Set-Based Design
Understanding Data Retrieval
Thinking in Data Sets
Identifying Data Sets
Using 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
Introducing Execution Plans
Reading Execution Plans
Index Usage in Execution Plans
Logical Join Types in Execution Plans
Chapter 8: Optimizing Databases for T-SQL
Using Query Store
Optimizing Logical Reads
Optimizing Duration
Optimizing Indexes
Automatic Database Tuning
Automatic Plan Correction
Automatic Index Management
Intelligent Query Processing
Memory Grant Feedback
Batch Mode on Rowstore
Adaptive Joins
Chapter 9: Coding Standards
Why Use Coding Standards
What to Include in Coding Standards
T-SQL Design
T-SQL Performance
T-SQL Usability
Chapter 10: Source Control
Reasons to Use Source Control
How to Use Source Control
Rollback Changes
Setting Up Source Control
Chapter 11: Testing
Unit Testing
Integration Testing
Load Testing
Static Code Analysis
Chapter 12: Deployment
Methodology
Types of Deployment
Styles of Development
Feature Flags
Automated Deployment
Chapter 13: Functionality
Inserting and Updating Data
Disable Functionality
Supporting Legacy Code
Reporting on Transactional Data
Dynamic SQL
Chapter 14: Logging
Data Modification
Error Handling
Chapter 15: Enhancement
Adding New Functionality
Application Rules
Feature Flags
Phasing Out Old Technology
Chapter 16: Managing Data Growth
Partitioning
Partition Foundation
Partitioned Tables
Partitioned Views
Hybrid Workloads
Chapter 17: Managing Data Long-Term
Data Retention and Archival
Switching Partitions
Sliding Window Partition
Chapter 18: Implementing Security Features
Data Discovery and Classification
Data Discovery and Classification in SQL Server
Data Discovery and Classification in Azure
Dynamic Data Masking
Row-Level Security
Ledger
Chapter 19: Implementing Encryption
Always Encrypted
Transparent Data Encryption
Index
