Size: 2.7 MB
Pages: 117
Date: 2011-12-24 19:51:07
Download: Dl
Copyright © 2010 by Itzik Ben-Gan, Dejan Sarka, Ed Katibah, Greg Low, Roger Wolter, and Isaac Kunen. All rights reserved. No part of the contents of this book may be reproduced or in any form or by any means. Without the written permission of the publisher.. Library of Congress Control Number: 2009932820. Printed and bound in the United States of America..
Distributed in Canada by H.B. Fenn and Company Ltd.. A CIP catalogue record for this book is available from the British Library.. Microsoft Press books are available through booksellers and distributors worldwide. For further information about. International editions, contact your local Microsoft Corporation office or contact Microsoft Press International directly at.
Overview
Fax (425) 936-7329. Visit our Web site at www.microsoft.com/mspress. Send comments to tkinput@microsoft.com.. Microsoft, Microsoft Press, Active Directory, BizTalk, MapPoint, MS, MultiPoint, SQL Server, Visio, Visual Basic, Visual C#,. Visual Studio and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or. Other countries. Other product and company names mentioned herein may be the trademarks of their respective owners.. The example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted. Herein are fictitious. No association with any real company, organization, product, domain name, e-mail address, logo,.
Person, place, or event is intended or should be inferred.. This book expresses the author’s views and opinions. The information contained in this book is provided without any. Express, statutory, or implied warranties. Neither the authors, Microsoft Corporation, nor its resellers, or distributors will. Be held liable for any damages caused or alleged to be caused either directly or indirectly by this book.. Editorial Production: Ashley Schneider, S4Carlisle Publishing Services.
Technical Reviewer: Steve Kass; Technical Review services provided by Content Master, a member of CM Group, Ltd.. Foreword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv. Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii. Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xxi. Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1.
What Are Views? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1. ORDER BY in a View . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3. Refreshing Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6. Modular Approach . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8. Updating Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16.
View Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20. ENCRYPTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20. SCHEMABINDING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20. CHECK OPTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22. VIEW_METADATA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23.
Indexed Views . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24. Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29. User-Defined Functions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31. Some Facts About UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31. Scalar UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32.
T-SQL Scalar UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32. Performance Issues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34. UDFs Used in Constraints . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40. CLR Scalar UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43. SQL Signature . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57.
Table-Valued UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 63. Inline Table-Valued UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6 4. Split Array . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66. What do you think of this book? We want to hear from you!. Microsoft is interested in hearing your feedback so we can continually improve our books and learning.
Resources for you. To participate in a brief online survey, please visit:. ORDER Option for CLR Table-Valued UDFs . . . . . . . . . . . . . . . . . . . . . . . . . 71. Multistatement Table-Valued UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73. Per-Row UDFs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77. Conclusion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80.
Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81. Types of Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81. User-Defined Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 82. Special Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85. System Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87.