Pro SQL Server 2005

by Rizzo, Thomas

• ISBN13: 9781590594773
• ISBN10: 1590594770
• Format: Paperback
• Copyright: 2005-11-25
• Publisher: Apress

Summary

SQL Server 2005 is the next generation database and represents a massive leap forward fordevelopers in terms of programming options, productivity, database management and analysis. This book takes a deep look at the full range of SQL Server enhancements that give meaningful and relevant examples, showing how these new features work and also how to architect their applications in order to exploit the new features most effectively. This book is all about getting developers working on their own SQL Server 2005 solutions as quickly and painlessly as possible. Some of the core topics covered in the book are: Enhancements to database programmability features and languages, such as ADO.NET, XML, .NET assemblies, and T-SQL; New Servers, services and tools such as Reporting Services, and the Service Broker and DTS; Enhancements to the BI capabilities such as Analysis Services and Notification services.

Table of Contents

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