Designed for
`flflr"‘4
`1?»wt-..fl
`0
`iiicrosort~
`Windows NT”
`Windows'95
`
`@
`
`Microsoft” Programming Series
`
`Micr
`
`.
`oft” Inslde
`
`..
`
`.1
`
`.2"
`
`:‘~.hL
`
`
`I
`
`_.:» 3
`
`6.5
`
`The Deve|oper’s
`
`Guideto Design,
`
`Architecture, and
`
`Implementation
`
`from a Leading
`Microsoft Expert
`
`Ron Soukup
`Foreword by Jim Gray,
`Head of the Microsoft
`San Francisco Research Lab
`Starbucks, 1081
`arbucks ‘V. Ameranth, CBM2015-00091
`
`Starbucks, Ex. 1081
`Starbucks v. Ameranth, CBM2015-00091
`
`

`
`Microsoft®
`SQL
`Server" 6.5
`
`Ron Soukup
`
`Starbucks, Ex. 1081
`-
`D
`1
`I I I O
`
`
` Microsoft Press
`
`Starbucks, Ex. 1081
`Starbucks v. Ameranth, CBM2015-00091
`
`

`
`PUBLISHED BY
`Microsoft Press
`
`A Division of Microsoft Corporation
`One Microsoft Way
`Redmond, Washington 98052-6399
`
`Copyright © 1997 by Microsoft Corporation
`
`AH rights ‘reserved. No part of the contents of this book may be reproduced or transmitted
`in any form or by any means without the written permission of the publisher.
`
`Library of Congress Cataloging-in-Publication Data
`Soukup, Ron.
`Inside Microsoft SQL Server 6.5 / Ron Soukup.
`p.
`cm.
`Includes index.
`ISBN 1-57231-331-5
`
`1. Database management.
`QA76.9.D3S66
`1997
`005.75'85-—dc21
`
`2. SQL Server.
`
`1. Title.
`
`Printed and bound in the United States of America.
`
`123456789 MLML 210987
`
`97-37611
`CIP
`
`Distributed to the book trade in Canada by Macmillan of Canada, a division of Canada Publishing
`Corporation.
`
`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 fax (425) 936-7329. Visit our Web site at mspress.microsoft.com.
`
`Macintosh is a registered trademark of Apple Computer, Inc. Intel is a registered trademark of Intel
`Corporation. BackOffice, FoxPro, Microsoft, Microsoft Press, MS-DOS, TransAccess, Visual Basic,
`' Visual C++, Windows, Windows NT, and Win32 are registered trademarks and ActiveX, Visual J++,
`Visual SourceSafe, and Visual Studio are trademarks of Microsoft Corporation. Java is a trademark of
`Sun Microsystems, Inc. Other product and company names mentioned herein may be the trademarks
`of their respective owners.
`
`Acquisitions Editor: David Clark
`Project Editor: Lisa Theobald
`Technical Editor: John Conrow
`
`Starbucks V. Ameranth, CBM201 5-00091
`
`Starbucks, Ex. 1081
`
`Starbucks, Ex. 1081
`Starbucks v. Ameranth, CBM2015-00091
`
`

`
`To Kay, Kelly, andJamie,
`
`for your love and support during
`
`the years ofship crunch.
`And
`
`To the SQL Server Development Team.
`
`Worleing with you has been the greatest
`
`privilege of my career.
`
`

`
` CONTENTS
`
`Foreword ............................................................................................ .. xi
`
`Preface ............................................................................................. .. xiii
`
`n The Evolution of Microsoft SQL Server: 1989 to 1996 .................. 3
`The Competitive Background That Spawned Microsoft SQL Sewer ..... .. 3
`The Early Days with the NDK .......................................................... .. 6
`
`Microsoft SQL Server Ships ............................................................. .. 7
`
`Development Roles Evolve .......
`
`...................................................... .. 9
`
`T 1
`OS/2 and ”Frienclly Fire” ............................................................. ..
`Version 4.2 .................................................................................. .. T2
`
`OS/2 2.0 Release on Hold ........................................................... .. 13
`SQL Server For Windows NT ......................................................... .. 14
`
`Success Brings Fundamental Change ............................................. .. T9
`
`The End of Joint Development ....................................................... .. 21
`
`The Charge to SQL95 ................................................................... .. 23
`The Next Version .......................................................................... .. 26
`
`E A Tour of Microsoft SQL Server ................................................. 27
`Introduction .................................................................................. .. 27
`
`The SQL Sewer Engine ................................................................. .. 28
`
`DBMS-Entorcecl Data Integrity ....................................................... .. 33
`
`Transaction Processing .................................................................. .. 37
`
`Symmetric Server Architecture .................................. ... .................. .. 39
`
`Security ....................................................................................... .. 42
`
`High Availability .......................................................................... .. 43
`
`

`
`USING MICROSOFT SQL SERVER
`
`Distributed Data Processing .......................................................... .. 44
`Data Replication ........................................................................... .. 45
`Systems Management ................................................................... .. 47
`SQL Sewer UtiIities and Extensions ................................................ .. 53
`DeveIopment Intertaces ................................................................. .. 58
`SUMMARY ................................................................................... .. 60
`
`a SQL Server Architecture ........................................................... 63
`Overview ................................................... ..; ........................
`.... .. 63
`The SQL Server Engine ................................................................. .. 63
`Large Memory Issues .................................................................... r. 93
`Transaction Logging and Recovery ................................................ .. 96
`The SQL Server Kernel and Interaction with Windows NT ............. .. I00
`SUMMARY ................................................................................. ..
`I I0
`
`a Planning for and Installing SQL Server ..................................... 113
`Setup Is Easy, but Think First ....................................................... .. I I3
`SQL Server vs. SQL Workstation ................................................. ..
`I I3
`Choosing Hardware ................................................................... .. II4
`Hardware GuideIines ................................................................. .. I I8
`The Operating System ................................................................ .. I4I
`The FiIe System ........................................................................... .. I42
`Security and User Context ........................................................... .. I43
`Licensing Choices ....................................................................... .. I44
`Network ProtocoI Choices ........................................................... .. I49
`Character Set and Sort Order Issues ............................................ .. I53
`Running Setup ............................................................................ .. I62
`Basic Contiguration Atter Setup .................................................. .. I63
`Unattended and Remote Setup .................................................... .. I66
`SUMMARY ................................................................................. .. I7I
`
`

`
`Contents
`
`B Databases and Devices .......................................................... .. 173
`What Is a Database? .................................................................. .. 173
`
`Database Devices ....................................................................... .. 174
`
`Creating Databases .................................................................... .. 180
`
`Maximum Database Size and Database Fragments ...................... .. 184
`Expanding and Shrinking Databases ........................................... .. 184
`Databases ”Under the Covers” .................................................... .. 185
`
`Database Options ...................................................................... .. 187
`
`Changing Database Options ....................................................... .. 189
`Other Database Considerations .................................................. .. 191
`
`SUMMARY ................................................................................. .. 194
`
`H Tables ................................................................................... .. 195
`Introduction ................................................................................ .. 195
`
`Creating Tables .......................................................................... .. 196
`
`Internal Storage—The Details ..................................................... .. 207
`Indexes ...................................................................................... .. 218
`
`User-Detined Datatypes .............................................................. .. 224
`
`Identity Property ......................................................................... .. 227
`Constraints ............................................................................... .. 231
`
`Temporary Tables ....................................................................... .. 265
`SUMMARY ................................................................................ .. 267
`
`B Querying Data ..................................................................... .. 269
`Introduction ................................................................................ .. 269
`
`The SELECT Statement ................................................................. .. 269
`Joins .......................................................................................... .. 272
`
`Dealing with NULL ...................................................................... .. 288
`
`Subqueries ................................................................................. .. 298
`Views and Derived Tables ........................................................... .. 31 1
`
`Other Search Expressions ........................................................... .. 315
`SUMMARY .................................................................................... 347
`
`vii
`
`

`
`U$|NG MICROSOFT SQL SERVER
`
`Modifying Data ...................................................................... 349
`Introduction ............................................................................ .. 349
`Basic Moditication Operations ................................................. .. 349
`Internal and Periormance Considerations ................................... .. 376
`SUMMARY ............................................................................... .. 398
`
`E Programming with Transact-SQL ............................................. 399
`Introduction .............................................................................. .. 399
`Transact-SQL as a Programming Language ................................. .. 400
`Transact-SQL Programming Constructs—The Basics ...................... .. 403
`SUMMARY . .
`. .
`.
`.
`. . .
`.
`. . . s . .
`. . .
`. . . .
`. . .
`.
`. .
`. .
`. . .
`.
`.
`.
`. . .
`. . . . . .
`............................. .. 448
`
`Batches, Transactions, Stored Procedures, and Triggers ........... 449
`Introduction ........................................................................... .. 449
`Batches .................................................................................... .. 449
`Transactions .............................................................................. .. 451
`Stored Procedures ....................
`......................................... .. 466
`Executing Batches, or What's Stored About a Stored Procedure?
`479
`Triggers ..................................................................................... .. 500
`Debugging Stored Procedures and Triggers ................................. .. 504
`Working with Text and Image Data ............................................. .. 508
`Environmental Concerns ............................................................. .. 521
`SUMMARY .............................................................................. .. 527
`
`m Cursors .................................................................................. 529
`Introduction .............................................................................. .. 529
`Cursor Basics ..................................................................... .. 530
`Important! Cursors and lSAMs .................................................. .. 532
`Cursor Models ......................................................................... .. 537
`Appropriate Use oi Cursors ..................................................... .. 542
`Transact-SQL Cursor Syntax and Behavior ................................... .. 552
`SUMMARY ............................................................................... .. 570
`
`m Transact-SQL Examples and Brainteasers .................................571
`Introduction ............................................................................... .. 571
`Using Triggers to impiement Reterentiai Actions . ........................ .. 571
`
`viii
`
`

`
`Contents
`
`Brainteasers ............................................................................... .. 578
`
`SUMMARY ................................................................................. .. 637
`
`[E] Locking ................................................................................ .. 639
`Introduction ............................................................................... .. 639
`
`The Lock Manager ...................................................................... .. 639
`
`Lock Types tor User Data ............................................................ .. 645
`
`Viewing Locks ............................................................................ .. 647
`
`Lock Compatibility ...................................................................... .. 647
`Lock EscaIation ........................................................................... .. 654
`
`Lock Hints and AppIication Issues ................................................ .. 655
`SUMMARY ............................. ... ................................................. .. 655
`
`m Design and Query Performance Implications ........................... 659
`Introduction ................................................................................ .. 659
`
`Pertormance Guidelines .............................................................. .. 660
`
`Develop Expertise on Your DeveIopment Team ............................. .. 660
`
`Entorce SoIid AppIication and Database Design ........................... .. 662
`State Pertormance Requirements For Peak Usage ......................... .. 667
`
`Consider Perceived Response Time tor Interactive Systems ............ .. 668
`Prototype, Benchmark, and Test Throughout Development ............. .. 670
`Create Useful Indexes ................................................................. .. 674
`
`Choose Appropriate Hardware ................................................... .. 679
`
`Use Cursors Judiciously .............................................................. .. 680
`
`Use Stored Procedures AImost AIways ......................................... .. 680
`
`Minimize Network Round-Trips ................................................... .. 681
`Understand Concurrency and Consistency Trade-Otts .................. .. 682
`Analyze and ResoIve Locking (Blocking) Problems ........................ .. 683
`AnaIyze and ResoIve DeadIock Problems ..................................... .. 685
`
`Consider Segregating OLTP and D55 AppIications ....................... .. 704
`Monitor and Tune Queries .......................................................... .. 704
`
`............................................. .. 724
`Monitor Query Pertormance ......
`SUMMARY ................................................................................. .. 738
`
`ix
`
`

`
`USING MICROSOFT SQL SERVER
`
`E Configuration and Monitoring for Performance ....................... 739
`Introduction ................................................................................ .. 739
`Review and Adjust Windows NT Configuration Settings ............... .. 740
`Review and Adjust SQL Server Configuration Settings .................. .. 742
`Maintain the System ................................................................... .. 766
`Monitor System Performance ....................................................... .. 767
`SUMMARY ................................................................................. .. 773
`
`Appendix: SQL Server Bui/f-In Global Variables ................................ .. 777
`
`Bibliography .................................................................................... .. 78 I
`Suggested Reading .......................................................................... .. 783
`Index ............................................................................................... .. 787
`
`

`
`A Tour of
`
`Microsoft SQL Server
`
`.
`
`Introduction
`Microsoft SQL Server is a high—performance, client/server relational database
`management system (RDBMS). It was designed to support high—vo1ume trans-
`action processing (such as that for online order entry, inventory, accounting, or
`manufacturing) as well as data warehousing and decision-support applications
`(such as sales analysis applications) on Microsoft Windows NT Server—based net-
`works. SQL Server is fully operational on all hardware architectures supported
`by Windows NT, including Intel, DEC Alpha AXP, MIPS R4000, and Motorola
`PowerPC-based systems. For all these hardware platforms, SQL Server versions
`are built simultaneously from the same source code baseline, and all versions ship
`together on the same CD—ROM. SQL Server also provides many client tools and
`networking interfaces for the Microsoft Windows 95, Windows 3.1, and MS—DOS
`operating systems. And because of SQL Server’s open architecture, other systems
`(for example, UNIX—based systems) can interoperate with it as well.
`
`SQL Server is part of the core of a family of integrated products, including de-
`velopment tools, systems management tools, distributed system components, and
`open development interfaces, as shown in Figure 2-1 on the following page. It
`is also a key part of Microsoft BackOffice.
`
`This book focuses on the capabilities and uses of the SQL Server engine; this
`chapter provides an overview of the entire SQL Server family of components and
`describes the features and benefits of each component. Understanding these
`features and benefits will prove helpful to you as you develop applications.
`
`
`
`27
`
`

`
`PART 1 OVERVIEW
`
`
`
`Third-party tools
`PowerBuilder
`Borland Delphi
`Oracle Power Objects
`Microfocus COBOL
`
`Microsoft Visual Studio
`Microsoft Visual Basic
`Microsoft Visual C++
`Microsoft Visual |nterDev
`Microsoft Visual J‘''’’
`Microsoft Visual FoxPro
`
`Microsoft Office
`Microsoft Word
`Microsoft Excel
`Microsoft Access
`Microsoft Query
`
`------------- -- Open Interfaces ...-...------.
`
`DAOwith ODBCDirect| OLE DBODBCProvider
`ADO
`
`RDO
`
`SOL-DMO | Embedded
`
`SOL for C
`
`DB-Library
`
`SOL Enterprise
`Manager
`
`
`
`Ufilifies
`ISOL/w
`SQL Web A$5l5l0“l
`SOL Trace
`SOL Performance Monitor
`SOL Mail
`SOL SNMP
`SOL Security Manager
`
`:-- Server Programming - -,
`:
`I
`' Extended
`stored
`Procedure-9
`
`Extended
`stored
`P"°°ed”"e5
`
`I
`' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' ' '
`
`Open Data Services
`
` SOL Executive I
`
`
`Systems
`Windows Microsoft
`Exchange Management
`Server
`Server
`
`Microsoft
`Transaction
`Server
`
`Internet Information Server
`Microsoft Commercial
`Internet System
`
`Figure 2-1. SQL Server and itsfozmily of integrated components.
`
`The SQL Server Engine
`The Microsoft SQL Server engine is designed to support a variety of demanding
`applications, such as online transaction processing (OLTP) and decision—support
`applications. At the core of its decision—support capabilities is Transact—SQL,
`Microsoft’s Version of Structured Query Language. Beneath this query language
`are the components that support transaction processing and recoverability.
`
`

`
`1 AT¢wn!Hi4:rusui5I5iL5a:'vur
`
`Transact-SQL
`
`Industrywide, SQL is a well-known and widely used data access tool. Every
`mainstream database management system (DBMS) product implements SQL in
`some way. Transact—SQL (often referred to as “T—SQL”) is a powerful and unique
`superset of the SQL standard.
`
`The SQL SELECT statement provides tremendous power and flexibility for retrieving
`information. Data from multiple tables can be easily projected and the results
`returned in tabular format with information chosen and correctly combined from
`the multiple tables. Check out the following two tables from the pubs sample
`database. (The pubs database, used for many examples in this book, is installed
`when Microsoft SQL Server is installed. For brevity, an abbreviated amount of
`the data will sometimes be used, as is true in this example.)
`
`publishers Table
`pub_7'd
`pub_name
`0736
`New Moon Books
`0877
`Binnet & Hardley
`1389
`Algodata Infosystems
`
`city
`Boston
`Washington
`Berkeley
`
`state
`MA
`DC
`CA
`
`titles Table
`title_id
`BU1032
`BU1111
`
`title
`The Busy Executive's Database Guide
`Cooking with Computers: Surreptitious
`Balance Sheets
`
`BU2075
`BU7832
`MC2222
`MC3021
`MC3026
`
`You Can Combat Computer Stress!
`Straight Talk About Computers
`Silicon Valley Gastronomic Treats
`The Gourmet Microwave
`The Psychology of Computer Cooking
`
`pub_id
`1389
`1389
`
`0736
`1389
`@877
`@877
`@877
`
`The following simple SELECT statement logically joins the titles and publishers
`tables to project the names of the book titles with the names of the companies
`publishing each title.
`
`SELECT title. pub_name, city, state
`FROM titles, publishers
`WHERE titles.pub_id = publ1'shers.pub_id
`
`

`
`PART 1 OVERVIEW
`
`Here’s the result:
`
`title
`The Busy Executive's Database
`Guide
`
`Cooking with Computers:
`Surreptitious Balance Sheets
`You Can Combat Computer Stess!
`Straight Talk About Computers
`Silicon Valley Gastronomic
`Treats
`The Gourmet Microwave
`The Psychology of Computer
`Cooking
`
`pub_name
`Aigodata Infosystems
`
`city
`Berkeley
`
`state
`CA
`
`Algodata Infosystems
`
`Berkeley
`
`New Moon Books
`Algodata Infosystems
`Binnet & Hardley
`
`Binnet & Hardley
`Binnet & Hardley
`
`Boston
`BerkeIey
`Washington
`
`Washington
`Washington
`
`CA
`
`MA
`CA
`DC
`
`DC
`DC
`
`This query, a simple SQL statement, shows that standard SQL provides a power-
`ful way to query and manipulate data. (In Chapters 7 and 10, well explore SQL
`queries in much greater depth.)
`
`The National Institute of Standards and Technology (NIST) has certified Microsoft
`SQL Server version 6.5 as compliant with the American National Standards Insti-
`tute (ANSI) SQL—92 standard. However, considerably more power is available in
`Transact—SQL because of its unique extensions to the standard.
`
`
`
`Standards and Testing
`
`Although the ANSI standard is commonly referred to as “SQL—92,” the official
`standard is ANSI X3.135~1992 and is entitled “American National Standards In-
`stitute Database Language—SQL.” “X3H2” is the designator for the ANSI SQL
`committee. NIST, a division of the United States Department of Commerce, con-
`ducts a suite of tests (which vendors pay the costs of running) to certify com-
`pliance with the standard. You can find a summary of products currently certified
`
`
`
`s eck|e.ncsl.nist. 0v 5 I-testinas compliant at H : VPLs.
`
`Transact-SQL extensions
`Transact—SQL provides a number of capabilities that extend beyond typical imple-
`mentations of SQL. Queries that are difficult to write in standard SQL can be easily
`and efficiently written using these capabilities. Some of my favorites include the
`ability to embed additional SELECT statements in the SELECT list and the ability
`to drill into a result set by further selecting data directly from a SELECT statement,
`
`30
`
`

`
`2 A Tour of Microsoft SQL Server
`
`a feature known as a derived table. Transact-SQL provides many system func-
`tions for dealing with strings (for finding substrings and so on), for converting
`datatypes, and for manipulating and formatting date information. Transact—SQL
`also provides mathematical operations such as square root. In addition, special
`operators, such as CUBE and ROLLUP, allow multidimensional analysis to be
`efficiently projected at the database server, where the analysis can be optimized
`as part of the execution plan of a query. The CASE operator allows for complex
`conditional substitutions to be made easily in the SELECT statement. Multidimen-
`sional (sometimes referred to as OLAP, or online analytic processing) operators,
`such as CUBE, and conditional operators, such as CASE, are especially useful in
`implementing data warehousing solutions with SQL Server.
`
`The query optimizer
`In Transact-SQL, a cost-based query optimizer determines the likely best way to
`access data. This allows you to concentrate on defining your query criteria rather
`than defining how the query should be executed. For example, this nonprocedural
`approach eliminates the need for you to know which indexes exist and which,
`if any, should be used. Would it be more efficient to incur additional 1/05 to read
`index pages in addition to data pages, or would it be better just to scan the data
`and then sort it? The optimizer automatically, invisibly, and efficiently resolves
`these types of important questions for you.
`
`The SQL Server optimizer maintains statistics about the volume and dispersion
`of data, which it then uses to estimate the plan most likely to work best for the
`operation requested. Because a cost-based optimizer is by definition probability-
`based, an application might want to override the optimizer in some specialized
`cases. In your application, you can specify optimizer hints that will direct the
`execution plan chosen. In addition, you can use SQL Server’s SHOWPLAN fea-
`ture, which explains the execution plan chosen, provides insight into why it was
`chosen, and even allows for tuning of the application and database design.
`
`The programmable server
`Transact—SQL provides programming constructs—such as variables, conditional
`operations (IF—THEN-ELSE), and looping—that can dramatically simplify ap-
`plication development by allowing you to use a simple SQL script rather than a
`third—generation programming language (5GL). These branching and looping con-
`structs can dramatically improve performance in a client/server environment by
`eliminating the need for network conversations. Minimizing network latency is
`a key aspect of maximizing client/server application performance. For example,
`instead of returning a value to the calling application, which requires that the
`application evaluate and subsequently issue another request, you can build con-
`ditional logic directly into the SQL batch file so that the routine is completely
`evaluated and executed at the server.
`
`31
`
`

`
`PART 1
`
`OVERVIEW
`
`You can use Transact-SQL to write complex batches of SQL statements. (A batch
`of SQL statements in a complex application can be up to several hundred lines
`long.) An important new capability of SQL Server 6.5 is the SQL Debugging
`Interface (SDI), which allows debuggers such as those available with Microsoft
`Visual Studio 97 to fully debug Transact-SQL routines, including stepping through
`the statements, setting breakpoints, and setting watchpoints on Transact-SQL
`variables.
`
`Stored procedures
`Simply put, storedprocedures are collections of SQL statements stored within a
`SQL Server database. You can code complex queries and transactions into stored
`procedures and then invoke them directly from the front—end application. When-
`ever a dynamic SQL command is sent to a database server for processing, the
`server must parse the command, check its syntax for sense, determine whether
`the requester has the permissions necessary to execute the command, and for-
`mulate an optimal execution plan to process the request. Stored procedures
`execute faster than dynamic SQL batches, sometimes dramatically faster, because
`they eliminate the need for reparsing and reoptimizing the requests each time
`they are executed. SQL Server supports stored procedures that let developers store
`groups of compiled SQL statements on the server for later recall, to limit the over-
`head when the procedures are subsequently executed.
`
`Stored procedures differ from ordinary SQL statements and from batches of SQL
`statements in that they’re checked for syntax and compiled thefirst time they are
`executed. SQL Server stores this compiled version and then uses it to process
`subsequent calls, resulting in faster execution times. Stored procedures can also
`accept parameters, so a single procedure can be used by multiple applications
`using different input data.
`
`Even if stored procedures provided no performance advantage (which, of course,
`they do), there would still be a compelling reason to use them: they provide an
`important layer of insulation from changes in business practices. Suppose, for
`example, that an application is used to maintain a mailing list for a retailer’s
`catalog distribution. Subsequent to the application being deployed, a change in
`criteria and logic (that is, the business rules) occurs, thus affecting which cus-
`tomers should automatically receive new catalogs. If the business rules had been
`programmed directly into the company’s applications, every application would
`need to be modified, likely an expensive and time-consuming operation. Fur-
`thermore, if multiple developers worked on the applications, the rules might not
`have been programmed with the exact same semantics by every programmer.
`A stored procedure, on the other hand, could be modified once, in seconds, at
`the server. The applications would not need to be changed or even restarted. The
`next time each application executed the stored procedure, the new rules would
`be in place automatically.
`
`3}!
`
`

`
`
`
`In addition to providing a performance advantage, stored procedures can pro-
`vide an important security function. By granting users access to a stored proce-
`dure but not to the underlying tables, you can allow them to access or manipulate
`data only in the way prescribed by the stored procedure.
`
`Extended stored procedures
`A unique capability of Microsoft SQL Server, extended storedprocedures allow
`developers to extend the programming capabilities provided by Transact-SQL
`and to access resources outside of SQL Server. Messaging integration, security
`integration, the ability to write HTML (Hypertext Markup Language) files (files
`formatted for use on the Internet), and much of the power of SQL Enterprise Man-
`ager are all implemented using extended stored procedures. You can create ex-
`tended stored procedures as external dynamic link libraries (DLLs). (DLLs are
`typically written in C and C++, although implementation in other languages is
`also possible.)
`
`For example, you could write a DLL to establish a modem connection, dial the
`ACME Credit Service, and return a status indicating credit approval or rejection.
`(The C language more readily lends itself to particular tasks because of such
`language constructs as arrays, structures, and pointers.) For example, writing a
`financial function that uses recursion in-C (for example, the internal rate of re-
`turn, or IRR) might be more efficient that writing it as a Transact—SQL stored
`procedure. Open Data Services (ODS) is an application programming interface
`that lets you build extended stored procedures that can return self-describing
`result sets to the calling client applications, just as a “normal” procedure would.
`
`Extended stored procedures allow even Microsoft to extend SQL Server. Good
`engineering practices dictate that where code does not benefit from being shared
`or is not in common, it should be segregated and isolated. With this principle in
`mind, Microsoft added integration with messaging via MAPI as a set of extended
`stored procedures (xp_sendmail, xp_readmail, and so on) instead of directly
`modifying the SQL Server engine. Extended stored procedures allow us to add
`powerful features without any chance of disrupting the core server engine so that
`more features can be added quickly, with less risk of destabilizing the server. And
`because the code is loaded dynamically, the DLL is loaded only if a routine is
`implemented as an extended stored procedure, so the memory footprint of SQL
`Server does not grow for services that aren’t being used.
`
`DBMS-Enforced Data Integrity
`A database is only as useful as the user’s confidence in it. That's why the server
`must enforce data integrity rules and business policies. SQL Server enforces data
`integrity within the database itself, guarantee