دانلود کتاب Dynamic SQL: applications, performance, and security in Microsoft SQL Server

فهرست مطالب :

Table of Contents
About the Author
About the Technical Reviewer
Acknowledgments
Introduction
Chapter 1: What Is Dynamic SQL?
Understanding Dynamic SQL
A Simple Example
The EXEC Statement
Data Type to Use
Dynamic Execution Process
Dynamic SQL in Action
Advantages of Dynamic SQL
Optional or Customized Search Criteria
Customizable Everything
Optimize SQL Performance
Generate Large Amounts of T-SQL or Text, Fast!
Execute SQL Statements on Other Servers or Databases
Do the Impossible!
Dynamic SQL Considerations
Apostrophes Can Break Strings
NULL Can Break Strings
Difficult to Read and Debug
Permissions and Scope Are Different
Dynamic SQL Cannot be used in Functions
Dynamic SQL Style
Document Thoroughly
Debugging Dynamic SQL
Write Dynamic SQL Just Like Standard T-SQL
String Sizes and Truncation
Management Studio Text Display
Sp_executesql
Building Strings via Concatenation
Notes on Apostrophes
Conclusion
Chapter 2: Protecting Against SQL Injection
What Is SQL Injection?
Cleansing Inputs
Parameterizing Dynamic SQL
Schema Name and Square Brackets
Effective Spacing
Properly Type Inputs
Blind SQL Injection
Detection and Prevention
Security Testing
Scanning of Application Traffic
Log Review
Code Review
Software Patching
Limit URL Length
Use Views and/or Masking for Sensitive Data
Vendor Software
Login Pages
Conclusion
Chapter 3: Large Scale Searching
Why Use Dynamic Searches?
Custom Search Grids
Search Grid Considerations
Disallow Blank Searches
Data Paging
Conditional Paging
Search Limitations
Input-Based Search
Result Row Count
Additional Filtering Considerations
Alternatives
Conclusion
Chapter 4: Permissions and Security
The Principle of Least Privilege
Granular Permissions vs. Role Permissions
Dynamic SQL and Ownership Chaining
Changing Security Context On-the-Fly
Where Do Security Disasters Come From?
Users, Passwords, and Inconvenience
Dynamic SQL Maintenance
Cleaning House
Login and User Usage
Auditing Users and Logins
Memory Consumption
Row Level Security
Signing Stored Procedures
Conclusion
Chapter 5: Managing Scope
What Is Scope?
Why Is Scope Important?
Managing Scope in Dynamic SQL
Using OUTPUT in Dynamic SQL
Table Variables and Temporary Tables
Table Variables
Temporary Tables
Global Temporary Tables
Using Permanent Tables for Temporary Storage
Output Data Directly to a Table from Dynamic SQL
Conclusion
Cleanup
Chapter 6: Performance Optimization
Query Execution
Parsing
Binding
Optimization
Execution
Optimization Tools
Query Execution Plan
STATISTICS IO
Object
Scan Count
Logical Reads
Physical Reads
STATISTICS TIME
Use All of These Tools!
Dynamic SQL vs. Standard SQL
Query Parsing and Binding
Execution Plan Caching
Simplifying Queries
Paging Performance
Filtered Indexes
Cardinality
Statistics
Sys.dm_db_stats_properties
Trace Flag 2371
Back to Dynamic SQL
Query Hints
Conclusion
Cleanup
Chapter 7: Scalable Dynamic Lists
What Is a Dynamic List?
Using XML to Create a Dynamic List
Set-Based String Building
Revisiting Security
STRING_AGG
Conclusion
Chapter 8: Parameter Sniffing
What Is Parameter Sniffing?
Parameter Sniffing Examples
Identifying Parameter Sniffing
Design Considerations
Query Execution Details
The Red Herrings
Parameter Values
Local Variables
Forcing Cardinalities to the Optimizer
Dynamic SQL
Trace Flag 4136
Fix Bad Business Logic
Too Many Possible Code Paths
Lots of Parameters
Stored Procedure is Huge
Conclusion
Cleanup
Chapter 9: Dynamic Pivot and Unpivot
PIVOT
UNPIVOT
Additional Examples
Multiple PIVOT Operators
Multiple UNPIVOT Operators
Classification Using PIVOT and CASE
Conclusion
Chapter 10: Solving Common Problems
Collation Conflicts
The Problem
The Solution
Organizing and Archiving Data
The Problem
The Solution
Customized Database Objects
The Problem
The Solution
A Note on System Tables
Conclusion
Chapter 11: Applications of  Dynamic SQL
Database Backups
Saving Generated Scripts
Saving Scripts to a Table
Executing TSQL on Other Servers
Generating Schema from Metadata
Building a Solution
Conclusion
Chapter 12: Index Usage and Maintenance
Index Defragmentation
Index Rebuild
Index Reorganization
Creating an Index Maintenance Solution
Index Usage Statistics
Missing Index Statistics
Conclusion
Index