Microsoft SQL Seraer 6.5/7.0 €'
`uNrvoFrxArAusrN.LrBsroRAGÊ -ybase Adaptiue Seraer 1 1,5
`
`1iltililtiltiltililililtililtililtilililililililItil
`20421850
`
`UNIVERSITY OF TEXAS AT AUSTIN . GEN LIBS
`
`ilililllilllillililillillilllllillililillllilllilllillllilllilllililli
`3UUSArrl,h3
`o 5917 30054991 63
`
`O'REILLY'
`
`Keuin Kline, Lee Gould. I Andreu.t Zaneusky
`
`World Programming Limited EXHIBIT 1009
`Page 1 of 77
`
`

`

`,,'*_ -'-*'T¡,{il
`
`rliú l'í
`
`\t's/r
`
`'i,,i'
`
`I llll;
`ì:i,'
`,lri, ; lt 1,i l,i¡jl.,f'j..ï'
`i i 'l :rj,1i "i"i
`.,','ì'
`fi.irÍiTTþI
`
`¡d
`
`t\
`
`World Programming Limited EXHIBIT 1009
`Page 2 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 3 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 4 of 77
`
`

`

`,- -#f.:
`
`i',1 .,{)i ;}u[
`
`J(t
`
`Transact-SQl Programming
`
`**r'¿ ; ¿UU/
`
`World Programming Limited EXHIBIT 1009
`Page 5 of 77
`
`

`

`Transact-S0L Progran m í n g
`by Kevin Kline, Lee Gould, and Andrew Zanevsky
`Copyright A 1999 O'Reilly & Associates, Inc. Al1 rights reserved.
`Printed in the United States of America.
`Published by O'Reilly & Associates, Inc., 101 Morris Street, Sebastopoi, CA 95472
`Editor: Deborah Russell
`Produ cti on Ed itor: Jeffrey Liggett
`Pfinting History:
`March 7999:
`
`First Edition.
`
`Nutshell Handbook, rhe Nutshell Handbook logo, and the O'Reilly logo are registered trademarks of
`O'Reilly & Associates, Inc. The association berween the image of wallcreepers and thc topic of Transact-
`SQL programming is a trademark of O'Reilly & Associates, Inc
`Microsoft, lù7indows, ancl rWindows NT are registered tradcmarks of Microsoft Corporation. Sybasc ancl
`Transact-SQL are rcgisterecl trademarks, ancl Adaptive Scrver, Adaptive Server Anywhere, Adaptive Servcr
`Enterprise, DlI-Library, and SQL Seryer are trademarks of Sybase Corporation. Rapid SQL is a traclemark
`of Embarcadero Technologies, Inc. SQl-Programmer is a registered trademark of Sylvain Faust
`International. Many of the designations used by manufacturcrs and sellers to clistinguish their products
`are claimccl as traclcmarks. \ühere thosc designations appear in this book, and O'Reilly & Associates, Inc.
`was awâre of a trademark claim, the dcsignations have been printed in caps or initial caps.
`Spccific copyrigl"rt notices ancl restrictions for documents and programs ìncluded on the CD-ROM
`accompanying this book are included on that CD-ROM. All of the doct¡ments and pr<>grams <lescribed in
`this book and proviclcd by third parties for inclusion on the CD{IOM are subject to change without noticc
`by those third partics.
`SQL Server 7.0 l2}-day Evaluation Edition was reproduced by O'Reilly & Associates, under a special
`arrangcmenr with Microsoft Corporation, If your CDIìOM is defective, pleasc return it to O'Reilly, which
`will arrangc for its replaccment. PLEASE DO NOT RETURN IT TO MICIìOSOF| CORPORATION, PLEASE
`DO NOT CONTACT MICIìOSOF*T CORPORATION FOlì PIìODUCT SUPPORT. Encl users of this MicToSOft
`program shall not bc considered "registered owners" of a Microsoft product and therefore shall not be
`eligible for upgrades, promotions, or other benefits available to "registered owners" of Microsoft products.
`\X/hile cvery precaution has bccn taken in the preparation of this book anc.l its accompanying CI)-ROM,
`the publisher ancl auth<¡rs assume no lesponsibility for errors or omissions, or for damages resulting from
`the use of the inf<rrmation contained herein. They makc no warranties of any kind, expressecl or implied,
`with regard to the documentation or programs or clata contained in this book or the CD-ROM, The
`publisher and authors specifically disclaim, withorÌt any limitation, any implied warranties of
`merchantability ancl fitncss for a particular purpose with respect to thc CD-ROM, the programs therein,
`ancllor techniques dcscribed in the book, In no cvent shall the publisher or authors be responstble or
`liable for any loss of profit or any other commercial damages, including but not limited to special,
`incidcntal, consequential, or any other damages in connection with or ârising out of furnishing,
`perf<>rmancc, or usc of this book or the programs or clata.
`
`This book is printed on acid-free paper with B57o recycled content, 1570 post-consumer waste.
`O'Reilly & Associates ìs committed to using paper with the highest recycled content
`consisrent with high quality.
`ISBN: 1-56592-40L-0
`
`^vailable
`
`15/991
`
`l-
`
`World Programming Limited EXHIBIT 1009
`Page 6 of 77
`
`

`

`Ta,ble of Contents
`
`Foreuord
`
`Preføce .:.................,.
`L Tbe Basics: Programming in Transact-SQL
`1, Introduction to Trønsact-SQL ..........
`SQL and the Introduction of Transact-SQl
`The Relational Database Model
`Normalization ................
`Row Processing Versus Set Processing .....
`The History of SQt Server
`'W'hat Is Transact-SQL? ...............
`Summary
`2. Database Analysis ønd Design ...........
`The Need for Adequate Analysis and Design
`The Systems Life Cycle
`Overview of Analysis
`Oveliew of Design
`Putting it Together with CASE Technology ...
`Summary
`
`3. SQL Primer ,.....,,....,,...
`SQL Versus Transact-SQl
`Data Definition Language
`
`xt
`
`xiü
`
`1
`
`5t
`51
`52
`
`u
`
`World Programming Limited EXHIBIT 1009
`Page 7 of 77
`
`

`

`a,
`
`Table of Contents
`
`!l
`
`L
`
`Data Manipulation Language
`Summary
`4. Trønsact-SQl Fundamentals
`Server Character Sets .........,..........
`Server Sort Orders
`Identifiers
`Foundation Units .,.........
`Executing Transact-SQl ................
`The GO Statement
`Transact-SQL Batches
`Summary
`5. Format and Style
`\fhy Bother? ....,,,..........
`\Øhat Is a Good Coding Sryle? ......,...
`Case of Keywords and User-Defined Names
`Naming Conventions and Abbreviations .........
`Standard Error Handling ...............
`Style Odds and Ends
`Formatting DML and DDL Statements ...........
`Formatting Transaction Blocks and Procedural Blocks
`Commenting Your Code
`Summary
`
`il. Tbe Building Blocks: Tra,nsa,ct-S8L Language
`Elements
`
`6. Datatyþes and Variables
`Datatypes
`User-Defined Datatypes .........
`Processing Nulls ..........
`Variables
`Parameters
`Summary
`
`7. Conditional Processing .....,,,...,..
`BEGIN . . . END Constructs
`IF Constructs,.................
`GOTO Branching and Error Handling
`
`76
`119
`
`120
`120
`121
`125
`127
`136
`143
`144
`146
`
`147
`147
`149
`153
`155
`160
`161
`164
`168
`171
`173
`
`175
`
`177
`177
`189
`191
`192
`203
`208
`
`209
`210
`211
`218
`
`World Programming Limited EXHIBIT 1009
`Page 8 of 77
`
`

`

`Table of Contents
`
`\íAITFOR Construct
`\øHILE Loops ......,..
`RETURN Statement .
`Summary
`
`8. Rout Processing ulitb Cursors
`Cursor Fundamentals,.....................
`Declaring a Cursor
`Opening a Cursor
`Asynchronous KeYsets
`Fetching Data ...........
`Closing and Deallocating a Cursor .
`Working with Cursors
`Performance Issues for Cursors .....,
`Summary
`
`9, Error Høndling
`Code Processing and Error Handling: Basic Concepts
`Displaying an Error Message with PRINT
`Raising an Error with RAISERROR ...........
`Finding Error Conditions with Global Variables ........
`Handling Errors with GOTO
`Creating Specialized Error Messages ...............
`Finding Error Messages in Operating System Files ....
`Summaty
`
`10, Temþorary Obiects
`Creating and Manipulating Temporary Objects
`The tempdb Database
`Classes of TemPorary Obiects
`Query Optimization with Temporary Tables ..
`Management of the temPdb Database
`Summary
`
`11. Transactions ønd Locking
`About Transactions
`Locking
`Transactions and the Database
`Summary
`
`ut
`
`219
`222
`)27
`229
`
`230
`231
`232
`234
`235
`237
`241
`)22
`247
`254
`
`256
`256
`257
`259
`268
`269
`270
`271
`)7)
`
`274
`)-7 q
`276
`)'7-7
`292
`293
`295
`
`)07
`298
`309
`JJJ
`346
`
`World Programming Limited EXHIBIT 1009
`Page 9 of 77
`
`

`

`ulii
`III. Functions and Extensions
`12. Functions ...,,,
`A.ggregateu"*ri""r ....,.....
`Mathematical Functions .....
`String Functions ..................
`Date Functions ...................
`Text and Image Functions
`Type Conversion Functions
`System Functions
`Summary
`
`13. CASE Exþressions a.nd Transact-SQL Extensions
`CASE Expressions .........
`COMPUTE
`CUBE and ROLLUP
`Summary
`
`M Programming Transact-SQL Objects
`14. Stored Procedures and Modular Design ...
`About Stored Procedures
`CREATE PROCEDURE .......,.,......
`Types of Stored Procedures
`Advantages of Stored Procedures
`Stored Procedures Versus Other Oblects ................
`Executing Stored Procedures
`Remote Procedure Calls (RPCs)
`Stored Procedure Design
`Conditional Data Retrieval ...............,
`Data Formatting .............
`Data Securify and Encryption ..............
`Performance Issues .........
`Summary
`
`15. Triggers and Constraints ............
`Triggers
`Interaction of Triggers and Constraints
`Summary
`
`Table of Contents
`
`347
`
`349
`353
`359
`366
`381
`385
`386
`396
`412
`
`414
`414
`421
`424
`434
`
`435
`
`437
`438
`439
`450
`4)4
`4t4
`456
`459
`465
`470
`472
`476
`484
`488
`
`489
`489
`500
`502
`
`World Programming Limited EXHIBIT 1009
`Page 10 of 77
`
`

`

`Tøble of Contents
`
`16. Vieus
`About Views
`Managing Views
`Querying Views
`View Dependencies, Permissions, and Data Manipulations
`Limitations of Views
`View Overhead ............
`Summary
`
`17 System and Extended Stored Procedures
`System Stored Procedures
`Common System Stored Procedures
`Extended Stored Procedures
`Summary
`
`V Performa,nce Tuning and Optimization
`
`18. Trønsact-SQL Code Design
`Coding for Reuse
`The Black Box Program
`Cate gorization Schemas
`RETURN
`RAISERROR
`Temporary Data Storage
`Using Parameters Effectively ....
`Help ..........
`Summary
`
`19. Code Maintenønce ¡n SQL Seruer
`Using SQL Server System Tables
`Interdependence of Server-Side Objects
`Source Code Management
`Code Maintenance ........
`Related Topics
`Summary
`
`20. Trønsact-SQl Optimízation a,nd Tuning
`About Optimization
`Efficient Physical Database Design
`Indexing Strategies
`
`tx
`
`503
`503
`504
`509
`520
`524
`525
`526
`
`527
`527
`528
`558
`566
`
`567
`
`569
`. 569
`. 571
`. t/4
`,578
`.578
`.578
`.582
`.583
`.585
`
`586
`586
`589
`590
`598
`602
`603
`
`604
`605
`608
`612
`
`I
`
`World Programming Limited EXHIBIT 1009
`Page 11 of 77
`
`

`

`Table of Cohtents
`
`619
`625
`632
`638
`652
`670
`
`719
`
`721
`
`743
`
`779
`
`789
`
`x
`
`21.
`
`SQL Server Query Optimizer
`SHO\){/PLAN Output
`Optimizer Hints ...................
`Query Analysis
`Optimization Tips and Techniques
`Summary
`
`Debugging Trønsact-SQL Programs .......
`Testing Versus Debugging
`Successful Debugging
`Specific Debugging Techniques
`The SET Command
`Debugging Utilities
`Summary
`
`VL Appendixes
`A. System Tables
`B. Wltøt's New for Transact-SQL in Microsoft SQL Seruer 7.0 ,
`C. Tbe BCP Utílity
`
`Ind.ex
`
`\\
`
`World Programming Limited EXHIBIT 1009
`Page 12 of 77
`
`

`

`Foreouord
`
`I'm currently sitting at my desk imagining you, the potential reader of this book,
`wondering if you should invest in this excellent technical journal. If I just say
`"yes," you will think I'm some sales type. If I give you a long-winded analysis, you
`will think I'm an engineer. In fact, I'm somewhere in beñveen. I was part of the
`original team that designed the Transact-SQl language in 1!85 and built the archi-
`tecture that is part of the Sybase and Microsoft databases you are using for your
`PC and Unix development. I was also the lead on the negotiating team that
`recruited Microsoft into the database cause in which Sybase so strongly believes-
`and in which you are investing part of your professional career. The language and
`the database principles it enables are every bit as important today as they were in
`the beginning.
`Transact-SQl is the language of the PC database industry and the language that
`connects the lfeb to the data it needs. Collectively, Microsoft with SQL Serwer and
`Sybase with SQL Anywhere Studio and Adaptive Seler hold the combined unit
`volume (measured in millions) for developers who want to be in the mainstream
`of the PC industry. In addition, most large enterprises invest in both NT and Unix,
`and the two share the Transact-SQl language as their common face to the devel-
`oper. Until this book, there was nothing that gave you the details you needed to
`both optimize for and understand the differences between the Transact-SQl lan-
`guage for these two mainstream product lines.
`
`A well-designed database should accomplish two goals, First, it should protect your'
`data as a bank safe protects its contents-making sure that it is always there and
`available to whomever has a legitimate need to access it or store something new
`on it. Second, it should provide enough independence from the multiple applica-
`tions that are accessing it so that significant changes can be made in the semantics
`of the data without requiring changes in the application itself. The absolute joy of
`
`xt.
`
`World Programming Limited EXHIBIT 1009
`Page 13 of 77
`
`

`

`xtt
`
`Foreutord
`
`experiencing these features fof myself is something I'll nevef fofget. I was devel-
`oping the first course in Transact-SQl, had a distributed application running, and
`fealized that I needed to change the semantics of the data. It took about 30 sec-
`onds to modify the behavior of a stored procedure in the database, and I didn't
`even have to restart the application! Transact-SQl gives you control over how data
`is accessed and changed. This book gives you the detailed information a devel-
`oper neecls to get the best value out of the language as well as the specific differ-
`ences between the Microsoft and Sybase implementations.
`The market for knowledgeable dafabase developers has never been greater-nor
`has the usefulness of database products. Your knowledge of Transact-SQl, the
`leading ianguage for the PC server industry, can be used for:
`o A new category of applications for mobile computing in which a consolidated
`database is partitioned and replicated to mobile PCs
`. A new generation of mobile "sub-PCs" inspired by devices like the PalmPilot,
`Windows-CE, and the Palm-sized PC
`. Multidata source integration using Transact-SQl as a standard way to talk to a
`collection of different databases from different vendors
`. High-performance, high-availabilily Unix enterprise servers
`\øith so much going for Transact-SQl at the moment, it may seem ill-timed to ask
`whar product will replace it. ìlhile predictions are always difficult, in my mind it
`will not be replaced as much as it will be integrated with new languages. My per-
`sonal bet is on the integration of the Java ol>ject model (to extend what can be
`represented in a column) with the existing strength of Transact-SQl (the ability to
`manipulate data sets in a concise and data-independent way). Thus, the knowl-
`edge you gain for today's applications has a very long life.
`
`Bob Epstein
`Executive Vice-President
`Sybase, Inc.
`
`t\
`
`World Programming Limited EXHIBIT 1009
`Page 14 of 77
`
`

`

`Preføce
`
`Many of the most exciting and innovative information systems in development or
`production today employ the Transact-SQl programming language. Transact-SQl
`is a powerful language used by developers and database administrators to extend
`procedural functionality to the SQL relational database language on database prod-
`ucts supplied by Microsoft and Sybase.
`This book teaches both the basics of SQL programming and the language-extend-
`ing capabilities of Transact-SQl. It provides complete coverage of Transact-SQl,
`from the basics to the nitty-grity details of the Microsoft and Sybase implementa-
`tions of the language. It contains extensive examples of everything from the sim-
`plest query to complex, multistep operations. In addition to the examples in the
`book, you'll find many additional complete Transact-SQl programs and stored
`procedures on the companion CD-ROM.
`
`Wlty Learn Trønsact-SQL?
`Transact-SQl is a database programming language and SQL extension with enor-
`mous potential. Dialects of Transact-SQl are integral to several popular high-end
`reiational database management systems-Microsoft SQL Server and Sybase Adap-
`tive Seruer-and can also be found in the desktop database management system
`Sybase SQL Anywhere Studio. From the ubiquitous \findows NT Server installa-
`tion to the glass-enclosed, high-end Unix data centers of \flall Street, Transact-SQl
`is a language you would do well to learn.
`The Microsoft side of the equation alone shows some impressive numbers. In
`recent years, unit sales of Microsoft SQL Server have been increasing by 80 to
`1000/o a year. Microsoft SQL Server's installed base now exceeds three million, and
`Microsoft expects the number of licenses to double over the next year. Although
`
`tcttt
`
`World Programming Limited EXHIBIT 1009
`Page 15 of 77
`
`

`

`-
`
`xìu
`
`Preføce
`
`Microsoft currently has only l5o/o of the overall database market (in comparison,
`Oracle has about 300/o o1 that market), it has abour, 400/o of the Windows NT mar-
`ket, and NT sales doubled last year (statistics from International Data Corporation,
`Dataquest, and VARBusiness Magazine). Developer intefest is another good
`benchmark for the long-term prospects of a product; within a yeat, Microsoft
`expects to have 25,000 resellers, and another 25,000 end user database administra-
`tors, trained in the latest release, SQL Server 7.0, for atotal price tag of $20 million'
`Toss in the considerable market presence and growth of Sybase products from the
`Unix and \I/indows NT arena to $findows 95 and $findows 98, and you will dis-
`cover another group of products where the Transact-SQl language is growing fap-
`idly. Sybase's widely acclaimed Adaptive Server Enterprise on the high end, and
`SQL Anlnvhere Studio on the desktop, push the Transact-SQl language to even
`mofe usefs. Sybase has gone from a 1p85 startup cofounded by Mark Hoffman and
`Bob Epstein in Bob's home to one of the top ten independent software houses of
`1998, with revenues approaching $t billion. Sybase, which is credited with
`popularizing the client/server application architecture, is known for its popular
`enterprise-level database products, middleware and gateway products, and devel-
`opment tools unit, PowerSoft. Sybase products are firmly entrenched in much of
`the Fortune 500.
`
`Wltøt Does Transact-SQL Prouide?
`Transact-SQl, in the current versions offered by both Microsoft and Sybase, com-
`plies with current ANSI SQL-92 standards and with the Federal Information
`processing Standards (FIPS 1,27-2) set by the National Institute for Standards and
`Technology. In the process of meeting federal government and industrial stan-
`dards, Transact-SQl now provides database programmers with a broad range of
`tools that can create robust and full-featured programs.
`Transact-SQl isised to implement many application functions, such as:
`. Embedding important business rules into the database server through stored
`procedures, triggers, and table constraints
`. Constructing powerful, standalone programs that enhance the functionality of
`a business application through scheduled tasks and command-line execution
`. Building email-aware handling routines and applications that use them
`. Connecting \X/odd \lide \Øeb pages to a Microsoft or Sybase database man-
`agement system
`Although there are some minor variations between the Microsoft and Sybase
`implementations, Transact-SQl's abilities and features on both platforms include:
`
`World Programming Limited EXHIBIT 1009
`Page 16 of 77
`
`

`

`Preface
`
`t(a
`
`A rich set of datafypes, including specialized datatypes for identifiers, time-
`stamps, images, and long text fields
`Atomic-level transactions for Data Definition Language (DDL) commands and
`for Transact-SQl extensions like SELECT . . . INTO
`Fully programmable seryer objects like views, triggers, stored procedures, and
`batch command files; stored procedures and remote stored procedures are
`able to return entire data sets
`Local and global variables
`
`Conditional processing
`Multidirectional scrollable cursors
`
`Exception and error handling
`Temporary and persistent temporary tables
`
`Special aggregate operators such as ROLLUP and CUBE
`
`Full transaction control
`Trace flags to control server behavior
`System stored procedures that reduce the complexify of many operations, such
`as adding users or automatically generating HTML web pages
`
`a
`
`a
`
`a
`
`a
`
`a a a a a a a
`
`Audience for Tbis Book
`
`rùØe wrote this book with both the novice programmer and the advanced Transact-
`SQL developer in mind. Everyone from the liberal arts major looking for a real job
`skill to the veteran database administrator (DBA) should find value in this book.
`\üØe don't expect novices to necessarily know anything about the programming
`language, although you should know something about a good \lindows text edi-
`tor that will allow you to write programs. It would also help for you to have SQL
`Server installed either at work or at home. That way, you can test the program-
`ming concepts introduced in this book for yourself.
`
`On the other hand, for veterans, we go into great detail on much of the minutiae
`of the Transact-SQl programminglanguage. Even if you've already written many
`^
`Transact-SQl stored procedure, you'll still find material in this book that is new
`and interesting to you. Furthermore, we've included a number of stored proce-
`dures (found on the accompanying CD-ROM) that provide direct value to those
`who are running SQL Server in a development or production environment. In fact,
`we believe many of these stored procedures to be so valuable that some of you
`might buy this book solely for the Transact-SQl programs included with it. After
`all, some of these programs may save you 10 or 20 hours of work time.
`
`World Programming Limited EXHIBIT 1009
`Page 17 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 18 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 19 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 20 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 21 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 22 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 23 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 24 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 25 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 26 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 27 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 28 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 29 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 30 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 31 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 32 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 33 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 34 of 77
`
`

`

`14
`
`Cbøþter 1: Introductlon to Transact-SQL
`
`shopping c rt and wheel it up and down every single aisle searching for the parts
`you want. Furthermore, when you find a part you need, you have to take it out of
`the shipping cfate and, later, you have to barscan it and bag it yourself. That is
`what row processing is like. To compare set processing in the analory, you take
`the same list to another store, where you hand it to a clerk at a receiving window.
`The clerk disappears behind the window, presumably to fetch your pafts. !Ùíhy's it
`the water cooler or getting your parts? You
`taking so long? Is he having a t^lk
`^f
`really can't tell, because you can't See past the partition, but soon enough he
`returns with all your pafts, packaged up and ready to go. It seems that the clerk
`has been gone a long time. But, in tealily, the clerk got your list ready much faster
`than you would have at the first store, because he was able to skip all the aisles
`that didn,t have the parts you wanted. It's very similar with set processing.
`
`Tbe History of SgL Seruer
`sQL Seruer, like many things related to sql, is a story of diversity. At one time,
`both Sybase and Microsoft had virtually the same product. Today, the two prod-
`ucts are growing increasingly divergent. In fact, Sybase now calls their implementa-
`tion of the product Sybase Adaptive Server Enterprise. For purposes of simpliciry,
`in this book we refer to both Microsoft and Sybase implementations as SQL Server.
`Today, major differences in the rwo implementations are largely a result of their
`most popular operating system and hardware platforms. Sybase Adaptive Server
`Enterprise is deployable on many operating systems, including Iù(¡indows NT and
`stalwart Unix platforms. Adaptive Server has many features that leverage Very
`Large Database (VLDB) and multithreading capabilities. Sybase carries the Transact-
`SQL language uniformly throughout their product line. So, you're just as likely to
`see Transact-SQl programs on the powerful and mobile Sybase SQL Adaptive
`Server Anywhere (formedy known as SQL Anywhere) running on \Øindows 95
`and \íindows !8 computers. Going to the high end of the spectrum for online
`analytical processing (OIAP) and data warehouses, Sybase Adaptive Server IQ uti-
`lizes Transact-SQl for programmatic needs and, in fact, utilizes the same parser as
`Sybase Adaptive Server. Conversely, Microsoft has focused on ease of use and
`administration, as well as tightly integrating the product with other no-cost fea-
`tures like replication and alert forwarding. In any event, all of these database plat-
`forms provide their respective user groups with efficient and effective database
`management tools,
`Today, there's a good deal of discussion about encapsulating Java as the primary
`SQL programming extension for not only Sybase and Microsoft but also for com-
`petitors like Oracle. Even with this alternative looming in the distance, Transact-
`SQL will enjoy many years of active support and augmentation by the vendors.
`Even if you know or are learning Java, you'll still have to know Transact-SQl to
`
`World Programming Limited EXHIBIT 1009
`Page 35 of 77
`
`

`

`Tbe History of SQL Seruer
`
`15
`
`excel on either Sybase or Microsoft database platforms. Heck, the backward-com-
`patibility issues alone are enough to ensure that Transact-SQl will have many,
`many more years of coding ahead.
`But that's the present and future. Maybe you want to know about how things got
`to be the way they are? By reading this section, you are actually making an admis-
`sion that many programmers and analysts are loath to: history is interesting or per-
`haps even enjoyable, This is not actually a sign of weakness as some might attest.
`.\X/ith tongue planted firmly in cheek, please read on. Table 1-1 shows the evolu-
`tion of both Microsoft and Sybase's versions of SQL Sewer.
`
`Tøble 1-1. Tbe Euolution of SQL Sener
`
`Time
`
`1.987
`
`1988
`
`1989
`
`1990
`
`r991
`
`L992
`
`1,993
`
`1995
`
`1,996
`
`7997
`
`1998
`
`Event
`Mic¡osoft and Sybase announce a technology and marketing partnership.
`Microsoft gets exclusive rights to market Sybase's DataServe¡ product on OS/2
`and all Mic¡osoft-developed operating systems. Sybase gets royalties and added
`credibility in the Unix and VMS markets. Sybase ships its first commercial DBMS
`product, called DataSewer, for Sun workstations running Unix.
`Microsoft and Ashton-Tate announce a marketing partnership. Microsoft can't put
`a dent in dBase's tremendous market presence. Ashton-Tate wants access to
`Sybase's powerful multiuser database management technology, which ships this
`year. Microsoft forms a three-v/ay alliance.
`Ashton-Tate/Microsoft SQL Server Ve¡sion 1.0 ships.
`Ashton-Tate dBase IV was floundering. Microsoft wanted to beef up its offerings
`for the new OS/2 LAN Manager product. Microsoft and Ashton-Tate quit the pârt-
`nership. "Microsoft SQL Seruer" Version 1.1 ships by summer with support for
`\Windows 3.0.
`A proliferation of \Windows 3.0 f¡onrend tools spurs the growth of Microsoft SQL
`Server. Later that year Mic¡osoft and Sybase amend their contract to allow
`Microsoft to make actual bug fixes-all under Sybase superuision. IBM and
`Microsoft call off their OS/2 partnership with Microsoft to focus on W'indows.
`Sybase surpasses $100 million in revenue and goes public on NASDAQ.
`Microsoft and Sybase SQL Server 4.2 ships. Microsoft diverts its âttention away
`from OS/2 and into lØindows NT. Microsoft SQL Server for 'SØindows NT later
`ships its beta ¡elease while Sybase ships its much-vaunted System L0.
`Microsoft'S7indows NT 1.1 ships, closely followed by Microsoft SQL Server. By
`1994, Sybase SQL Server System 10 and Microsoft SQt Sewer were competing
`unabashedly as the two formally ended their partnership.
`Microsolt SQL Server 6.0, a very significant release, ships in June. Sybase ships
`the highly lauded, technically potent Sybase SQL Server 11. Sybase and Power-
`Soft merge to form the seventh largest independent soffware house.
`Microsoft SQL Server 6.5 goes into production. Sybase Adaptive Server Enterprise
`11,5 production ships.
`Microsoft SQL Server 7.0 beta ships. Sybase ships the production version of Adap-
`tive Server Enterprise 11..5.
`Sybase Adaptive Server Anywhere 6.0 ships in the summer, while the Adaptive
`Seruer Enterprise 1.1,.9.2 production release goes out the door in September.
`Microsoft SQL Server 7.0 ships release to manufacturing copy late in the year.
`
`World Programming Limited EXHIBIT 1009
`Page 36 of 77
`
`

`

`World Programming Limited EXHIBIT 1009
`Page 37 of 77
`
`

`

`76
`
`Cbapter 3: SQL Primer
`
`You cannot drop .indexes on system tables or indexes created as a paft of a con-
`straint (i.e., those created with a CREATE TABLE of ALTER TABLE statement). On
`Microsoft, the database owner may need to specify the name of the owner of the
`table to propedy drop the index. For example:
`
`DROP INDEX Cít1es.Pu-b-id
`c,o
`
`Datø Manipulation Languøge
`Data Manipulation Language, or DML for short, is a subset of SQL that
`encompasses the commands used to cfeate, manipulate, and delete data within a
`relational database. In general, any database that uses SQL can use the DML com-
`mands cliscussed here. So the good news is that if you learn DML for SQL Server,
`that knowleclge can also be applied to other SQl-based RDBMS. The bad news is
`that almost all of the vendors have implemented some degree of variation, even in
`the basic SQL commands, if for no other reason than they do not uantf,o be com-
`pletely identical in their SQL implementations.
`
`This section covers the major DML statements:
`
`SELECT
`INSERT
`DELETE
`TRUNCATE
`UPDATE
`It also describes the READTEXT, UPDATETEXT, and \X¡RITETEXT statements used
`to handle TEXT and IMAGE data,
`
`SELECT Retrieuíng Data
`The logical place to start learning about SQL is with the process of retrieving exist-
`ing dala from the database: the SELECT command. Instead of using the term
`"select" to denote the act of retrieving data from the database, we more com-
`monly cail this a quety. The data retrieved by a query is commonly called the
`result set or simply the results. The SELECT statement is important not only for the
`obvious l'eason that you use it for data retrieval, but also because it forms the basis
`for powerful variants of the other DML commands-INSERT, DELETE, and
`UPDATE-as you will soon see. The SELECT command follows this syntax:
`
`World Programming Limited EXHIBIT 1009
`Page 38 of 77
`
`

`

`Data Man¡Þulottion Language
`
`77
`
`Microsoft
`SELECT IALL I DISTINCT] select_item1,
`select_ite¡n2, select_item3, ...
`I INTO nevr_table_name ]
`IF'ROM tablename lalias] [,...,
`tablenamel6 [a1ias16] I thinrl l
`(tablename CROSS ,JOIN ta.blename I
`ta.blename tjoin typel
`,JOIN tablename
`ON search_conditions)
`[ffilERE clause]
`IGROUP BY clausel
`IHAVING clause]
`IORDER BY clausel
`ICO¡4PUrE clausel
`IFOR BROWSE] ]
`
`Sybase
`SELECT tAi,L I DTSTTNCTI select_itenl,
`select_iten2, select_item3, ...
`I ITVTO new_table_name ]
`IFRoM tablename taliasl [,...,
`tabl-enameL6 [a1ias16] I thintl l
`{tablename CROSS ,JOIN tablename I
`tablename tjoin Eypel
`,fOIN tablename
`oN search_conditíons)
`[l/\iHERE c]_ausel
`IGROUP BY clause]
`IHAVING clausel
`IORDER BY clause]
`ICOMPUTE clausel
`IFOR {READ ONLY I UPDATE IOF liST Of
`columnsl ) l
`IAT TSOLATTON
`iREAD I]NCOMMITTED I READ
`I SERTALTZABLE)l
`COMMITTED
`[FOR BROWSE] ]
`As you can tell, there's a lot to the sELECT command. Each separate line of the
`preceding command constitutes a clótuse of the SELECT command. Thus, when
`discussing a DML statement, we refer to the FROM clause, the ìøHERE clause, the
`ORDER BY clause, or perhaps the GRoup By clause. The important thing to
`remember at this point, though, is that you don't need every clause to make a
`valid SELECT statement. In fact, all you really need to retrieve data are the SELECT
`and FROM clauses, as you,ll soon see.
`The order of the clauses, as shown in the preceding syntax example, is signifi-
`cant, Each clause must appear in the correct order. For example, the ORDER By
`clause must appear after the \øHERI clause or else the query will fail.
`Each of these clauses has different rules governing the way it is used and how it
`affects the general behavior of the query. In the following sections we'll discuss
`the clauses of a query in the order in which they must appear.
`The FoR BRo\ØsE clause is used only in DB-Library applications and not in direct
`queries against the database. The FoR BRo\øsE clause allows a table to be
`browsed in an application under the following circumstances:
`' The table must include a column defined with the TIMESTAMP datatype.
`. The table must have a unique index.
`r The FoR BRo'üøsE statement is the last clause in the sE