Apress - Pro SQL Server 2008 Relational Database Design and Implementation (2008)01

Chia sẻ: Hoang Nhan | Ngày: | Loại File: PDF | Số trang:15

Thêm vào BST

Báo xấu

2.008
lượt xem 20
download

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

Apress - Pro SQL Server 2008 Relational Database Design and Implementation (2008)01

Chủ đề:

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

Lưu

Nội dung Text: Apress - Pro SQL Server 2008 Relational Database Design and Implementation (2008)01

The eXperT’s Voice ® in sQl serVer Pro SQL Server 2008 Relational Database Design and Implementation Design for performance! Create optimized SQL Server databases that scale well and are logically sound in order to meet your business requirements. Louis Davidson With Kevin Kline, Scott Klein, and Kurt Windisch Foreword by Paul Nielsen, SQL Server MVP
Pro SQL Server 2008 Relational Database Design and Implementation Louis Davidson With Kevin Kline, Scott Klein, and Kurt Windisch
Pro SQL Server 2008 Relational Database Design and Implementation Copyright © 2008 by Louis Davidson 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-0866-2 ISBN-13 (electronic): 978-1-4302-0867-9 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. Java™ and all Java-based marks are trademarks or registered trademarks of Sun Microsystems, Inc., in the US and other countries. Apress, Inc., is not affiliated with Sun Microsystems, Inc., and this book was writ- ten without endorsement from Sun Microsystems, Inc. Lead Editor: Jon Gennick Technical Reviewers: Wayne Snyder, Evan Terry, Don Watters Editorial Board: Clay Andres, Steve Anglin, Ewan Buckingham, Tony Campbell, Gary Cornell, Jonathan Gennick, Matthew Moodie, Joseph Ottinger, Jeffrey Pepper, Frank Pohlmann, Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh Project Manager: Tracy Brown Collins Copy Editors: Heather Lang, Kim Wimpsett Associate Production Director: Kari Brooks-Copony Production Editor: Ellie Fountain Compositor: Lynn L’Heureux Proofreader: Patrick Vincent Indexer: Broccoli Information Management Artist: Kinetic Publishing Services, LLC Cover Designer: Kurt Krames Manufacturing Director: Tom Debolski 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 contact Apress directly at 2855 Telegraph Avenue, Suite 600, Berkeley, CA 94705. Phone 510-549-5930, fax 510-549-5939, 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 precau- tion 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.
To my father-in-law, Verlin Wheeler. He passed away during the writing of this book, and I really never got to know him as I was told he was in his younger days. At the very least, he rivaled my love for gadgets and was an early adopter of lots of technology, like satellite dishes and even computers. I use his computer every day to manage my media collection— he probably would have liked that, though I think he would have preferred to have used the media in the library.
Contents at a Glance Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii About the Technical Reviewers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii sCHAPTER 1 Introduction to Database Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 sCHAPTER 2 The Language of Data Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 sCHAPTER 3 Conceptual Data Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 sCHAPTER 4 The Normalization Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 sCHAPTER 5 Implementing the Base Table Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177 sCHAPTER 6 Protecting the Integrity of Your Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 273 sCHAPTER 7 Patterns and Query Techniques. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339 sCHAPTER 8 Securing Access to the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385 sCHAPTER 9 Table Structures and Indexing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447 sCHAPTER 10 Coding for Concurrency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 499 sCHAPTER 11 Considering Data Access Strategies. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 555 sAPPENDIX A Codd’s 12 Rules for an RDBMS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595 sAPPENDIX B Scalar Datatype Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603 sINDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 635 v
Contents Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii About the Technical Reviewers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii sCHAPTER 1 Introduction to Database Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Database Design Phases . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Conceptual . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Logical. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 Physical . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Relational Data Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Database and Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Tables, Rows, and Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 The Information Principle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Domains . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Metadata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16 Missing Values (NULLs) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Relationship Between Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 Data Access Language (SQL) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Understanding Dependencies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Functional Dependency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Determinant . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32 sCHAPTER 2 The Language of Data Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Introduction to Data Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Attributes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Primary Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39 Alternate Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 vii
viii sCONTENTS Foreign Keys . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Domains . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Naming . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Identifying Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Nonidentifying Relationships . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Role Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 50 Relationship Cardinality. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 52 Verb Phrases (Relationship Names) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59 Descriptive Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Alternative Modeling Methodologies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Information Engineering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63 Chen ERD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65 Visio . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66 Management Studio Database Diagrams . . . . . . . . . . . . . . . . . . . . . . . . . . . 67 Best Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 sCHAPTER 3 Conceptual Data Modeling . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71 Understanding the Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72 Documenting the Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 Requirements Gathering . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74 Client Interviews . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Questions to Be Answered . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 Existing Systems and Prototypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 Other Types of Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80 Identifying Objects and Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82 Identifying Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 Relationships Between Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 Identifying Attributes and Domains . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98 Identifying Business Rules and Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108 Identifying Business Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Identifying Fundamental Processes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110 Finishing the Conceptual Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 Identifying Obvious Additional Data Needs . . . . . . . . . . . . . . . . . . . . . . . . . 113 Review with the Client . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 Repeat Until the Customer Agrees with Your Model . . . . . . . . . . . . . . . . . 114 Best Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115
sCONTENTS ix sCHAPTER 4 The Normalization Process . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 Why Normalize? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 Eliminating Duplicated Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 Avoiding Unnecessary Coding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 Keeping Tables Thin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 Maximizing Clustered Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119 Lowering the Number of Indexes Per Table . . . . . . . . . . . . . . . . . . . . . . . . 119 How Far to Normalize? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 The Process of Normalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120 Entity and Attribute Shape: First Normal Form . . . . . . . . . . . . . . . . . . . . . . . . . . . 121 All Attributes Must Be Atomic . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121 All Instances in an Entity Must Contain the Same Number of Values. . . . 126 All Occurrences of an Entity Type in an Entity Must Be Different . . . . . . . 127 Programming Anomalies Avoided by First Normal Form . . . . . . . . . . . . . . 128 Clues That An Existing Design Is Not in First Normal Form . . . . . . . . . . . . 133 Relationships Between Attributes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 Second Normal Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135 Third Normal Form. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Boyce-Codd Normal Form. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 147 Multivalued Dependencies in Entities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 Fourth Normal Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 Fifth Normal Form . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165 Denormalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 Best Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 Bonus Example. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 The Story of the Book So Far . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 sCHAPTER 5 Implementing the Base Table Structures . . . . . . . . . . . . . . . . . . . 177 Reviewing the Logical Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 Transforming the Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 Choosing Names . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 182 Dealing with Subtypes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 186 Determining Tree Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190 Choosing Key Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191 Determining Domain Implementation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198 Setting Up Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 Reviewing the “Final” Implementation Model . . . . . . . . . . . . . . . . . . . . . . 210
x sCONTENTS Implementing the Design . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 211 Creating the Basic Table Structures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212 Adding Uniqueness Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221 Building Default Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 228 Adding Relationships (Foreign Keys) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 235 Dealing with Collations and Sorting . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 246 Computed Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 251 Implementing User-Defined Datatypes . . . . . . . . . . . . . . . . . . . . . . . . . . . . 254 Documenting Your Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 263 Working with Dependency Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . 266 Best Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 270 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271 sCHAPTER 6 Protecting the Integrity of Your Data . . . . . . . . . . . . . . . . . . . . . . . . 273 Best Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 274 Automatic Data Protection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 276 Declarative Data Protection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 277 Basic Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 279 CHECK Constraints Based on Simple Expressions . . . . . . . . . . . . . . . . . . . 281 CHECK Constraints Based on Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . 283 Errors Caused by Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 288 DML Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 290 Handling Errors from Triggers and Constraints . . . . . . . . . . . . . . . . . . . . . 327 Manual Data Protection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 330 Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 332 Client Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 334 More Best Practices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 338 sCHAPTER 7 Patterns and Query Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 339 Precalculated Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 340 Sequence Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341 Calculations with Dates . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 348 Binary Large Valued Objects (BLOB) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 357 Storing User-Specified Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 360 Big Old List of Generic Columns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 362 Entity-Attribute-Value (EAV) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 363 Adding Columns to a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 367 Commonly Implemented Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 373
sCONTENTS xi Anti-patterns . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 374 One-Size-Fits-All Key Domain . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375 Generic Key References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 378 Overusing Unstructured Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 383 The Continuing Story of the Book So Far . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 384 sCHAPTER 8 Securing Access to the Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 385 Principals and Securables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 387 Database Security Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 388 Impersonation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 389 Permissions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 391 Controlling Access to Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393 Roles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396 Schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 403 Controlling Object Access Via T-SQL Coded Objects . . . . . . . . . . . . . . . . . . . . . . 405 Stored Procedures and Scalar Functions . . . . . . . . . . . . . . . . . . . . . . . . . . 406 Impersonation Within Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 408 Crossing Database Lines. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413 Different Server (Distributed Queries) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 418 Views and Table-Valued Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419 General Usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 419 Implementing Configurable Row-Level Security with Views . . . . . . . . . . . 422 Obfuscating Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426 Monitoring and Auditing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 429 Server and Database Audit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 430 Watching Table History Using DML Triggers. . . . . . . . . . . . . . . . . . . . . . . . 434 DDL Triggers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 438 Logging with Profiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441 Best Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 444 sCHAPTER 9 Table Structures and Indexing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 447 Physical Database Structure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 448 Files and Filegroups. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 448 Extents and Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 452 Data on Pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 454 Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 458 Indexes Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460 Basic Index Structure. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460
xii sCONTENTS Index Types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 462 Clustered Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 463 Nonclustered Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 464 Nonclustered Indexes on Clustered Tables . . . . . . . . . . . . . . . . . . . . . . . . . 465 Basics of Index Creation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 468 Basic Index Usage Patterns. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470 Using Clustered Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 472 Using Nonclustered Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 474 Using Unique Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487 Advanced Index Usage Scenarios . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487 Foreign Key Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487 Indexed Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 492 Best Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 495 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 496 sCHAPTER 10 Coding for Concurrency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 499 What Is Concurrency? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 500 Query Optimization Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 501 OS and Hardware Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 504 Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 505 Transaction Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 506 Compiled SQL Server Code . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 514 SQL Server Concurrency Controls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 522 Locks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 522 Isolation Levels. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 527 Coding for Integrity and Concurrency . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539 Pessimistic Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 539 Implementing a Single Threaded Code Block . . . . . . . . . . . . . . . . . . . . . . 541 Optimistic Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 544 Row-Based Locking. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 546 Logical Unit of Work . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 550 Best Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 552 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 553 sCHAPTER 11 Considering Data Access Strategies . . . . . . . . . . . . . . . . . . . . . . . . . 555 Ad Hoc SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 557 Advantages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 557 Pitfalls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 565
sCONTENTS xiii Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 569 Encapsulation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 571 Dynamic Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 572 Security . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 574 Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 576 Pitfalls . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 578 Opinions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 582 T-SQL and the CLR . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 584 Guidelines for Choosing T-SQL. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 588 Guidelines for Choosing .NET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589 CLR Object Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 589 Best Practices. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 592 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 594 sAPPENDIX A Codd’s 12 Rules for an RDBMS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595 Rule 1: The Information Rule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595 Rule 2: Guaranteed Access Rule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 596 Rule 3: Systematic Treatment of NULL Values . . . . . . . . . . . . . . . . . . . . . . . . . . . 596 Rule 4: Dynamic Online Catalog Based on the Relational Model . . . . . . . . . . . . 596 Rule 5: Comprehensive Data Sublanguage Rule . . . . . . . . . . . . . . . . . . . . . . . . . 597 Rule 6: View Updating Rule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597 Rule 7: High-Level Insert, Update, and Delete . . . . . . . . . . . . . . . . . . . . . . . . . . . 598 Rule 8: Physical Data Independence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 598 Rule 9: Logical Data Independence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599 Rule 10: Integrity Independence. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600 Rule 11: Distribution Independence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600 Rule 12: Non-Subversion Rule . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 600 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 601 sAPPENDIX B Scalar Datatype Reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 603 Precise Numeric Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 605 Integer Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 605 Decimal Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 608 Approximate Numeric Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 611 Date and Time Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613 date . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613 time [(precision)] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 613 smalldatetime. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 614 datetime . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 614 datetime2 [(precision)] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615 datetimeoffset [(precision)] . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615 Discussion on All Date Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 616