Cover image for Microsoft SQL Server 2000 unleashed
Microsoft SQL Server 2000 unleashed
Rankins, Ray.
Personal Author:
Publication Information:
[Indianapolis, Ind.] : Sams, [2002]

Physical Description:
xxxii, 1472 pages : illustrations ; 24 cm + 1 computer optical disc (4 3/4 in.).
General Note:
Includes index.
Reading Level:
"User level: Intermediate-Advanced".
Title Subject:
Added Corporate Author:
Format :


Call Number
Material Type
Home Location
Item Holds
QA76.9.C55 R38 2002 Book and Software Set Central Closed Stacks

On Order



Itâe(tm)s important to take care of your teeth. Flossing and brushing should be part of your every day routine. What are some other ways to keep your teeth healthy?

Author Notes

Ray Rankins is currently owner and president of Gotham Consulting Services, Inc. near Saratoga Springs, New York (the summer place to be). Ray has been working with Sybase and Microsoft SQL Server since 1987 as a DBA, application developer, database designer, project manager, consultant, and instructor. He has worked in a variety of industries including financial, manufacturing, health care, retail, insurance, communications, public utilities, and government. His expertise is in database performance and tuning, query analysis, advanced SQL programming and stored procedure development, and database application design and development. Ray''s presentations on these topics at past user group conferences have been very well received. Ray is co-author of Sybase SQL Server 11 Unleashed, Microsoft SQL Server 6.5 Unleashed, and Sybase SQL Server 11 DBA Survival Guide, Second Edition, all published by Sams Publishing. He can be reached at

Paul Jensen is an MCDBA, MCT, MCSE, and OCP (Oracle Certified Professional) who has been involved with database and system administration for almost 15 years. His experience has made him a popular trainer, and the past few years have seen him bouncing around North America and Europe in his role as an MCT, bringing new SQL Server converts in to the fold. Paul lives in Ottawa, Canada, and can be contacted through his Web site at

Paul Bertucci is managing principal and founder of Database Archi-techs (, a database consulting firm based in San Francisco, California and with European offices in Paris, France. He has more than 20 years of experience doing database design, data architecture, data replication, performance and tuning, distributed data systems, data integration, and systems integration for numerous Fortune 500 companies including Intel, 3COM, Apple Computers, Toshiba, Lockheed, Wells Fargo, Safeway, Texaco, Charles Schwab, Cisco Systems, Sybase, Webgain, and i2 Technologies. He has authored numerous articles, standards, and courses such as Sybase''s "Performance and Tuning" course and "Physical Database Design" course. Paul is a frequent conference speaker and regularly teaches database design, performance and tuning, data modeling, OLAP, Supply Chain Management, and SQL courses. He has worked heavily with MS SQL Server, Sybase, DB2, and Oracle, and has architected several commercially available tools in the database, data modeling, performance and tuning, and data integration arena. Paul serves part-time as chief technical advisor for a data integration server software company as well as an advisory board member of a software services company in Silicon Valley. Paul received his formal education in computer science from UC Berkeley. He lives in northern California with his wife, Vilay, and four children (with a fifth on the way). Paul can be reached at and by phone at 925-674-0000.

Bennett McEwan is president of Parry Five Consulting, LLC, in Northern Virginia. Ben consults on SQL Server projects in the Washington, DC area. His top skills are in SQL Server replication, performance tuning, high availability database design, and data warehouse architecture. He is always on the lookout for challenging projects. Contact him at

Chris Gallelli is a database administrator working for Alltel Corporation in Latham, New York. Chris has more than seven years of experience with SQL Server and more than 15 years of computing experience. His experience is varied and includes work as both a Visual Basic developer and a DBA. He has a Bachelors degree in electrical engineering and a Masters degree in business administration from Union College. Chris currently lives near Albany, New York with his lovely wife Laura and two daughters Rachael and Kayla. Chris can be reached at

Alex T. Silverstein is a Client/Server developer for Alltel Information Services in New York''s capital region. He specializes in thin-client enterprise software design and programming. His expertise is in Microsoft-powered Internet solutions based on SQL Server 2000 and Internet Information Server (IIS), Extensible Markup Language (XML) and Extensible Stylesheet Language for Transformations (XSLT), Active Server Pages (ASP), Dynamic HTML (DHTML) and scripting languages. He is a graduate of Rutgers University. Alex can be contacted at

Table of Contents

Introductionp. 1
Part I Welcome to Microsoft SQL Serverp. 7
1 The Microsoft SQL Server Environmentp. 9
Overview of Client/Serverp. 10
SQL Server Database Enginep. 13
SQL Server Enterprise Managerp. 16
SQL Service Managerp. 18
SQL Server Agentp. 18
SQL Query Analyzerp. 19
SQL Profilerp. 20
Data Transformation Servicesp. 22
Replicationp. 23
Microsoft Search Server (Full Text Search)p. 25
Analysis Services (OLAP Server)p. 27
Summaryp. 28
2 SQL Server 2000 Environments and Editionsp. 29
SQL Server 2000 Environmentsp. 30
SQL Server 2000 Editionsp. 34
SQL Server 2000 Licensing Modelsp. 39
Summaryp. 42
3 What's New in SQL Server 2000p. 43
New SQL Server 2000 Featuresp. 44
SQL Server 2000 Enhancementsp. 49
Summaryp. 52
Part II Installation and Upgradep. 53
4 Installing a New SQL Server Instancep. 55
Choosing a SQL Server Editionp. 56
Hardware Requirementsp. 57
Software Requirementsp. 60
Selecting Installation Configuration Optionsp. 61
Verifying Installationp. 67
Starting, Stopping, and Pausing SQL Serverp. 68
Post-Installation Configurationp. 69
Installation Troubleshootingp. 71
Remote Installationp. 71
Unattended Installationp. 71
Installing a Named Instancep. 72
Upgrading from Previous Versionsp. 73
Summaryp. 74
5 Client Installation and Configurationp. 75
The SQL Server Client Architecturep. 76
Installing Client Toolsp. 78
Client Configurationp. 80
Network Protocol Supportp. 85
Installing ODBCp. 92
Connecting to SQL Server Over the Internetp. 94
Summaryp. 95
Part III SQL Server Administration Tasks and Toolsp. 97
6 SQL Server System and Database Administrationp. 99
Responsibilities of the System Administratorp. 100
System Databasesp. 101
System Tablesp. 102
System-Stored Proceduresp. 107
Useful System Proceduresp. 108
Other Methods of Querying the System Tablesp. 109
Summaryp. 110
7 SQL Server Enterprise Managerp. 111
Establishing Server Groups and Registering SQL Server in Enterprise Managerp. 112
Server Configurationp. 115
Databasesp. 117
Securityp. 120
Server Managementp. 122
Data Transformation Servicesp. 126
Support Servicesp. 127
System Toolsp. 128
Replicationp. 131
Using the Database Taskpadp. 132
Using the Query Designerp. 133
Scripting Objectsp. 135
Getting Helpp. 137
Summaryp. 137
8 SQL Server Command-Line Utilitiesp. 139
BCPp. 140
Data Transformation Services Utilitiesp. 141
Makepipe and readpipep. 144
ODBCcmptp. 145
Odbcpingp. 145
Rebuildmp. 146
Regrebldp. 147
Replication Utilitiesp. 148
SQLDiagp. 149
SQLMaintp. 150
SQLServrp. 151
VSwitchp. 152
Summaryp. 153
9 Creating and Managing Databasesp. 155
What Makes Up a SQL Server Database?p. 156
Data Storage in SQL Serverp. 156
Database Filesp. 157
Creating Databasesp. 160
Managing Databasesp. 164
Setting Database Optionsp. 168
Summaryp. 174
10 Creating and Managing Tables in SQL Serverp. 175
Datatypesp. 177
Numeric Datatypesp. 181
Datatype Synonymsp. 182
User-Defined Datatypesp. 183
Creating Tablesp. 184
Creating Tables Using T-SQLp. 186
Creating Tables Using Table Designerp. 193
Viewing Table Properties and Data in Enterprise Managerp. 196
Limitations on Table Size and Dimensionsp. 197
Dropping Tablesp. 197
Temporary Tablesp. 198
Summaryp. 198
11 Creating and Managing Indexesp. 199
Types of Indexesp. 200
Creating Indexes Using T-SQLp. 201
Creating Indexes Using Enterprise Managerp. 204
Managing Indexesp. 205
Dropping Indexesp. 205
Summaryp. 206
12 Data Integrityp. 207
Types of Data Integrityp. 208
Enforcing Data Integrityp. 209
Constraintsp. 209
Rulesp. 219
Defaultsp. 220
Summaryp. 225
13 Creating and Managing Views in SQL Serverp. 227
Definition of Viewsp. 228
Views to Simplify Retrieval of Datap. 228
Views as a Security Mechanismp. 230
Data Modifications and Viewsp. 231
Creating Viewsp. 232
Altering and Dropping Viewsp. 237
Partitioned Viewsp. 238
Indexed Viewsp. 240
Summaryp. 242
14 Creating and Managing Stored Proceduresp. 243
Advantages of Stored Proceduresp. 244
Creating and Executing Stored Proceduresp. 245
Deferred Name Resolutionp. 249
Viewing and Modifying Stored Proceduresp. 252
Using Input Parametersp. 258
Using Output Parametersp. 264
Returning Procedure Statusp. 265
Cursors in Stored Proceduresp. 267
Nested Stored Proceduresp. 272
Using Temporary Tables in Stored Proceduresp. 276
Using the table Datatypep. 282
Remote Stored Proceduresp. 285
Debugging Stored Procedures with Query Analyzerp. 285
Debugging with Microsoft Visual Studio and Visual BASICp. 287
System Stored Proceduresp. 288
Stored-Procedure Performancep. 295
Using Dynamic SQL in Stored Proceduresp. 308
Autostart Proceduresp. 314
Extended Stored Proceduresp. 318
Stored Procedure Coding Guidelines and Limitationsp. 324
Summaryp. 328
15 Creating and Managing Triggersp. 329
Benefits and Uses of Triggersp. 330
Creating Triggersp. 331
After Triggersp. 334
inserted and deleted Tablesp. 338
Checking for Column Updatesp. 341
Nested Triggersp. 342
Recursive Triggersp. 343
Enforcing Referential Integrity with Triggersp. 344
INSTEAD OF Triggersp. 350
Summaryp. 359
16 User-Defined Functionsp. 361
Why Use User-Defined Functions?p. 362
Types of Functionsp. 364
Creating and Managing Functionsp. 368
Invoking User-Defined Functionsp. 382
System-Wide Table Valued Functionsp. 382
Rewriting Stored Procedures as Functionsp. 383
Summaryp. 385
17 Security and User Administrationp. 387
An Overview of SQL Server Securityp. 388
Authentication Methodsp. 388
SQL Server Security: Loginsp. 390
SQL Server Security: Usersp. 391
SQL Server Security: Rolesp. 392
Managing SQL Server Loginsp. 396
Managing SQL Server Usersp. 400
Managing Database Rolesp. 401
Managing SQL Server Permissionsp. 403
SQL Server Permission Approachesp. 406
Object Ownersp. 407
Using Encryptionp. 408
Summaryp. 408
18 Database Backup and Recoveryp. 409
Develop a Backup and Restore Planp. 410
Why Back Up Your Databases?p. 411
A Typical Backup and Restore Scenariop. 411
Types of Backupsp. 413
Setting the Recovery Modep. 416
Backup Devicesp. 417
Media Sets and Familiesp. 419
Creating Backup Devices with Transact-SQLp. 420
Creating Backup Devices with SQL Enterprise Managerp. 423
Backing Up the Databasep. 424
Backing Up the Transaction Logp. 430
Restoring the Databasep. 435
Transact-SQL Restore Examplesp. 438
Restoring to a Different Databasep. 442
Restoring a File or Filegroupp. 443
Restoring to a Point in Timep. 443
Performing a Partial Database Restorep. 444
Restoring the System Databasesp. 445
Additional Backup Considerationsp. 445
Summaryp. 448
19 Database Maintenancep. 449
What Needs to Be Maintainedp. 450
The Database Maintenance Plan Wizardp. 450
Setting Up Your Own Maintenance Tasksp. 457
The Database Console Commands (DBCC)p. 458
Using SQLMAINT. EXE for Database Maintenancep. 460
Summaryp. 461
20 SQL Server Scheduling and Notificationp. 463
SQL Agent Overviewp. 464
Configuring SQL Server Agentp. 464
Viewing the SQL Server Agent Error Logp. 467
Operatorsp. 467
Jobsp. 468
Alertsp. 472
Scripting Jobs and Alertsp. 475
Multiserver Job Managementp. 476
Event Forwardingp. 477
Summaryp. 478
21 Managing Linked and Remote Serversp. 479
Remote Serversp. 480
Linked Serversp. 486
Adding, Dropping, and Configuring Linked Serversp. 488
Mapping Local Logins to Logins on Linked Serversp. 495
Obtaining General Information About Linked Serversp. 499
Executing a Stored Procedure Via a Linked Serverp. 500
Setting Up Linked Servers Through Enterprise Managerp. 501
Summaryp. 503
22 Importing and Exporting SQL Server Data Using BCPp. 505
The Bulk-Copy Programp. 514
Logged and Non-Logged Operationsp. 533
The BULK INSERT Statement (Transact-SQL)p. 538
Improving Load Performancep. 539
BCP Extrasp. 540
Summaryp. 543
23 Importing and Exporting SQL Server Data Using Data Transformation Services (DTS)p. 545
DTS Architecture and Conceptsp. 550
Package Execution Utilitiesp. 553
Running the DTS Wizardp. 557
DTS Designerp. 561
A Bit More on Metadatap. 567
Summaryp. 567
24 Administering Very Large SQL Server Databasesp. 569
Do I Have a Very Large Database (VLDB)?p. 570
VLDB Maintenance Issuesp. 570
Partitioning Datap. 573
Summaryp. 575
25 Data Replicationp. 577
What Is Replication?p. 578
The Publisher, Distributor, and Subscriber Metaphorp. 580
Replication Scenariosp. 585
Replication Agentsp. 594
Planning for SQL Server Data Replicationp. 602
SQL Server Replication Typesp. 604
User Requirements Drive the Replication Designp. 607
Setting Up Replicationp. 609
Scripting Replicationp. 618
Monitoring Replicationp. 620
Summaryp. 634
26 Defining SQL Server Environment and Naming Standardsp. 637
The SQL Server Environment Approachp. 638
SQL Server Naming Standardsp. 648
Summaryp. 664
Part IV Transact-SQLp. 667
27 Using Transact-SQL in SQL Server 2000p. 669
T-SQL and ANSI/ISO SQLp. 670
What's New for T-SQL in SQL Server 2000p. 671
Select, Insert, Update, and Deletep. 680
SQL Server Functionsp. 702
Programming Constructsp. 723
Cursorsp. 757
Summaryp. 780
28 Transaction Management and the Transaction Logp. 781
What Is a Transaction?p. 782
How SQL Server Manages Transactionsp. 783
Defining Transactionsp. 783
Transaction Logging and the Recovery Processp. 793
Transactions and Batchesp. 799
Transactions and Stored Proceduresp. 802
Transactions and Triggersp. 806
Transactions and Lockingp. 811
Coding Effective Transactionsp. 813
Long-Running Transactionsp. 814
Bound Connectionsp. 816
Distributed Transactionsp. 819
Summaryp. 819
29 Distributed Transaction Processingp. 821
Distributed Transaction Processingp. 822
Distributed Queriesp. 825
Linked Serversp. 826
Distributed Transactionsp. 832
MS DTC Architecturep. 833
Summaryp. 841
Part V SQL Server Internals and Performance Tuningp. 843
30 SQL Server Internalsp. 845
SQL Server Memory Managementp. 846
SQL Server Process Managementp. 853
SQL Server Disk I/Op. 855
SQL Server Storage Structuresp. 857
Database Files and Filegroupsp. 858
Database Pagesp. 873
Tablesp. 904
Indexesp. 906
Data Modification and Performancep. 940
Summaryp. 949
31 Indexes and Performancep. 951
Index Usage Criteriap. 952
Index Selectionp. 955
Evaluating Index Usefulnessp. 956
Index Statisticsp. 959
Index Design Guidelinesp. 974
Indexed Viewsp. 982
Indexes on Computed Columnsp. 983
The Index Tuning Wizardp. 984
Choosing Indexes: Query Versus Update Performancep. 994
Summaryp. 997
32 Understanding Query Optimizationp. 999
What Is a Query Optimizer?p. 1000
Query Compilation and Optimizationp. 1001
Step 1 Query Analysisp. 1003
Step 2 Index Selectionp. 1009
Step 3 Join Selectionp. 1027
Step 4 Execution Plan Selectionp. 1032
Reusing Query Plansp. 1034
Other Query Processing Strategiesp. 1039
Parallel Query Processingp. 1042
Data Warehousing and Large Database Query Strategiesp. 1046
Common Query Optimization Problemsp. 1047
Managing the Optimizerp. 1050
Summaryp. 1059
33 Query Analysisp. 1061
Displaying Execution Plans in Query Analyzerp. 1063
Query Analyzer Server Tracep. 1076
Query Analyzer Client Statisticsp. 1077
Showplan_All and Showplan_Textp. 1078
Statisticsp. 1081
Query Analysis with SQL Profilerp. 1089
Summaryp. 1089
34 Using the SQL Server Profilerp. 1091
SQL Server Profiler Architecturep. 1092
Creating Tracesp. 1093
Saving and Exporting Tracesp. 1107
Replaying Trace Datap. 1109
Defining Server Side Tracesp. 1110
Profiler Scenariosp. 1111
Summaryp. 1116
35 Using the SQL Debugger in Query Analyzerp. 1117
The First T-SQL Debuggerp. 1118
Using the T-SQL Debugger in Query Analyzerp. 1119
Limits of the Debuggerp. 1123
Stepping Through a Stored Procedurep. 1123
Summaryp. 1124
36 Monitoring SQL Server Performancep. 1125
Performance-Monitoring Approachp. 1127
Performance Monitorp. 1128
Windows Performance Countersp. 1131
SQL Server Performance Countersp. 1141
SNMP Supportp. 1146
Using DBCC to Examine Performancep. 1147
Other SQL Server Performance Considerationsp. 1150
Summaryp. 1150
37 Locking and Performancep. 1151
The Need for Lockingp. 1152
Transaction Isolation Levels in SQL Serverp. 1153
The Lock Managerp. 1157
Monitoring Lock Activity in SQL Serverp. 1158
SQL Server Lock Typesp. 1169
SQL Server Lock Granularityp. 1178
Lock Compatibilityp. 1189
Locking Contention and Deadlocksp. 1190
Table Hints for Lockingp. 1206
Optimistic Lockingp. 1210
Summaryp. 1213
38 Database Design and Performancep. 1215
Basic Tenets of Designing for Performancep. 1216
Logical Database Design Issuesp. 1217
Denormalizing the Databasep. 1220
Indexes and Performancep. 1227
Index Design Guidelinesp. 1236
SQL Server Index Maintenancep. 1239
Updates and Performancep. 1247
Database File Groups and Performancep. 1248
RAID Technologyp. 1249
Summaryp. 1252
39 Configuring, Tuning, and Optimizing SQL Server Optionsp. 1255
SQL Server Instance Architecturep. 1256
Configuration Optionsp. 1257
Fixing an Incorrect Option Settingp. 1264
Setting Configuration Options with SQL Enterprise Managerp. 1264
Obsolete Configuration Optionsp. 1265
Configuration Options and Performancep. 1266
Summaryp. 1286
Part VI Integrating SQL Server into the Microsoft Architecturep. 1287
40 SQL Mailp. 1289
Setting Up an E-Mail Client/Profilep. 1290
Configuring SQL Mailp. 1296
Configuring SQLAgentMailp. 1298
SQL Mail Stored Proceduresp. 1305
Summaryp. 1314
41 Using XML in SQL Server 2000p. 1315
Creating a Virtual Directory for Use with SQL Server 2000p. 1316
Exploring Extensible Markup Language (XML)p. 1318
Retrieving Data Using the FOR XML Clausep. 1321
Using FOR XML RAWp. 1321
Using FOR XML AUTOp. 1323
Retrieving XML-Data Schemasp. 1328
Retrieving Binary Data in XMLp. 1330
The Basics of XML Path Language (XPath)p. 1331
Using XML in Stored Proceduresp. 1333
Using OPENXML to Read XMLp. 1335
Using URL Queriesp. 1338
Using XML Templatesp. 1340
Using XML Updategramsp. 1343
Summaryp. 1344
42 Microsoft SQL Server Analysis Servicesp. 1347
What Is Analysis Services and OLAP?p. 1348
Understanding the Analysis Services Environment and the "Land of Wizards"p. 1350
An Analytics Design Methodologyp. 1356
An OLAP Requirements Examplep. 1359
OLAP Cube Creationp. 1360
Creating an OLAP Databasep. 1362
Working with a Relational Databasep. 1381
Files at the Operating System Levelp. 1385
Summaryp. 1391
43 Microsoft Transaction Serverp. 1393
MTS Overviewp. 1394
Building an MTS Componentp. 1400
Installing an MTS Componentp. 1403
Configuring Securityp. 1407
Running an MTS Applicationp. 1410
Using Database Connection Poolingp. 1414
Summaryp. 1415
44 SQL Server Clusteringp. 1417
From Windows NT Enterprise Edition to Windows 2000 Advanced Serverp. 1419
Cluster Servicesp. 1421
SQL Clustering and Fail-Over Supportp. 1423
Network Load Balancingp. 1426
Summaryp. 1428
45 Full Text Searchp. 1429
How Search Server Worksp. 1430
Setting Up a Full Text Indexp. 1431
Maintaining Full Text Indexesp. 1433
Full Text Searchesp. 1436
Using Document Filtersp. 1439
Index Server and Full-Text Searching of Data Filesp. 1440
Summaryp. 1441
Indexp. 1443