Designing SQL Server 2000 Databases for .Net Enter

Travis Laird, Syngress

ISBN:1928994199, Edition: 1, 2001-01-15

Price: $49.95

Contents

Introduction ~ xxvii

Chapter 1 SQL Server 2000 Overview and Migration Strategies ~ 1
Introduction ~ 2
Overview of SQL Server 2000: A .NET Enterprise Server ~ 3
The Future of Windows DNA: Microsoft.NET ~ 4
New and Enhanced Features of SQL Server 2000 ~ 8
XML Support ~ 10
Development Tools and Technologies ~ 14
Query Analyzer ~ 14
New Data Types ~ 15
Indexed Views ~ 16
Trigger Enhancements ~ 16
Referential Integrity Enhancements ~ 17
User-Defined Functions ~ 18
Index Enhancements ~ 18
Shared Session Information ~ 19
Collation Support ~ 19
Extended Properties ~ 20
Meta Data Services ~ 20
Analysis Services ~ 20
OLAP Enhancements ~ 21
Data Mining ~ 22
SQL Server Administration ~ 23
Windows 2000 Active Directory Integration ~ 24
Scalability and Availability ~ 24
Scalability Enhancements ~ 25
Distributed Partitioned Views ~ 26
Fail-Over Clustering ~ 26
Log Shipping ~ 27
Data Transformation Services ~ 27
Replication Services ~ 28
Active Directory Integration ~ 29
Queued Updating Subscribers ~ 29
Multiple Server Instance Support ~ 29
Web and Internet Standards Support ~ 31
Web Access Using Hypertext Transfer Protocol ~ 31
Secure Sockets Layer ~ 31
SQL Server 2000 Versions, Features, and Requirements ~ 32
Common Edition Requirements ~ 33
SQL Server Licensing and Pricing ~ 34
Enterprise Edition ~ 35
Hardware Requirements and Capacity Limits ~ 35
Operating System Compatibility ~ 36
Standard Edition ~ 36
Hardware Requirements ~ 36
Software and Operating System Compatibility ~ 37
Personal Edition ~ 37
Hardware Requirements ~ 37
Software and Operating System Compatibility ~ 38
Developer Edition ~ 38
SQL Server 2000 Desktop Engine ~ 39
SQL Server 2000 Windows CE Edition ~ 39
Should You Migrate to SQL Server 2000? ~ 41
How Will SQL Server 2000 Benefit My Organization? ~ 42
Will SQL Server 2000 Fit into My Organization? ~ 43
Steps to a Successful SQL Server Migration ~ 43
Planning a SQL Server Migration ~ 44
Determine Existing and Future Application Requirements ~ 45
Inventory Existing Database Servers ~ 45
Train Database Administrators and Support Personnel ~ 45
Next Steps to Successful SQL Server Migration ~ 46
Migrating to SQL Server 2000 ~ 46
Upgrading from SQL Server 6.5: Active/Passive Mode ~ 49
Upgrading from SQL Server 6.5: Active/Active Mode ~ 50
Upgrading from SQL Server 7.0: Active/Passive Mode ~ 50
Upgrading from SQL Server 7.0: Active/Active Mode ~ 50
Summary ~ 52
FAQs ~ 54

Chapter 2 Installing and Configuring SQL Server 2000 ~ 57
Introduction ~ 58
Planning a SQL Server Installation ~ 59
Installation Requirements ~ 60
Hardware Requirements ~ 60
Software Requirements ~ 61
SQL Server Licensing ~ 62
Installation Options ~ 63
Local vs. Remote Installation ~ 63
Creating Service Accounts for SQL Server ~ 65
Changing User Accounts ~ 66
Disk Imaging Support ~ 67
Answer File for Automated Installations ~ 67
Multiple Server Instances ~ 68
Collation Options ~ 68
Upgrading to SQL Server 2000 ~ 69
Installing SQL Server ~ 71
Standard Installation ~ 71
Advanced Installation ~ 75
Configuring Cluster Support ~ 75
Unattended Installation ~ 76
Configuration Options and Settings ~ 76
SQL Server Properties ~ 76
Server Network Utility ~ 86
Client Network Utility ~ 87
SQL Server Agent ~ 89
SQL Mail ~ 90
Summary ~ 92
FAQs ~ 92

Chapter 3 SQL Server Scalability and Availability ~ 93
Introduction ~ 94
Scaling Up vs. Scaling Out ~ 94
TPC Benchmarks ~ 97
SQL Server Fail-Over Clustering ~ 98
SQL 2000 Fail-Over Clustering Architecture ~ 99
Planning for SQL Server Clustering ~ 99
Clustering Capabilities and Requirements ~ 100
Microsoft Cluster Service ~ 101
System Area Networks ~ 101
Shared Disks ~ 102
Log Shipping ~ 102
Implementing Fail-Over Clustering ~ 102
Setting Up Network Adapters ~ 103
Setting Up Shared Disks ~ 103
Setting Up MSCS on NT 4.0 ~ 104
Setting Up MSCS on Windows 2000 (Advanced Server/Datacenter Server) ~ 104
Upgrading to SQL Fail-Over Clustering ~ 105
Setting Up SQL Server 2000 Fail-Over Clustering ~ 108
Distributed Partitioned Views ~ 109
Federated Servers ~ 109
Data Partitioning ~ 111
Creating Distributed Partitioned Views ~ 111
Creating Linked Servers ~ 111
Partitioning Your Data ~ 112
Creating a Distributed View ~ 112
Using and Updating a Distributed View ~ 113
Log Shipping ~ 115
Setting Up Log Shipping ~ 116
Monitoring Log Shipping ~ 120
Indexed Views ~ 121
Requirements for an Indexed View ~ 122
Creating an Indexed View ~ 124
Summary ~ 124
FAQs ~ 125

Chapter 4 Designing and Creating SQL Server Databases ~ 127
Introduction ~ 128
SQL Server 2000 Architecture ~ 128
Relational Databases ~ 129
SQL Server System Databases ~ 131
Master ~ 132
TempDB ~ 132
msdb ~ 133
Model ~ 134
Pubs ~ 134
Northwind ~ 135
Physical Storage Architecture ~ 135
Filegroups ~ 136
Data Files ~ 137
Transaction Logs ~ 137
Indexes ~ 138
SQL Server Services ~ 139
SQL Server Service ~ 139
SQL Server Agent Service ~ 142
Microsoft Distributed Transaction Coordinator Service ~ 142
Microsoft Search Service ~ 143
MSSQLServerADHelper Service ~ 143
MSSQLServerOLAPService ~ 143
Creating SQL Server Databases ~ 144
Designing Your Database Solution ~ 144
Database Modeling ~ 145
Designing the Physical Database ~ 146
The Disk Subsystem ~ 146
Capacity and Growth Planning ~ 148
Creating and Configuring Your Database ~ 151
Getting Started ~ 151
Using the Create Database Wizard ~ 153
Create Database Wizard: Name the Database and Specify Its Location ~ 155
Create Database Wizard: Name the Database Files ~ 155
Create Database Wizard: Define the Database File Growth ~ 156
Create Database Wizard: Name the Transaction Log Files ~ 156
Create Database Wizard: Define the Transaction Log File Growth ~ 157
Create Database Wizard: Completing the Create Database Wizard ~ 158
Configuring Your Database ~ 158
Southwind Properties | Filegroups ~ 161
Southwind Properties | Data Files ~ 162
Southwind Properties | Transaction Log ~ 164
Southwind Properties | Options ~ 164
Southwind Properties | Permissions ~ 168
Reviewing the Southwind Configuration ~ 168
Using T-SQL to Create and Alter a Database ~ 169
Monitoring and Maintenance ~ 171
Database Maintenance Plan Wizard ~ 171
Maintenance Plan Wizard: Select Databases ~ 172
Maintenance Plan Wizard: Update Data Optimization Information ~ 172
Maintenance Plan Wizard: Database Integrity Check ~ 174
Maintenance Plan Wizard: Specify the Database Backup Plan ~ 175
Maintenance Plan Wizard: Specify the Backup Disk Directory ~ 175
Maintenance Plan Wizard: Specify the Transaction Log Backup Plan ~ 176
Maintenance Plan Wizard: Specify Transaction Log Backup Disk Directory ~ 177
Maintenance Plan Wizard: Reports to Generate ~ 177
Maintenance Plan Wizard: Maintenance Plan History ~ 178
Maintenance Plan Wizard: Completing Your Maintenance Plan ~ 178
Database Modeling Tools ~ 180
Entity-Relationship Diagrams ~ 180
SQL Server Database Designer ~ 184
Summary ~ 186
FAQs ~ 188

Chapter 5 Database and Server Security ~ 189
Introduction ~ 190
Planning SQL Server Security ~ 190
Understanding SQL Server Security ~ 191
C2 Certification ~ 193
Administration Access and Server Security ~ 194
Configuring the SQL Server 2000 Service Accounts ~ 194
Securing the SQL Server 2000 Executable and Data Files ~ 197
Object and Data Security ~ 197
Securing Login Access to SQL Server ~ 198
Assigning Privileges to Perform Serverwide Operations ~ 199
Granting Access to Databases ~ 199
Grouping Users into Database Roles ~ 200
Using Views, Stored Procedures, and User-Defined Functions to Simplify Security ~ 201
Network Communications Security ~ 201
Security Options in SQL Server ~ 202
Understanding the Windows Authentication Mode ~ 202
Understanding the SQL Authentication Mode ~ 205
Database Users, Roles, and Permissions ~ 206
Selecting a Security Mode ~ 207
SQL Server and Windows Authentication ~ 207
Windows-Only Authentication Mode ~ 210
Logins ~ 210
Adding New Windows Logins ~ 210
Server Roles ~ 216
Fixed Roles ~ 216
Database Users ~ 218
Adding New Database Users ~ 219
The Guest User Account ~ 221
Assigning User Permissions ~ 222
Database Roles ~ 225
Fixed Roles ~ 225
User-Defined Roles ~ 227
Implementing Database and Server Security ~ 231
The Scenario ~ 231
User Authentication ~ 232
Operating System Administrative Access ~ 233
Windows 2000 ~ 233
SQL Server Logins ~ 234
Assigning Permissions ~ 234
Adding Users to Database Roles ~ 234
Assigning Permissions to Users and Roles ~ 235
Network Communications Security ~ 236
Multiprotocol Encryption ~ 236
SSL Support ~ 237
IPSec in Windows 2000 ~ 239
Summary ~ 239
FAQs ~ 240

Chapter 6 Administration and Active Directory Integration ~ 243
Introduction ~ 244
Windows 2000 Active Directory Integration ~ 244
Registering SQL Servers in Active Directory ~ 246
SQL Server Properties ~ 246
sp_ActiveDirectory_SCP ~ 248
Registering Databases in Active Directory ~ 249
Database Properties ~ 249
SQL Replication Services and Active Directory ~ 249
SQL Server 2000 Replication ~ 250
Registering Publications in Active Directory ~ 257
Locating and Using Publications in Active Directory ~ 258
Analysis Services and Active Directory ~ 260
Registering Analysis Servers in Active Directory ~ 261
Tools and Techniques for SQL Server Administration ~ 262
Windows 2000 Active Directory ~ 262
Active Directory Users and Computers ~ 263
Microsoft Management Console ~ 265
SQL Server Enterprise Manager ~ 266
SQL Server MMC Snap-Ins ~ 273
Moving and Copying SQL Server Databases ~ 275
Enterprise Manager ~ 277
Copy Database Wizard ~ 278
Detaching and Attaching Databases ~ 281
Linked Servers ~ 282
Distributed Queries ~ 284
Database Maintenance Tools ~ 284
DBCC ~ 285
Database Maintenance Plans ~ 286
Maintenance Plan Wizard ~ 286
SQL-DMO ~ 288
Automating Administrative Tasks ~ 290
SQL Server Agent ~ 290
Alerts and Operators ~ 290
SQL Mail ~ 290
Setting Up Operators ~ 292
Defining Alerts ~ 292
Summary ~ 293
FAQs ~ 294

Chapter 7 SQL Server Backup and Recovery ~ 295
Introduction ~ 296
Planning and Implementing a Successful Backup and Recovery Strategy ~ 296
Determining Data Recovery Requirements ~ 296
Frequency of Database Changes ~ 297
Cost of Data Loss and Availability ~ 298
Planning for Hardware Failure ~ 299
The Tape Unit ~ 299
The Disk Unit ~ 300
The Server ~ 301
The Network ~ 301
Selecting a Backup Strategy ~ 302
Backup Strategy Options ~ 302
Database Backup Options ~ 304
Backup Storage ~ 307
Determining Storage Requirements ~ 307
Backup Storage Media ~ 311
Media Sets, Media Families, and Multiple Drives ~ 313
Secure Offsite Storage ~ 315
Sample Backup Scheme ~ 315
Creating a Recovery Strategy ~ 317
Backup and Restore Tools and Techniques ~ 319
The Create Database Backup Wizard ~ 319
The Database Maintenance Plan Wizard ~ 320
Transact-SQL ~ 321
Backing Up SQL Server Databases ~ 322
Performing a Database Backup ~ 323
Backing Up System Databases ~ 335
Restoring SQL Server Databases ~ 344
Restoring a Database Backup ~ 344
Restoring System Databases ~ 348
Database Options and Settings ~ 352
Testing Your Backup and Recovery Strategy ~ 354
Summary ~ 355
FAQs ~ 356

Chapter 8 Microsoft English Query and Full-Text Search ~ 359
Introduction ~ 360
Overview of English Query ~ 360
What’s New in English Query? ~ 362
More Powerful Applications ~ 363
Installing English Query ~ 364
Installing English Query ~ 364
Installation Requirements ~ 365
Creating an English Query Application ~ 366
Planning Your English Query Application ~ 366
Understanding Users’ Questions ~ 366
Creating an English Query Project ~ 367
English Query Project Components ~ 368
English Query SQL Project ~ 369
Creating an English Query SQL Project Using the SQL Project Wizard ~ 370
English Query OLAP Project ~ 371
The English Query Model ~ 373
Building and Deploying Your English Query Application ~ 376
Implementing Web-Based English Query Applications ~ 376
Testing Your English Query Application ~ 377
Putting It All Together ~ 379
Creating a Web-Based English Query Solution ~ 379
An Overview of Full-Text Search ~ 381
File Filtering ~ 381
Full-Text Search Architecture ~ 382
Microsoft Search Service ~ 382
Performance Considerations for Full-Text Indexes ~ 383
Enabling Full-Text Search ~ 387
Creating a Full-Text Catalog ~ 387
Enabling a Database for Full-Text Search ~ 388
Enabling a Table for Full-Text Search ~ 388
Enabling a Column for Full-Text Search ~ 389
Creating a Full-Text Index on the Products Table in the Northwind Database ~ 390
Building the Full-Text Index ~ 391
Querying Full-Text Indexes ~ 393
FREETEXT and FREETEXTTABLE ~ 393
CONTAINS and CONTAINSTABLE ~ 395
Administering Full-Text Catalogs and Indexes ~ 398
Backing Up Full-Text Catalogs ~ 399
Populating Full-Text Indexes ~ 401
Scheduling Index Rebuilds ~ 402
Summary ~ 403
FAQs ~ 404

Chapter 9 Importing and Exporting Data ~ 407
Introduction ~ 408
Overview of Data Import and Export Tools ~ 408
Data Transformation Services ~ 410
What’s New in DTS? ~ 410
Data Transformation Services Architecture ~ 412
Packages ~ 413
Tasks ~ 413
Transformations ~ 414
Connections ~ 415
Package Workflow ~ 415
Security in DTS Packages ~ 416
DTS Performance Considerations ~ 417
Creating and Editing DTS Packages ~ 419
Creating a Simple Package ~ 419
DTS Import/Export Wizard ~ 420
Copying a Table Between Databases ~ 421
DTS Designer ~ 422
Creating a Data Connection ~ 422
Creating a Task ~ 423
Saving DTS Packages ~ 427
Saving to a SQL Server ~ 427
Saving to Meta Data Services ~ 428
Saving to a DTS Package File ~ 428
Saving to a Visual Basic Script File ~ 429
Executing DTS Packages ~ 429
Executing a Package in the DTS Designer Interface ~ 430
Executing a Package in SQL Enterprise Manager ~ 430
Executing a Package Using the dtsrun.exe Utility ~ 430
Executing a Package Using the dtsrunui.exe Utility ~ 432
Executing a Package Programmatically in Visual Basic ~ 432
The Bulk Copy Program ~ 433
Using BCP ~ 436
SQL-DMO BulkCopy ~ 440
Using the BulkCopy Object ~ 440
The BULK INSERT Command ~ 444
Using BULK INSERT ~ 445
Choosing a Data Import and Export Method ~ 447
Import/Export Job Requirements ~ 447
Existing Data Format ~ 448
Frequency of Import or Export Task ~ 448
Data Manipulation Tasks ~ 448
Performance Considerations ~ 449
Summary ~ 449
FAQs ~ 450

Chapter 10 SQL Server Analysis Services ~ 453
Introduction ~ 454
Online Analytical Processing and Data Mining ~ 454
OLTP vs. OLAP vs. Data Warehousing ~ 455
Data Mining ~ 458
New Features in Analysis Services ~ 459
OLAP Enhancements ~ 459
Cubes ~ 459
Storage Locations ~ 459
Actions ~ 459
Access from Client Applications ~ 460
Dimensions ~ 460
Security ~ 460
Integrating the Add-Ins ~ 461
Data-Mining Capabilities ~ 461
The Analysis Services Architecture ~ 461
Analysis Server ~ 461
Analysis Manager ~ 462
Cubes ~ 464
Mining Models ~ 465
PivotTable Service ~ 466
Decision Support Objects ~ 466
Installing Analysis Services ~ 466
Analysis Services Requirements ~ 467
Installing Analysis Services for the First Time ~ 467
Upgrading from Earlier Versions ~ 468
Designing and Building an OLAP Solution ~ 469
Designing and Building a Data Warehouse ~ 469
Normalization vs. Denormalization ~ 470
Components of a Data Warehouse ~ 470
Populating Data Warehouses ~ 470
Using DTS to Transform and Load Data ~ 471
Creating an Analysis Services Database ~ 474
Create a Database and Data Source ~ 474
Designing and Building Cubes ~ 475
Using the Cube Wizard ~ 475
Editing Cubes ~ 479
Data Security in Cubes ~ 479
Defining Measures and Dimensions ~ 480
Understanding Measures and Dimensions ~ 480
Creating and Editing Measures and Dimensions ~ 480
Using Your OLAP Solution ~ 482
Querying Cubes ~ 482
HTTP Cube Access ~ 483
Multidimensional Expressions ~ 483
Data Mining in SQL Server ~ 485
Mining Models ~ 485
Relational Data-Mining Models ~ 485
OLAP Data-Mining Models ~ 486
Data Mining Algorithms ~ 486
Creating and Editing Data-Mining Models ~ 486
Mining Model Wizard ~ 487
OLAP Mining Model Editor ~ 488
Using Data-Mining Models ~ 488
Data-Mining Training ~ 488
Data-Mining Model Browser ~ 489
Multidimensional Expressions for Data Mining ~ 491
OLE DB for Data Mining ~ 491
Security in Analysis Services ~ 492
Users and Groups ~ 492
Roles ~ 492
Data Security ~ 493
Implementing Security in Analysis Services ~ 493
Accessing Analysis Services Over the Web ~ 494
Configuring IIS for Analysis Services ~ 494
Performance Tuning and Optimization ~ 495
Usage Analysis Wizard ~ 495
Usage-Based Optimization Wizard ~ 496
Summary ~ 496
FAQs ~ 498

Chapter 11 Using XML with SQL Server ~ 499
Introduction ~ 500
Overview of XML and SQL Server Support ~ 500
What Is XML? ~ 500
The Benefits of XML ~ 501
Working with XML ~ 502
XML Documents ~ 502
Extensible Stylesheet Language ~ 504
XML Data-Reduced Schemas ~ 505
XML Path Language ~ 506
XML Support and Limitations in SQL Server ~ 509
Additional XML Resources on the Web ~ 509
W3C.org ~ 510
Biztalk.org ~ 510
XML.org ~ 510
MSDN.Microsoft.com/XML ~ 510
HTTP and URL Query Support ~ 510
Configuring IIS for HTTP Query Support ~ 510
Creating a Database Virtual Directory in IIS ~ 511
Querying SQL Server Using HTTP ~ 512
Supported Query Methods Using HTTP ~ 513
Executing SQL Using HTTP ~ 513
Creating an XML Query Template ~ 515
Executing XML Query Templates ~ 516
XPath Queries ~ 518
Overview of XPath Queries and SQL Server Limitations ~ 518
XPath Limitations in SQL Server ~ 519
Additional Information on the XML Path Language Specification ~ 519
XPath Data Types and Conversions ~ 519
Using XPath Queries ~ 520
Select…For XML ~ 522
FOR XML Syntax and Use ~ 522
Limitations of FOR XML ~ 525
XML Views ~ 525
XML Data-Reduced Schemas ~ 526
Mapping XML Data to Database Tables and Columns ~ 529
SQL Server XML View Mapper ~ 530
Using the SQL Server View Mapper ~ 530
Using and Updating XML Data ~ 534
Updategrams ~ 534
Downloading SQL Server 2000 XML Updategrams Support ~ 534
Understanding Updategrams ~ 535
T-SQL OPENXML Statement ~ 536
sp_xml_preparedocument and sp_xml_removedocument ~ 536
ActiveX Data Objects ~ 539
XML Support in ADO 2.6 ~ 539
Summary ~ 543
FAQs ~ 544

Chapter 12 Database Replication Techniques and Configuration ~ 545
Introduction ~ 546
SQL Server Replication Architecture ~ 546
Publisher ~ 546
Subscriber ~ 547
Distributor ~ 547
Publication ~ 547
Article ~ 547
Subscription ~ 548
SQL Server Agent ~ 548
Replication Agents ~ 548
New Replication Features in SQL Server ~ 549
Replication Compatibility ~ 550
Previous Versions of SQL Server ~ 550
Heterogeneous Publishers and Subscribers ~ 551
Heterogeneous Subscribers ~ 551
Heterogeneous Publishers ~ 552
Designing for Database Replication ~ 552
Replication Requirements ~ 552
Data Location ~ 552
Data Modification ~ 552
Connection Bandwidth and Availability ~ 553
Application and Database Design Considerations ~ 553
Minimize Potential Conflicts ~ 554
Table RowGuid and Identity Values ~ 555
Replication Methods in SQL Server ~ 560
Transactional Replication ~ 560
Queued Updating Subscribers ~ 561
Merge Replication ~ 561
Snapshot Replication ~ 563
Selecting a Replication Method ~ 564
Configuring SQL Server Replication ~ 565
Enabling Server Publishing ~ 565
Assigning a Distributor Server ~ 565
Creating Publications ~ 566
Create Publication Wizard ~ 567
Adding Subscribers ~ 569
Push vs. Pull Subscriptions ~ 569
Replicating Data Over the Internet ~ 571
Replicating Via a Virtual Private Network ~ 571
Replicating through Microsoft Proxy Server ~ 571
Replication Via FTP ~ 573
Configuring a Publisher or Distributor to Listen on TCP/IP ~ 573
Configuring a Publication to Allow Subscribers to Retrieve Snapshots Using FTP ~ 574
Configuring a Subscription to Use FTP to Retrieve a Snapshot ~ 575
Dealing with Replication Conflicts ~ 575
Replication Conflict Viewer ~ 576
Viewing Conflicts ~ 577
Row-Level vs. Column-Level Conflict Tracking ~ 577
Resolving Conflicts ~ 577
Default Resolvers vs. Custom Resolvers ~ 579
SQL Server CE Edition Replication Features ~ 580
Windows CE Subscribers ~ 581
Replication and Active Directory Integration ~ 582
Registering Publications in Active Directory ~ 582
Browsing and Subscribing to Publications in Active Directory ~ 584
Replication Performance Considerations ~ 585
Hardware Upgrades ~ 586
How to Enhance Snapshot and Transactional Replication Performance ~ 587
How to Enhance Transactional Replication Performance ~ 588
How to Enhance Merge Replication Performance ~ 588
Replication Backup Strategies ~ 588
Backing Up and Restoring Snapshot Replication ~ 590
Backup and Restore of Transactional Replication ~ 590
Using Log Shipping as a Backup to Transactional Replication ~ 590
Backing Up and Restoring Merge Replication ~ 591
How to Restore a Replicated Database from Backup ~ 591
Summary ~ 594
FAQs ~ 595

Chapter 13 Programming Tools and Technologies in SQL Server ~ 597
Introduction ~ 598
Overview of SQL Server Programming ~ 598
New Programming Features in SQL Server 2000 ~ 599
Data Types ~ 600
Bigint ~ 600
Sql_variant ~ 601
Table ~ 603
Query Analyzer ~ 604
SQL Debugger ~ 607
User-Defined Functions ~ 609
Scalar User-Defined Functions ~ 610
Table User-Defined Functions ~ 611
Inline User-Defined Functions ~ 612
Referential Integrity Enhancements ~ 613
Cascading Updates and Deletes ~ 613
Trigger Enhancements ~ 614
INSTEAD OF ~ 614
AFTER ~ 616
Indexed Views ~ 617
Meta Data Services ~ 622
Transact-SQL ~ 624
Data Definition Language ~ 625
Data Manipulation Language ~ 626
Data Control Language ~ 631
Data Access Tools and Technologies ~ 633
Command-Line Utilities ~ 633
ADO, OLE DB, and ODBC ~ 634
Programming Administrative Tasks ~ 639
Distributed Management Objects ~ 640
Namespaces ~ 642
Analysis Services Programming ~ 643
DTS Programming ~ 645
Replication Programming ~ 646
Meta Data Services Programming ~ 647
Summary ~ 648
FAQs ~ 649

Chapter 14 Performance-Tuning Tools and Techniques ~ 651
Introduction ~ 652
Partitioning Data and Federated Database Servers ~ 652
Overview ~ 653
Designing Your Tables ~ 656
Configuring the Servers ~ 657
Creating the View ~ 659
Optimizing Query Performance ~ 659
Understanding Indexes ~ 660
Types of Indexes ~ 661
Clustered Indexes ~ 661
Nonclustered Indexes ~ 663
Covering Indexes ~ 664
Optimizing Database Performance with SQL Profiler ~ 665
Exercise 1: Setting Up a Trace with SQL Profiler for the Northwind Database ~ 667
Index Tuning Wizard ~ 668
Exercise 2: Loading SQL Profiler trace file into the Index Tuning Wizard ~ 668
SQL Query Analyzer ~ 669
Tips for Writing Better Queries ~ 669
Query Execution Plan ~ 671
Optimizing Server Performance ~ 673
Hardware Configuration ~ 674
Processors ~ 674
Memory ~ 675
Disks ~ 675
Software Configuration ~ 678
SQL Server Settings ~ 679
Windows 2000 Settings ~ 682
Performance Monitor ~ 684
Summary ~ 688
FAQs ~ 690

Index ~ 695