Register your product to gain access to bonus material or receive a coupon.
Microsoft SQL Server 2000: A Guide to Enhancements and New Features
- By Rahul Sharma
- Published Mar 19, 2002 by Addison-Wesley Professional.
Book
- Sorry, this book is no longer in print.
Description
- 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.
0201752832B02212002
Extras
Related Articles
Application Performance Tuning
Intermediate MDX Concepts, Part 1
Intermediate MDX Concepts, Part 2
Introduction to Database Replication
Introduction to MDX: Basic Concepts and Navigation
Key MDX Components: Building Calculated Members, Part 1
Key MDX Components: Building Calculated Members, Part 2
Key MDX Components: Drilling Through to Details
Key MDX Components: Working with Calculated Cells
Monitoring and Troubleshooting Transactional Replication
Setting Up Transactional Replication with SQL Server
SQL Server Tuning: Database Maintenance
SQL Server: Optimizing Database Performance Through Indexes
SQL Server: Tuning Database Design
Tuning MDX: Improving Performance with Caches
Sample Content
Table of Contents
(NOTE: Each chapter concludes with a Summary.)
Preface.
Acknowledgments.
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.
I. NEW FEATURES AND IMPROVEMENTS OVER SQL SERVER 7.0.
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.
DTS.
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.
OPENXML.
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.
Examples.
4. Engine Enhancements in SQL Server 2000.The Enhancements.
Interacting with Data.
Reading Data More Effectively.
Top n Enhanced.
Shared Scans.
Concurrency.
Tables and Indexes.
In-Row Text.
New Data Types.
Indexes.
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.
General.
Events.
Data Columns.
Filters.
Templates.
Defining Your Own Trace Using Stored Procedures.
Defining a Server-Side Trace.
Known Bug in SQL Server 2000 Profiler.
Symptoms.
Cause.
Resolution.
Index Tuning Wizard.
Analyzing Index Tuning Wizard Output.
Starting the Index Tuning Wizard.
Itwiz.exe.
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.Bigint.
sql_variant.
Table.
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.
Templates.
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.
Capabilities.
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?
UCS-2.
UTF-16.
UTF-8.
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.
II. ADVANCED DBA TOPICS.
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.
Settings.
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.
III. COMPARISON WITH ORACLE 9I.
25. Oracle 9i vs. SQL Server 2000.Database Definition.
Database System Catalogs.
Physical and Logical Storage Structures.
Striping Data.
Transaction Logs and Automatic Recovery.
Networks.
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.
Views.
Indexed Views/Materialized Views.
Indexes.
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.
Triggers.
Transactions, Locking, and Concurrency.
Transactions.
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.
TRUNCATE TABLE Statement.
Manipulating Data in Identity and Timestamp Columns.
Locking Requested Rows.
Row Aggregates and the Compute Clause.
Join Clauses.
Reading and Modifying BLOBs.
Functions.
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.
CURRENT OF Clause.
Closing a Cursor.
Developing and Administering Database Replication.
Moving Data and Applications.
IV. SCRIPTS AND UTILITIES.
27. Administration and Utility Scripts.Scripts.
Disable Constraints.
Enable Constraints.
USP_Find_Lock.
USP_Lock_Info.
Greatest.
Update Statistics.
USP_Active.
Objects.
Number_Of_Rows.
USP_Update_One_Table.
USP_System_Info.
USP_Null_Columns.
USP_SetUP_Paths.
USP_Identity_Columns.
USP_DB_File_Info.
USP_NOIndexes.
USP_RenameDB.
USP_ForceDropDB.
USP_Trace_BlackBox.
Registry_Info.
USP_KillConnections.
USP_FK_PK.
USP_DefragDatabase.
USP_DBUpdate.
USP_Calc_Space.
BackUp.vbs.
USP_PingServers.
Schema Comparison Scripts.
USP_MonitorServices.
USP_DTSReplaceOwner.
DTS Packages.
Refresh Databases.
Back Up Databases and Version Control.
ScriptData.
ExportPackages.vbs.
ImportPackages.vbs.
Executing DTS Through T-SQL.
Copying Databases from Server to Server.
Method 1.
Method 2.
Method 3.
Method 4.
Method 5.
V. SQL SERVER.NET.
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.
Appendix.Index. 0201752832T03082002
Preface
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 BookThe 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."
0201752832P03112002
Index
& (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
A
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
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
Administration
features for, improvements to, 93-95
Administrative Tools program group, 328
ADO (Microsoft ActiveX Data Objects), 102, 519, 521. See also ActiveX controls (Microsoft)
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
Alerts
event logs and, 328
settings for, 318
trapping errors for, 328
Algorithms
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
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
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)
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
ATTRIB.EXE, 13
At sign (@), 245
Audits, 113
Authentication. See also Login; Security
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
B
Backup(s), 12-13, 30-31. See also Recovery
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
BEGIN TRANSCACTION statement, 89
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
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
Cells
security for, 218
Change tracking, 43
Character(s). See also Character sets; Collations
data type (char), 251-252
functions, 433
reserved, 53-57
Character sets. See also Characters; Collations
linked servers and, 308
single-byte, 246, 248
SQL Server installation and, 26-27
CHECK command, 93
CHECK constraint, 162, 409
distributed partitioned views and, 224, 225, 231
Check frequency, changing, 305-306
Checkpoints, 88
Client(s)
orphaned sessions and, 303
CLOSE CURSOR statement, 448-449
CLR (common language runtime), 522, 524
Clustering
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
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
Column(s)
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)
replication and, 150
SQL-DMO and, 276
Unicode and, 251
COM+ (Component Object Model+), 228, 361
Commands (listed by name)
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
SHRINKDATABASE command, 30
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
Comparison
scripts, 503
Components
rebuilding the registry and, 23
COMPUTE clause, 429-330
Computer Name dialog box, 16, 18-21, 23
Concatenated keys, correct use of, 344
Concurrency
Oracle 9i and, 414-421
Conditional
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
Constraint(s)
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
Copying
with the Copy Database Wizard, 201-203
objects, 219
from server to server, 513-516
COUNT_BIG function, 174
Counter(s)
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
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
Cursor(s)
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
D
Data. See also Data types; Meta data
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
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
OLE DB and, 217
task, 104
user interface, 217
Data types. See also Data types (listed by name)
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
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
Date(s)
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
engine enhancements and, 77-79, 93-94
MSDE and, 290
running, 30
DBCC commands. See also DBCC (database consistency checks)
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
SHRINKDATABASE command, 30
TAB command, 300
TEXTALL command, 293
TEXTALLOC command, 293
db-owner role, 391
DBWeb, 12
Deadlocks, 80, 127
SQL Profiler and, 127, 135-136
Debugging. See also Errors
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
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)
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)
indexed views and, 169
upgrades and, 17-18
Development time, reductions in, 38
Dimension(s)
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
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)
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
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
Dynamic
locking, 417-418
tuning, 79, 95
Dynamic Properties task, 103
E
E-commerce solutions, scalability for, 37
Edge tables, 66, 71
Editions (SQL Server). See also Enterprise Edition (SQL Server)
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)
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)
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
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
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
Event(s)
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)
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
Expressions
collations in, 261-262
Extensible Markup Language (XML), 35-36, 47-76
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
F
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
File(s)
-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
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
EXPLICIT mode, 50, 58-61
RAW mode, 50
FOREIGN KEY constraint, 242
Foreign keys
joins and, 344
Oracle and, 410
Foreign languages. See Characters; Collations
Formulas
member, 216
rollup, 216
Forward slash (/), 54
FROM clause, 41, 134, 164, 310
indexed views and, 169, 172
FTP (File Transfer Protocol), 39, 42
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
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
Functionality
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
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
G
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
Groups
Oracle and, 388-389
Guest accounts, 389-390
GUIDs (globally unique identifiers), 50
GUIs (graphical user interfaces), 2, 314
H
Hard disk(s). See also Hardware
requirements, 5
Hardware
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)
DTS and, 107
HTTP (HyperText Transfer Protocol), 36, 214, 218
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
HTTP_ACCEPT_LANGUAGE variable, 267
HTTPS (HyperText Transfer Protocol, Secure), 368
I
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
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
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
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
Information
schema views, 301-302
.ini file format, 23
Initialization, 20-23
Input/output (I/O)
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
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
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
Instance(s)
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
Internet
generating XML over, 51
Internet Explorer browser (Microsoft)
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
J
Jacobson, Reed, 219
Java, 522
Jet (Microsoft), 107, 284
joins, 170, 172, 344, 430
Jscript (Microsoft), 104
JVM (Java Virtual Machine), 522
K
KeepAliveTime for TCP/IP, 306
Kerberos, 45
KILL command, 79-80, 94-95, 304, 305
KIPD (kernel process ID), 326
Knight, Brian, 42
L
Languages, foreign. See Characters; Collations
Latches, 84
LCIDs (local IDs), 266-267
Linked server(s)
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
uninstalling SQL Server from, 20
upgrades on, 16, 18
Local Computer option, 16, 19
Locking
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
LOCK_TIMEOUT, 365-366
Log(s). See also Transaction logs
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
Looping
SQL-DMO and, 279, 282
Lowercase String task, 108
M
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
Member(s)
grouping, automatic, 216
Memory. See also RAM (random-access memory)
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
implementing, 148-149
optimizing synchronization with, 148
updatable subscriptions and, 147
Meta data
management, 193-194
multiple purposes of, 193
SQL-DMO and, 275
Meta Data Browser, 196
Meta Data Services, 109