Expert SQL Server 2008 Development- P1

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

Thêm vào BST

Báo xấu

103
lượt xem 17
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Chuyên viên phát triển SQL Server 2008 là nhằm mục đích phát triển SQL Server đã sẵn sàng để di chuyển vượt ra ngoài Sách Online. Tác giả và nhà phát triển có kinh nghiệm Alastair Aitchison cho bạn thấy làm thế nào để suy nghĩ về phát triển SQL Server như thể nó là bất kỳ loại hình khác phát triển. Youll tìm hiểu để quản lý các thử nghiệm trong SQL Server và đúng đối phó với các lỗi và ngoại lệ. Cuốn sách cũng bao gồm các quan trọng, là trung tâm cơ sở dữ liệu các...

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Expert SQL Server 2008 Development- P1

THE EXPERT’S VOICE ® IN SQL SERVER Expert SQL Server 2008 Development Advanced SQL Server techniques for database professionals Alastair Aitchison and Adam Machanic
Expert SQL Server 2008 Development Alastair Aitchison Adam Machanic
Expert SQL Server 2008 Development Copyright © 2009 by Alastair Aitchison and Adam Machanic All rights reserved. No part of this work may be reproduced or transmitted in any form or by any means, electronic or mechanical, including photocopying, recording, or by any information storage or retrieval system, without the prior written permission of the copyright owner and the publisher. ISBN-13 (pbk): 978-1-4302-7213-7 ISBN-13 (electronic): 978-1-4302-7212-0 Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1 Trademarked names may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. President and Publisher: Paul Manning Lead Editor: Jonathan Gennick Technical Reviewer: Evan Terry Editorial Board: Clay Andres, Steve Anglin, Mark Beckner, Ewan Buckingham, Gary Cornell, Jonathan Gennick, Jonathan Hassell, Michelle Lowman, Matthew Moodie, Duncan Parkes, Jeffrey Pepper, Frank Pohlmann, Douglas Pundick, Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh Coordinating Editor: Mary Tobin Copy Editor: Damon Larson Compositor: Bytheway Publishing Services Indexer: Barbara Palumbo Artist: April Milne Cover Designer: Anna Ishchenko Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax 201-348-4505, e-mail orders-ny@springer- sbm.com, or visit http://www.springeronline.com. For information on translations, please e-mail info@apress.com, or visit http://www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing web page at http://www.apress.com/info/bulksales. The information in this book is distributed on an “as is” basis, without warranty. Although every precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have any liability to any person or entity with respect to any loss or damage caused or alleged to be caused directly or indirectly by the information contained in this work. The source code for this book is available to readers at http://www.apress.com. You will need to answer questions pertaining to this book in order to successfully download the code.
For Clare and Douglas
CONTENTS Contents at a Glance Contents at a Glance.............................................................................................. iv Contents.................................................................................................................. v About the Author.................................................................................................. xvi About the Technical Reviewer ............................................................................ xvii Acknowledgments ............................................................................................. xviii Preface................................................................................................................. xix Chapter 1: Software Development Methodologies for the Database World ............1 Chapter 2: Best Practices for Database Programming .........................................23 Chapter 3: Testing Database Routines..................................................................49 Chapter 4: Errors and Exceptions .........................................................................71 Chapter 5: Privilege and Authorization...............................................................101 Chapter 6: Encryption .........................................................................................121 Chapter 7: SQLCLR: Architecture and Design Considerations.............................159 Chapter 8: Dynamic T-SQL ..................................................................................195 Chapter 9: Designing Systems for Application Concurrency ..............................235 Chapter 10: Working with Spatial Data...............................................................283 Chapter 11: Working with Temporal Data...........................................................321 Chapter 12: Trees, Hierarchies, and Graphs .......................................................371 Index ....................................................................................................................419 iv
Contents Contents at a Glance .............................................................................................. iv Contents.................................................................................................................. v About the Author.................................................................................................. xvi About the Technical Reviewer ............................................................................ xvii Acknowledgments ............................................................................................. xviii Preface................................................................................................................. xix Chapter 1: Software Development Methodologies for the Database World ............1 Architecture Revisited .......................................................................................................1 Coupling.................................................................................................................................................... 3 Cohesion................................................................................................................................................... 4 Encapsulation ........................................................................................................................................... 5 Interfaces.................................................................................................................................................. 5 Interfaces As Contracts ............................................................................................................................ 6 Interface Design ....................................................................................................................................... 6 Integrating Databases and Object-Oriented Systems........................................................8 Data Logic............................................................................................................................................... 10 Business Logic........................................................................................................................................ 11 Application Logic .................................................................................................................................... 12 The “Object-Relational Impedance Mismatch” ...............................................................12 Are Tables Really Classes in Disguise? .................................................................................................. 13 Modeling Inheritance.............................................................................................................................. 14 ORM: A Solution That Creates Many Problems ................................................................17 v
CONTENTS Introducing the Database-As-API Mindset.......................................................................18 The Great Balancing Act ..................................................................................................19 Performance ........................................................................................................................................... 19 Testability ............................................................................................................................................... 20 Maintainability ........................................................................................................................................ 20 Security .................................................................................................................................................. 21 Allowing for Future Requirements.......................................................................................................... 21 Summary .........................................................................................................................22 Best Practices for Database Programming......................................................................23 Chapter 2: Best Practices for Database Programming .........................................23 Defensive Programming ..................................................................................................23 Attitudes to Defensive Programming...................................................................................................... 24 Why Use a Defensive Approach to Database Development?.................................................................. 27 Best Practice SQL Programming Techniques ..................................................................28 Identify Hidden Assumptions in Your Code............................................................................................. 29 Don’t Take Shortcuts .............................................................................................................................. 33 Testing.................................................................................................................................................... 36 Code Review........................................................................................................................................... 39 Validate All Input..................................................................................................................................... 40 Future-proof Your Code .......................................................................................................................... 42 Limit Your Exposure................................................................................................................................ 43 Exercise Good Coding Etiquette.............................................................................................................. 43 Comments ......................................................................................................................................... 44 Indentations and Statement Blocks................................................................................................... 45 If All Else Fails. . . ................................................................................................................................... 46 Creating a Healthy Development Environment ................................................................46 Summary .........................................................................................................................47 vi
CONTENTS Chapter 3: Testing Database Routines..................................................................49 Approaches to Testing .....................................................................................................49 Unit and Functional Testing .............................................................................................50 Unit Testing Frameworks ....................................................................................................................... 52 Regression Testing ................................................................................................................................. 55 Guidelines for Implementing Database Testing Processes and Procedures....................55 Why Is Testing Important?...................................................................................................................... 56 What Kind of Testing Is Important? ........................................................................................................ 56 How Many Tests Are Needed?................................................................................................................ 57 Will Management Buy In?....................................................................................................................... 58 Performance Monitoring Tools ........................................................................................58 Real-Time Client-Side Monitoring .......................................................................................................... 59 Server-Side Traces................................................................................................................................. 60 System Monitoring ................................................................................................................................. 61 Dynamic Management Views (DMVs)..................................................................................................... 62 Extended Events ..................................................................................................................................... 63 Data Collector ......................................................................................................................................... 65 Analyzing Performance Data ...........................................................................................67 Capturing Baseline Metrics .................................................................................................................... 67 Big-Picture Analysis ............................................................................................................................... 68 Granular Analysis.................................................................................................................................... 68 Fixing Problems: Is It Sufficient to Focus on the Obvious?..................................................................... 70 Summary .........................................................................................................................70 Chapter 4: Errors and Exceptions .........................................................................71 Exceptions vs. Errors .......................................................................................................71 How Exceptions Work in SQL Server ...............................................................................72 Statement-Level Exceptions................................................................................................................... 73 Batch-Level Exceptions .......................................................................................................................... 73 vii
CONTENTS Parsing and Scope-Resolution Exceptions ............................................................................................. 75 Connection and Server-Level Exceptions ............................................................................................... 76 The XACT_ABORT Setting ....................................................................................................................... 77 Dissecting an Error Message.................................................................................................................. 78 Error Number..................................................................................................................................... 78 Error Level ......................................................................................................................................... 79 Error State ......................................................................................................................................... 79 Additional Information ....................................................................................................................... 80 SQL Server’s RAISERROR Function......................................................................................................... 81 Formatting Error Messages ............................................................................................................... 82 Creating Persistent Custom Error Messages ..................................................................................... 83 Logging User-Thrown Exceptions...................................................................................................... 85 Monitoring Exception Events with Traces .............................................................................................. 85 Exception Handling ..........................................................................................................85 Why Handle Exceptions in T-SQL?.......................................................................................................... 86 Exception “Handling” Using @@ERROR................................................................................................. 86 SQL Server’s TRY/CATCH Syntax ............................................................................................................ 87 Getting Extended Error Information in the Catch Block ..................................................................... 89 Rethrowing Exceptions...................................................................................................................... 90 When Should TRY/CATCH Be Used? .................................................................................................. 91 Using TRY/CATCH to Build Retry Logic .............................................................................................. 91 Exception Handling and SQLCLR ............................................................................................................ 93 Transactions and Exceptions...........................................................................................96 The Myths of Transaction Abortion......................................................................................................... 96 XACT_ABORT: Turning Myth into (Semi-)Reality .................................................................................... 98 TRY/CATCH and Doomed Transactions................................................................................................... 99 Summary .......................................................................................................................100 Chapter 5: Privilege and Authorization...............................................................101 The Principle of Least Privilege .....................................................................................102 viii
CONTENTS Creating Proxies in SQL Server............................................................................................................. 103 Server-Level Proxies ....................................................................................................................... 103 Database-Level Proxies................................................................................................................... 104 Data Security in Layers: The Onion Model............................................................................................ 104 Data Organization Using Schemas.................................................................................105 Basic Impersonation Using EXECUTE AS .......................................................................107 Ownership Chaining.......................................................................................................110 Privilege Escalation Without Ownership Chains ............................................................112 Stored Procedures and EXECUTE AS .................................................................................................... 112 Stored Procedure Signing Using Certificates ....................................................................................... 114 Assigning Server-Level Permissions .................................................................................................... 117 Summary .......................................................................................................................119 Chapter 6: Encryption .........................................................................................121 Do You Really Need Encryption?....................................................................................121 What Should Be Protected?.................................................................................................................. 121 What Are You Protecting Against?........................................................................................................ 122 SQL Server 2008 Encryption Key Hierarchy...................................................................123 The Automatic Key Management Hierarchy ......................................................................................... 123 Symmetric Keys, Asymmetric Keys, and Certificates...................................................................... 124 Database Master Key....................................................................................................................... 125 Service Master Key.......................................................................................................................... 125 Alternative Encryption Management Structures................................................................................... 125 Symmetric Key Layering and Rotation ............................................................................................ 126 Removing Keys from the Automatic Encryption Hierarchy.............................................................. 126 Extensible Key Management ........................................................................................................... 127 Data Protection and Encryption Methods ......................................................................128 Hashing................................................................................................................................................. 129 Symmetric Key Encryption ................................................................................................................... 130 ix
CONTENTS Asymmetric Key Encryption.................................................................................................................. 134 Transparent Data Encryption ................................................................................................................ 136 Balancing Performance and Security ............................................................................139 Implications of Encryption on Query Design ..................................................................145 Equality Matching Using Hashed Message Authentication Codes........................................................ 148 Wildcard Searches Using HMAC Substrings......................................................................................... 153 Range Searches.................................................................................................................................... 157 Summary .......................................................................................................................158 Chapter 7: SQLCLR: Architecture and Design Considerations.............................159 Bridging the SQL/CLR Gap: The SqlTypes Library..........................................................160 Wrapping Code to Promote Cross-Tier Reuse ...............................................................161 The Problem ......................................................................................................................................... 161 One Reasonable Solution...................................................................................................................... 161 A Simple Example: E-Mail Address Format Validation ......................................................................... 162 SQLCLR Security and Reliability Features .....................................................................163 Security Exceptions .............................................................................................................................. 164 Host Protection Exceptions................................................................................................................... 165 The Quest for Code Safety .................................................................................................................... 168 Selective Privilege Escalation via Assembly References...................................................................... 168 Working with Host Protection Privileges ......................................................................................... 169 Working with Code Access Security Privileges ............................................................................... 173 Granting Cross-Assembly Privileges..................................................................................................... 175 Database Trustworthiness............................................................................................................... 175 Strong Naming................................................................................................................................. 177 Performance Comparison: SQLCLR vs. TSQL.................................................................178 Creating a “Simple Sieve” for Prime Numbers..................................................................................... 179 Calculating Running Aggregates .......................................................................................................... 181 String Manipulation .............................................................................................................................. 183 x
CONTENTS Enhancing Service Broker Scale-Out with SQLCLR .......................................................185 XML Serialization.................................................................................................................................. 185 XML Deserialization .............................................................................................................................. 186 Binary Serialization with SQLCLR ......................................................................................................... 187 Binary Deserialization........................................................................................................................... 191 Summary .......................................................................................................................194 Chapter 8: Dynamic T-SQL ..................................................................................195 Dynamic T-SQL vs. Ad Hoc T-SQL..................................................................................196 The Stored Procedure vs. Ad Hoc SQL Debate...............................................................196 Why Go Dynamic?..........................................................................................................197 Compilation and Parameterization ....................................................................................................... 198 Auto-Parameterization.......................................................................................................................... 200 Application-Level Parameterization...................................................................................................... 202 Performance Implications of Parameterization and Caching ............................................................... 203 Supporting Optional Parameters....................................................................................205 Optional Parameters via Static T-SQL .................................................................................................. 206 Going Dynamic: Using EXECUTE ........................................................................................................... 212 SQL Injection......................................................................................................................................... 218 sp_executesql: A Better EXECUTE ........................................................................................................ 220 Performance Comparison..................................................................................................................... 223 Dynamic SQL Security Considerations...........................................................................230 Permissions to Referenced Objects...................................................................................................... 230 Interface Rules ..................................................................................................................................... 230 Summary .......................................................................................................................232 Chapter 9: Designing Systems for Application Concurrency ..............................235 The Business Side: What Should Happen When Processes Collide?.............................236 Isolation Levels and Transactional Behavior .................................................................237 Blocking Isolation Levels ...................................................................................................................... 239 xi
CONTENTS READ COMMITTED Isolation............................................................................................................. 239 REPEATABLE READ Isolation............................................................................................................ 239 SERIALIZABLE Isolation.................................................................................................................... 240 Nonblocking Isolation Levels ................................................................................................................ 241 READ UNCOMMITTED Isolation ........................................................................................................ 241 SNAPSHOT Isolation ........................................................................................................................ 242 From Isolation to Concurrency Control ................................................................................................. 242 Preparing for the Worst: Pessimistic Concurrency ........................................................243 Progressing to a Solution ..................................................................................................................... 244 Enforcing Pessimistic Locks at Write Time .......................................................................................... 249 Application Locks: Generalizing Pessimistic Concurrency ................................................................... 250 Hoping for the Best: Optimistic Concurrency.................................................................259 Embracing Conflict: Multivalue Concurrency Control ....................................................266 Sharing Resources Between Concurrent Users.............................................................269 Controlling Resource Allocation............................................................................................................ 272 Calculating Effective and Shared Maximum Resource Allocation ........................................................ 277 Controlling Concurrent Request Processing ......................................................................................... 279 Summary .......................................................................................................................281 Chapter 10: Working with Spatial Data...............................................................283 Modeling Spatial Data....................................................................................................283 Spatial Reference Systems................................................................................................................... 286 Geographic Coordinate Systems ..................................................................................................... 286 Projected Coordinate Systems ........................................................................................................ 286 Applying Coordinate Systems to the Earth ........................................................................................... 288 Datum .............................................................................................................................................. 288 Prime Meridian ................................................................................................................................ 288 Projection ........................................................................................................................................ 289 Spatial Reference Identifiers ................................................................................................................ 290 xii
CONTENTS Geography vs. Geometry................................................................................................292 Standards Compliance ......................................................................................................................... 293 Accuracy............................................................................................................................................... 294 Technical Limitations and Performance ............................................................................................... 294 Creating Spatial Data.....................................................................................................296 Well-Known Text .................................................................................................................................. 296 Well-Known Binary ............................................................................................................................... 297 Geography Markup Language............................................................................................................... 298 Importing Data...................................................................................................................................... 298 Querying Spatial Data ....................................................................................................302 Nearest-Neighbor Queries.................................................................................................................... 304 Finding Locations Within a Given Bounding Box .................................................................................. 308 Spatial Indexing .............................................................................................................313 How Does a Spatial Index Work?.......................................................................................................... 313 Optimizing the Grid............................................................................................................................... 315 Summary .......................................................................................................................319 Chapter 11: Working with Temporal Data...........................................................321 Modeling Time-Based Information ................................................................................321 SQL Server’s Date/Time Data Types ..............................................................................322 Input Date Formats ............................................................................................................................... 323 Output Date Formatting ........................................................................................................................ 325 Efficiently Querying Date/Time Columns .............................................................................................. 326 Date/Time Calculations......................................................................................................................... 329 Truncating the Time Portion of a datetime Value ............................................................................ 330 Finding Relative Dates..................................................................................................................... 332 How Many Candles on the Birthday Cake?...................................................................................... 335 Defining Periods Using Calendar Tables........................................................................336 Dealing with Time Zones ...............................................................................................341 xiii
CONTENTS Storing UTC Time.................................................................................................................................. 343 Using the datetimeoffset Type.............................................................................................................. 344 Working with Intervals...................................................................................................346 Modeling and Querying Continuous Intervals ....................................................................................... 347 Modeling and Querying Independent Intervals ..................................................................................... 354 Overlapping Intervals............................................................................................................................ 358 Time Slicing.......................................................................................................................................... 362 Modeling Durations........................................................................................................365 Managing Bitemporal Data ............................................................................................366 Summary .......................................................................................................................370 Chapter 12: Trees, Hierarchies, and Graphs .......................................................371 Terminology: Everything Is a Graph...............................................................................371 The Basics: Adjacency Lists and Graphs .......................................................................373 Constraining the Edges......................................................................................................................... 374 Basic Graph Queries: Who Am I Connected To? ................................................................................... 376 Traversing the Graph ............................................................................................................................ 378 Adjacency List Hierarchies ............................................................................................388 Finding Direct Descendants.................................................................................................................. 389 Traversing down the Hierarchy ............................................................................................................ 391 Ordering the Output ......................................................................................................................... 392 Are CTEs the Best Choice? .............................................................................................................. 396 Traversing up the Hierarchy ................................................................................................................. 400 Inserting New Nodes and Relocating Subtrees .................................................................................... 401 Deleting Existing Nodes........................................................................................................................ 401 Constraining the Hierarchy................................................................................................................... 402 Persisted Materialized Paths .........................................................................................405 Finding Subordinates............................................................................................................................ 406 Navigating up the Hierarchy ................................................................................................................. 407 xiv
CONTENTS Inserting Nodes .................................................................................................................................... 408 Relocating Subtrees ............................................................................................................................. 409 Deleting Nodes ..................................................................................................................................... 411 Constraining the Hierarchy................................................................................................................... 411 The hierarchyid Datatype...............................................................................................412 Finding Subordinates............................................................................................................................ 413 Navigating up the Hierarchy ................................................................................................................. 414 Inserting Nodes .................................................................................................................................... 415 Relocating Subtrees ............................................................................................................................. 416 Deleting Nodes ..................................................................................................................................... 417 Constraining the Hierarchy................................................................................................................... 417 Summary .......................................................................................................................418 Index ................................................................................................................419 xv
About the Author Alastair Aitchison is a freelance technology consultant based in Norwich, England. He has experience across a wide variety of software and service platforms, and has worked with SQL Server 2008 since the earliest technical previews were made publicly available. He has implemented various SQL Server solutions requiring highly concurrent processes and large data warehouses in the financial services sector, combined with reporting and analytical capability based on the Microsoft business intelligence stack. Alastair has a particular interest in analysis of spatial data, and is the author of Beginning Spatial with SQL Server 2008 (Apress, 2009). He speaks at user groups and conferences, and is a highly active contributor to several online support communities, including the Microsoft SQL Server Developer Center forums. xvi
About the Technical Reviewer Evan Terry is the Chief Technical Consultant at The Clegg Company, specializing in data management, information and data architecture, database systems, and business intelligence. His past and current clients include the State of Idaho, Albertsons, American Honda Motors, and Toyota Motor Sales, USA. He is the coauthor of Beginning Relational Data Modeling, has published several articles in DM Review, and has presented at industry conferences and conducted private workshops on the subjects of data and information quality, and information management. He has also been the technical reviewer of several Apress books relating to SQL Server databases. For questions or consulting needs, Evan can be reached at evan_terry@cleggcompany.com. xvii
CONTENTS Acknowledgments When I was asked to write this book, I jumped at the chance to work with the great bunch of folks at Apress again. I am particularly lucky to have the assistance once more of two hugely talented individuals, in the form of Jonathan Gennick and Evan Terry. As my editor, Jonathan has encouraged, taught, and mentored me through the authoring process, and has never wavered in his support even when the going got a bit tough (which, as in any publication schedule, at times it did!). Evan not only provided the benefit of his wealth of technical knowledge, but also his authoring expertise, and at times he simply provided a sensible voice of reason, all of which helped to improve the book significantly. I would also like to thank Mary Tobin, who managed to keep track of all the deadlines and project management issues, Damon Larson, for correcting my wayward use of the English language, and all the other individuals who helped get this book into the form that you are now holding in your hands. Thank you all. My family have once again had to endure me spending long hours typing away at the keyboard, and I thank them for their tolerance, patience, and support. I couldn’t do anything without them. And thankyou to you, the reader, for purchasing this book. I hope that you find the content interesting, useful, and above all, enjoyable to read. xviii