SQL Server 2005 T-SQL Recipes_ A Problem - Sack_ Joseph.pdf

(4004 KB) Pobierz
19074740 UNPDF
19074740.004.png
SQL Server 2005
T-SQL Recipes
A Problem-Solution Approach
Joseph Sack
19074740.005.png 19074740.006.png 19074740.007.png
SQL Server 2005 T-SQL Recipes: A Problem-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 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.
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 orders-ny@springer-sbm.com , 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 info@apress.com , 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.com in the Source Code section.
You will need to answer questions pertaining to this book in order to successfully download the code.
19074740.001.png
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
■
■
■
■
■
■
■
■
■
■
19074740.002.png
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
■
19074740.003.png

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin