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

Description

• Copyright 2002
• Dimensions: 7-3/8" x 9-1/4"
• Pages: 800
• Edition: 1st

• Book
• ISBN-10: 0-201-70046-8
• ISBN-13: 978-0-201-70046-6

SQL Server developers worldwide raved about Ken Henderson's The Guru's Guide to Transact-SQL: its exceptionally clear, conversational explanations, and its powerfully useful projects and code examples. This book helps SQL Server developers take the next step -- building more powerful, robust applications than ever before. Henderson identifies several key areas of SQL Server development that offer the greatest power -- and then covers each of them in exceptional detail. The book includes especially thorough coverage of Transact-SQL stored procedure programming, including features such as extended procedures, database design, and XML that are often disregarded in competitive books. Henderson introduces a method he has developed to add arrays to T-SQL, something previously thought impossible. He offers an ideal balance of theory and code, gradually building on basic techniques to create increasingly sophisticated solutions, and teaching the philosophy of Transact-SQL programming alongside syntax and technique. An accompanying CD-ROM includes extensive source code, including valuable proprietary code that makes T-SQL queries run faster. For every SQL Server developer.



Downloads

Downloads

Code Files (3.7 MB .zip)



Extras

Related Articles

Options Affecting SQL Server Locking Behavior

SQL Server and OPENXML

SQL Server: Blocking Problems

SQL Server: Details of Locking

SQL Server: Transaction and Locking Architecture



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

