Home > Store > Data > SQL Server

Guru's Guide to SQL Server Stored Procedures, XML, and HTML, The

Register your product to gain access to bonus material or receive a coupon.

Guru's Guide to SQL Server Stored Procedures, XML, and HTML, The

Book

  • Your Price: $55.99
  • List Price: $69.99
  • We're temporarily out of stock, but order now and we'll send it to you later.

Description

  • Copyright 2002
  • Dimensions: 7-3/8x9-1/4
  • Pages: 800
  • Edition: 1st
  • Book
  • ISBN-10: 0-201-70046-8
  • ISBN-13: 978-0-201-70046-6

"This is a book that deserves a prominent place by anyone who aspires to be a real professional developer of SQL Server applications."

--from the Foreword by Ron Soukup

The message of this book is that building stored procedures in Transact-SQL is very much like building programs in any other language. It requires the same type of skill, planning, attention to detail, and overall grasp of technology that successful development in other languages requires. To master Transact-SQL, one must first master the fundamental concepts of software development, then build on this foundation by embracing and studying Transact-SQL as a programming language in its own right. This book teaches you how to do that and more.

More than just a catalog of coding tricks and syntax subtleties, The Guru's Guide to SQL Server(TM) Stored Procedures, XML, and HTML explores the philosophy of Transact-SQL programming. It teaches readers how to apply this philosophy in order to develop their own coding techniques and discover their own solutions to real-world programming problems. A follow-up to the widely acclaimed The Guru's Guide to Transact-SQL, this book teaches that stored procedure development does not occur in a vacuum--it involves a wide variety of skills, subjects, and technologies--and helps the reader become a better software engineer, not just a stored procedure expert.

Blending theoretical detail with practical application, this comprehensive reference begins with a foundational overview of SQL Server(TM) stored procedure programming. From there, the focus moves on to best practices and design considerations before progressing to advanced topics and a general philosophy of software craftsmanship. In all, this book provides the most complete coverage of SQL Server stored procedure programming available in one source.

Topics such as user-defined functions, views, triggers, extended procedures, error handling, OLE Automation, database design, and XML are covered in detail. The book spotlights undocumented language features and brings the first application of design patterns to the SQL language. The preview of .NET and a groundbreaking approach to adding arrays to Transact-SQL make for the most thorough and engaging read published to date on SQL Server programming.

The accompanying CD-ROM contains the book's source code. More than 700 SQL scripts, programming utilities, and extended procedures provide a veritable treasure trove of high-quality example code.

Theoretically sound, yet immensely practical, The Guru's Guide to SQL Server(TM) Stored Procedures, XML, and HTML provides developers with the tools they need to become expert stored procedure programmers and better software engineers.



0201700468B11262001

Sample Content

Online Sample Chapter

SQL Server Stored Procedure Basics

Downloadable Sample Chapter

Click below for Sample Chapter related to this title:
hendersonch01.pdf

Table of Contents



Preface.


Introduction.

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

Preface

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.

Ken Henderson
January 2001


0201700468P10152001

Index

A

Abbreviations, 60
ActiveX, 530
ADDEXTENDEDPROC(), 659
ADDINSTANCE(), 659
Administrative stored procedures. See Stored procedures, administrative
ADO, 71
Advanced Revelation, 152
After the Gold Rush (McConnell), 714, 729-731
AFTER triggers, 215, 229
Aggregations, COM, 535
Allow POST, 385
Allow XPath, 384
ALTER PROCEDURE, 17-18
ALTER TABLE, 16, 23
ALTER TABLE...DISABLE TRIGGER, 239-240
ALTER TABLE...ENABLE TRIGGER, 239-240
ANSI, 340
ANSI SQL schema views, 249-265
Arrays
example, 692-694
multidimensional, 694-698
system functions, 689-692
xp_array.dll, 676-689
Art of Computer Programming, The (Knuth), 191
Associative arrays, 152
Asterisk, used by Query Analyzer, 115
Attribute repository, 166
Attributes, 146
AUDITEVENT(), 660
Auditing, triggers and, 229-233
AUTO, 402, 403-404
_CREATE_STATISTICS, 495
_UPDATE_STATISTICS, 496
Automation. See COM automation
Autostart, 26

B

BCPTABLOCK(), 660
Beck, Kent, 713, 716, 725
BEGIN...END, 42
BEGIN/END, 54-56
Berglund, Anders, 340
Berners-Lee, Tim, 339
Best practices, 106-112
triggers and, 240-243
BETWEEN, 281-284
Bosak, Jon, 358
Boyce-Codd Normal Form (BCNF), 153
Bray, Tim, 358
BREAK, 42
B-trees, 481-482
BUFFER(), 660
Business 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-136
BYTES(), 661

C

Caching, 24-26
CALLFULLTEXT(), 661
C and C++, 555
Capitalization, 50-51
Cardinality, 146, 492
CASE tools, 129-131
cdata directive, 412-413
CERN, 339, 340
Chain of Responsibility pattern, 93, 101-102
Chen, Peter, 143
Clipper, 675-676
Clipping, 315-316
CLOSE, 87
COBOL, 152
Code reviews and reading, 742-743
Coding 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-71
Column aliases, 57-58
Column headings, HTML, 342-344
Column statistics, 495
COLUMNS_UPDATE(), 216, 217-222
COM
aggregations, 535
interfaces, 533-534
marshaling, 535
overview of, 529-536
QueryInterface and IUnknown, 534
reference counting, 534
COM automation
sp_checkspelling, 536-539
sp_exporttable, 539-545
sp_getSQLregistry, 550-554
sp_importtable, 545-550
sp_OA, 536
Command-line parameters, 516-517
Commands
invalid with triggers, 224
undocumented DBCC, 658-667
Comments, 65
disabling, 114
COMPUTE, 71
Computed columns, indexes on, 488
@@CONNECTIONS, 300
Connectivity, 146
Connolly, Dan, 358
Constraints
defined, 164
naming, 62-64
XML Bulk Load and enforcing, 442
CONTINUE, 42
Conveyor pattern, 91-93
CREATE DEFAULT, 18
CREATE FUNCTION, 18
CREATE INDEX, 16
CREATE PROCEDURE, 4, 5
permissions and limitations, 9
CREATE RULE, 18
CREATE SCHEMA, 18
CREATE STATISTICS, 496
CREATE TABLE, 16, 79
CREATE TRIGGER, 18, 215
CREATE VIEW, 4, 18
Cross join, 184-188
CUBE, 71
Cunningham, Ward, 716
Cursors
cleaning, 69
output parameters, 39-40

D

Data, 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-196
Data, OPENXML() for inserting, 427-431
Data, 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-402
Database, defined, 163
Database context, setting, 78-79
Database 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-127
Database schemas, generating, 447-448
Data Definition Language. See DDL
Data dictionary
constructing, 165-167
using, 167-169
Data Modification Language. See DML
DBCC ADDEXTENDEDPROC(), 555
DBCC CHECKTABLE, 288-289
DBCC CLEANTABLE, 289
DBCC commands, 16, 658-667
DBCC INDEXDEFRAG, 289, 484, 485-486, 487
DBCC SHOWCONTIG, 289, 484-485, 486
DBCONTROL(), 662
DBINFO(), 662
dbname, 17
DBRECOVER(), 662
DBREINDEXALL(), 662
DBCC DBTABLE(), 662
DDL (Data Definition Language), 7, 22-24, 58-59
generating, 172-179
DEALLOCATE, 87
Debugging, 513-516
extended procedures, 576-577
triggers, 516
UDFs, 516
Declarative referential integrity (DRI), 216
DEFAULT, 37
Default parameter values, 68
Deferred name resolution, 5-8
Defragmenting indexes, 487
DELETEINSTANCE(), 663
Density, 492-493
DES(), 663
Design 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-101
Design Patterns (Gamma), 73
DETACHDB(), 663
Developer Edition (DE), 287
Directives, 408-410
cdata, 412-413
hide, 412
id, idref, and idrefs, 413-415
DISTINCT, 510
DLLs, 530-532
extended stored procedures and, 555-556
DML (Data Modification Language), 7, 22-24
restrictions, 249
Document Object Model (DOM), 357, 378-379
Document Type Definitions (DTDs), 357, 364-366
Domain, 146
Domain integrity, 146
DONE_IN_PROC, 17
Doubling, 191-193
DROPEXTENDEDPROC(), 663
DROP INDEX, 16
DROP TABLE, 16
dt procs, 105-106
Dynamic Data Exchange (DDE), 529
Dynamic views, 269-271

E

Edge table format, 426-427
ELEMENTS, 402, 404-406
ENCRYPT(), 668
Encryption, 109-112
Enterprise Edition (EE), 287
Enterprise Manager, 122
database diagrams in, 179-180
Entity classes, 145
Entity identifiers
choosing, 158-159
defined, 147
Entity instance, 145
Entity-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-144
Environmental issues, 34-37
@@ERROR, 44-45, 68, 203-210
ERRORLOG, 663
Errors
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, 446
EXEC(), 7, 8, 18, 71
Executing stored procedures, 18-32
Execution, monitoring, 20-26
Execution plans
automatically loading, 26
compilation, 20, 22-23
recompilation of, 24, 26
Executor pattern, 89-91
EXPLICIT mode, 402, 406-415
Extended properties, 66
Extended 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-584
Extensible Stylesheet Language Transformation (XSLT), 370-378
EXTENTINFO(), 663
Extreme programming (XP), 725-732
Extreme Programming Explained (Beck), 713

F

Facade pattern, 101
@@FETCH_STATUS, 42
Field repository, 166
Fifth normal form (5NF), 154
File extensions recognized by GGSQLBuilder, 118
First normal form (1NF), 151-152
Flags parameter, 425-426
Flow control statements, 42-44
FLUSHPROCINDB(), 664
fn
_arraylen, 691
_createarray, 691
_destroyarray, 691
_getarray, 691
_greatest(), 306-307
_least(), 306-307
_listarray, 691
_listextendedproperty(), 66
_setarray, 691
_soundex(), 310-315
Formatting 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, 57
FOR XML, 382, 386-387, 390, 391, 401-402
Fourth normal form (4NF), 154
Fowler, Martin, 74, 716, 719, 721-722
Fragmentation, index, 483-486
Functions, undocumented, 667-670

G

Gamma, Erich, 73
Gane-Sarson, 133
GETDATE(), 270-271, 300
GET_SID(), 668
GGSQLBuilder, 115-119
Globally unique identifiers (GUIDs), 440
GML (General Markup Language), 340
GoF's Composite pattern, 101
GOTO, 42
GROUP BY, 510

H

Hash joins, 504, 507-508
hide directive, 412
Histograms, 316-317
HTML (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-364
HTTP (Hypertext Transfer Protocol), 339
Hunt, Andrew, 715-716, 717
Hyperlinks, 346-348

I

IBM, 340
id attribute, 437
id directives, 413-415
IDENT_CURRENT(), 222-224
@@IDENTITY, 222, 224
Identity column values, 438-440
XML Bulk Load and, 443-444
Idioms, 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-81
idref and idrefs directives, 413-415
IF...ELSE, 42, 56
IF EXISTS, 76-77
IIS utility, 383
IND(), 664
Indexed views, 286-289
Indexes
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-488
INFORMATION_SCHEMA.
PARAMETERS, 41
TABLES, 77
user-defined function, creating, 257-262
views, creating, 251-257, 655-656
Inline functions, 291, 295-296
INSERT...EXEC, 19, 193-194
Inspections, 743-744
INSTEAD OF trigger, 101, 215, 226-229
Interfaces, COM, 533-534
Intersector pattern, 87-88
Iterator pattern, 84-87
IUnknown, 534
INVALIDATE_TEXTPTR(), 664

J

Java, 355
Johnson, Ralph, 716
Joins
hash, 504, 507-508
merge, 504, 506-507
nested loop, 504, 506

K

Kaizen, 713
Keys
candidate, 164
duplicate, 442-443
foreign, 164, 170-171
primary, 164, 300
Keywords, 11-12, 60
list of supported VSS, 109
to sign files, 107-108
Kernighan, Brian, 73
Knuth, Donald, 191

L

@lastupdated, 345
Law of parsimony, 74
Least Recently Used (LRU) algorithm, 22
Least squares linear regression, 325-328
LOCKOBJECTSCHEMA(), 664
Locks, indexes and, 492
LOG(), 665
Looping, 81-82

M

Mapping data, updategrams, 433-435
Mapping schemas, 415-419
annotated, 419
disabling caching, 385
updategrams, 434-435
Marshaling, COM, 535
McConnell, Steve, 714, 729-731, 742
Memory, leaking, 577-578
MEMORYSTATUS, 665
Merge joins, 504, 506-507
Merise, 133
Messages, sending ODS, 559-560
Meta-data, retrieving, 75-76
Microsoft
See also COM; .NET; Web releases
criticism of, 474-476
SQL7010Stress, 524
undocumented code, 627, 628
@@MICROSOFTVERSION, 668
Modality, 146

N

Names, choosing, 61-64
Nested loop joins, 504, 506
@@NESTLEVEL, 42, 46, 239
.NET
defined, 466-474
future applications, 465
NEWID(), 300
NOCOUNT ON, 17
Node pages, 481
Nondeterministic functions, 299-300
Normalization, 147, 150-155
NO_TEXTPTR(), 665
NULL, 38, 82-83
updategrams, 435
XML Bulk Load and, 444

O

OBJECT_ID(), 669
OBJECTPROPERTY(), 77-78, 245-246
for user-defined functions, 300
Objects
creating, 76-78
dropping, 64-67
storing in scripts, 106
ODBC
calls to interpret result set, 572-573
connecting from, 567-569
to execute query, 571-572
tracing, 524
ODBC CALL, 392-393
ODS (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-559
OLE (Object Linking and Embedding), 529
Opdyke, Bill, 716
Open Data Services. See ODS
OPENQUERY(), 263-265
OPENROWSET(), 264
OPENXML()
basic example of, 421-425
edge table format, 426-427
flags parameter, 425-426
inserting data with, 427-431
role of, 421
Operators, logical and physical, 508-511
@@OPTIONS, 42
ORDER BY, 248-249, 510
OUTPUT, 40
@outputfile, 345
Owner qualification, 19, 59-60

P

PAGE(), 665
Page chain, 482
Paoli, Jean, 358
Parameterized
user-defined functions, 329-335
views, 268-269
Parameters
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-437
Parentheses, 56-57
Partitioned views, 271
basic, 272-274
BETWEEN, 281-284
distributed, 272, 284-285
local, 272
partitioning columns/queries, 275-281
Performance, improving
indexing, 480-492
query optimization, 498-511
statistics, 492-498
Perl, 152
Physical operators, 508-511
PLATFORM(), 669
POST, 385
Practice of Programming, The (Kernighan), 73
Pragmatic Programmer, The (Hunt and Thomas), 715-716
PRINT, 71
Procedures, naming, 62
@@PROCID, 42
Profiling, 516-524
Prototype pattern, 97-98
PRTIPAGE(), 666
PSS, 666
PWDCOMPARE(), 669
PWDENCRYPT, 300

R

Random(), 188-191
RANGE_DENSITY, 495-496
RANGE_ROWS, 495-496
RAW mode, 402-403
Recursion, 46-47, 328-329
Refactoring, 716-725
Refactoring: Improving the Design of Existing Code (Fowler), 74, 716, 721
Reference counting, COM, 534
Relational 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-179
Relational 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-172
Relationship, 146
Relationship integrity, 146
Remote procedure calls (RPCs), 27
RESOURCE, 666
Restorer pattern, 94-97
@resultstitle, 345-346
RETURN, 38-39, 42
Return values, stored procedure, 67
ROLLBACK, 96-97
ROLLUP, 71
@@ROWCOUNT, 68, 210-212

S

Scalar functions, 291-292
SCHEMABINDING, 301-304, 489
SCOPE_IDENTITY(), 222-224
ScrambleFloat(), 191
Script(s)
files, 66
maintaining separate, 106
segments, 66-67
storing objects in, 106
version control with automated script generation, 115-119
Search arguments (SARGs), 502-504
Second normal form (2NF), 152-153
Security issues, 513-515
SELECT...FOR XML, 382, 386-387, 390, 391, 401-402
SELECT...INTO, 80, 97-98
Selectivity, 493
SET, 80-81
SET ANSI_NULLS, 15-16, 34, 35-37, 249
SET CURSOR_CLOSE_ON_COMMIT, 37
SET IMPLICIT_TRANSACTIONS, 37
SETINSTANCE(), 666
SET QUOTED_IDENTIFIER, 15, 34-37, 249
SET SHOWPLAN_ALL, 4, 18
SET SHOWPLAN_TEXT, 4, 18
SET TEXTSIZE, 37
SET XACT_ABORT, 37, 212-214
SGML (Standard Generalized Markup Language), 339-340
Singleton pattern, 98-101
Software development
entropy/rot, 715-716
extreme programming, 725-732
making changes and, 714-716
refactoring, 716-725
SOUNDEX(), 307-315
Source code formatting. See Formatting source code
Source 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-115
sp
_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-452
Space, white, 51-56
Spacing, horizontal, 57
Sperberg-McQueen, C. M., 358
@@SPID, 42
Spooling, 510-511
SQLAllocHandle(), 567-574
SQLColAttribute(), 572-573
SQLConnect(), 567-574
SQLExecDirect(), 572
SQLISAPI, 382
SQLOLEDB, 382
SQL server, accessing over HTTP, 382-385
SQL7010Stress, 524
SQLVersionControl.VCS_SQL, 105-106
SQLXMLBulkLoad, 445-446
srv
_bindsession(), 570
_describe(), 561, 573
_getbindtoken(), 570
_paramdata(), 560
_paraminfo(), 560
_paramtype(), 560
_senddone(), 562
_sendmsg(), 559
_sendrow(), 561, 573
_setcoldata(), 561
SRV_PROC, 557-559
STACKDUMP, 667
statblob, 494, 495
Statistics
column, 495
listing, 495-496
performance issues, 493-498
storage of, 494-495
terms, 492-493
updating, 496-497
Stored 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-393
Stored 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-607
Stress testing, 524-528
Subtypes, 145
Sun Microsystems, 358
Supertypes, 145
Symbols
dollar ($), 107, 114
double and single pound signs (#) and (# #), 27-28
in URL queries, 388
sysindexes, 480
System functions, 42
array, 689-692
creating, 304-307, 656-658
System objects, 30-32
System procedures, 28-30

T

TAB(), 667
Tables
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-348
Table-valued functions, 291, 292-295
Tags, 11-12
Template queries, 393
client-side, 399-400
parameterized, 394-395
style sheets, 395-398
Templates, 348-351
Temporary procedures, 27-28
Testing
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-737
Third normal form (3NF), 153
Thomas, David, 715-716, 717
Time series fluctuation, 318-324
Tokens, 114-115
Trace/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, 516
Trace flags
4022, 26
undocumented, 671-672
@@TRANCOUNT, 42
Transactions, XML Bulk Load, 445-446
Trend analysis, 324-325
Triggers
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, 234
TRUNCATE TABLE, 16, 79
TSEQUAL(), 670
T-SQL, ad hoc, 71

U

UDFs (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-295
UML (Unified Modeling Language), 133
UNCOMPRESS(), 670
Undocumented 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-672
Unicode, 106-107
UNIX, 461-462
UPDATE(), 216-217
updategrams, 431, 432-440
UPDATE STATISTICS, 16, 496
Updating
statistics, 496-497 views, 265-266
UPGRADEDB(), 667
@url_query, 347, 348
URLs (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-389
USE, 67, 78-79
User-defined functions. See UDFsV
Variables
declaring, 67
naming, 62
VARYING, 40
Versions
See also Source code management
controlling with automated script generation, 115-119
controlling with Query Analyzer, 113-115
labels to denote, 107
Views
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-267
Virtual directories, configuring, 383-385
Visual Basic, 461
Visual Studio Enterprise, 105-106
VSS, 103
See also Source code management
list of keywords, 109
project folders recognized by GGSQLBuilder, 117
tool menu entries, 113

W

WAITFOR, 42
Walkthroughs,744-745
Ward-Mellor, 133
Web releases, 431-448
updategrams, 431, 432-440
XML Bulk Load, 431, 441-448
@whentype, 346
WHILE, 42, 81-82
Windows 3.0 SDK, 460-461
WITH CHECK OPTION clause, 266-267
WITH ENCRYPTION, 17
WITH LOG, 45
WITH NOWAIT, 45
WITH RECOMPILE, 26
WITH SETERROR, 45
Workable environment
benefits of small changes, 713-714
creating a, 703-709
Wrapping procedures, 32-34, 575-576X
XML
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-364
XML Bulk Load, 431, 441-448
XMLDATA, 402
XML-Data Reduced (XDR), 416-419
XMLFragment, 442
xp
_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, 655
XPath, 384
XP (extreme programming), 725-732
XSLT (Extensible Stylesheet Language Transformation), 370-378

Y

Yourdon-DeMarco, 133

Updates

Submit Errata

More Information

Unlimited one-month access with your purchase
Free Safari Membership