SQL پویا: کاربردها، عملکرد و امنیت در مایکروسافت SQL سرور

Table of Contents......Page 5 About the Author......Page 12 About the Technical Reviewer......Page 13 Acknowledgments......Page 14 Introduction......Page 15 A Simple Example......Page 17 The EXEC Statement......Page 18 Dynamic Execution Process......Page 19 Dynamic SQL in Action......Page 20 Customizable Everything......Page 23 Generate Large Amounts of T-SQL or Text, Fast!......Page 24 Do the Impossible!......Page 25 NULL Can Break Strings......Page 26 Permissions and Scope Are Different......Page 27 Document Thoroughly......Page 28 Debugging Dynamic SQL......Page 32 Write Dynamic SQL Just Like Standard T-SQL......Page 35 String Sizes and Truncation......Page 36 Management Studio Text Display......Page 37 Sp_executesql......Page 38 Building Strings via Concatenation......Page 39 Notes on Apostrophes......Page 45 Conclusion......Page 46 What Is SQL Injection?......Page 47 Cleansing Inputs......Page 55 Parameterizing Dynamic SQL......Page 58 Schema Name and Square Brackets......Page 64 Properly Type Inputs......Page 66 Blind SQL Injection......Page 67 Security Testing......Page 69 Log Review......Page 70 Software Patching......Page 71 Limit URL Length......Page 72 Vendor Software......Page 73 Login Pages......Page 74 Conclusion......Page 76 Why Use Dynamic Searches?......Page 77 Custom Search Grids......Page 83 Search Grid Considerations......Page 95 Data Paging......Page 96 Conditional Paging......Page 99 Search Limitations......Page 102 Input-Based Search......Page 103 Result Row Count......Page 109 Additional Filtering Considerations......Page 115 Alternatives......Page 117 Conclusion......Page 118 The Principle of Least Privilege......Page 119 Granular Permissions vs. Role Permissions......Page 122 Dynamic SQL and Ownership Chaining......Page 123 Changing Security Context On-the-Fly......Page 128 Where Do Security Disasters Come From?......Page 136 Users, Passwords, and Inconvenience......Page 140 Dynamic SQL Maintenance......Page 142 Cleaning House......Page 154 Login and User Usage......Page 156 Auditing Users and Logins......Page 158 Memory Consumption......Page 162 Row Level Security......Page 169 Signing Stored Procedures......Page 174 Conclusion......Page 179 What Is Scope?......Page 180 Why Is Scope Important?......Page 183 Managing Scope in Dynamic SQL......Page 184 Using OUTPUT in Dynamic SQL......Page 185 Table Variables......Page 190 Temporary Tables......Page 192 Global Temporary Tables......Page 196 Using Permanent Tables for Temporary Storage......Page 200 Output Data Directly to a Table from Dynamic SQL......Page 202 Conclusion......Page 205 Cleanup......Page 206 Binding......Page 207 Optimization Tools......Page 208 Query Execution Plan......Page 209 Scan Count......Page 212 STATISTICS TIME......Page 213 Query Parsing and Binding......Page 214 Execution Plan Caching......Page 216 Simplifying Queries......Page 220 Paging Performance......Page 229 Filtered Indexes......Page 242 Cardinality......Page 247 Statistics......Page 248 Sys.dm_db_stats_properties......Page 258 Trace Flag 2371......Page 259 Back to Dynamic SQL......Page 260 Query Hints......Page 261 Cleanup......Page 268 What Is a Dynamic List?......Page 270 Using XML to Create a Dynamic List......Page 273 Set-Based String Building......Page 277 Revisiting Security......Page 280 STRING_AGG......Page 287 Conclusion......Page 291 What Is Parameter Sniffing?......Page 292 Parameter Sniffing Examples......Page 295 Identifying Parameter Sniffing......Page 310 Design Considerations......Page 311 Query Execution Details......Page 312 The Red Herrings......Page 315 Parameter Values......Page 320 Local Variables......Page 321 Forcing Cardinalities to the Optimizer......Page 329 Dynamic SQL......Page 333 Fix Bad Business Logic......Page 335 Lots of Parameters......Page 336 Stored Procedure is Huge......Page 337 Cleanup......Page 338 PIVOT......Page 340 UNPIVOT......Page 350 Additional Examples......Page 356 Multiple PIVOT Operators......Page 360 Multiple UNPIVOT Operators......Page 363 Classification Using PIVOT and CASE......Page 371 Conclusion......Page 373 Collation Conflicts......Page 374 The Problem......Page 375 The Solution......Page 382 The Problem......Page 385 The Solution......Page 387 The Solution......Page 393 A Note on System Tables......Page 401 Conclusion......Page 402 Database Backups......Page 404 Saving Scripts to a Table......Page 412 Executing TSQL on Other Servers......Page 422 Building a Solution......Page 425 Conclusion......Page 433 Index Defragmentation......Page 434 Index Rebuild......Page 436 Creating an Index Maintenance Solution......Page 437 Index Usage Statistics......Page 443 Missing Index Statistics......Page 479 Conclusion......Page 498 Index......Page 499 Take a deep dive into the many uses of dynamic SQL in Microsoft SQL Server. This edition has been updated to use the newest features in SQL Server 2016 and SQL Server 2017 as well as incorporating the changing landscape of analytics and database administration. Code examples have been updated with new system objects and functions to improve efficiency and maintainability. Executing dynamic SQL is key to large-scale searching based on user-entered criteria. Dynamic SQL can generate lists of values and even code with minimal impact on performance. Dynamic SQL enables dynamic pivoting of data for business intelligence solutions as well as customizing of database objects. Yet dynamic SQL is feared by many due to concerns over SQL injection or code maintainability. Dynamic SQL: Applications, Performance, and Security in Microsoft SQL Server helps you bring the productivity and user-satisfaction of flexible and responsive applications to your organization safely and securely. Your organization's increased ability to respond to rapidly changing business scenarios will build competitive advantage in an increasingly crowded and competitive global marketplace. With a focus on new applications and modern database architecture, this edition illustrates that dynamic SQL continues to evolve and be a valuable tool for administration, performance optimization, and analytics. What You'ill Learn Build flexible applications that respond to changing business needs Take advantage of creative, innovative, and productive uses of dynamic SQL Know about SQL injection and be confident in your defenses against it Address performance concerns in stored procedures and dynamic SQL Troubleshoot and debug dynamic SQL to ensure correct results Automate your administration of features within SQL Server Who This Book is For Developers and database administrators looking to hone and build their T-SQL coding skills. The book is ideal for developers wanting to plumb the depths of application flexibility and troubleshoot performance issues involving dynamic SQL. The book is also ideal for programmers wanting to learn what dynamic SQL is about and how it can help them deliver competitive advantage to their organizations. Take a deep dive into the many uses of dynamic SQL in Microsoft SQL Server. This edition has been updated to use the newest features in SQL Server 2016 and SQL Server 2017 as well as incorporating the changing landscape of analytics and database administration. Code examples have been updated with new system objects and functions to improve efficiency and maintainability.Executing dynamic SQL is key to large-scale searching based on user-entered criteria. Dynamic SQL can generate lists of values and even code with minimal impact on performance. Dynamic SQL enables dynamic pivoting of data for business intelligence solutions as well as customizing of database objects. Yet dynamic SQL is feared by many due to concerns over SQL injection or code maintainability.**__Dynamic SQL: Applications, Performance, and Security in Microsoft SQL Server__****What You'ill Learn**Build flexible applications that respond to changing business needsTake advantage of creative, innovative, and productive uses of dynamic SQLKnow about SQL injection and be confident in your defenses against itAddress performance concerns in stored procedures and dynamic SQLTroubleshoot and debug dynamic SQL to ensure correct resultsAutomate your administration of features within SQL Server**Who This Book is For**Developers and database administrators looking to hone and build their T-SQL coding skills. The book is ideal for developers wanting to plumb the depths of application flexibility and troubleshoot performance issues involving dynamic SQL. The book is also ideal for programmers wanting to learn what dynamic SQL is about and how it can help them deliver competitive advantage to their organizations.