Online Sample Chapter
SQL Server Stored Procedure Basics
Downloadable Sample Chapter
Click below for Sample Chapter related to this title:
Table of Contents
I. THE BASICS. 1. Stored Procedure Primer. 2. Suggested Conventions. 3. Common Design Patterns. 4. Source Code Management. 5. Database Design. 6. Data Volumes.
II. OBJECTS. 7. Error Handling. 8. Triggers. 9. Views. 10. User-Defined Functions.
III. HTML, XML, AND .NET. 11. HTML. 12. Introduction to XML. 13. XML and SQL Server: HTTP Queries. 14. XML and SQL Server: Retrieving Data. 15. XML and SQL Server: OPENXML. 16. Introduction to .NET.
IV. ADVANCED TOPICS. 17. Performance Considerations. 18. Debugging and Profiling. 19. Automation. 20. Extended Stored Procedures. 21. Administrative Stored Procedures. 22. Undocumented Code. 23. Arrays.
V. ESSAYS ON SOFTWARE ENGINEERING. 24. Create a Workable Environment. 25. Make Small Changes. 26. Test Your Work. Index. 0201700468T10152001
This is an advanced coder's book. Geared toward the intermediate-to-advanced developer, it is designed to help you reach the next level as a SQL Server stored procedure developer. This book assumes you already know how to write Transact-SQL queries and how to build stored procedures. This book offers little in the way of beginner-level instruction. It is aimed at developers with moderate-to-advanced skills who want to become better stored procedure programmers - developers who want to go to the next level of software craftsmanship as it relates to Transact-SQL, stored procedure programming, and XML.
The opening epigraph of my previous book, The Guru's Guide to Transact-SQL, is a quote by my friend, the renowned author and lecturer Joe Celko, regarding the importance of unlearning procedural programming in order to master non-procedural languages such as SQL. At the time, I agreed with Joe that writing Transact-SQL code in a procedural manner was the single biggest impediment to writing good Transact-SQL code. When I penned the first Guru's Guide book, I firmly believed that attempting to code in Transact-SQL in the same way that, say, C++ is written was the main reason that people who are competent developers in other languages often run into difficulties when they try to code in Transact-SQL. Their whole approach was wrong, I reasoned, and that's why they had problems. I believed that they weren't thinking like database programmers; that instead, they were thinking like traditional programmers, which just won't work in the world of database programming. So I thought.
Since then, I've had a change of heart. I once read an interview where Eddie Van Halen said that a band's music albums are snapshots of where the band is at a particular point in time (musically and otherwise). That's true of books, too. The Guru's Guide to Transact-SQL is where I was in 1998 and 1999 when I wrote it. Since then, my thinking on the relationship between procedural programming and Transact-SQL has evolved. Why? Well, let me tell you a little story...
Sometime during the two years I spent writing the first Guru's Guide, one of the book's technical reviewers wrote asking me about an article I'd written several years before for my column in Sybase Developer's Journal that demonstrated some tricks with bitmasks in Transact-SQL. He wanted to know if I could send him a copy of the article because he was doing some work with bitmasks and wanted to use one of the techniques I'd written about. I searched high and low for the article, but couldn't find it anywhere on the various computers I use. The machine on which I'd written the column had long since been retired, as had any backup tapes I might have had of it - if they had ever existed in the first place.
Finally, a search on the Internet turned up the ancient piece, and I forwarded it to the fellow who'd asked for it. With some amusement, I sat at my desk and read through the article for a few minutes (most writers really like to read what they write, no matter how old it is and no matter what they tell you). I wondered to myself: what ever possessed me to try these bit twiddling techniques in Transact-SQL of all things? Why do I think of things like this in the first place? I wondered what drove me to make discoveries like the techniques about which I'd written the article. I reasoned that if I could figure out how or why I went about making discoveries like this, perhaps I could unlock the secret of innovation itself, or, at least, how I occasionally stumble upon it. Perhaps I could move to the next level as a Transact-SQL coder myself.
I thought about it for a few days and finally realized why I came up with ideas like the bit-twiddling technique. The conclusion I came to was that, as much as I would have liked to have believed that I thought of them all on my own, many of my "discoveries" in Transact-SQL were due to what those in academia refer to as crosspollination. It was because of, not in spite of, my experiences with other languages that I'd come up with a number of the innovative coding techniques I'd discovered over the years. Most of the discoveries I'd made in Transact-SQL had grown from seeds planted in my brain by my work in traditional programming languages such as Pascal, C/C++, assembly language, and various others. It occurred to me that there were likely scant few true innovations originating in the relatively pubescent world of Transact-SQL programming. After all, languages like C and Pascal had pre-dated Transact-SQL by many years - languages like COBOL and BASIC by even longer. We don't see many new problems in the world of computing. What we see are new solutions to the same old problems. People were solving these problems long before Transact-SQL or SQL Server even existed. Surely, most of the discoveries to be made in the field of software engineering have already been made. Surely, those of us innovating in Transact-SQL are merely standing on the shoulders of the giants who came before us.
In their book, The Pragmatic Programmer (Addison-Wesley, 1999), Andrew Hunt and Dave Thomas make the bold recommendation that people who aspire to be better programmers should learn at least one new programming language per year. I'll make the same recommendation here. If you want to master Transact-SQL stored procedure programming, you should master programming itself first. Programming, coding, software engineering - whatever you want to call it - requires many years and many languages to master. Like the martial arts apprentice who must first master a number of separate martial arts before he can attain an advanced belt, a programmer who would master Transact-SQL should master the various aspects of programming in general before he can hope to master Transact-SQL itself.
The big-picture perspective and the cross-pollination that interdisciplinary work affords is the chief reason universities require schooling in fields outside of one's major focus of study. By studying the way that other fields do things, you see the many similarities and differences between your field and theirs, you gain deeper insight into those similarities and differences, and you learn to apply what you've discovered about topics completely outside your field to your own work in ways that have perhaps not been tried before. In other words, you learn to innovate. By embracing the broad view of the world the university espouses, you begin to understand your field more holistically - you begin to understand its philosophy more intimately, and you begin to grasp where it fits in the grand scheme of things.
I think the same kinds of insight can be gained through studying languages and techniques outside the realm of SQL Server. Were it not for my work in assembly language and my study of the works of masters like Steve Gibson, I might never have stumbled upon the bit twiddling techniques about which I wrote that ancient column. If not for my work in Pascal and Delphi and my study of code by gurus such as Anders Hejlsberg and Kim Kokkonen, I wouldn't have come up with a good number of the techniques I've developed in Transact-SQL over the years, including many of the data manipulation routines you'll see in this book. My research into common design patterns in Transact-SQL was inspired by the book Design Patterns by Erich Gamma and company, which I keep close at hand for my work in languages such as C++ and Object Pascal. The book The Practice of Programming, by Brian Kernighan and Rob Pike, has largely influenced my insistence on idiomatic programming. I'm a stickler for testing because of books like After the Gold Rush by Steve McConnell and Extreme Programming Explained by Kent Beck, and I'm a big proponent of the value of re-factoring because of works like Refactoring: Improving the Design of Existing Code by Martin Fowler. Many of the algorithms discussed in this book were inspired by those in Donald Knuth's three-volume work, The Art of Computer Programming and by Jon Bentley's book, Programming Pearls, as well as by many others.
None of these books are about Transact-SQL, per se, or even SQL Server. None of them demonstrate techniques that can be easily translated to a set-oriented language like SQL. They are, however, about programming, and my work in other languages is responsible for my knowledge of them. I have benefited - immensely - from interdisciplinary work - from the crosspollination between my work in Transact-SQL and my work in other languages and from the perspective such work affords a programmer. And I think you will, too.
So, rather than preaching that you must give up your sinful procedural programming ways in order to reach the nirvana that is Transact-SQL mastery, I will instead encourage you to explore other languages and other tools. Pick one per year - it can be any language or tool in which you're not already an expert - anything from Visual Basic to Delphi to Ruby to C#, C++, or Java. Come up with a few projects to undertake with your new language, ideally - but not necessarily - things that tie it back to SQL Server in some way, and then dive in. Buy the books you need, read the newsgroups, do the research, build your software. You will be surprised at how much you learn about programming, and how much you grow as a developer through the experience.
Then, sometime throughout these research projects, think about how you might apply what you're learning to your work as a Transact-SQL developer. How does SQL Server employ this or that language element that is featured by the tool you're studying? How does it implement functionality that you've found particularly helpful in your new language? How do they differ? How does, say, automation differ between Transact-SQL and Delphi? Given that Transact-SQL, like all of SQL Server, is written in C and C++, what language nuances can you trace to its origins?
After a few years of this, and after you've gained the perspective that forays into the world outside of SQL Server can afford, you will be well on your way to having the tools necessary to truly master Transact-SQL and stored procedure programming. You will appreciate software engineering as a discipline; you will love programming for its own sake.That is the key to mastering any programming language, including Transact-SQL.
So, my apologies to Joe Celko notwithstanding, I no longer believe that procedural programming is the single biggest impediment to good Transact-SQL coding. It is quite the opposite. Not truly grasping a language's strengths and weaknesses - the things that make it unique - is the single biggest impediment to building good software with it. And you can only gain the perspective necessary to accurately assess those strengths and weaknesses through interdisciplinary work and cross-pollination. In Transact-SQL's case, its strength is set-oriented development, its chief weakness is top-down programming. That doesn't mean that you can only write set-oriented programs with Transact-SQL or that writing procedural Transact-SQL code is only for the foolhardy. After all, they call them stored procedures for a reason. It just means that your style of coding will be different with Transact-SQL than it would be with, say, Visual Basic. That's not just true of Transact-SQL, it's true of many languages - many have nuances and idioms that make them unique. You wouldn't code in C++ the way you code in VB, either. Use the right tool for the job. Play to your tool's strengths and away from its weaknesses. Become intimate with those strengths and weaknesses by mastering not only the tool, but also software development in general. Purpose to become a master programmer, not just a stored procedure expert.
And about that conversation we had over dinner in San Francisco, Joe: I still think C# is the best thing to happen to programming in a good long while.
AAbbreviations, 60ActiveX, 530ADDEXTENDEDPROC(), 659ADDINSTANCE(), 659Administrative stored procedures. See Stored procedures, administrativeADO, 71Advanced Revelation, 152After the Gold Rush (McConnell), 714, 729-731AFTER triggers, 215, 229Aggregations, COM, 535Allow POST, 385Allow XPath, 384ALTER PROCEDURE, 17-18ALTER TABLE, 16, 23ALTER TABLE...DISABLE TRIGGER, 239-240ALTER TABLE...ENABLE TRIGGER, 239-240ANSI, 340ANSI SQL schema views, 249-265Arrays example, 692-694 multidimensional, 694-698 system functions, 689-692 xp_array.dll, 676-689Art of Computer Programming, The (Knuth), 191Associative arrays, 152Asterisk, used by Query Analyzer, 115Attribute repository, 166Attributes, 146AUDITEVENT(), 660Auditing, triggers and, 229-233AUTO, 402, 403-404 _CREATE_STATISTICS, 495 _UPDATE_STATISTICS, 496Automation. See COM automationAutostart, 26
BBCPTABLOCK(), 660Beck, Kent, 713, 716, 725BEGIN...END, 42BEGIN/END, 54-56Berglund, Anders, 340Berners-Lee, Tim, 339Best practices, 106-112 triggers and, 240-243BETWEEN, 281-284Bosak, Jon, 358Boyce-Codd Normal Form (BCNF), 153Bray, Tim, 358BREAK, 42B-trees, 481-482BUFFER(), 660Business process modeling, 130, 131 data structures, adding, 137-143 elements in, 132 external entities, adding, 134-135 flow objects, adding, 136-137 notation style, 133 processes, adding, 135 stores, adding, 135-136BYTES(), 661
CCaching, 24-26CALLFULLTEXT(), 661C and C++, 555Capitalization, 50-51Cardinality, 146, 492CASE tools, 129-131cdata directive, 412-413CERN, 339, 340Chain of Responsibility pattern, 93, 101-102Chen, Peter, 143Clipper, 675-676Clipping, 315-316CLOSE, 87COBOL, 152Code reviews and reading, 742-743Coding conventions comments, 65 dropping objects, 64-67 extended properties, 66 script files, 66 script recommendations, 64 script segments, 66-67 stored procedures and functions, 67-68 tables and views, 68-71Column aliases, 57-58Column headings, HTML, 342-344Column statistics, 495COLUMNS_UPDATE(), 216, 217-222COM aggregations, 535 interfaces, 533-534 marshaling, 535 overview of, 529-536 QueryInterface and IUnknown, 534 reference counting, 534COM automation sp_checkspelling, 536-539 sp_exporttable, 539-545 sp_getSQLregistry, 550-554 sp_importtable, 545-550 sp_OA, 536Command-line parameters, 516-517Commands invalid with triggers, 224 undocumented DBCC, 658-667Comments, 65 disabling, 114COMPUTE, 71Computed columns, indexes on, 488@@CONNECTIONS, 300Connectivity, 146Connolly, Dan, 358Constraints defined, 164 naming, 62-64 XML Bulk Load and enforcing, 442CONTINUE, 42Conveyor pattern, 91-93CREATE DEFAULT, 18CREATE FUNCTION, 18CREATE INDEX, 16CREATE PROCEDURE, 4, 5 permissions and limitations, 9CREATE RULE, 18CREATE SCHEMA, 18CREATE STATISTICS, 496CREATE TABLE, 16, 79CREATE TRIGGER, 18, 215CREATE VIEW, 4, 18Cross join, 184-188CUBE, 71Cunningham, Ward, 716Cursors cleaning, 69 output parameters, 39-40
DData, generating, 183 cross join, 184-188 doubling, 191-193 INSERT...EXEC, 193-194 Random(), 188-191 speed of methods, 197 sp_generate_test_data, 194-196Data, OPENXML() for inserting, 427-431Data, retrieving AUTO mode, 402, 403-404 ELEMENTS, 402, 404-406 EXPLICIT mode, 402, 406-415 RAW mode, 402-403 mapping schemas, 415-419 SELECT...FOR XML, 382, 386-387, 390, 391, 401-402Database, defined, 163Database context, setting, 78-79Database design business processes modeling, 131-143 defining functions, 127-131 entity-relationship modeling, 130-131, 143-162 general, 121-122 modeling tools, 122-123 refactoring, 722-724 relational data modeling, 162-180 steps/processes, 123-127Database schemas, generating, 447-448Data Definition Language. See DDLData dictionary constructing, 165-167 using, 167-169Data Modification Language. See DMLDBCC ADDEXTENDEDPROC(), 555DBCC CHECKTABLE, 288-289DBCC CLEANTABLE, 289DBCC commands, 16, 658-667DBCC INDEXDEFRAG, 289, 484, 485-486, 487DBCC SHOWCONTIG, 289, 484-485, 486DBCONTROL(), 662DBINFO(), 662dbname, 17DBRECOVER(), 662DBREINDEXALL(), 662DBCC DBTABLE(), 662DDL (Data Definition Language), 7, 22-24, 58-59 generating, 172-179DEALLOCATE, 87Debugging, 513-516 extended procedures, 576-577 triggers, 516 UDFs, 516Declarative referential integrity (DRI), 216DEFAULT, 37Default parameter values, 68Deferred name resolution, 5-8Defragmenting indexes, 487DELETEINSTANCE(), 663Density, 492-493DES(), 663Design patterns, 73 conveyor, 91-93 executor, 89-91 intersector, 87-88 iterator, 84-87 other types of, 101-102 prototype, 97-98 qualifier, 88-89 restorer, 94-97 singleton, 98-101Design Patterns (Gamma), 73DETACHDB(), 663Developer Edition (DE), 287Directives, 408-410 cdata, 412-413 hide, 412 id, idref, and idrefs, 413-415DISTINCT, 510DLLs, 530-532 extended stored procedures and, 555-556DML (Data Modification Language), 7, 22-24 restrictions, 249Document Object Model (DOM), 357, 378-379Document Type Definitions (DTDs), 357, 364-366Domain, 146Domain integrity, 146DONE_IN_PROC, 17Doubling, 191-193DROPEXTENDEDPROC(), 663DROP INDEX, 16DROP TABLE, 16dt procs, 105-106Dynamic Data Exchange (DDE), 529Dynamic views, 269-271
EEdge table format, 426-427ELEMENTS, 402, 404-406ENCRYPT(), 668Encryption, 109-112Enterprise Edition (EE), 287Enterprise Manager, 122 database diagrams in, 179-180Entity classes, 145Entity identifiers choosing, 158-159 defined, 147Entity instance, 145Entity-relationship modeling (E-R), 130-131 building, 147-150 completing, 156-158 defined, 145 entity identifiers, 147, 158-159 finishing, 159-162 normalization, 147, 150-155 terms, 145-147 types of diagrams, 143-144Environmental issues, 34-37@@ERROR, 44-45, 68, 203-210ERRORLOG, 663Errors fatal, 208 handling, 203-214 orphaned transactions, 211-212 reporting, 201-203 @@ROWCOUNT, 68, 210-212 SET XACT_ABORT, 212-214 user, 204-208 XML Bulk Load, 446EXEC(), 7, 8, 18, 71Executing stored procedures, 18-32Execution, monitoring, 20-26Execution plans automatically loading, 26 compilation, 20, 22-23 recompilation of, 24, 26Executor pattern, 89-91EXPLICIT mode, 402, 406-415Extended properties, 66Extended stored procedures, 32-34 in C or C++, 555-556 debugging, 576-577 differences between stored procedures and, 556 examples, 562-574 isolating procedures, 577-578 making, easier to use, 575-576 obtaining, 555 ODS (Open Data Services), 556-562 xp_setpriority, 578-584Extensible Stylesheet Language Transformation (XSLT), 370-378EXTENTINFO(), 663Extreme programming (XP), 725-732Extreme Programming Explained (Beck), 713
FFacade pattern, 101@@FETCH_STATUS, 42Field repository, 166Fifth normal form (5NF), 154File extensions recognized by GGSQLBuilder, 118First normal form (1NF), 151-152Flags parameter, 425-426Flow control statements, 42-44FLUSHPROCINDB(), 664fn _arraylen, 691 _createarray, 691 _destroyarray, 691 _getarray, 691 _greatest(), 306-307 _least(), 306-307 _listarray, 691 _listextendedproperty(), 66 _setarray, 691 _soundex(), 310-315Formatting source code abbreviations and keywords, 60 BEGIN/END, 54-56 capitalization, 50-51 clauses and predicates, alignment of, 53 column and table aliases, 57-58 DDL statements, 58-59 expressions, 53-54 indentations, 51-56 names, choosing, 61-64 owner qualification, 59-60 parentheses, 56-57 passing parameters, 60 space, white, 51-56 spacing, horizontal, 57FOR XML, 382, 386-387, 390, 391, 401-402Fourth normal form (4NF), 154Fowler, Martin, 74, 716, 719, 721-722Fragmentation, index, 483-486Functions, undocumented, 667-670
GGamma, Erich, 73Gane-Sarson, 133GETDATE(), 270-271, 300GET_SID(), 668GGSQLBuilder, 115-119Globally unique identifiers (GUIDs), 440GML (General Markup Language), 340GoF's Composite pattern, 101GOTO, 42GROUP BY, 510
HHash joins, 504, 507-508hide directive, 412Histograms, 316-317HTML (Hypertext Markup Language) column headings, 342-344 limitations of, 357 origins of, 339-340 producing, from sp_makewebtask, 344-351 producing, from Transact-SQL, 340-344 tables, 340-342 XML versus, 358-364HTTP (Hypertext Transfer Protocol), 339Hunt, Andrew, 715-716, 717Hyperlinks, 346-348
IIBM, 340id attribute, 437id directives, 413-415IDENT_CURRENT(), 222-224@@IDENTITY, 222, 224Identity column values, 438-440 XML Bulk Load and, 443-444Idioms, 73 creating objects, 76-78 looping, 81-82 nullability, 82-83 querying meta-data, 75-76 retrieving topmost rows, 83-84 setting database context, 78-79 tables, copying, 79-80 tables, emptying, 79 variable assignment, 80-81idref and idrefs directives, 413-415IF...ELSE, 42, 56IF EXISTS, 76-77IIS utility, 383IND(), 664Indexed views, 286-289Indexes allocation map (IAM), 480-481 B-trees, 481-482 covering queries, 483 defragmenting, 487 fragmentation, 483-486 for improving performance, 480-492 intersection, 483 locks and, 492 naming, 61 prerequisites, 488-491 types of, 481 on views and computed columns, 487-488INFORMATION_SCHEMA. PARAMETERS, 41 TABLES, 77 user-defined function, creating, 257-262 views, creating, 251-257, 655-656Inline functions, 291, 295-296INSERT...EXEC, 19, 193-194Inspections, 743-744INSTEAD OF trigger, 101, 215, 226-229Interfaces, COM, 533-534Intersector pattern, 87-88Iterator pattern, 84-87IUnknown, 534INVALIDATE_TEXTPTR(), 664
JJava, 355Johnson, Ralph, 716Joins hash, 504, 507-508 merge, 504, 506-507 nested loop, 504, 506
KKaizen, 713Keys candidate, 164 duplicate, 442-443 foreign, 164, 170-171 primary, 164, 300Keywords, 11-12, 60 list of supported VSS, 109 to sign files, 107-108Kernighan, Brian, 73Knuth, Donald, 191
L@lastupdated, 345Law of parsimony, 74Least Recently Used (LRU) algorithm, 22Least squares linear regression, 325-328LOCKOBJECTSCHEMA(), 664Locks, indexes and, 492LOG(), 665Looping, 81-82
MMapping data, updategrams, 433-435Mapping schemas, 415-419 annotated, 419 disabling caching, 385 updategrams, 434-435Marshaling, COM, 535McConnell, Steve, 714, 729-731, 742Memory, leaking, 577-578MEMORYSTATUS, 665 Merge joins, 504, 506-507Merise, 133Messages, sending ODS, 559-560Meta-data, retrieving, 75-76Microsoft See also COM; .NET; Web releases criticism of, 474-476 SQL7010Stress, 524 undocumented code, 627, 628@@MICROSOFTVERSION, 668Modality, 146
NNames, choosing, 61-64Nested loop joins, 504, 506@@NESTLEVEL, 42, 46, 239.NET defined, 466-474 future applications, 465NEWID(), 300NOCOUNT ON, 17Node pages, 481Nondeterministic functions, 299-300Normalization, 147, 150-155NO_TEXTPTR(), 665NULL, 38, 82-83 updategrams, 435 XML Bulk Load and, 444
OOBJECT_ID(), 669OBJECTPROPERTY(), 77-78, 245-246 for user-defined functions, 300Objects creating, 76-78 dropping, 64-67 storing in scripts, 106ODBC calls to interpret result set, 572-573 connecting from, 567-569 to execute query, 571-572 tracing, 524ODBC CALL, 392-393ODS (Open Data Services), 556-562 extended procedure activities, 559 overview of, 556-557 processing parameters, 560-561 returning data, 561-562 sending messages, 559-560 start-up code, 557-559OLE (Object Linking and Embedding), 529Opdyke, Bill, 716Open Data Services. See ODSOPENQUERY(), 263-265OPENROWSET(), 264OPENXML() basic example of, 421-425 edge table format, 426-427 flags parameter, 425-426 inserting data with, 427-431 role of, 421Operators, logical and physical, 508-511@@OPTIONS, 42ORDER BY, 248-249, 510OUTPUT, 40@outputfile, 345Owner qualification, 19, 59-60
PPAGE(), 665Page chain, 482Paoli, Jean, 358Parameterized user-defined functions, 329-335 views, 268-269Parameters coding convention, 68 command-line, 516-517 listing procedure, 41 output, 39-40 passing, 37-38, 60 processing ODS, 560-561 return status codes, 38-39 tips on, 41-42 updategrams, 435-437Parentheses, 56-57Partitioned views, 271 basic, 272-274 BETWEEN, 281-284 distributed, 272, 284-285 local, 272 partitioning columns/queries, 275-281Performance, improving indexing, 480-492 query optimization, 498-511 statistics, 492-498Perl, 152Physical operators, 508-511PLATFORM(), 669POST, 385Practice of Programming, The (Kernighan), 73Pragmatic Programmer, The (Hunt and Thomas), 715-716PRINT, 71Procedures, naming, 62@@PROCID, 42Profiling, 516-524Prototype pattern, 97-98PRTIPAGE(), 666PSS, 666PWDCOMPARE(), 669PWDENCRYPT, 300
RRandom(), 188-191RANGE_DENSITY, 495-496RANGE_ROWS, 495-496RAW mode, 402-403Recursion, 46-47, 328-329Refactoring, 716-725Refactoring: Improving the Design of Existing Code (Fowler), 74, 716, 721Reference counting, COM, 534Relational data modeling, 131 data dictionary, constructing, 165-167 data dictionary, using, 167-169 describing model elements, 169-170 diagrams in Enterprise Manager, 179-180 generating DDL, 172-179Relational data modeling (cont.) generating foreign keys, 170-171 loading E-R diagram into, 165 sizing columns, 169 terms and concepts, 162-164 verifying integrity, 171-172Relationship, 146Relationship integrity, 146Remote procedure calls (RPCs), 27RESOURCE, 666Restorer pattern, 94-97@resultstitle, 345-346RETURN, 38-39, 42Return values, stored procedure, 67ROLLBACK, 96-97ROLLUP, 71@@ROWCOUNT, 68, 210-212
SScalar functions, 291-292SCHEMABINDING, 301-304, 489SCOPE_IDENTITY(), 222-224ScrambleFloat(), 191Script(s) files, 66 maintaining separate, 106 segments, 66-67 storing objects in, 106 version control with automated script generation, 115-119Search arguments (SARGs), 502-504Second normal form (2NF), 152-153Security issues, 513-515SELECT...FOR XML, 382, 386-387, 390, 391, 401-402SELECT...INTO, 80, 97-98Selectivity, 493SET, 80-81SET ANSI_NULLS, 15-16, 34, 35-37, 249SET CURSOR_CLOSE_ON_COMMIT, 37SET IMPLICIT_TRANSACTIONS, 37SETINSTANCE(), 666SET QUOTED_IDENTIFIER, 15, 34-37, 249SET SHOWPLAN_ALL, 4, 18SET SHOWPLAN_TEXT, 4, 18SET TEXTSIZE, 37SET XACT_ABORT, 37, 212-214SGML (Standard Generalized Markup Language), 339-340Singleton pattern, 98-101Software development entropy/rot, 715-716 extreme programming, 725-732 making changes and, 714-716 refactoring, 716-725SOUNDEX(), 307-315Source code formatting. See Formatting source codeSource code management benefits of, 104-105 best practices, 106-112 dt procs, 105-106 version control with automated script generation, 115-119 version control with Query Analyzer, 113-115sp _addextendedproperty, 66, 555 _autostats, 496-497 _checknames [@mode], 629 _checkspelling, 536-539 _create_backup_job, 617-621 _createstats, 496 _delete backuphistory @oldest date, 629 _diff, 588-590 _diffdb, 622-625 _dropextendedproperty, 66 _enumerrorlogs, 629 _enumoledbdatasources, 629 _executesql, 18, 24-26 _exporttable, 539-545 _fixindex @dbname, @tabname, @indid, 629-630 _generate_script, 590-602 _generate_test_data, 194-196 _getSQLregistry, 550-554 _gettypestring @tabid, @colid, @typestring output, 630 _helptext, 9, 246-247 _hexstring, 34, 576 _importtable, 545-550 _list_trace, 609-612 _makewebtask, 344-351 _MSaddguidcol @sourceOwner, @source_table, 630 _MSaddguidindex @source_owner, @source_table, 631 _MSaddlogin_implicit_ntlogin @loginame, 631 _MSadduser_implicit_ntlogin @ntname, 631 _MScheck_uid_owns_anything @uid, 631 _MSdbuseraccess @mode='perm'l'db', @qual=db name mask, 631-632 _MSdbusepriv @mode='perm'l'serv'l'ver'l'role', 632 _MSdependencies @objname, @objtype, @flags int, @objlist, 632 _MSdrop_object [@object_id] [,@object_name] [,@object_owner}, 632-633 _MSforeachdb @command1 @replacechar = '?' [,@command2] [,@command3] [,@precommand] [,@postcommand], 633-635 _MSforeachtable @command1 @replacechar = '?' [,@command2] [,@command3] [,@whereand] [,precommand] [,@postcommand], 635-638 _MSget_oledbinfo @server [,@infotype] [,@login] [,@password], 639 _MSget_qualified_name @object_id, @qualified_name OUT, 639 _MSget_type @tabid, @colid, @colname OUT, @type OUT, 639 _MSguidtostr @guid, @mystr OUT, 639 _MShelpindex @tablename [,@indexname] [,@flags], 639-640 _MShelptype [@typename], [,@flags='sdt'l'uddt' INULL], 640-641 _MSindexspace @tablename [,@index_name}, 641 _MSis_pk_col @source_table, @colname, @indid, 642 _MSkilldb @dbname, 642 _MSloginmappings @loginname, 642-643 _MS_marksystemobject @objname, 630 _MStable_has_unique_index @tabid, 643 _MStablekeys [tablename] [,@colname] [,@type] [,@keyname] [,@flags], 643-644 _MStablerefs @tablename, @type=N'acutaltables',@direction= N'primary', @reftable, 644 _MStablespace [@name], 644 _MSunc_to_drive @unc+path, @local_server, @local_path OUT, 644 _MSuniquecolname table_name, @base_colname, @unique_colname OUT, 645 _MSuniquename @Seed, @start, 645 _MSuniqueobjectname @name_in, @name_out OUT, 645 _MSuniquetempname @name_in, @name_out OUT, 645-646 _MS_upd_sysobj_category @pSeqMode integer, 630 _OA, 536 _object_script_comments, 9-11 _proc_runner, 612-617 _readerroriog [@lognum], 646 _readtextfile, 585-588 _recompile, 26 _remove_tempdb_file @filename, 646 _run_xml_proc, 452-458 _set_local_time [@server_name] [,@adjustment_in_minutes] (for Win9x), 646 _showstatdate, 497-498 _start_trace, 602-607 _stop_trace, 607-609 _tempdbspace, 647 _updatestats, 496 _usage, 11, 12-15 use of prefix, 16-17 _xml_concat, 449-452Space, white, 51-56Spacing, horizontal, 57Sperberg-McQueen, C. M., 358@@SPID, 42Spooling, 510-511SQLAllocHandle(), 567-574SQLColAttribute(), 572-573SQLConnect(), 567-574SQLExecDirect(), 572SQLISAPI, 382SQLOLEDB, 382SQL server, accessing over HTTP, 382-385SQL7010Stress, 524SQLVersionControl.VCS_SQL, 105-106SQLXMLBulkLoad, 445-446srv _bindsession(), 570 _describe(), 561, 573 _getbindtoken(), 570 _paramdata(), 560 _paraminfo(), 560 _paramtype(), 560 _senddone(), 562 _sendmsg(), 559 _sendrow(), 561, 573 _setcoldata(), 561SRV_PROC, 557-559STACKDUMP, 667statblob, 494, 495Statistics column, 495 listing, 495-496 performance issues, 493-498 storage of, 494-495 terms, 492-493 updating, 496-497Stored procedures See also Extended stored procedures advantages of, 4-5 altering, 17-18 calling, from views, 262-265 coding convention, 67-68 creating, 5-17 defined, 3-4 differences between extended stored procedures and, 556 environmental issues, 34-37 executing, 18-32 extended, 32-34 listing, 9 parameters, 37-42 return values, 67 triggers and calling, 235-239 URL queries for executing, 392-393Stored procedures, administrative sp_create_backup_job, 617-621 sp_diff, 588-590 sp_diffdb, 622-625 sp_generate_script, 590-602 sp_list_trace, 609-612 sp_proc_runner, 612-617 sp_readtextfile, 585-588 sp_stop_trace, 607-609 sp_start_trace, 602-607Stress testing, 524-528Subtypes, 145Sun Microsystems, 358Supertypes, 145Symbols dollar ($), 107, 114 double and single pound signs (#) and (# #), 27-28 in URL queries, 388sysindexes, 480System functions, 42 array, 689-692 creating, 304-307, 656-658System objects, 30-32System procedures, 28-30
TTAB(), 667Tables aliases, 57-58 closing, 69 copying, 79-80 derived, 267-268 emptying, 79 HTML, 340-342 loading trace files into, 521-522 locks, 444-445 naming, 61 retrieving topmost rows, 83-84 system, 69-71 temporary, 68-69@table_urls, 347-348Table-valued functions, 291, 292-295Tags, 11-12Template queries, 393 client-side, 399-400 parameterized, 394-395 style sheets, 395-398Templates, 348-351Temporary procedures, 27-28Testing benefits of, 740-741 futility of, 737 stress, 524-528 time and, 739-740 types of tests, 738, 741-745 when to, 739 where to start, 735-737Third normal form (3NF), 153Thomas, David, 715-716, 717Time series fluctuation, 318-324Tokens, 114-115Trace/tracing files as XML, 522-523 grouping data, 523-524 guidelines, 517-521 loading, into a table, 521-522 OBDC, 524 replaying, 521 starting, 516 versus viewing, 516Trace flags 4022, 26 undocumented, 671-672@@TRANCOUNT, 42Transactions, XML Bulk Load, 445-446Trend analysis, 324-325Triggers AFTER, 215, 229 auditing and, 229-233 best practices, 240-243 calling stored procedures, 235-239 debugging, 516 defined, 164, 215 determining what has changed, 216-222 disabling, 239-240 execution, 234-235 INSTEAD OF, 101, 215, 226-229 managing sequential values, 222-224 names for, 61 nested, 239 restrictions, 224-226 as transactions, 234TRUNCATE TABLE, 16, 79TSEQUAL(), 670T-SQL, ad hoc, 71
UUDFs (user-defined functions) creating INFORMATION_SCHEMA, 257-262 creating your own system functions, 304-307 debugging, 516 inline, 291, 295-296 limitations, 296-300 naming, 62 parameterized, 329-335 recursion, 328-329 retrieving information on, 300-304 scalar, 291-292 SOUNDEX(), 307-315 statistical examples, 315-328 table-valued, 291, 292-295UML (Unified Modeling Language), 133UNCOMPRESS(), 670Undocumented code care when using, 627 DBCC commands, 658-667 defined, 628 functions, 667-670 INFORMATION_SCHEMA views, creating, 251-257, 655-657 procedures, description of, 628-655 system functions, creating, 656-658 trace flags, 671-672Unicode, 106-107UNIX, 461-462UPDATE(), 216-217updategrams, 431, 432-440UPDATE STATISTICS, 16, 496Updating statistics, 496-497 views, 265-266UPGRADEDB(), 667@url_query, 347, 348URLs (uniform resource locators), 339 content type, 390-391 executing stored procedures, 392-393 non-XML results, 391-392 queries, 385-393 special characters, 387-388 style sheets, 388-389USE, 67, 78-79User-defined functions. See UDFsVVariables declaring, 67 naming, 62VARYING, 40Versions See also Source code management controlling with automated script generation, 115-119 controlling with Query Analyzer, 113-115 labels to denote, 107Views accessing, 70 ANSI SQL schema, 249-265 calling stored procedures from, 262-265 defined, 164 derived tables, 267-268 dynamic, 269-271 indexed, 286-289, 487-488 INFORMATION_SCHEMA user-defined function, creating, 257-262 INFORMATION_SCHEMA views, creating, 251-257, 655-657 listing source code for, 246-247 list of, 250 OBJECTPROPERTY() meta-data, 245-246 parameterized, 268-269 partitioned, 271-285 restrictions, 247-249 updateable, 265-266 WITH CHECK OPTION clause, 266-267Virtual directories, configuring, 383-385Visual Basic, 461Visual Studio Enterprise, 105-106VSS, 103 See also Source code management list of keywords, 109 project folders recognized by GGSQLBuilder, 117 tool menu entries, 113
WWAITFOR, 42Walkthroughs,744-745Ward-Mellor, 133Web releases, 431-448 updategrams, 431, 432-440 XML Bulk Load, 431, 441-448@whentype, 346WHILE, 42, 81-82Windows 3.0 SDK, 460-461WITH CHECK OPTION clause, 266-267WITH ENCRYPTION, 17WITH LOG, 45WITH NOWAIT, 45WITH RECOMPILE, 26WITH SETERROR, 45Workable environment benefits of small changes, 713-714 creating a, 703-709Wrapping procedures, 32-34, 575-576XXML See also OPENXML() Document Object Model (DOM), 357, 378-379 Document Type Definitions (DTDs), 357, 364-366 Extensible Stylesheet Language Transformation (XSLT), 370-378 features, 382 history of, 358 notational nuances, 361-364 overview of, 356-357 schemas, 367-370 sources of information on, 379 tools, 379-380 trace files as, 522-523 understanding, 353-356 versus HTML, 358-364XML Bulk Load, 431, 441-448XMLDATA, 402XML-Data Reduced (XDR), 416-419XMLFragment, 442xp _array.dll, 676-689 _cmdshell, 208 _createarray, 677-679 _destroyarray, 684-686 _dirtree 'rootpath', 647-648 _dsnifo @systemdsn, 648 _enumoledb_providers, 648-649 _enumdsn, 649 _enumerrorlogs, 649 _exec, 208, 567, 569, 570-571 _execresultset 'code query','database', 650 _fileexist 'filename', 650 _fixeddrives, 650-651 _getarray, 682-684 _getfiledetails 'filename', 651 _get_MAPI_default_profile, 651 _get_MAPI_profiles, 651 _getnetname, 652 _listarray, 686-689 _listfile, 562-567 _logevent, 203 _oledbinfo @providername, @datasource, @location, @providerstring, @catalog, @login, @password, @infotype, 652 _readerrorlog, 588 _readerrorlog [lognum][filenmae], 652-653 _regaddmultistr, 654 _regdeletekey, 654 _regdeletevalue, 654 _regenumvalues, 653-654 _regread, 654 _regremovemultistring, 654 _regwrite, 654 _setarray, 679-682 _setpriority, 578-584 _sprintf, 299 _subdirs, 654 _test_MAPI_profile 'profile", 655 _varbintohexstr, 575-576, 655XPath, 384XP (extreme programming), 725-732XSLT (Extensible Stylesheet Language Transformation), 370-378