Home > Store

Microsoft SQL Server 2000: A Guide to Enhancements and New Features

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

Microsoft SQL Server 2000: A Guide to Enhancements and New Features


  • Sorry, this book is no longer in print.
Not for Sale


  • Copyright 2002
  • Edition: 1st
  • Book
  • ISBN-10: 0-201-75283-2
  • ISBN-13: 978-0-201-75283-0

Microsoft's SQL Server™ 2000 promises performance, scalability, and speed. Microsoft SQL Server™ 2000: A Guide to Enhancements and New Features shows database administrators and developers how to deliver on these promises. This book illustrates all the significant enhancements and additions in SQL Server 2000 and shows how they can be used most effectively. Readers learn by example how to use security features, replication, backup procedures, and data warehousing and optimization techniques.

Beginning with an overview of SQL Server 2000, this book discusses online transaction processing (OLTP) and online analytical processing (OLAP), features a tour of different SQL Server releases, and offers a guide to installation. The author describes and demonstrates the changes since SQL Server 7.0, thoroughly exploring SQL Server 2000's capacity as a Web-enabled database server. Readers are then immersed in advanced database administration topics such as performance optimization and debugging techniques.

Microsoft SQL Server™ 2000 also serves as a resource for those new to SQL Server, providing a basic introduction and details about how to make a conversion work, as well as a feature-by-feature comparison to Oracle 9i. Database administrators will find a collection of clearly explained scripts and utilities to simplify day-to-day tasks. This book concludes with a discussion of how SQL Server fits into the .NET environment and what to expect of the next release.

Sample code is used extensively to illustrate feature sets and illuminate topics covered. The accompanying CD includes this sample code as well as scripts and utilities. Rahul Sharma also shares his tips for optimizing applications' performance with adjustments to code, settings, and hardware. He alerts readers to common errors and fixes, and provides a checklist to guide readers through the installation and verification of the database server's stability. Rahul has translated his experience into knowledge and ready-to-use tools that database administrators and developers need to realize the many benefits of moving to SQL Server 2000.


Sample Content

Table of Contents

(NOTE: Each chapter concludes with a Summary.)



1. SQL Server 2000 Overview.

SQL Server Editions.

Hardware Requirements for Installation.

Installation of SQL Server 2000.

Debugging Installation Issues with SQL Server 2000.

Upgrading from a Previous Version of SQL Server.

Edition Upgrades.

Adding Components to an Instance of SQL Server 2000.

Uninstalling SQL Server 2000.

Unattended Installs.

Installing SQL Server Using SMS.

Rebuilding the Registry.

Multiple Instances.

Default Instances.

Named Instances.

File Locations for Multiple Instances of SQL Server.

Network Protocols.

Collations Support for Internationalization.

Checklist for a Successful Install of SQL Server.

Maintenance and Integrity Checks.


2. Enhancements and Changes to Existing Features.

Feature Sets.

Fully Web-Enabled.

English Query.

More Scalable and Reliable.

BizTalk Server 2000.

Multi-Instance Support.

Reduction in Development Time.

Accessing Data via Internet.

Web-Based Analysis.

VI SAN Support.

Indexed Database Views.

T-SQL Debugger.

User-Defined Functions.

New Data Types.

Cascading DRI.

Distributed Partitioned Views.

OLAP Services.


INSTEAD OF and AFTER Triggers.

Collation Enhancements.

Full-Text Search Enhancements.

Multiple Instances of SQL Server.

Index Enhancements.

Failover Clustering Enhancements.

Net-Library Enhancements.

64-GB Memory Support.

Distributed Query Enhancements.

Kerberos and Security Delegation.

Backup and Restore Enhancements.

Scalability Enhancements for Utility Operations.

Text in Row Data.

3. XML Support in SQL Server 2000.

Generating XML with the SELECT Statement.

Generating XML over the Internet.

Retrieving XML-Formatted Data from SQL Server.

Reserved Characters.

XML Templates.

Using EXPLICIT Mode.

XSL Stylesheets.

XML System Stored Procedures.


Writing Queries Against an XML Document.

OPENXML Parameters.

XPath Expression to Identify the Nodes to Be Processed (rowpattern).

Description of the Rowset to Be Generated.

Mapping Between the Rowset Columns and the XML Nodes.

Specifying Metaproperties in OPENXML.


4. Engine Enhancements in SQL Server 2000.

The Enhancements.

Interacting with Data.

Reading Data More Effectively.

Top n Enhanced.

Shared Scans.


Tables and Indexes.

In-Row Text.

New Data Types.


Logging and Recovery.

Logical Log Marks.

Shrinking the Transaction Log.

Recovery Models.

Improved Backup Functionality.

Administrative Improvements.

Database Verification.

Database State Control.

System Process IDs and Units of Work.

Dynamic Tuning.

Data Storage Components.

Memory Pool.

Files, Filegroups, and Disks.

5. DTS Enhancements.

New Features in DTS.

New Custom Tasks.

Enhanced Logging Facilities.

Saving DTS Packages to Visual Basic Files.

Using the Multiphase Data Pump.

Using Parameterized Queries.

Using Global Variables to Pass Information Between DTS Packages.

Using DTS Designer.

Tasks: Defining Steps in a Package.

Workflows: Setting Task Precedence.

Connections: Accessing and Moving Data.

Data Pump: Transforming Data.

Data Pump Error Logging.

Data Pump Phases.

Options for Saving DTS Packages.

DTS as an Application Development Platform.

6. Profiler and Index-Tuning Wizard Enhancements.

SQL Profiler Terminology 113Starting Profiler Trace.



Data Columns.



Defining Your Own Trace Using Stored Procedures.

Defining a Server-Side Trace.

Known Bug in SQL Server 2000 Profiler.




Index Tuning Wizard.

Analyzing Index Tuning Wizard Output.

Starting the Index Tuning Wizard.


7. Replication Enhancements.

Types of Replication.

Replication Options.

New Features and Improvements.

Implementing Replication.

Merge Replication.

Transactional Replication.

Queued Updating.

Transforming Published Data.

Replication Usability.

Log Shipping.

Configuring Log Shipping with the Database Maintenance Plan Wizard.

Configuring Log Shipping Manually.

8. New Data Types.




9. User-Defined Functions.

Types of User-Defined Functions.

Scalar Functions.

Table-Valued Functions.

Obtaining Information About Functions.

10. Indexed Views.

Performance Gains from Indexed Views.

Benefits of Using Indexed Views.

Query Optimizer.

Design Considerations.

Creating an Indexed View.

Using SET Options to Obtain Consistent Results.

Using Deterministic Functions.

Additional Requirements.

Maintaining Indexed Views.

11. Trigger Alternatives.

Types of Triggers.

AFTER Triggers.

INSTEAD OF Triggers.

Core Difference Between INSTEAD OF and AFTER Triggers.

Which Trigger to Pick?

Designing INSTEAD OF Triggers.

12. Meta Data Services Enhancements.

Meta Data Fundamentals.

Meta Data Is Abstract.

Meta Data Has Context.

Meta Data Has Multiple Purposes.

Meta Data Management.

Information Model Fundamentals.

Information Model Building Blocks.

Standard Information Models.

Importance of Information Models.

New Features in Meta Data Services.

Meta Data Browser Enhancement.

XML Encoding Enhancements.

XML in Meta Data Services.

13. Tool Enhancements.

Query Analyzer.


Using Templates in SQL Query Analyzer.

Building Your Own Template.

Copy Database Wizard.

Uses of Copy Database Wizard.

Copy and Move Process.

14. Backup and Recovery Enhancements.

Backup and Restore Architecture.


Recovery Models.

Simple Recovery.

Full Recovery.

Bulk-Logged Recovery.

Switching Recovery Models.

15. Analysis Services Enhancements.

Cube Enhancements.

Dimension Enhancements.

Data Mining Enhancements.

Other Enhancements.

16. Distributed Partitioned Views.

What Are Distributed Partitioned Views?

How Distributed Partitioned Views Work.

Building Distributed Partitioned Tables and Views.

How the Query Processor Uses Distributed Partitioned Views.

Designing Systems That Maximize Distributed Partitioned View Performance.

Designing Partitions.

Designing the Application Tier for Load Balancing.

Designing for High Availability.

Backing Up and Restoring Federated Database Servers.

Updatable Partitioned Views.

Table Rules.

Column Rules.

Partitioning Column Rules.

Data Modification Rules.

INSERT Statements.

UPDATE Statements.

DELETE Statements.

Distributed Partition View Rules.

17. T-SQL Debugger.
18. Cascading Declarative Referential Integrity.

Cascading Referential Integrity Constraints.

Multiple Cascading Actions.

Triggers and Cascading Referential Actions.

Cascading Referential Constraints Catalog Information.

19. Collation Enhancements.

How Character Data Is Stored.

International Data and Unicode.

Sort Order.

What Is Unicode and How Can It Be Used?




Data Types in SQL Server 2000.

Non-Unicode Text Types: char, varchar, text.

Unicode Text Types: nchar, nvarchar, ntext.

Date/Time Types: datetime, smalldatetime.

Collation in SQL Server 2000.

Collations Specified at Multiple Levels.

Collations at the Server Level.

Collations at the Database Level.

Collations at the Column Level.

Collations in Expressions.

Considerations Before Changing the Collation of a Database.

Collation Precedence.

COLLATE Keyword.

Limitations of the COLLATE Keyword.

Issues with Defining Collation at the Column Level.

LCIDs and Collations.

ISO Strings and Collations.

Multilingual Data in the User Interface.

Multilingual Information in the Grid and SQL Panes of SQL Query Analyzer.

Format Issues in the Query Designer.

Sort Order.

Double-Byte (DBCS) Characters.

Getting to SQL Server Data (Data Access Methods).

Multilingual Transact-SQL.


20. SQL-Distributed Management Objects.

What Is SQL-DMO?

SQL-DMO Object Model.

ExecuteImmediate Method.

Script Method.

SQL-DMO Code for Executing a Job.

SQL-DMO Code to Play with Triggers.

21. Microsoft Desktop Engine.

Portable and Compatible.

Minimum Requirements.

Features and Limitations.

MSDE Install.

Scenarios for Using MSDE.

Developing Applications with MSDE.

Sample Application.

MSDE Service Pack1.

22. Administration.

Database Consistency Checks (DBCCs).

Undocumented DBCC Commands.

Information Schema Views.

Orphaned Sessions.

Issues with Orphaned Sessions.

Resolving Issues through the Query Analyzer.

Changing the Check Frequency.

Linked Servers.

Creating Linked Servers.

Setting the Linked Server Options.

Creating Logins for the Linked Server.

Querying the Linked Server.

23. Debugging Database Performance Issues.

Performance Monitor or Task Manager?

Performance Monitor.

Using the Performance Monitor.

Which Process Is the Bottleneck?

Task Manager.

Performance Monitor Counters.

CPU Performance Counters.

I/O Performance Counters.

Memory Performance Counters.

Network Performance Counters.

SQL Server Performance Counters.


Event Viewer.

SQL Server Profiler.

Hardware Issues.

Database Setup.

SQL Tips.

Create and Use Proper Indexes.

Use Clustered Indexes.

Short Index Keys.

Use Covering Indexes.

Help SQL Server Choose Indexes.

Estimate Index Selectivity.

Use the SQL Server Query Optimizer.

Use Indexes Effectively.

Use Foreign Keys for Joins.

Use Concatenated Keys Correctly.

Use Only Selective Indexes.

Avoid NULL Checks.

Avoid Functions and Expressions on the Indexed Columns.

Avoid the Inequality Operator.

Avoid Full Table Scans.

Use EXISTS to Check for the Existence of a Record.

Replace NOT IN with NOTEXISTS.

Large Insert, Update, and Delete SQLs.

Do Not Use Positional Numbers.

Use Group Functions Carefully.

24. SQL Server 2000 Service Pack 1.

Before the Install.

Installing Service Pack 1.

Install Database Components SP1.

Install Analysis Services SP1.

Install Desktop Engine SP1.

Removing Service Pack 1/Rolling Back to the Previous State.

Fixing Bugs.

Fixes for MDAC.

SQL Server 2000 Fixes.


25. Oracle 9i vs. SQL Server 2000.

Database Definition.

Database System Catalogs.

Physical and Logical Storage Structures.

Striping Data.

Transaction Logs and Automatic Recovery.


Comparison of Features Provided by the Two RDBMS.

New Features in Oracle 9i.

26. Migrating from Oracle 9i Databases to SQL Server 2000.

Database Security and Roles.

Database File Encryption.

Network Security.

Login Accounts.

Groups, Roles, and Permissions.

Database Users and the Guest Account.

Sysadmin Role.

db-owner Role.

Defining Database Objects.

Database Object Identifiers.

Qualifying Table Names.

Creating Tables.

Table and Index Storage Parameters.


Indexed Views/Materialized Views.


Clustered Indexes.

Nonclustered Indexes.

Index Syntax and Naming.

Index Data Storage Parameters.

Ignoring Duplicate Keys.

Indexes on Computed Columns.

Using Temporary Tables.

Data Types.

Using Unicode Data.

Object-Level Permissions.

Enforcing Data Integrity and Business Rules.

Naming Constraints.

Primary Keys and Unique Columns.

Adding and Removing Constraints.

Generating Unique Values.

Domain Integrety.

Referential Integrity.

User-Defined Integrity.

Delaying the Execution of a Stored Procedure.

Specifying Parameters in a Stored Procedure.


Transactions, Locking, and Concurrency.


Locking and Transaction Isolation.

Dynamic Locking.

Changing Default Locking Behavior.

Handling Deadlocks.

Insert Statements: The Forgotten Culprit.

Remote Transactions.

Distributed Transactions.

Two-Phase Commit Processing.

SQL Language Support.

SELECT and Data Manipulation Statements.

SELECT Statements.

INSERT Statements.

UPDATE Statements.

DELETE Statements.


Manipulating Data in Identity and Timestamp Columns.

Locking Requested Rows.

Row Aggregates and the Compute Clause.

Join Clauses.

Reading and Modifying BLOBs.


Conditional Tests.

Converting Values to Different Data Types.

User-Defined Functions.

Comparison Operators.

Pattern Matches.

Using NULL in Comparisons.

String Concatenation.

Control-of-Flow Language.

Declaring Variables.

Assigning Variables.

Statement Blocks.

Conditional Processing.

Repeated Statement Execution (Looping).

GOTO Statement.

PRINT Statement.

Returning from Stored Procedures.

Raising Program Errors.

Implementing Cursors.

Declaring a Cursor.

Opening a Cursor.

Fetching Data.


Closing a Cursor.

Developing and Administering Database Replication.

Moving Data and Applications.


27. Administration and Utility Scripts.


Disable Constraints.

Enable Constraints.




Update Statistics.






















Schema Comparison Scripts.



DTS Packages.

Refresh Databases.

Back Up Databases and Version Control.




Executing DTS Through T-SQL.

Copying Databases from Server to Server.

Method 1.

Method 2.

Method 3.

Method 4.

Method 5.


28. SQL Server 2000 and .NET.

What Is Microsoft.NET?

From Products Vendor to Services Vendor.

Web Services.

.NET Servers.

SQL Server 2000 and .NET.

Next Release of Microsoft Server.

Windows XP and .NET.

Index. 0201752832T03082002


This book is written for SQL Server developers, DBAs, database managers, systems engineers, and business analysts. It does briefly cover the basics of SQL Server in Part IV, where SQL Server 2000 is compared with Oracle 9i, but throughout the book it is assumed that you have some familiarity with database concepts and SQL Server (any version).

When I began writing this book, I had these goals in mind:

  • Cover the new feature sets of SQL Server 2000 and its enhancements over SQL Server 7.0.
  • Give a checklist of common errors made with SQL Server and point out how to rectify them to make sure that installs and servers are functioning correctly.
  • Provide sample code wherever possible to illustrate the feature sets and emphasize the points.
  • Avoid excessive screenshots and other fillers often used in computer books.
  • Provide tips (on SQL Server code, settings, and hardware) for improving the performance of SQL Server applications.
  • Provide SQL Server developers and DBAs with scripts and utilities that ease their day-to-day administration jobs.
  • Provide a consolidated list comparing features of Oracle 9i and SQL Server 2000 and provide examples of what users should look out for when they start converting their Oracle database to SQL Server.
  • Provide information about SQL Server.NET and how SQL Server 2000 fits into the .NET environment.

You, the readers, will have to judge for yourselves whether I have met these goals have been met or not. Regardless of the degree of success, my hope is that at least the effort that went into writing this book will be evident.

At times where I had to illustrate the command parameters (as for the Index Tuning Wizard--itwiz.exe) and describe what the command line parameters are, it may seem like a rehash of SQL Server Books Online (BOL). This is because the commands are exactly the same, and there can be no better description than BOL for what those command line parameters stand for. They are included only if necessary to illustrate a particular command functionality. All credit for these descriptions of command parameters goes to the BOL team at Microsoft.

Short Outline of the Book

The book is divided into five parts that contain 28 chapters.

Chapter 1: Overview of SQL Server 2000 and what it has to offer in the world of OLTP (online transaction processing) and OLAP (online analytical processing). The SQL Server install, different editions and their feature set differences, upgrading from the previous version to SQL Server 2000 and between different editions, multiple instances, and how to debug SQL Server install are also covered in this part. It also contains a checklist for checking the database server's stability from time to time.

Part I: New Features and Improvements over SQL Server 7.0: Listing and brief description of the enhancements and the new features of SQL Server 2000. Chapters 2-19 cover SQL Server 7.0 (in brief) and what is replaced/added in SQL Server 2000 (in detail, along with examples). Emphasis is on XML and why/how SQL Server 2000 is a Web-enabled database server, how data can be retrieved/added/modified through XML, SQL 2000's tight integration with the Internet, and other new features such as indexed views, DPVs (distributed partitioned views), UDFs (user-defined functions), debugger, and so on.

Part II: Advanced DBA Topics: Chapters 20-24 cover some advanced DBA topics such as using the object models of SQL-DMO to write effective programs, DBCC (database consistency checks) commands and performance optimization and debugging techniques. Orphaned sessions and MSDE are discussed. The installation and bug fixes in SQL Server 2000 Service Pack 1 are also discussed.

Part III: Comparison with Oracle 9i: Since Oracle is the biggest competitor of SQL Server 2000 and since many companies that use Oracle are either shifting to SQL Server 2000 (because it's economically viable) or are planning to support SQL Server 2000 as well in order to broaden their customer base, it is necessary to know the differences between the two and how one can convert applications from Oracle to SQL Server and what it takes from the database perspective to do so.

SQL Server basics are also discussed, as this part is oriented toward people who know Oracle and are interested in migrating their applications to SQL Server 2000. The basics will also be beneficial for novice users of SQL Server 2000.

Part IV: Scripts and Utilities: Chapter 27 contains scripts and utilities to make the day-to-day task of database administration easy. All the scripts are explained and are included on the CD-ROM as well.

Part V: SQL Server.NET: .NET is the latest buzzword in the Microsoft arena. In Chapter 28 we take a look at how SQL Server 2000 fits into the .NET scenario and what can be expected in the next release of SQL Server, code named "Yukon."



& (ampersand), 24, 54
<> (angle brackets), 54
Ô (apostrophe), 54
@ (at sign), 245
$ (dollar sign), 269
" (double quote), 54
= (equal sign), 430
! (exclamation point), 245
/ (forward slash), 54
% (percent sign), 54, 245
| (pipe symbol), 439
+ (plus sign), 54, 430, 439
# (pound sign), 54, 245
? (question mark), 54, 252
_ (underscore), 24


Access (Microsoft), 99, 100, 107, 358
Access Connectivity (Microsoft), 99
ACID (atomicity, consistency, isolation, durability) properties, 146
Actions, 214-215
Active Directory (Microsoft)
Analysis Services and, 219

distributed partitioned views and, 228
replication and, 151
service, 37

ActiveX controls (Microsoft), 10, 103-109, 149. See also ADO (Microsoft ActiveX Data Objects)
Adapters, adding, 11
Add Adapter option, 11
Add-ins, 215, 219
advanced topics for, 293-306
features for, improvements to, 93-95

Administrative Tools program group, 328
ADO (Microsoft ActiveX Data Objects), 102, 519, 521. See also ActiveX controls (Microsoft)
collations and, 270, 271
Service Pack 1 and, 360, 362

ADO.NET, 521
Advanced Options dialog box, 21, 23
Advanced Options screen, 20-21
Advanced Server, 96, 284
AFTER triggers, 43, 181, 182-186, 241-242. See also Triggers
Aggregations, 170, 214, 432-435
basic description of, 314, 318
event logs and, 328
settings for, 318
trapping errors for, 328

adaptive, 79
data mining and, 217
dynamic, 79, 95
engine enhancements and, 77-78, 79, 95
sorting, 248

Aliases, 219, 366
Allow template queries option, 57
ALTER DATABASE statement, 79, 94, 259
ALTER FUNCTION statement, 161, 166
Alternate synchronization partners, 147-148
ALTER TABLE statement, 239-240, 261
American National Standards Institute (ANSI). See ANSI (American National Standards Institute)
American Standard Code for Information Interchange (ASCII). See ASCII (American Standard Code for Information Interchange)
Ampersand (&), 24, 54
Analysis Manager, 218, 219
Analysis Services, 40, 104
data mining and, 217-218
enhancements, 42, 213-220
Service Pack 1 and, 351-352, 355, 366-367

Angle brackets (<>), 54
ANSI (American National Standards Institute), 230, 271, 275
APIs (application program interfaces), 136, 191
collations and, 251, 252
MSDE and, 283-284
orphaned sessions and, 305
Service Pack 1 and, 360
Unicode and, 251

Apostrophe (Ô), 54
AppendToFile flag, 279
Application(s). See also APIs (application program interfaces)
client, data mining in, 218
developing, with MSDE, 290-291
lock manager, 78
logs, 329-330
moving, from Oracle, 454-455
object, 278
surrogate-safe, 250
tier, designing, for load balancing, 228

Archive and Restore Databases Add-in, 219
ARITHABORT settings, 176
ASCII (American Standard Code for Information Interchange), 250, 263
ASP (Microsoft Active Server Pages), 157, 520
ASP.NET, 520
ATL (Active Template Library), 107-108, 360. See also Templates
At sign (@), 245
Audits, 113
Authentication. See also Login; Security
additional methods for, 218
data mining and, 218
Kerberos and, 45
Mixed Mode, 355
SQL Server installation and, 10

Authentication Mode dialog box, 355
AUTO mode, 50, 52
Auto Rollback option, 235
Autorun.exe, 16, 18, 23
autoshrink option, 30
Availability, high, designing for, 228-229
AVG function, 174, 348
AWE (Advanced Windows Extension), 45, 80


Backup(s), 12-13, 30-31. See also Recovery
architecture, 205-207
capabilities, 206-207
cold, 30
data transfer rates for, 207
DBCCs and, 293
differential, 37, 79, 92, 208, 210-211
distributed partitioned views and, 229
enhancements, 45, 79-80, 88-92, 205-212
federated database tier and, 229
full, 31
history tables, 206
hot, 30
options, 46, 206
Oracle and, 381
packages, 507
passwords for, 80
to production databases, 30
serverless, 37
snapshot, 37, 81, 91-92
SQL Server installation and, 12
unattended, 91

Backup Exec, 13
Backup object, 276
BACKUP statement, 206
BackUp.vbs script, 31, 500-501
Backward compatibility, 15
Batch files, 21-22
bcp utility, 271
BEGIN...END block, 162, 163, 164
BETWEEN operator, 231
BIG_COUNT function, 155
bigint data type, 41, 67, 86, 155
Binary sort order, 26
BizTalk Server (Microsoft), 37
BLOBs (Binary Large Objects), 71, 431
Bottlenecks, 317-318, 322, 331-335
Breakpoints, 41, 235-327
Buffer cache, 87, 88
Bugs. See Errors
BULK INSERT statement, 271
Bulk Insert task, 103
Business rules, 405-414


C (high-level language), 199
C++ (high level language), 199, 276
Caches, 87, 88
Calculated Member Manager Add-in, 215
Callstack option, 235
CALL statement, 362
Cascading referential integrity constraints, 239-243
CAST operator, 156
CAST statement, 131, 363-364
Catalogs, 61, 242-243
Catalog.xml, 61
CE Edition (SQL Server), 1-9
calculated, 214
security for, 218

Change tracking, 43
Character(s). See also Character sets; Collations
data, storage of, 245-247
data type (char), 251-252
functions, 433
reserved, 53-57

Character sets. See also Characters; Collations
double-byte, 246, 270, 271
linked servers and, 308
single-byte, 246, 248
SQL Server installation and, 26-27

CHECK command, 93
CHECK constraint, 162, 409
collations and, 262
distributed partitioned views and, 224, 225, 231

Check frequency, changing, 305-306
Checkpoints, 88
applications, data mining in, 218
orphaned sessions and, 303

CLOSE CURSOR statement, 448-449
CLR (common language runtime), 522, 524
failover, 37, 44, 228-229
load balanced, 228-229
multi-instance support and, 37

CODEPAGE clause, 271
Coercion rules, 263-265
COLLATE clause, 260, 265-266
Collation(s), 26-27, 255-258
basic description of, 26
changing, considerations before, 262-273
data access methods and, 270-271
enhancements, 43, 245-260
in expressions, 261-262
linked servers and, 308
precedence rules, 263-265
specified at multiple levels, 258-263
suffixes for, 257

Collation dialog box, 261
COLLATIONPROPERTY function, 252, 256
ColPattern, 65-66, 68-69, 71-73
avoiding functions and expressions on, 345-346
collations and, 259-261, 266
computed, indexes on, 402
distributed partitioned views and, 230-231
rules, 230
unique, 406-407

COLUMNPROPERTY function, 177
COM (Component Object Model)
collations and, 270, 271
replication and, 150
SQL-DMO and, 276
Unicode and, 251

COM+ (Component Object Model+), 228, 361
Commands (listed by name)
BUFFER command, 295
BYTES command, 295-296
CHECKALLOC command, 293
CHECKCATALOG command, 30, 294
CHECK command, 78-79
CHECKDB command, 30, 293, 365
CHECKIDENT command, 294
CHECKTABLE command, 293, 363-364
DBINFO command, 296
DBTABLE command, 296
DES command, 296
INDEXDEFRAG command, 79, 80, 87, 294-295
KILL command, 79-80, 94-95, 304, 305
PINTABLE command, 290, 294
pss command, 299-300
Register Custom Task command, 104
SHOWCONFIG command, 79-80, 87
SHOW_STATISTICS command, 342
TAB command, 300
TEXTALL command, 293
TEXTALLOC command, 293

CommandShellImmediate method, 279
CommandText property, 362
Commit processing, two-phase, 423
Compaq, 13, 42
Compatibility, backward, 15
operators, 438-439
scripts, 503

installing/adding, 16, 18-19
rebuilding the registry and, 23

COMPUTE clause, 429-330
Computer Name dialog box, 16, 18-21, 23
Concatenated keys, correct use of, 344
engine enhancements and, 77, 80, 83-84
Oracle 9i and, 414-421

processing, 442
tests, 434-436

Configure SQL XML Support in IIS option, 57
@@CONNECTIONS function, 165
Connections toolbar, 107
Connect to Server dialog box, 17
Connect to SQL Server dialog box, 115-116
adding/removing, 407
cascading referential integrity, 239-243
disabling, 459-461
enabling, 461-462
information, displaying, 302
naming, 406
Oracle 9i and, 405-414

Contents tab, 124
Control Panel, 11, 12
Control-of-flow language, 440-443
Conversion functions, 254-255, 269, 434
CONVERT function, 254-255, 269
CONVERT statement, 131
Copy and Paste Objects Add-in, 219
columns, 108
with the Copy Database Wizard, 201-203
objects, 219
from server to server, 513-516

COUNT_BIG function, 174
adding, 315-316
categories of, 319-320
I/O, 319, 322
memory, 319, 322-323
monitoring, 317-318
network, 319, 323-324
Performance Monitor, 219, 314, 316-318, 319-326
processor, 319, 320-322, 364-365
server, 319, 324-326
SQL Profiler and, 135

COUNT function, 155, 174
@@CPU_BUSY function, 165
CPUs (central processing units). See Processors
Create Dynamic Snapshot Job Wizard, 152
CREATE FUNCTION statement, 41, 161, 165-166
CREATE INDEX statement, 44, 163, 178, 208, 209
CreateProcess function, 289
Create Publication Wizard, 152
Create Pull Subscription Wizard, 152
Create Push Subscription Wizard, 152
CREATE statement, 363
CREATE TABLE statement, 239-240, 260, 395-396
Cube(s), 39, 40
Analysis Services and, 213-215
data mining and, 217-218
default measures for, 215
dimension enhancements and, 215-217
distributed partitioned, 213
elements, hidden, 215
enhancements, 42, 213-215
linked, 214
processing, 214
Service Pack 1 and, 366-367
virtual, 215

Cube Editor, 219, 366
closing, 448-449
data type, 162
declaring, 445-447
fetching data and, 447-448
forward-only, 449-440
implementing, 445
opening, 447
scrollable, 451-452
server, 450-452


Data. See also Data types; Meta data
integrity rules, 405-414
interacting with, 81-84
modification rules, 231
reading, more effectively, 82
transforming, with the DTS data pump, 107-109

Database Components Service Pack 1, 351, 354-355
Database Maintenance Plan Wizard, 152-153
DatabasePropertyEx function, 94
databaseproperty function, 94
DataCenter Server, 77, 96, 284
Data columns
basic description of, 115, 122
DML statements and, 120
selecting, 122-124

Data Columns tab, 122-124
Data Driven Query task, 103, 107, 109
Data File Auto Grow event, 119
Data File Auto Shrink event, 119
Data Guard, 384
Data Link (Microsoft), 107
Data mining
enhancements, 40, 42, 217-218
OLE DB and, 217
task, 104
user interface, 217

Data types. See also Data types (listed by name)
basic description of, 251-255
character sets and, 247
converting values to different, 436, 437
new, 41, 86, 155-160
Oracle and, 381, 384, 403-405, 436, 437
XML and, 67

Data types (listed by name). See also Data types
bigint data type, 41, 67, 86, 155
cursor data type, 162
datetime data type, 253-254
image data type, 155, 162
int data type, 67, 86, 161
nchar data type, 247, 252-253
new table data type, 157-159
ntext data type, 67, 71, 162, 155, 247, 252-253
nvarchar data type, 67, 247, 252-253
smalldatetime data type, 253-254
sql_variant data type, 41, 86, 155-157, 161
table data type, 41
tempdb data type, 86
text data type, 155, 162, 251-252
timestamp data type, 155, 162
varchar data type, 161, 251-252
variant data type, 155
XMLType data type, 384

data types, 253-254
formats, 269
functions, 176, 434

DATEADD function, 176
datetime data type, 253-254
DateTime String task, 108
DBCC (database consistency checks), 46, 120. See also DBCC commands
basic description of, 293-295
engine enhancements and, 77-79, 93-94
MSDE and, 290
running, 30

DBCC commands. See also DBCC (database consistency checks)
BUFFER command, 295
BYTES command, 295-296
CHECKALLOC command, 293
CHECKCATALOG command, 30, 294
CHECK command, 78-79
CHECKDB command, 30, 293, 365
CHECKIDENT command, 294
CHECKTABLE command, 293, 363-364
DBINFO command, 296
DBTABLE command, 296
DES command, 296
INDEXDEFRAG command, 79, 80, 87, 294-295
PINTABLE command, 290, 294
pss command, 299-300
SHOWCONFIG command, 79-80, 87
SHOW_STATISTICS command, 342
TAB command, 300
TEXTALL command, 293
TEXTALLOC command, 293

db-owner role, 391
DBWeb, 12
Deadlocks, 80, 127
Oracle and, 420
SQL Profiler and, 127, 135-136

Debugging. See also Errors
database setup, 335-336
DELETE statement and, 337, 341
hardware, 331-335
options, 235-236
performance issues, 313-349
settings, 326-328
SQL Server installation, 14-15
stopping, 235
stored procedures, 199
T-SQL and, 235-247

DECLARE CURSOR statement, 445-447
DECLARE statement, 157, 164
DEFAULT clause, 162, 232
DEFAULT constraint, 409
DEFAULT keyword, 232
Defragmentation, 30, 79-80, 87, 294-295, 485-486
Degree of Parallelism events, 120
DELETE_RULE column, 242-243
DELETE statement, 151, 165, 240-242
debugging and, 337, 341
distributed partitioned views and, 231-233
indexed views and, 176, 178-179
Oracle and, 427
triggers and, 181, 185

Dependent dimensions, 216
Desktop Edition (SQL Server)
basic description of, 1-9
upgrades and, 17-18

Desktop Engine Service Pack 1, 351-352, 356
Deterministic functions, 176-177
Developer Center (MSDN), 51, 111
Developer Edition (SQL Server)
basic description of, 1-9
indexed views and, 169
upgrades and, 17-18

Development time, reductions in, 38
changing, 216
dependent, 216
enhancements, 215-217
filters, 217
parent-child, 215
ragged, 216
ROLAP, 216
security, 218
write-enabled, 216

Dimension Editor, 219
Dimension Wizard, 366-367
Disable constraints script, 459-461
Disk Keeper, 12
DistinctCount function, 215
Distributed marks, 89
Distributed partition views, 42, 221-226
building, 223-225
designing partitions for, 226-229
how they work, 223
rules for, 233
maximizing the performance of, 226-229
updatable, 229-233

Distributed query enhancements, 45
DLLs (Dynamic Link Libraries)
DBCCs and, 294
Service Pack 1 and, 360

DML (Data Manipulation Language), 30, 120, 347-348
Dollar sign ($), 269
Domain integrity, 409-410
DOS (Disk Operating System), 14
Double quote ("), 54
DRI (declarative referential integrity), 41
Drilldown, 216
Drillthrough, 214, 366
DROP FUNCTION statement, 161
DSNs (data source names), 358
DTS (data transformation services), 42-43, 191, 193. See also DTS Designer; Packages
as an application development platform, 110
custom tasks, 101
data mining tasks, 218
data pump, 107-109
enhancements, 99-112
executing, through T-SQL, 512-513
Import/Export Wizard, 99, 101, 110, 364
MSDE and, 283-285
new features in, 100-102
object model, 100, 102, 110
Oracle and, 454-455
packages, code for, 506-513
replication and, 147, 151
Service Pack 1 and, 362, 365, 367-368

DTS Designer, 99, 101-107, 110. See also DTS (data transformation services)
DTSRun, 99, 357, 365
DTSRUN.EXE, 357, 365
DTSRunUI, 99
Duplicate keys, 401
algorithms, 79, 95
locking, 417-418
tuning, 79, 95

Dynamic Properties task, 103


E-commerce solutions, scalability for, 37
Edge tables, 66, 71
Editions (SQL Server). See also Enterprise Edition (SQL Server)
CE Edition, 1-9
Desktop Edition, 1-9, 17-18
Developer Edition, 1-9, 17-18, 169
MSDE Edition, 1-9
Personal Edition, 1-9, 17-18, 24
Standard Edition, 1-9, 17-18, 24, 37, 95, 285, 332

Editor pane, 200
EFS (Encrypting File System), 387
EmpInternal.xsl, 63, 64
EmpList.xml, 62
Empty tables, space-efficient, 81
Enable constraints script, 461-462
Encryption, 45, 368, 376, 387-388
Engine(s). See also Microsoft Desktop Engine (MSDE)
enhancements, 77-98
generating of events for, 114-115
relational, 78, 81-84, 213-214
storage, 78, 81-86, 93-96

English Query, 36, 333
Enterprise Edition (SQL Server)
basic description of, 1-9
debugging and, 332, 334
engine enhancements and, 79, 85, 95-96
feature sets, 5-9
indexed views and, 169
log shipping and, 152-153
memory and, 45
scalability and, 37
upgrades and, 17-18
using single and multiple instances with, 24

Enterprise Manager
backup/restore operations and, 206
collations and, 258-261, 268
Index Tuning Wizard and, 140
SQL-DMO and, 275, 279
SQL Profiler and, 128
state control and, 94

Equal sign (=), 430
Error(s). See also DBCCs (database consistency checks); Debugging
data transformation, 108-109
DTS and, 101
fixes, in the Service Pack 1, 356-358, 362-368
logging, 14, 108-109
raising, 444-445
replication and, 150
SQL Profiler and, 134
SQL Server installation and, 10-14
timeout, 366-367
trapping, 328
triggers and, 187
user-defined functions and, 162

basic description of, 114
categories of, 114, 119-120
classes, 115
limiting the number of collected, with filters, 125-127
logs, 328
selecting, for traces, 118-122

Events tab, 118-122
Event Viewer, 328-330, 367
Excel (Microsoft)
DTS and, 99, 100, 107
SQL Profiler and, 118

Exchange (Microsoft), 13, 45
Exchange Server (Microsoft), 12
Exclamation point (!), 245
ExecuteImmediate method, 278-279
Execute Package task, 104
Execute Process task, 104
Execute SQL task, 101, 103
EXECUTE statement, 165, 362
Execution Plan event, 119
Existing Installation dialog box, 16, 18-20
EXPAND VIEWS option, 173
EXPLICIT mode, 50, 58-61
ExportPackages.vbs script, 508-510
avoiding, on indexed columns, 345-346
collations in, 261-262

Extensible Markup Language (XML), 35-36, 47-76
defining tables and, 158-159
encoding enhancements, 196
-formatted data, retrieving, 51-53
Meta Data Services and, 191, 194, 196
nodes and rowset columns, mapping between, 72-73
parsers, 72
reduced development time and, 38
reserved characters and, 53-57
Service Pack 1 and, 361
SQL Server.NET and, 520, 522-525
table data type and, 158
Technology preview, 50
templates and, 51-52, 57-58, 62, 63
Unicode and, 251
View, 39-40
XSL and, 62-64


Failback, 44
Failover clustering, 37, 44, 228-229
FAT (file allocation table), 218
Fault tolerance, 88
Feature sets, comparison of, 5
Federated database tier, 229
FETCH statement, 165, 447-448
creating, for unattended installations, 21
-groups, 96-97
locations, for multiple instances, 25-26
paths, specifying, 25
storage of, 96-97

File dialog box, 201
File-rollover option, 117
Filters, 125-127, 217
basic description of, 114
replication and, 146, 149
user-defined functions and, 163

Filters tab, 125-127
Firewalls, 39
flags parameter, 65, 66, 69, 72
Flashback Query, 384
Flow control language, 440-443
fn_helpcollations function, 256, 266
fn_trace_gentinfo function, 133-134
fn_trace_getfilterinfo function, 134
fn_trace_gettable function, 118, 134
FOR XML clause, 36, 38-39, 50
AUTO mode, 50, 52
EXPLICIT mode, 50, 58-61
RAW mode, 50

FOREIGN KEY constraint, 242
Foreign keys
cascading referential integrity constraints and, 240-243
joins and, 344
Oracle and, 410

Foreign languages. See Characters; Collations
custom, 216
member, 216
rollup, 216

Forward slash (/), 54
FROM clause, 41, 134, 164, 310
distributed partitioned views and, 230
indexed views and, 169, 172

FTP (File Transfer Protocol), 39, 42
DTS and, 100-101, 104
Service Pack 1 and, 362
SQL Server installation and, 12

Full table scans, 346-347
Full-text searches, 35, 39, 43
Function(s). See also Functions (listed by name); User-defined functions
avoiding, on indexed columns, 345-346
binding, to schema, 165-166
built-in, 161, 165
character, 433
conversion, 254-255, 269, 434
date, 434
deterministic, 176-177
group, 348-349
number/mathematical, 432
user-defined, 41, 436-438

adding, while upgrading, 17
Analysis Services and, 213, 215, 218
backup, 91-92
cluster, 38
OLAP cubes and, 213, 215
XML and, 51

Functions (listed by name). See also Functions
AVG function, 174, 348
BIG_COUNT function, 155
COLLATIONPROPERTY function, 252, 256
COLUMNPROPERTY function, 177
@@CONNECTIONS function, 165
CONVERT function, 254-255, 269
COUNT_BIG function, 174
COUNT function, 155, 174
@@CPU_BUSY function, 165
CreateProcess function, 289
DatabasePropertyEx function, 94
databaseproperty function, 94
DATEADD function, 176
DistinctCount function, 215
fn_helpcollations function, 256, 266
fn_trace_gentinfo function, 133-134
fn_trace_getfilterinfo function, 134
fn_trace_gettable function, 118, 134
@@GETDATE function, 165
GETDATE function, 176, 303
@@GETUTCDate function, 165
Greatest function, 466
@@IDLE function, 165
InternetReadFile function, 368
@@IO_BUSY function, 165
@@MAX_CONNECTIONS function, 165
MAX function, 348
MIN function, 348
@@NEWID function, 165
NEWID function, 363-364
OBJECT PROPERTY function, 242
OPENDATASOURCE function, 45, 229
OPENQUERY function, 309-310
OPENROWSET function, 229, 310
@@PACKET_ERRORS function, 165
@@PACK_RECEIVED function, 165
@@PACK_SENT function, 165
@@RAND function, 165
ROW_COUNT function, 155
SERVERPROPERTY function, 258, 471-473
SetLocale function, 267
SQLExecDirect function, 360, 361
SQLPrepare function, 361
SUM function, 174
@@TEXTPTR function, 165
@@TIMETICKS function, 165
@@TOTAL_ERRORS function, 165
@@TOTAL_READ function, 165
@@TOTAL_WRITE function, 165


General tab, 116
@@GETDATE function, 165
GETDATE function, 176, 303
@@GETUTCDate function, 165
Go option, 235
GOTO statement, 443
Greatest function, 466
Gregorian calendar, 253
GROUP BY clause, 176-177, 178, 338
GROUP BY list, 172
file-, 96-97
Oracle and, 388-389

Guest accounts, 389-390
GUIDs (globally unique identifiers), 50
GUIs (graphical user interfaces), 2, 314


Hard disk(s). See also Hardware
MSDE and, 284
requirements, 5

debugging, 331-335
requirements, 3-9

HAVING clause, 348-349
High availability, designing for, 228-229
HKEY_LOCAL_MACHINE key, 10, 14, 15, 305
HTML (HyperText Markup Language)
derivation of, from SGML, 49
DTS and, 107

HTTP (HyperText Transfer Protocol), 36, 214, 218
collations and, 267
direct queries via, 39
locations, posting data to, 39
retriving XML-formatted data from, 51-52
Service Pack 1 and, 368
SQL Server.NET and, 520, 524

HTTPS (HyperText Transfer Protocol, Secure), 368


Identity columns, 428-429
IDENTITY property, 338, 363
@@IDLE function, 165
Idoc, 65, 70
IIS (Microsoft Internet Information Server), 12-13, 39, 51, 57
image data type, 155, 162
Image filtering, 43
Impersonation, 45
Import/Export Wizard, 99, 101, 110, 364
IN clause, 364
Index(es), 149, 336-341. See also Indexed views; Index Tuning Wizard
building, 86-87
choosing, 341
clustered, 337-339
on computed columns, 402
creating, 336-337
defragmenting, 87
descending order key columns on, 79
effective use of, 344
enhancements, 44, 79-80, 85-87
keys, short, 339-340
naming, 400
online reorganization of, 80
Oracle and, 398-402
read-ahead, 79
reorganization, 79
selectivity of, 341-342, 344-345
Service Pack 1 and, 363-364
space-efficient, 81
storage parameters for, 396-397, 401
syntax for, 400

Indexed views
aggregations and, 214
base table requirements for, 177
basic description of, 169-179
creating, 175-178
design considerations for, 173-175
deterministic functions and, 176-177
engine enhancements and, 85
function requirements for, 177
maintaining, 178-179
OLAP cubes and, 214
Oracle and, 398-402
performance gains from, 170-171
replication and, 150
view requirements for, 177

Index Recommendations dialog box, 138-139
Index Tuning Wizard, 136-144, 173
arguments, 141-143
executing, with itwiz, 140-144
output, analyzing, 138-139
report options, 138-139
starting, 140

Index Usage Report, 139
inequality operator, 346
INFO clause, 165
models, 194-195
schema views, 301-302

.ini file format, 23
Initialization, 20-23
Input/output (I/O)
asynchronous, 97
controllers, 333
debugging and, 319, 322, 331, 333, 339-340
engine enhancements and, 80, 83, 85-86, 96-97
memory pools and, 96
orphaned sessions and, 306
performance counters, 319, 322
read-ahead, optimization of, 80
reducing the amount of, 83, 96
shared scans and, 83

INSERT statement, 151, 165
debugging and, 337, 341
distributed partitioned views and, 231-232
indexed views and, 176, 178-179
Oracle and, 420-421, 425-426
triggers and, 181, 185

Insert Template dialog box, 200
Installation. See also Setup
checklists, 27-31
debugging, 14-15
MSDE, 286-289
multi-instance support and, 37-38
re-, 15
rebuilding the registry during, 15
SQL Server, 9-23
un-, 14-15, 20
unattended, 20-22
using SMS, 22-23

Installation Selection dialog box, 16, 18-21, 23
Installation Wizard, 16, 18-21, 23
Install Database Server option, 16, 18-21
default, 16, 24-25
file locations for, 25-26
maximum number of supported, 24
multiple, 23-26, 202, 285
names, 16, 18-20, 23-25

Instance Name dialog box, 16, 18-20, 23-24
INSTEAD OF triggers, 43, 181-189, 233, 240-242
int data type, 67, 86, 161
Intel, 37
Internationalization. See Collations
International Standards Organization (ISO), 246, 249, 267
accessing data via, 39-40
generating XML over, 51

Internet Explorer browser (Microsoft)
orphaned sessions and, 305
reserved characters and, 53-54
SQL Server installation and, 9-12
Unicode and, 250
XML and, 52-56, 61-64

InternetReadFile function, 368
@@IO_BUSY function, 165
IP (Internet Protocol), 11
IsDeterministic property, 177
ISO (International Standards Organization), 246, 249, 267
IsPrecise property, 177
.iss files, 22
itwiz utility, 140-144


Jacobson, Reed, 219
Java, 522
Jet (Microsoft), 107, 284
joins, 170, 172, 344, 430
Jscript (Microsoft), 104
JVM (Java Virtual Machine), 522


KeepAliveTime for TCP/IP, 306
Kerberos, 45
KILL command, 79-80, 94-95, 304, 305
KIPD (kernel process ID), 326
Knight, Brian, 42


Languages, foreign. See Characters; Collations
Latches, 84
LCIDs (local IDs), 266-267
Linked server(s)
basic description of, 306-307
creating, 307-308
creating logins for, 309
options, setting, 308
querying, 309-311

Little Endian encoding system, 250
Load balanced clustering, 228-229
Local computers
adding components to, 19
uninstalling SQL Server from, 20
upgrades on, 16, 18

Local Computer option, 16, 19
Analysis Services and, 219
behavior, changing default, 418-420
concurrency and, 83-84
dynamic, 417-418
engine enhancements and, 80-81, 84
Oracle and, 414-421, 429
orphaned sessions and, 304-305
requested rows, 429
transaction isolation and, 416-417

Lock manager, 80, 84
Lock Monitor, 364-365
Log(s). See also Transaction logs
application, 329-330
debugging and, 335, 328-330
DTS and, 101
error, 14, 108-109
event, 328
marks, logical, 80, 88-89
monitoring, with the Event Viewer, 328-330
package, 101
security, 329-330
shipping, 152-153
shrinking, 81
system, 329-330

Log File Auto Grow event, 119
Log File Auto Shrink event, 119
Log Files tab, 316
Logical log marks, 80, 88-89
Login, 10, 45, 309. See also Authentication
LoginMode, 10
Loopback adapters, 11
Oracle and, 442-443
SQL-DMO and, 279, 282

Lowercase String task, 108


Macintosh, 2
makepipe utility, 11
Management group, 140
Materialized views, 398-402. See also Indexed views
@@MAX_CONNECTIONS function, 165
MAX function, 348
MDAC (Microsoft Data Access Components), 10, 23, 107, 270, 354, 357-362
MDC (Meta Data Coalition), 196
MDX (multidimensional expressions), 42, 214, 218-219
default, 217
grouping, automatic, 216

Memory. See also RAM (random-access memory)
bottlenecks, 334
concurrency and, 83
DBCCs and, 294
debugging and, 319, 322-323, 327, 334
leaks, 357, 360, 365
locking, 80
MSDE and, 284
Oracle features for, 379
performance counters, 319, 322-323
pool, 96
requirements, 3-4, 284
SQL Profiler and, 116
table data type and, 157
Unicode and, 250

Merant/Intersolve, 2
Merge replication
basic description of, 146-150
implementing, 148-149
optimizing synchronization with, 148
updatable subscriptions and, 147

Meta data
fundamentals, 191-192
management, 193-194
multiple purposes of, 193
SQL-DMO and, 275

Meta Data Browser, 196
Meta Data Services, 109


Submit Errata

More Information

InformIT Promotional Mailings & Special Offers

I would like to receive exclusive offers and hear about products from InformIT and its family of brands. I can unsubscribe at any time.


Pearson Education, Inc., 221 River Street, Hoboken, New Jersey 07030, (Pearson) presents this site to provide information about products and services that can be purchased through this site.

This privacy notice provides an overview of our commitment to privacy and describes how we collect, protect, use and share personal information collected through this site. Please note that other Pearson websites and online products and services have their own separate privacy policies.

Collection and Use of Information

To conduct business and deliver products and services, Pearson collects and uses personal information in several ways in connection with this site, including:

Questions and Inquiries

For inquiries and questions, we collect the inquiry or question, together with name, contact details (email address, phone number and mailing address) and any other additional information voluntarily submitted to us through a Contact Us form or an email. We use this information to address the inquiry and respond to the question.

Online Store

For orders and purchases placed through our online store on this site, we collect order details, name, institution name and address (if applicable), email address, phone number, shipping and billing addresses, credit/debit card information, shipping options and any instructions. We use this information to complete transactions, fulfill orders, communicate with individuals placing orders or visiting the online store, and for related purposes.


Pearson may offer opportunities to provide feedback or participate in surveys, including surveys evaluating Pearson products, services or sites. Participation is voluntary. Pearson collects information requested in the survey questions and uses the information to evaluate, support, maintain and improve products, services or sites, develop new products and services, conduct educational research and for other purposes specified in the survey.

Contests and Drawings

Occasionally, we may sponsor a contest or drawing. Participation is optional. Pearson collects name, contact information and other information specified on the entry form for the contest or drawing to conduct the contest or drawing. Pearson may collect additional personal information from the winners of a contest or drawing in order to award the prize and for tax reporting purposes, as required by law.


If you have elected to receive email newsletters or promotional mailings and special offers but want to unsubscribe, simply email information@informit.com.

Service Announcements

On rare occasions it is necessary to send out a strictly service related announcement. For instance, if our service is temporarily suspended for maintenance we might send users an email. Generally, users may not opt-out of these communications, though they can deactivate their account information. However, these communications are not promotional in nature.

Customer Service

We communicate with users on a regular basis to provide requested services and in regard to issues relating to their account we reply via email or phone in accordance with the users' wishes when a user submits their information through our Contact Us form.

Other Collection and Use of Information

Application and System Logs

Pearson automatically collects log data to help ensure the delivery, availability and security of this site. Log data may include technical information about how a user or visitor connected to this site, such as browser type, type of computer/device, operating system, internet service provider and IP address. We use this information for support purposes and to monitor the health of the site, identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents and appropriately scale computing resources.

Web Analytics

Pearson may use third party web trend analytical services, including Google Analytics, to collect visitor information, such as IP addresses, browser types, referring pages, pages visited and time spent on a particular site. While these analytical services collect and report information on an anonymous basis, they may use cookies to gather web trend information. The information gathered may enable Pearson (but not the third party web trend services) to link information with application and system log data. Pearson uses this information for system administration and to identify problems, improve service, detect unauthorized access and fraudulent activity, prevent and respond to security incidents, appropriately scale computing resources and otherwise support and deliver this site and its services.

Cookies and Related Technologies

This site uses cookies and similar technologies to personalize content, measure traffic patterns, control security, track use and access of information on this site, and provide interest-based messages and advertising. Users can manage and block the use of cookies through their browser. Disabling or blocking certain cookies may limit the functionality of this site.

Do Not Track

This site currently does not respond to Do Not Track signals.


Pearson uses appropriate physical, administrative and technical security measures to protect personal information from unauthorized access, use and disclosure.


This site is not directed to children under the age of 13.


Pearson may send or direct marketing communications to users, provided that

  • Pearson will not use personal information collected or processed as a K-12 school service provider for the purpose of directed or targeted advertising.
  • Such marketing is consistent with applicable law and Pearson's legal obligations.
  • Pearson will not knowingly direct or send marketing communications to an individual who has expressed a preference not to receive marketing.
  • Where required by applicable law, express or implied consent to marketing exists and has not been withdrawn.

Pearson may provide personal information to a third party service provider on a restricted basis to provide marketing solely on behalf of Pearson or an affiliate or customer for whom Pearson is a service provider. Marketing preferences may be changed at any time.

Correcting/Updating Personal Information

If a user's personally identifiable information changes (such as your postal address or email address), we provide a way to correct or update that user's personal data provided to us. This can be done on the Account page. If a user no longer desires our service and desires to delete his or her account, please contact us at customer-service@informit.com and we will process the deletion of a user's account.


Users can always make an informed choice as to whether they should proceed with certain services offered by InformIT. If you choose to remove yourself from our mailing list(s) simply visit the following page and uncheck any communication you no longer want to receive: www.informit.com/u.aspx.

Sale of Personal Information

Pearson does not rent or sell personal information in exchange for any payment of money.

While Pearson does not sell personal information, as defined in Nevada law, Nevada residents may email a request for no sale of their personal information to NevadaDesignatedRequest@pearson.com.

Supplemental Privacy Statement for California Residents

California residents should read our Supplemental privacy statement for California residents in conjunction with this Privacy Notice. The Supplemental privacy statement for California residents explains Pearson's commitment to comply with California law and applies to personal information of California residents collected in connection with this site and the Services.

Sharing and Disclosure

Pearson may disclose personal information, as follows:

  • As required by law.
  • With the consent of the individual (or their parent, if the individual is a minor)
  • In response to a subpoena, court order or legal process, to the extent permitted or required by law
  • To protect the security and safety of individuals, data, assets and systems, consistent with applicable law
  • In connection the sale, joint venture or other transfer of some or all of its company or assets, subject to the provisions of this Privacy Notice
  • To investigate or address actual or suspected fraud or other illegal activities
  • To exercise its legal rights, including enforcement of the Terms of Use for this site or another contract
  • To affiliated Pearson companies and other companies and organizations who perform work for Pearson and are obligated to protect the privacy of personal information consistent with this Privacy Notice
  • To a school, organization, company or government agency, where Pearson collects or processes the personal information in a school setting or on behalf of such organization, company or government agency.


This web site contains links to other sites. Please be aware that we are not responsible for the privacy practices of such other sites. We encourage our users to be aware when they leave our site and to read the privacy statements of each and every web site that collects Personal Information. This privacy statement applies solely to information collected by this web site.

Requests and Contact

Please contact us about this Privacy Notice or if you have any requests or questions relating to the privacy of your personal information.

Changes to this Privacy Notice

We may revise this Privacy Notice through an updated posting. We will identify the effective date of the revision in the posting. Often, updates are made to provide greater clarity or to comply with changes in regulatory requirements. If the updates involve material changes to the collection, protection, use or disclosure of Personal Information, Pearson will provide notice of the change through a conspicuous notice on this site or other appropriate way. Continued use of the site after the effective date of a posted revision evidences acceptance. Please contact us if you have questions or concerns about the Privacy Notice or any objection to any revisions.

Last Update: November 17, 2020