Pro SQL Server 2005

By: Thomas Rizzo, Adam Machanic, Julian Skinner

Write A Review

Paperback | 1 November 2005

At a Glance

Paperback
704 Pages

Dimensions(cm)
22.86 x 17.15 x 3.81

Paperback

$69.99

or 4 interest-free payments of $17.50 with

Ships in 5 to 7 business days

SQL Server 2005 is a dramatically expanded and enhanced version of SQL Server 2000 that not only makes SQL Server a true enterprise-level RDBMS, but also changes how SQL Server professionals design databases and develop database applications.

Pro SQL Server 2005 provides in-depth coverage of new SQL Server features and enhancements. It focuses on what experienced SQL Server database administrators, developers, and business intelligence practitioners need to know to get up to speed with SQL Server 2005 as quickly as possible. It carefully presents each new feature and precisely illustrates the feature with examples that show you how to use it effectively.

The authors provide practical, in-depth coverage of the core topics that they specialize inall supported by realistic examples, so you'll be able to design and implement your own SQL Server 2005 solutions quickly and painlessly. This book is a roadmap for exploring and exploiting SQL Server 2005. So fasten your seatbelts, start your computers, and race to mastery of SQL Server 2005!

Shipping

	Standard Shipping	Express Shipping
Metro postcodes:	$9.99	$14.95
Regional postcodes:	$9.99	$14.95
Rural postcodes:	$9.99	$14.95

Orders over $79.00 qualify for free shipping.

How to return your order

At Booktopia, we offer hassle-free returns in accordance with our returns policy. If you wish to return an item, please get in touch with Booktopia Customer Care.

Additional postage charges may be applicable.

Defective items

If there is a problem with any of the items received for your order then the Booktopia Customer Care team is ready to assist you.

For more info please visit our Help Centre.

You Can Find This Book In

Non-Fiction Computing & I.T.Databases Computer Programming & Software Development Database Programming Programming & Scripting Languages Software Engineering

About the Authors	p. xvii
About the Technical Reviewers	p. xix
Acknowledgments	p. xxi
Introduction	p. xxiii
SQL Server Overview and Installation	p. 1
Evolution of SQL Server	p. 1
SQL Server 2005 Overview	p. 3
Editions	p. 3
Features	p. 4
Installation	p. 8
Minimum System Requirements	p. 8
Installation Process	p. 10
Server Registration	p. 19
Sample Databases	p. 22
Side-by-Side Installation	p. 23
Upgrading from SQL Server 2000 and 7.0	p. 23
Summary	p. 23
SQL Server Management Technologies	p. 25
Connecting to and Managing Your SQL Servers	p. 25
Context-Sensitive Menus for Each Object Type	p. 26
Mobile Database Support	p. 27
SQL Server 2000 Support	p. 28
User Interface Enhancements	p. 28
Asynchronous Treeview and Object Filtering	p. 29
Nonmodal and Resizable Dialog Boxes	p. 29
Script and Schedule Enhancements	p. 30
Code Authoring	p. 31
Results Pane	p. 33
Activity Monitor	p. 34
Summary Views	p. 34
Functional Enhancements	p. 35
Dedicated Administrator Connection	p. 35
Deadlock Visualization	p. 36
Performance Monitor Correlation	p. 37
Server Registration Import/Export	p. 38
Maintenance Plan Designer	p. 38
SQL Server Agent Changes	p. 39
Database Mail	p. 39
Catalog Views and Dynamic Management Views	p. 41
Default Server Trace	p. 43
Profiler Enhancements	p. 44
SQL Configuration Manager	p. 44
Surface Area Configurator	p. 45
Enhanced Help and Community Integration	p. 47
SQLCMD	p. 48
Server Management Objects	p. 49
Summary	p. 52
T-SQL Enhancements for Developers	p. 53
Enhancements Affecting DML	p. 53
Old-Style Outer Joins Deprecated	p. 54
Common Table Expressions	p. 55
TOP	p. 63
Extensions to the FROM Clause	p. 66
OUTPUT	p. 76
Ranking Functions	p. 77
EXCEPT and INTERSECT	p. 83
Synonyms	p. 85
General Development	p. 86
Error Handling	p. 87
.WRITE Extension to the UPDATE Statement	p. 94
EXECUTE	p. 96
Code Security Context	p. 96
.NET Declarations	p. 99
Summary	p. 101
T-SQL Enhancements for DBAs	p. 103
Metadata Views	p. 103
Compatibility Views	p. 104
Catalog Views	p. 104
Dynamic Management Views and Functions	p. 106
DDL Triggers	p. 107
Creating and Altering DDL Triggers	p. 108
Dropping DDL Triggers	p. 109
Enabling and Disabling DDL Triggers	p. 109
Enumerating DDL Triggers Using Catalog Views	p. 109
Programming DDL Triggers with the eventdata() Function	p. 110
Indexing and Performance Enhancements	p. 112
Online Indexing	p. 112
Controlling Locking During Index Creation	p. 113
Creating Indexes with Additional Columns Included	p. 113
Altering Indexes	p. 114
Using Statistics for Correlated DATETIME Columns	p. 117
Improving Performance of Ordering for Tertiary Collations	p. 118
Table and Index Partitioning	p. 119
Partition Functions	p. 120
Partition Schemes	p. 120
Creating Partitioned Tables and Indexes	p. 121
Adding and Removing Partitions	p. 122
Modifying Partition Functions and Schemes	p. 123
Switching Tables into and out of Partitions	p. 124
Managing Table and Index Partitions	p. 125
Enhancements to Tables and Views	p. 125
Enhancements to Indexed Views	p. 125
Persisted Computed Columns	p. 126
Snapshots	p. 127
SNAPSHOT Isolation Level	p. 127
Database Snapshots	p. 130
Data Integrity Enhancements	p. 132
Verifying a Database's Pages	p. 132
Putting a Database into an Emergency State	p. 132
Summary	p. 133
.NET Integration	p. 135
Introduction to SQL Server .NET Integration	p. 135
Why Does SQL Server 2005 Host the CLR?	p. 136
When to Use CLR Routines	p. 136
When Not to Use CLR Routines	p. 136
How SQL Server Hosts .NET: An Architectural Overview	p. 137
SQL Server .NET Programming Model	p. 137
Enhancements to ADO.NET for SQL Server Hosting	p. 138
Overview of the New .NET Namespaces for SQL Server	p. 138
Programming a CLR Stored Procedure	p. 139
Starting a Visual Studio 2005 SQL Server Project	p. 140
Anatomy of a Stored Procedure	p. 143
Adding Parameters	p. 144
Defining the Problem	p. 144
Using the SqlPipe	p. 146
Putting It All Together: Coding the Body of the Stored Procedure	p. 148
Testing the Stored Procedure	p. 151
Debugging the Procedure	p. 152
Throwing Exceptions in CLR Routines	p. 153
Deploying CLR Routines	p. 157
Summary	p. 159
Programming Assemblies	p. 161
CLR User-Defined Types	p. 161
Applications for User-Defined Types	p. 162
Adding a User-Defined Type to a SQL Server Project	p. 162
Parts of a User-Defined Type	p. 164
A Simple Example: The PhoneNumber Type	p. 167
Another Example: The StringArray Type	p. 175
Managing User-Defined Types	p. 182
CLR User-Defined Functions	p. 183
Adding a User-Defined Function to a Visual Studio Project	p. 184
The Visual Studio 2005 User-Defined Function Template	p. 184
The SqlFunction Attribute	p. 184
Scalar User-Defined Functions	p. 185
Table-Valued User-Defined Functions	p. 188
Managing CLR User-Defined Functions	p. 192
CLR User-Defined Aggregates	p. 193
Adding a User-Defined Aggregate to a SQL Server Project	p. 193
Parts of a User-Defined Aggregate	p. 195
CLR User-Defined Triggers	p. 200
Adding a CLR User-Defined Trigger to a SQL Server Project	p. 200
Programming CLR Triggers	p. 201
Managing User-Defined Triggers	p. 205
Managing Assemblies	p. 205
A Note Regarding Visual Studio 2005	p. 206
Summary	p. 206
SQL Server and XML	p. 207
What Is XML?	p. 207
What Are XPath and the XMLDOM?	p. 208
XPath Syntax	p. 210
XPath Functions	p. 211
The XMLDOM-XML Document Object Model	p. 211
The XPathDocument, XPathNavigator, and XPathExpression Classes	p. 212
Getting XML into the Database	p. 213
What Is SQLXML?	p. 214
Configuring SQL Server	p. 214
OPENXML	p. 215
SQLXML: XML Views Using Annotated XML Schemas	p. 220
SQLXML Updategrams	p. 226
XML BulkLoad	p. 228
Getting XML Out of the Database: FOR XML	p. 230
FOR XML (Server-Side)	p. 230
FOR XML (Client-Side)	p. 236
Using Templates	p. 236
Enhancements to FOR XML	p. 237
Programming SQLXML from .NET and COM	p. 238
SqlXmlCommand	p. 238
SqlXmlParameter	p. 239
SqlXmlAdapter	p. 240
SqlXmlException	p. 240
Code Samples	p. 240
FOR XML: Server-Side and Client-Side	p. 242
Using an XML TextReader	p. 242
Using Parameters with SQLXML	p. 243
Executing XPath or SQL Queries with Templates	p. 244
Interoperating with the ADO.NET Dataset	p. 244
Programming Updategrams	p. 245
Summary	p. 245
SQL Server 2005 XML and XQuery Support	p. 247
Using the XML Datatype	p. 248
Understanding How XML Is Stored by SQL Server	p. 249
Creating XML Columns	p. 250
Setting Permissions for Schema Creation	p. 254
Constraining XML Columns	p. 255
Examining the XML Datatype Limitations	p. 256
Inserting Data into XML Columns	p. 257
Using SSIS with XML Data	p. 257
Bulkloading XML	p. 259
Writing a Custom Query or Application	p. 259
Querying XML Data	p. 260
XQuery 101	p. 260
Basic XML Query Methods	p. 266
Cross-Domain Queries	p. 268
Modifying XML Data	p. 269
Limitations of XML Modification	p. 270
Indexing XML for Performance	p. 270
Understanding How XML Indexing Works	p. 271
Examining Secondary XML Indexes	p. 272
Full-Text Search and the XML Datatype	p. 273
Dynamic Management Views and XML	p. 274
Applications and XML	p. 274
XML Web Services Support	p. 275
Creating an Endpoint	p. 276
Using Advanced Web Services	p. 280
Monitoring Performance of XML Web Services	p. 285
Summary	p. 286
SQL Server 2005 Reporting Services	p. 287
Reporting Services Components	p. 287
Report Server	p. 289
Metadata Catalog	p. 289
Report Designer	p. 289
Report Manager Web Application	p. 291
Reporting Services Security	p. 292
Building a Basic Report with SSRS 2000	p. 293
Launching the Designer	p. 293
Working with Data Sources and Datasets	p. 293
Laying Out and Previewing the Report	p. 294
Working with Expressions	p. 294
Deploying Your Report	p. 295
Upgrading from SQL Server 2000 Reporting Services	p. 295
Licensing Changes for Reporting Services	p. 296
SQL Server Management Studio Integration	p. 297
Walkthrough: Management Studio and Reporting Services	p. 298
Management Changes	p. 304
WMI Provider	p. 305
Management and Execution Web Services	p. 307
Reporting Services Configuration Tool	p. 307
Report Design and Execution Improvements	p. 308
Expression Editor	p. 309
Multivalued Parameters	p. 310
DatePicker for Date Values	p. 312
Interactive Sorting	p. 313
Analysis Services Integration	p. 314
Walkthrough: Building a Report in BIDS	p. 315
Floating Headers	p. 322
Data Source Changes: Expressions, XML/Web Services, SSIS, and SAP	p. 323
Custom Report Items	p. 328
Visual Studio Integration and ReportViewer Controls	p. 329
Using WinForm Controls	p. 329
Working with the ReportViewer Controls Programmatically	p. 332
LocalReport and ServerReport Objects	p. 336
SharePoint Integration	p. 337
End-User Ad Hoc Query and Reporting	p. 337
The Report Builder Client	p. 338
The Semantic Model Definition Language	p. 338
Walkthrough: Report Builder	p. 339
Summary	p. 346
Analysis Services	p. 347
SSAS 2005 Enhancements	p. 347
Architecture	p. 348
Performance, Scalability, Availability	p. 349
Usability	p. 349
Development	p. 350
Installation	p. 351
What Is Analysis?	p. 352
OLAP, OLTP, and Data Warehouses	p. 352
OLAP Concepts	p. 353
Cubes	p. 353
Cells	p. 354
Measures and Fact Tables	p. 354
Dimensions and Attributes	p. 354
Hierarchies	p. 354
Analysis Services Projects	p. 355
Defining Data Sources	p. 356
Designers vs. Wizards	p. 359
Defining Data Source Views	p. 360
Defining Cubes	p. 363
Deploying Projects and Configuring Projects for Deployment	p. 367
Cube Operations	p. 369
Browsing Cubes	p. 370
Browsing Cubes with Hierarchies	p. 372
Managing Displayed Data	p. 374
Calculations and MDX	p. 376
Key Performance Indicators (KPIs)	p. 379
Analysis Services Scripting Language (ASSL)	p. 382
Example ASSL Object: A Data Source View with a Named Query	p. 382
ASSL Drilldown	p. 384
Summary	p. 385
Security	p. 387
A Word about sa	p. 387
Surface Area Configuration	p. 387
Remote Connections	p. 388
Dedicated Administrator Connection	p. 388
.NET Framework	p. 388
Database Mail	p. 389
SQLMail	p. 389
Service Broker	p. 389
HTTP Connectivity	p. 390
Database Mirroring	p. 390
Web Assistant	p. 390
xp_cmdshell	p. 390
Ad Hoc Remote Queries	p. 390
OLE Automation XPs	p. 390
SMO and DMO XPs	p. 391
Principals and Securables	p. 391
Principals	p. 391
Securables	p. 398
Permissions	p. 401
Types of Permission	p. 401
Managing Permissions	p. 403
Code Access Security	p. 405
Imperative and Declarative CAS	p. 406
Using CAS with SQL Server	p. 406
Encryption	p. 410
The SQL Server 2005 Encryption Hierarchy	p. 411
Encryption with a User-supplied Password	p. 411
Encryption with a Symmetric Key	p. 412
Asymmetric Key Encryption	p. 413
Encryption with a Certificate	p. 414
Certificates and Web Services	p. 415
Summary	p. 416
Service Broker	p. 419
What Is Service Broker?	p. 419
Service Broker Architecture	p. 420
Service Broker Scenarios	p. 422
Creating Service Broker Applications	p. 423
Enabling Service Broker	p. 423
Creating Message Types	p. 424
Creating Contracts	p. 424
Creating Queues	p. 424
Creating Services	p. 425
Creating Service Broker Stored Procedures	p. 425
A Simple Service Broker Example	p. 428
Service Broker Routing and Security	p. 434
Creating Distributed Service Broker Applications	p. 434
Distributed Service Broker Example	p. 437
Summary	p. 457
Automation and Monitoring	p. 459
SQL Server Agent	p. 459
Connect to SQL Server	p. 460
Create the Agent Job	p. 461
Security Enhancements	p. 465
Agent Subsystems	p. 473
Sharing Job Schedules	p. 474
Logging to the sysjobstepslogs Table	p. 476
WMI Events and Agent Alerts	p. 476
Agent Performance Counters	p. 478
Agent Upgrade	p. 479
Maintenance Plans	p. 480
Creating a Maintenance Plan	p. 481
Toolbox	p. 482
Maintenance Plan Designer Document Window	p. 483
SQLCMD	p. 488
Connecting to SQL Server	p. 488
Passing Variables	p. 489
Using the Dedicated Admin Connection	p. 490
Creating Scripts	p. 490
Database Mail	p. 491
Overview	p. 492
Configuring Database Mail	p. 493
Sending Mail	p. 497
SQL Profiler	p. 498
Performance Monitor Correlation	p. 501
ShowPlan	p. 502
Deadlock Visualization	p. 504
Summary	p. 505
Integration Services	p. 507
What's New in SSIS?	p. 508
SSIS's New IDE	p. 509
Connecting to SSIS in Management Studio	p. 509
Creating a New SSIS Project in BIDS	p. 511
SSIS Fundamentals	p. 511
Control Flow Design Surface	p. 512
Data Flow Design Surface	p. 514
Event Handlers Design Surface	p. 526
Package Explorer	p. 528
Connection Managers	p. 529
Solution Explorer	p. 529
Properties Window	p. 529
Control Flow Toolbox Tasks	p. 530
Containers	p. 531
Analysis Services Tasks	p. 535
Data Flow Task	p. 535
Execute Package Tasks	p. 536
Bulk Insert Task	p. 536
Execute SQL Task	p. 536
Execute Process Task	p. 537
File System Task	p. 537
File Transfer Protocol Task	p. 538
Maintenance Plan Tasks	p. 538
Message Queue Task	p. 538
Send Mail Task	p. 539
Scripting Tasks	p. 539
Web Service Task	p. 542
WMI Tasks	p. 542
XML Task	p. 542
Data Flow Designer Tasks	p. 542
Source Adapters	p. 543
Destination Adapters	p. 543
Transformations	p. 544
Logging	p. 550
Configurations	p. 554
Using the Package Configuration Organizer	p. 554
Variables	p. 556
Precedence Constraints	p. 559
Checkpoints	p. 559
Transactions	p. 560
Debugging	p. 560
Control Flow Visual Debugging	p. 560
Data Flow Visual Debugging	p. 562
Data Viewers	p. 562
Breakpoints Window	p. 562
Other Debug Windows	p. 563
The SSIS Package Deployment Utility	p. 563
Migrating SQL Server 2000 Packages	p. 564
Scheduling an SSIS Package	p. 564
Summary	p. 565
Database Mirroring	p. 567
High Availability Defined	p. 568
Database Mirroring Overview	p. 569
Database Mirroring in Context	p. 571
Setting Up Database Mirroring	p. 572
Prerequisites, Connectivity, and Security	p. 573
Back Up and Restore the Principal Database	p. 576
Establish the Principal/Mirror Partnership	p. 577
Changing Transaction Safety Levels	p. 577
Database Mirroring States, Heartbeats, and Quorums	p. 578
Initiating a Failover	p. 579
Suspending and Resuming Mirroring	p. 579
Terminating Database Mirroring	p. 580
Full-Text Indexing and Mirroring	p. 580
Service Broker and Database Mirroring	p. 580
Setting Up Mirroring Using Management Studio	p. 581
Client Applications and Database Mirroring	p. 587
Monitoring Database Mirroring	p. 588
Catalog Views	p. 588
Performance Monitor Counters	p. 592
Profiler	p. 593
Windows Event Log and SQL Server Error Log	p. 593
Performance Considerations for Mirroring	p. 594
Limitations of Database Mirroring	p. 594
Sample Application Walk-Through	p. 595
Database Snapshots and Mirroring	p. 601
Database Snapshots Overview	p. 601
Working with Snapshots in T-SQL	p. 603
Performance Considerations When Using Snapshots on Mirrors	p. 604
Using, Monitoring, and Managing Database Snapshots	p. 604
Programming Database Snapshots	p. 605
Limitations of Database Snapshots	p. 605
Windows Clustering in SQL Server 2005	p. 606
Replication in SQL Server 2005	p. 607
Summary	p. 607
Notification Services	p. 609
Notification Services Architecture	p. 610
Subscribers	p. 611
Subscriptions	p. 611
Events	p. 611
Notifications	p. 612
Building a Notification Services Application	p. 612
Defining an NS Instance: The Instance Configuration File	p. 613
Defining the NS Application: The Application Definition File	p. 617
Compiling and Running Your NS Application	p. 633
Monitoring and Troubleshooting Your NS Application	p. 636
Programmatically Working with NS	p. 639
Programming NS from Visual Studio	p. 639
Managing NS Programmatically	p. 642
Summary	p. 643
Index	p. 645
Table of Contents provided by Ingram. All Rights Reserved.

Pro SQL Server 2005

At a Glance

Paperback

Shipping

How to return your order

Defective items

You Can Find This Book In

More in Databases

Python All-in-One For Dummies

3rd Edition

SPSS Survival Manual

7th Edition - A step by step guide to data analysis using IBM SPSS

Linguistic Data Science and the English Passive

Modeling Diachronic Developments and Regional Variation

The Kaggle Book - Second Edition

Master data science competitions with machine learning, GenAI, and LLMs

Human-centered Data Analytics

Technology for Sustainable Development : Third Analytics Global Conference, AGC 2025, Kolkata, India, March 8-9, 2025, Revised Selected Papers

Neural Information Processing

32nd International Conference, ICONIP 2025, Okinawa, Japan, November 20-24, 2025, Proceedings, Part III

Storytelling with Data

A Data Visualization Guide for Business Professionals, 10th Anniversary Edition

Database Systems

A Practical Approach

Tools and Applications of Data Mining

Big Data Analytics

A Practical Guide

The Data Science and Psychology

Data Mining

Concepts, Techniques and Applications

Designing Semantic Knowledge Graphs

A Guide to Utilizing Semantic Web Technologies for Agile Data Management

The Data Warehouse Toolkit 3ed

The Definitive Guide to Dimensional Modeling

The Crypto Trader

How anyone can make money trading Bitcoin and other cryptocurrencies

Microsoft Power BI For Dummies

For Dummies (Computer/Tech)

Designing Data-Intensive Applications

Big Ideas Behind Reliable, Scalable, and Maintainable Systems

Hands-On Programming with R

Write Your Own Functions and Simulations

Social Research Methods

4th Edition

Microsoft Power BI For Dummies

For Dummies (Computer/Tech)

Microsoft 365 Access For Dummies

Access for Dummies

Data Analytics for Accounting ISE

3rd Edition

Mindf*ck

Inside Cambridge Analytica's Plot to Break the World

Don't Make Me Think, Revisited

A Common Sense Approach to Web Usability

This product is categorised by