Table Of Content

570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page i SQL Server 2005 T-SQL Recipes A Problem-Solution Approach Joseph Sack 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page ii SQL Server 2005 T-SQL Recipes: AProblem-Solution Approach Copyright © 2006 by Joseph Sack 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 (pbk): 1-59059-570-X 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 atrademark symbol with every occurrence of atrademarked 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. Lead Editor: Tony Davis Technical Reviewer: Evan Terry Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Tony Davis, Jason Gilmore, Jonathan Hassell, Chris Mills, Dominic Shakeshaft, Jim Sumser Project Manager: Beth Christmas Copy Edit Manager: Nicole LeClerc Copy Editor: Julie M. Smith Assistant Production Director: Kari Brooks-Copony Production Editor: Katie Stence Compositor and Artist: Kinetic Publishing Services, LLC Proofreader: Patrick Vincent Indexer: Brenda Miller 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 [email protected], or visit http://www.springeronline.com. For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley, CA 94710. Phone 510-549-5930, fax 510-549-5939, e-mail [email protected], or visit http://www.apress.com. 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.comin the Source Code section. You will need to answer questions pertaining to this book in order to successfully download the code. 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page iii Contents at a Glance About the Author. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii About the Technical Reviewer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv Acknowledgments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix ■ CHAPTER 1 SELECT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 ■ CHAPTER 2 INSERT,UPDATE,DELETE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 ■ CHAPTER 3 Transactions,Locking,Blocking,and Deadlocking . . . . . . . . . . . . . . 83 ■ CHAPTER 4 Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 ■ CHAPTER 5 Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 ■ CHAPTER 6 Full-Text Search. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 ■ CHAPTER 7 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191 ■ CHAPTER 8 SQL Server Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 ■ CHAPTER 9 Conditional Processing,Control-Of-Flow,and Cursors. . . . . . . . . . 255 ■ CHAPTER 10 Stored Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271 ■ CHAPTER 11 User-Defined Functions and Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289 ■ CHAPTER 12 Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 ■ CHAPTER 13 CLR Integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337 ■ CHAPTER 14 XML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355 ■ CHAPTER 15 Web Services. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375 ■ CHAPTER 16 Error Handling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393 ■ CHAPTER 17 Principals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407 ■ CHAPTER 18 Securables and Permissions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 433 ■ CHAPTER 19 Encryption. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459 ■ CHAPTER 20 Service Broker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485 ■ CHAPTER 21 Configuring and Viewing SQL Server Options . . . . . . . . . . . . . . . . . . 517 ■ CHAPTER 22 Creating and Configuring Databases . . . . . . . . . . . . . . . . . . . . . . . . . . 523 iii 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page iv ■ CHAPTER 23 Database Integrity and Optimization. . . . . . . . . . . . . . . . . . . . . . . . . . . 571 ■ CHAPTER 24 Maintaining Database Objects and Object Dependencies. . . . . . . 589 ■ CHAPTER 25 Database Mirroring. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595 ■ CHAPTER 26 Database Snapshots. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615 ■ CHAPTER 27 Linked Servers and Distributed Queries . . . . . . . . . . . . . . . . . . . . . . . 621 ■ CHAPTER 28 Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637 ■ CHAPTER 29 Backup and Recovery. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683 INDEX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 717 iv 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page v Contents About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi Acknowledgments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv ■ CHAPTER 1 SELECT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 The Basic SELECT Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Selecting Specific Columns for Every Row. . . . . . . . . . . . . . . . . . . . . . 2 Selecting Every Column for Every Row . . . . . . . . . . . . . . . . . . . . . . . . . 3 Selective Querying Using a Basic WHERE Clause . . . . . . . . . . . . . . . . . . . . . 3 Using the WHERE Clause to Specify Rows Returned in the Result Set. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Combining Search Conditions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Negating a Search Condition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Keeping Your WHERE Clause Unambiguous . . . . . . . . . . . . . . . . . . . . . 6 Using Operators and Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Using BETWEEN for Date Range Searches . . . . . . . . . . . . . . . . . . . . . . 8 Using Comparisons. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Checking for NULL Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Returning Rows Based on a List of Values . . . . . . . . . . . . . . . . . . . . . 10 Using Wildcards with LIKE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Ordering Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Using the ORDER BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Using the TOP Keyword with Ordered Results . . . . . . . . . . . . . . . . . . 13 Grouping Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Using the GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Using GROUP BY ALL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Selectively Querying Grouped Data Using HAVING. . . . . . . . . . . . . . . 17 v 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page vi vi ■ CONTENTS SELECT Clause Techniques. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Using DISTINCT to Remove Duplicate Values . . . . . . . . . . . . . . . . . . 19 Using DISTINCT in Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . 19 Using Column Aliases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Using SELECT to Create a Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Performing String Concatenation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Creating a Comma Delimited List Using SELECT. . . . . . . . . . . . . . . . 22 Using the INTO Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 SubQueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Using Subqueries to Check for the Existence of Matches . . . . . . . . 25 Querying from More Than One Data Source. . . . . . . . . . . . . . . . . . . . . . . . . 26 Using INNER Joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Using OUTER Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Using CROSS Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Performing Self-Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Using Derived Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Combining Result Sets with UNION . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Using APPLY to Invoke a Table-Valued Function for Each Row . . . . . . . . . 33 Using CROSS APPLY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Using OUTER APPLY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Data Source Advanced Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Using the TABLESAMPLE to Return Random Rows. . . . . . . . . . . . . . 37 Using PIVOT to Convert Single Column Values into Multiple Columns and Aggregate Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Normalizing Data with UNPIVOT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Returning Distinct or Matching Rows Using EXCEPT and INTERSECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Summarizing Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Summarizing Data with WITH CUBE . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Using GROUPING with WITH CUBE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Summarizing Data with WITH ROLLUP. . . . . . . . . . . . . . . . . . . . . . . . . 46 Hints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Using Join Hints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Using Query Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Using Table Hints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Common Table Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Using a Non-Recursive Common Table Expression (CTE). . . . . . . . . 53 Using a Recursive Common Table Expression (CTE) . . . . . . . . . . . . . 56 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page vii ■ CONTENTS vii ■ CHAPTER 2 INSERT,UPDATE,DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 INSERT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Inserting a Row into a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Inserting a Row Using Default Values. . . . . . . . . . . . . . . . . . . . . . . . . . 63 Explicitly Inserting a Value into an IDENTITY Column . . . . . . . . . . . . 64 Inserting a Row into a Table with a Uniqueidentifier Column. . . . . . 65 Inserting Rows Using an INSERT...SELECT Statement. . . . . . . . . . . . 66 Inserting Data from a Stored Procedure Call. . . . . . . . . . . . . . . . . . . . 67 UPDATE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 Updating a Single Row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 Updating Rows Based on a FROM and WHERE Clause. . . . . . . . . . . 70 Updating Large Value Data Type Columns. . . . . . . . . . . . . . . . . . . . . . 71 Inserting or Updating an Image File Using OPENROWSET and BULK. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Deleting Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 Truncating a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 The OUTPUT Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Using the OUTPUT Clause with INSERT,UPDATE,DELETE . . . . . . . . 78 Chunking Data Modifications with TOP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Deleting Rows in Chunks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 ■ CHAPTER 3 Transactions,Locking,Blocking,and Deadlocking. . . . . . . 83 Transaction Control. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 Using Explicit Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Displaying the Oldest Active Transaction with DBCC OPENTRAN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 Locking. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 Viewing Lock Activity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 Transaction,Locking,and Concurrency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 Using SET TRANSACTION ISOLATION LEVEL. . . . . . . . . . . . . . . . . . . . 94 Blocking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Identifying and Resolving Blocking Processes . . . . . . . . . . . . . . . . . . 99 Using SET LOCK TIMEOUT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 Deadlocking. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 Identifying Deadlocks with a Trace Flag. . . . . . . . . . . . . . . . . . . . . . . 103 Setting Deadlock Priority . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page viii viii ■ CONTENTS ■ CHAPTER 4 Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Table Basics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Creating a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 Adding a Column to an Existing Table . . . . . . . . . . . . . . . . . . . . . . . . 112 Changing a Column Definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 Creating a Computed Column. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 Dropping a Table Column. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 Reporting Table Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Dropping a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Collation Basics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Viewing Collation Metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 Designating a Column’s Collation. . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 Creating a Table with a Primary Key. . . . . . . . . . . . . . . . . . . . . . . . . . 120 Adding a Primary Key Constraint to an Existing Table. . . . . . . . . . . 121 Creating a Table with a Foreign Key Reference . . . . . . . . . . . . . . . . 121 Adding a Foreign Key to an Existing Table. . . . . . . . . . . . . . . . . . . . . 122 Creating Recursive Foreign Key References. . . . . . . . . . . . . . . . . . . 123 Allowing Cascading Changes in Foreign Keys . . . . . . . . . . . . . . . . . 124 Surrogate Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 Using the IDENTITY Property During Table Creation . . . . . . . . . . . . 126 Using DBCC CHECKIDENT to View and Correct IDENTITY Seed Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 Using the ROWGUIDCOL Property. . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 Creating a Unique Constraint. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130 Adding a UNIQUE Constraint to an Existing Table. . . . . . . . . . . . . . . 131 Using CHECK Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 Adding a CHECK Constraint to an Existing Table . . . . . . . . . . . . . . . 133 Disabling and Enabling a Constraint. . . . . . . . . . . . . . . . . . . . . . . . . . 134 Using a DEFAULT Constraint During Table Creation. . . . . . . . . . . . . 135 Adding a DEFAULT Constraint to an Existing Table. . . . . . . . . . . . . . 136 Dropping a Constraint from a Table . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Temporary Tables and Table Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Using a Temporary Table for Multiple Lookups Within a Batch. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138 Creating a Table Variable to Hold a Temporary Result Set . . . . . . . 140 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page ix ■ CONTENTS ix Manageability for Very Large Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141 Implementing Table Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 Determining the Location of Data in a Partition. . . . . . . . . . . . . . . . 145 Adding a New Partition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146 Removing a Partition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148 Moving a Partition to a Different Table. . . . . . . . . . . . . . . . . . . . . . . . 149 Removing Partition Functions and Schemes. . . . . . . . . . . . . . . . . . . 151 Placing a Table on a Filegroup. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 ■ CHAPTER 5 Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 Indexes Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 Create a Table Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 Enforce Uniqueness on Non-Key Columns . . . . . . . . . . . . . . . . . . . . 159 Create a Composite Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160 Define Index Column Sort Direction. . . . . . . . . . . . . . . . . . . . . . . . . . 161 View Index Meta Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 Disable an Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 Dropping Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 Change an Existing Index with DROP_EXISTING . . . . . . . . . . . . . . . 164 Controlling Index Build Performance and Concurrency. . . . . . . . . . . . . . . 164 Intermediate Index Creation in Tempdb. . . . . . . . . . . . . . . . . . . . . . . 165 Controlling Parallel Plan Execution for Index Creation. . . . . . . . . . . 165 Allowing User Table Access During Index Creation . . . . . . . . . . . . . 166 Index Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 Using an Index INCLUDE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 Using PAD_INDEX and FILLFACTOR . . . . . . . . . . . . . . . . . . . . . . . . . . 167 Disabling Page and/or Row Index Locking . . . . . . . . . . . . . . . . . . . . 168 Managing Very Large Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 Creating an Index on a Filegroup . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 Implementing Index Partitioning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170 ■ CHAPTER 6 Full-Text Search. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 Full-Text Indexes and Catalogs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 Creating a Full-Text Catalog. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 Creating a Full-Text Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 Modifying a Full-Text Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177 Modifying a Full-Text Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178 Dropping a Full-Text Catalog. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 Dropping a Full-Text Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 ta. . . . . . . . . . . . . . . 181

ebook img

SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach PDF

769 Pages·2005·3.911 MB·English
by  Joseph Sack
#Computers #System Administration
Save to my drive
Quick download
Download
Upgrade Premium
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview SQL Server 2005 T-SQL Recipes: A Problem-Solution Approach

570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page i SQL Server 2005 T-SQL Recipes A Problem-Solution Approach Joseph Sack 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page ii SQL Server 2005 T-SQL Recipes: AProblem-Solution Approach Copyright © 2006 by Joseph Sack 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 (pbk): 1-59059-570-X 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 atrademark symbol with every occurrence of atrademarked 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. Lead Editor: Tony Davis Technical Reviewer: Evan Terry Editorial Board: Steve Anglin, Dan Appleman, Ewan Buckingham, Gary Cornell, Tony Davis, Jason Gilmore, Jonathan Hassell, Chris Mills, Dominic Shakeshaft, Jim Sumser Project Manager: Beth Christmas Copy Edit Manager: Nicole LeClerc Copy Editor: Julie M. Smith Assistant Production Director: Kari Brooks-Copony Production Editor: Katie Stence Compositor and Artist: Kinetic Publishing Services, LLC Proofreader: Patrick Vincent Indexer: Brenda Miller 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 [email protected], or visit http://www.springeronline.com. For information on translations, please contact Apress directly at 2560 Ninth Street, Suite 219, Berkeley, CA 94710. Phone 510-549-5930, fax 510-549-5939, e-mail [email protected], or visit http://www.apress.com. 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.comin the Source Code section. You will need to answer questions pertaining to this book in order to successfully download the code. 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page iii Contents at a Glance About the Author. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii About the Technical Reviewer. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv Acknowledgments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxvii Introduction. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxix ■ CHAPTER 1 SELECT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 ■ CHAPTER 2 INSERT,UPDATE,DELETE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 ■ CHAPTER 3 Transactions,Locking,Blocking,and Deadlocking . . . . . . . . . . . . . . 83 ■ CHAPTER 4 Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 ■ CHAPTER 5 Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 ■ CHAPTER 6 Full-Text Search. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 ■ CHAPTER 7 Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191 ■ CHAPTER 8 SQL Server Functions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 209 ■ CHAPTER 9 Conditional Processing,Control-Of-Flow,and Cursors. . . . . . . . . . 255 ■ CHAPTER 10 Stored Procedures. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 271 ■ CHAPTER 11 User-Defined Functions and Types . . . . . . . . . . . . . . . . . . . . . . . . . . . . 289 ■ CHAPTER 12 Triggers. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 ■ CHAPTER 13 CLR Integration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 337 ■ CHAPTER 14 XML. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355 ■ CHAPTER 15 Web Services. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 375 ■ CHAPTER 16 Error Handling. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 393 ■ CHAPTER 17 Principals . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 407 ■ CHAPTER 18 Securables and Permissions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 433 ■ CHAPTER 19 Encryption. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 459 ■ CHAPTER 20 Service Broker . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 485 ■ CHAPTER 21 Configuring and Viewing SQL Server Options . . . . . . . . . . . . . . . . . . 517 ■ CHAPTER 22 Creating and Configuring Databases . . . . . . . . . . . . . . . . . . . . . . . . . . 523 iii 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page iv ■ CHAPTER 23 Database Integrity and Optimization. . . . . . . . . . . . . . . . . . . . . . . . . . . 571 ■ CHAPTER 24 Maintaining Database Objects and Object Dependencies. . . . . . . 589 ■ CHAPTER 25 Database Mirroring. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 595 ■ CHAPTER 26 Database Snapshots. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 615 ■ CHAPTER 27 Linked Servers and Distributed Queries . . . . . . . . . . . . . . . . . . . . . . . 621 ■ CHAPTER 28 Performance Tuning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 637 ■ CHAPTER 29 Backup and Recovery. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 683 INDEX. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 717 iv 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page v Contents About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xix About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi Acknowledgments. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxiii Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxv ■ CHAPTER 1 SELECT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 The Basic SELECT Statement. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 Selecting Specific Columns for Every Row. . . . . . . . . . . . . . . . . . . . . . 2 Selecting Every Column for Every Row . . . . . . . . . . . . . . . . . . . . . . . . . 3 Selective Querying Using a Basic WHERE Clause . . . . . . . . . . . . . . . . . . . . . 3 Using the WHERE Clause to Specify Rows Returned in the Result Set. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 Combining Search Conditions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Negating a Search Condition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 Keeping Your WHERE Clause Unambiguous . . . . . . . . . . . . . . . . . . . . . 6 Using Operators and Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Using BETWEEN for Date Range Searches . . . . . . . . . . . . . . . . . . . . . . 8 Using Comparisons. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 Checking for NULL Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9 Returning Rows Based on a List of Values . . . . . . . . . . . . . . . . . . . . . 10 Using Wildcards with LIKE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 Ordering Results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 Using the ORDER BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Using the TOP Keyword with Ordered Results . . . . . . . . . . . . . . . . . . 13 Grouping Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Using the GROUP BY Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 Using GROUP BY ALL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 Selectively Querying Grouped Data Using HAVING. . . . . . . . . . . . . . . 17 v 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page vi vi ■ CONTENTS SELECT Clause Techniques. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18 Using DISTINCT to Remove Duplicate Values . . . . . . . . . . . . . . . . . . 19 Using DISTINCT in Aggregate Functions . . . . . . . . . . . . . . . . . . . . . . . 19 Using Column Aliases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20 Using SELECT to Create a Script . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21 Performing String Concatenation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22 Creating a Comma Delimited List Using SELECT. . . . . . . . . . . . . . . . 22 Using the INTO Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23 SubQueries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25 Using Subqueries to Check for the Existence of Matches . . . . . . . . 25 Querying from More Than One Data Source. . . . . . . . . . . . . . . . . . . . . . . . . 26 Using INNER Joins. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 Using OUTER Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28 Using CROSS Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Performing Self-Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29 Using Derived Tables . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30 Combining Result Sets with UNION . . . . . . . . . . . . . . . . . . . . . . . . . . . 31 Using APPLY to Invoke a Table-Valued Function for Each Row . . . . . . . . . 33 Using CROSS APPLY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33 Using OUTER APPLY. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35 Data Source Advanced Techniques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36 Using the TABLESAMPLE to Return Random Rows. . . . . . . . . . . . . . 37 Using PIVOT to Convert Single Column Values into Multiple Columns and Aggregate Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38 Normalizing Data with UNPIVOT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40 Returning Distinct or Matching Rows Using EXCEPT and INTERSECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Summarizing Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Summarizing Data with WITH CUBE . . . . . . . . . . . . . . . . . . . . . . . . . . 45 Using GROUPING with WITH CUBE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Summarizing Data with WITH ROLLUP. . . . . . . . . . . . . . . . . . . . . . . . . 46 Hints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Using Join Hints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 Using Query Hints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49 Using Table Hints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51 Common Table Expressions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53 Using a Non-Recursive Common Table Expression (CTE). . . . . . . . . 53 Using a Recursive Common Table Expression (CTE) . . . . . . . . . . . . . 56 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page vii ■ CONTENTS vii ■ CHAPTER 2 INSERT,UPDATE,DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 INSERT. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61 Inserting a Row into a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62 Inserting a Row Using Default Values. . . . . . . . . . . . . . . . . . . . . . . . . . 63 Explicitly Inserting a Value into an IDENTITY Column . . . . . . . . . . . . 64 Inserting a Row into a Table with a Uniqueidentifier Column. . . . . . 65 Inserting Rows Using an INSERT...SELECT Statement. . . . . . . . . . . . 66 Inserting Data from a Stored Procedure Call. . . . . . . . . . . . . . . . . . . . 67 UPDATE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 Updating a Single Row. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69 Updating Rows Based on a FROM and WHERE Clause. . . . . . . . . . . 70 Updating Large Value Data Type Columns. . . . . . . . . . . . . . . . . . . . . . 71 Inserting or Updating an Image File Using OPENROWSET and BULK. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73 DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75 Deleting Rows . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 76 Truncating a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77 The OUTPUT Clause . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78 Using the OUTPUT Clause with INSERT,UPDATE,DELETE . . . . . . . . 78 Chunking Data Modifications with TOP. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 Deleting Rows in Chunks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81 ■ CHAPTER 3 Transactions,Locking,Blocking,and Deadlocking. . . . . . . 83 Transaction Control. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83 Using Explicit Transactions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85 Displaying the Oldest Active Transaction with DBCC OPENTRAN. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 88 Locking. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 90 Viewing Lock Activity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 91 Transaction,Locking,and Concurrency. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93 Using SET TRANSACTION ISOLATION LEVEL. . . . . . . . . . . . . . . . . . . . 94 Blocking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 99 Identifying and Resolving Blocking Processes . . . . . . . . . . . . . . . . . . 99 Using SET LOCK TIMEOUT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102 Deadlocking. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103 Identifying Deadlocks with a Trace Flag. . . . . . . . . . . . . . . . . . . . . . . 103 Setting Deadlock Priority . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page viii viii ■ CONTENTS ■ CHAPTER 4 Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Table Basics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109 Creating a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112 Adding a Column to an Existing Table . . . . . . . . . . . . . . . . . . . . . . . . 112 Changing a Column Definition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113 Creating a Computed Column. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 114 Dropping a Table Column. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 115 Reporting Table Information . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Dropping a Table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Collation Basics. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116 Viewing Collation Metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 117 Designating a Column’s Collation. . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 118 Creating a Table with a Primary Key. . . . . . . . . . . . . . . . . . . . . . . . . . 120 Adding a Primary Key Constraint to an Existing Table. . . . . . . . . . . 121 Creating a Table with a Foreign Key Reference . . . . . . . . . . . . . . . . 121 Adding a Foreign Key to an Existing Table. . . . . . . . . . . . . . . . . . . . . 122 Creating Recursive Foreign Key References. . . . . . . . . . . . . . . . . . . 123 Allowing Cascading Changes in Foreign Keys . . . . . . . . . . . . . . . . . 124 Surrogate Keys. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 Using the IDENTITY Property During Table Creation . . . . . . . . . . . . 126 Using DBCC CHECKIDENT to View and Correct IDENTITY Seed Values. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127 Using the ROWGUIDCOL Property. . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 Creating a Unique Constraint. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 130 Adding a UNIQUE Constraint to an Existing Table. . . . . . . . . . . . . . . 131 Using CHECK Constraints. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 Adding a CHECK Constraint to an Existing Table . . . . . . . . . . . . . . . 133 Disabling and Enabling a Constraint. . . . . . . . . . . . . . . . . . . . . . . . . . 134 Using a DEFAULT Constraint During Table Creation. . . . . . . . . . . . . 135 Adding a DEFAULT Constraint to an Existing Table. . . . . . . . . . . . . . 136 Dropping a Constraint from a Table . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Temporary Tables and Table Variables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Using a Temporary Table for Multiple Lookups Within a Batch. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 138 Creating a Table Variable to Hold a Temporary Result Set . . . . . . . 140 570Xch00FMFINALQ6.qxd 11/7/05 10:08 AM Page ix ■ CONTENTS ix Manageability for Very Large Tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141 Implementing Table Partitioning . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 142 Determining the Location of Data in a Partition. . . . . . . . . . . . . . . . 145 Adding a New Partition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146 Removing a Partition. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148 Moving a Partition to a Different Table. . . . . . . . . . . . . . . . . . . . . . . . 149 Removing Partition Functions and Schemes. . . . . . . . . . . . . . . . . . . 151 Placing a Table on a Filegroup. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 ■ CHAPTER 5 Indexes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 Indexes Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 155 Create a Table Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 158 Enforce Uniqueness on Non-Key Columns . . . . . . . . . . . . . . . . . . . . 159 Create a Composite Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 160 Define Index Column Sort Direction. . . . . . . . . . . . . . . . . . . . . . . . . . 161 View Index Meta Data. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 161 Disable an Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 Dropping Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163 Change an Existing Index with DROP_EXISTING . . . . . . . . . . . . . . . 164 Controlling Index Build Performance and Concurrency. . . . . . . . . . . . . . . 164 Intermediate Index Creation in Tempdb. . . . . . . . . . . . . . . . . . . . . . . 165 Controlling Parallel Plan Execution for Index Creation. . . . . . . . . . . 165 Allowing User Table Access During Index Creation . . . . . . . . . . . . . 166 Index Options. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 Using an Index INCLUDE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166 Using PAD_INDEX and FILLFACTOR . . . . . . . . . . . . . . . . . . . . . . . . . . 167 Disabling Page and/or Row Index Locking . . . . . . . . . . . . . . . . . . . . 168 Managing Very Large Indexes. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 Creating an Index on a Filegroup . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 Implementing Index Partitioning. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 170 ■ CHAPTER 6 Full-Text Search. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 Full-Text Indexes and Catalogs. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 Creating a Full-Text Catalog. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 174 Creating a Full-Text Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 175 Modifying a Full-Text Catalog . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177 Modifying a Full-Text Index . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 178 Dropping a Full-Text Catalog. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 Dropping a Full-Text Index. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 181 ta. . . . . . . . . . . . . . . 181

See more

The list of books you might like

Upgrade Premium
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.
DMCA & Copyright: Dear all, most of the website is community built, users are uploading hundred of books everyday, which makes really hard for us to identify copyrighted material, please contact us if you want any material removed.
Copyright @ PDFdrive.to, 2022 | We love our users