book

SQL Server™ 2005 Bible

Name: SQL Server™ 2005 Bible
Author: Paul Nielsen
ISBN: 9780764542565

by Paul Nielsen

November 2006

Intermediate to advanced

1340 pages

34h 21m

English

Wiley

Read now

Unlock full access

Copyright
Credits
About the Author
About the Co-Authors and Contributors
Foreword
Acknowledgments
Introduction
Who Should Read This BookHow This Book Is OrganizedPart I: Laying the FoundationPart II: Manipulating Data with SelectPart III: Developing with SQL ServerPart IV: Enterprise Data ManagementPart V: Business IntelligencePart VI: Optimization StrategiesPart VII: AppendixesHow to Use This BookConventions and FeaturesIconsWhat's on the Companion WebsiteWhere to Go from Here
I. Laying the Foundation
1. The Information Architecture Principle
1.1. Simplicity vs. Complexity1.1.1. Complexity1.1.2. Simplicity1.2. The Usability Objective1.2.1. Suitability of Design1.2.2. Data Store Configurations1.2.3. Master Data Stores Design Styles1.2.3.1. Relational DBMSs1.2.3.2. Object-Oriented DBMSs1.2.3.3. Generic Pattern DBMS1.3. Data Integrity1.3.1. Entity Integrity1.3.2. Domain Integrity1.3.3. Referential Integrity1.3.4. User-Defined Integrity1.3.5. Transactional Integrity1.3.6. Transactional Faults1.3.6.1. Dirty Reads1.3.6.2. Non-Repeatable Reads1.3.6.3. Phantom Rows1.3.6.4. Lost Updates1.3.6.5. Deadlocks1.3.7. Isolation Levels1.3.8. The Value of Null1.3.8.1. Working with Nulls1.3.8.2. Null Controversy1.4. Performance1.4.1. Design1.4.2. Set-Based Processing1.4.3. Indexing1.4.4. Partitioning1.4.5. Caching1.5. Availability1.5.1. Redundancy1.5.2. Recovery1.6. Extensibility1.6.1. Abstraction Layer1.6.2. Generalization1.7. Security1.7.1. Restricted Access1.7.2. Information Ownership1.7.3. Audit Trails1.8. Optimization Theory and SQL Server1.8.1. Schema Design1.8.2. Queries1.8.3. Indexing1.8.4. Concurrency1.8.5. Advanced Scalability1.9. Summary
2. Relational Database Modeling
2.1. Modeling Reality2.2. Visible Entities2.2.1. Every Tuple (Row) Is an Island2.2.2. Primary Keys2.2.3. Tables, Rows, Columns2.3. Identifying Multiple Entities2.3.1. Multiple Objects2.3.2. Relationships Between Objects2.3.3. Organizing or Grouping Objects2.3.4. Consistent Lookup Values2.3.5. Complex Objects2.4. Relational Patterns2.4.1. Secondary Entities and Foreign Keys2.4.2. Relationship Cardinality2.4.3. Relationship Optionality2.4.4. Data-Model Diagramming2.4.5. One-to-Many Relationships2.4.6. One-to-One Relationships2.4.7. Supertype/Subtype Relationships2.4.8. Many-to-Many Relationships2.4.9. Category Entities2.4.10. Reflexive Relationships2.5. Normalization2.5.1. Entity/Attribute Design Principles2.5.2. Normal Forms2.5.3. Simplicity and Normalization2.5.4. First Normal Form (1NF)2.5.5. The Second Normal Form (2NF)2.5.6. The Third Normal Form (3NF)2.5.7. The Boyce-Codd Normal Form (BCNF)2.5.8. The Fourth Normal Form (4NF)2.5.9. The Fifth Normal Form (5NF)2.6. Relational Algebra2.7. Summary
3. Exploring SQL Server 2005 Architecture
3.1. Data Access Architectures3.1.1. The Client/Server Database Model3.1.1.1. Client/Server Databases3.1.1.2. Client/Server Roles3.1.2. N-Tier Design3.1.3. Service-Oriented Architecture3.2. SQL Server Services3.2.1. Relational Engine3.2.2. Transact-SQL3.2.3. Visual Studio and the CLR3.2.4. Service Broker3.2.5. Replication Services3.2.6. Full-Text Search3.2.7. Notification Services3.2.8. Server Management Objects3.2.9. SQL Server Agent3.2.10. Distributed Transaction Coordinator (DTC)3.2.11. SQL Mail3.3. Business Intelligence Services3.3.1. Integration Services3.3.2. Reporting Services3.3.3. Analysis Services3.4. SQL Server 2005 Editions3.4.1. Enterprise (Developer) Edition3.4.2. Standard Edition3.4.3. Workgroup Edition3.4.4. SQL Server Express Edition3.4.5. SQL Server Everywhere Edition3.5. SQL Server Tools and Components3.5.1. SQL Server Management Studio3.5.2. SQL Server Configuration Manager3.5.3. Surface Area Configuration Tool3.5.4. Business Intelligence Development Studio3.5.5. SQL Integrated Help3.5.6. SQL Profiler3.5.7. Performance Monitor3.5.8. Database Tuning Advisor3.5.9. Command-Line Utilities: SQLCmd, Bulk Copy3.6. SQL Server 2005 Feature Pack3.7. AdventureWorks3.8. Exploring the Metadata3.8.1. System Databases3.8.2. Metadata Views3.9. Summary

4. Installing SQL Server 2005
4.1. Planning Your Installation4.1.1. Operating System4.1.2. Security Accounts4.1.3. Authentication Mode4.1.4. Server Instances4.2. Hardware Recommendations4.2.1. Dedicated Server4.2.2. Copious Memory4.2.3. Using Multiple CPUs4.2.4. Disk-Drive Subsystems4.2.5. RAID Disk Subsystems4.2.6. Network Performance4.3. Performing the Installation4.3.1. Attended Installations4.3.2. Unattended Installations4.3.3. Remote Installations4.3.4. Installing in a Clustered Environment4.3.5. Installing Multiple Instances4.3.6. Testing the Installation4.4. Upgrading from Previous Versions4.4.1. SQL Server 2005 Upgrade Advisor Tool4.4.2. Upgrading from SQL Server 20004.5. Migrating to SQL Server4.5.1. Migration Assistant4.5.1.1. Assessment4.5.2. Schema Conversion4.5.3. Data Migration4.5.4. Business Logic Conversion4.5.5. Validation and Integration4.6. Configuring the Surface Area of SQL Server4.6.1. Surface Area Configuration Tool4.6.1.1. Surface Area Configuration for Services and Connections4.6.1.2. Surface Area Configuration for Features4.6.1.2.1. Database Engine4.6.1.2.2. Analysis Services4.6.1.2.3. Reporting Services4.6.2. Command Prompt Utilities4.7. Removing SQL Server4.8. Summary
5. Client Software Connectivity
5.1. Enabling Server Connectivity5.1.1. Server Configuration Manager5.1.2. SQL Native Client Connectivity (SNAC)5.2. SQL Server Native Client Features5.2.1. Requirements5.2.2. Database Mirroring5.2.3. Asynchronous Operations5.2.4. Multiple Active Result Sets (MARS)5.2.5. XML Data Types5.2.6. User-Defined Types5.2.7. Large Value Types5.2.8. Handling Expired Passwords5.2.9. Snapshot Isolation5.3. Summary
6. Using Management Studio
6.1. Organizing the Interface6.1.1. Window Placement6.1.2. The Context Menu6.1.3. The Summary Page6.2. Registered Servers6.3. Object Explorer6.3.1. Navigating the Tree6.3.2. Filtering Object Explorer6.3.3. The Table Designer6.3.4. Building Database Diagrams6.3.5. The Query Designer6.4. Using the Query Editor6.4.1. Connecting to a Server6.4.2. Opening a .sql File6.4.3. Executing SQL Batches6.4.4. Shortcuts and Bookmarks6.4.5. Viewing Query Execution Plans6.5. Using the Solution Explorer6.6. Introducing the Templates6.7. Summary
II. Manipulating Data with Select
7. Understanding Basic Query Flow
7.1. Understanding Query Flow7.1.1. Syntactical Flow of the Query Statement7.1.2. A Graphical View of the Query Statement7.1.3. Logical Flow of the Query Statement7.1.4. Physical Flow of the Query Statement7.2. From Clause Data Sources7.2.1. Possible Data Sources7.2.2. Named Ranges7.2.3. [Table Name]7.2.4. Four-Part Table Names7.3. Where Conditions7.3.1. Using the Between Search Condition7.3.2. Using the In Search Condition7.3.3. Using the Like Search Condition7.3.4. Multiple Where Conditions7.3.5. Select...Where7.4. Ordering the Result Set7.4.1. Specifying the Order by Using Column Names7.4.2. Specifying the Order by Using Expressions7.4.3. Specifying the Order by Using Column Aliases7.4.4. Specifying the Order by Using Column Ordinal Position7.4.5. Order by and Collation7.5. Select Distinct7.6. Ranking7.6.1. Top7.6.2. The With Ties Option7.7. Summary
8. Using Expressions and Scalar Functions
8.1. Building Expressions8.1.1. Operators8.1.2. Bitwise Operators8.1.2.1. Boolean And8.1.2.2. Boolean Or8.1.2.3. Boolean Exclusive Or8.1.2.4. Bitwise Not8.2. Case Expressions8.2.1. Simple Case8.2.2. Boolean Case8.3. Working with Nulls8.3.1. Testing for Null8.3.2. Handling Nulls8.3.2.1. Using the IsNull() Function8.3.2.2. Coalesce()8.3.2.3. Nullif()8.3.2.4. Non-Default Null Behavior8.4. Scalar Functions8.4.1. User Information Functions8.4.2. Data-Time Functions8.4.3. String Functions8.4.4. Soundex Functions8.4.4.1. Using the Soundex() Function8.4.4.2. Using the Difference() Soundex Function8.4.5. Data-Type Conversion Functions8.4.6. Server Environment Information8.5. Summary
9. Merging Data with Joins and Unions
9.1. Using Joins9.2. Inner Joins9.2.1. Creating Inner Joins within SQL Code9.2.2. Number of Rows Returned9.2.3. ANSI SQL 89 Joins9.2.4. Multiple Table Joins9.3. Outer Joins9.3.1. Outer Joins and Optional Foreign Keys9.3.2. Full Outer Joins9.3.3. Placing the Conditions within Outer Joins9.4. Self-Joins9.5. Cross (Unrestricted) Joins9.6. Exotic Joins9.6.1. Θ(theta) Joins9.6.2. Multiple-Condition Joins9.6.3. Non-Key Joins9.7. Set Difference9.8. Using Unions9.8.1. Intersection Union9.8.2. Difference Union/Except9.9. Summary
10. Including Data with Subqueries and CTEs
10.1. Methods and Locations10.2. Simple Subqueries10.2.1. Common Table Expressions10.2.2. Using Scalar Subqueries10.2.3. Using Subqueries as Lists10.2.4. Using Subqueries as Tables10.3. Correlated Subqueries10.4. Relational Division10.4.1. Relational Division with a Remainder10.4.2. Exact Relational Division10.5. Summary
11. Aggregating Data
11.1. Simple Aggregations11.1.1. Basic Aggregations11.1.2. Beginning Statistics11.2. Grouping within a Result Set11.2.1. Simple Groupings11.2.2. Aggravating Queries11.2.2.1. Including group by Descriptions11.2.2.2. Including All Group By Values11.2.2.3. Nesting Aggregations11.2.2.4. Including Detail Descriptions11.2.2.5. Filtering Grouped Results11.3. Generating Totals11.3.1. Rollup Subtotals11.3.2. Cube Queries11.3.3. Computing Aggregates11.4. Building Crosstab Queries11.4.1. Fixed-Column Crosstab Queries11.4.1.1. Correlated Subquery Method11.4.1.2. Case Method11.4.1.3. Pivot Method11.4.2. Dynamic Crosstab Queries11.4.2.1. Cursor and Pivot Method11.4.2.2. Multiple Assignment Variable and Pivot Method11.5. Summary
12. Navigating Hierarchical Data
12.1. Adjacency List Schema Patterns12.1.1. Basic Adjacency List Pattern12.1.2. Adjacency List Variations12.1.2.1. Dual Parents12.1.2.2. Multiple Cardinalities12.2. Navigating the Adjacency List12.2.1. Using a Standard select Statement12.2.2. Using a Recursive Cursor12.2.3. Using a Set-Based Solution12.2.4. Using a User-Defined Function12.2.5. Using Recursive Common Table Expressions12.3. Summary
13. Using Full-Text Search
13.1. Configuring Full-Text Search Catalogs13.1.1. Creating a Catalog with the Wizard13.1.2. Creating a Catalog with T-SQL Code13.1.3. Pushing Data to the Full-Text Index13.1.4. Maintaining a Catalog with Management Studio13.1.5. Maintaining a Catalog in T-SQL Code13.1.6. Noise Files13.2. Word Searches13.2.1. The Contains Function13.2.2. ContainsTable13.3. Advanced Search Options13.3.1. Multiple Word Searches13.3.2. Searches with Wildcards13.3.3. Phrase Searches13.3.4. Word-Proximity Searches13.3.5. Word-Inflection Searches13.3.6. Thesaurus Searches13.3.7. Variable-Word-Weight Searches13.4. Fuzzy Searches13.4.1. Freetext13.4.2. FreetextTable13.5. Binary Object Indexing13.6. Performance13.7. Summary
14. Creating Views
14.1. Why Use Views?14.2. Working with Views14.2.1. Creating Views Using Management Studio14.2.2. Creating Views with DDL Code14.2.3. Order By and Views14.2.4. View Restrictions14.2.5. Executing Views14.3. Locking Down the View14.3.1. Protecting the Data14.3.2. Protecting the View14.3.2.1. Schema Changes14.3.2.2. Encrypting the View's select Statement14.3.2.3. Application Metadata14.4. Updating through Views14.5. Nesting Views14.6. Using Synonyms14.7. Summary
15. Working with Distributed Queries
15.1. Distributed Query Concepts15.2. Accessing a Local SQL Server Database15.3. Linking to External Data Sources15.3.1. Linking with Management Studio15.3.1.1. Selecting the Server15.3.1.2. Configuring the Logins15.3.1.3. Configuring the Options15.3.2. Linking with T-SQL15.3.2.1. Establishing the Link15.3.2.2. Distributed Security and Logins15.3.2.3. Linked Server Options15.3.3. Linking with Non-SQL Server Data Sources15.3.3.1. Linking to Excel15.3.3.2. Linking to MS Access15.4. Developing Distributed Queries15.4.1. Distributed Queries and Management Studio15.4.2. Distributed Views15.4.3. Local-Distributed Queries15.4.3.1. Using the Four-Part Name15.4.3.2. OpenDataSource()15.4.4. Pass-Through Distributed Queries15.4.4.1. Using the Four-Part Name15.4.4.2. OpenQuery()15.4.4.3. OpenRowSet()15.5. Distributed Transactions15.5.1. Distributed Transaction Coordinator15.5.2. Developing Distributed Transactions15.5.3. Monitoring Distributed Transactions15.6. Summary
16. Modifying Data
16.1. Inserting Data16.1.1. Inserting One Row of Values16.1.2. Inserting a Result Set from Select16.1.3. Inserting the Result Set from a Stored Procedure16.1.4. Creating a Default Row16.1.5. Creating a Table While Inserting Data16.2. Updating Data16.2.1. Updating a Single Table16.2.2. Performing Global Search and Replaces16.2.3. Referencing Multiple Tables While Updating Data16.3. Deleting Data16.3.1. Referencing Multiple Tables While Deleting16.3.2. Cascading Deletes16.3.3. Alternatives to Physically Deleting Data16.4. Returning Modified Data16.4.1. Returning Data from an Insert16.4.2. Returning Data from an Update16.4.3. Returning Data from a Delete16.4.4. Returning Data into a @Table Variable16.5. Potential Data-Modification Obstacles16.5.1. Data Type/Length Obstacles16.5.2. Primary Key Obstacles16.5.3. Foreign Key Obstacles16.5.4. Unique Index Obstacles16.5.5. Null and Default Obstacles16.5.6. Check Constraint Obstacles16.5.7. Instead of Trigger Obstacles16.5.8. After Trigger Obstacles16.5.9. Calculated Columns16.5.10. Non-Updateable View Obstacles16.5.11. Views with-check-option Obstacles16.5.12. Security Obstacles16.6. Summary
III. Developing with SQL Server
17. Implementing the Physical Database Schema
17.1. Designing the Physical Database Schema17.1.1. Physical Schema Design Options17.1.2. Refining the Data Patterns17.1.3. Designing for Performance17.1.4. Designing for Extensibility17.1.5. Responsible Denormalization17.2. Creating the Database17.2.1. The Create DDL Command17.2.2. Database-File Concepts17.2.3. Configuring File Growth17.2.4. Using Multiple Files17.2.4.1. Creating a Database with Multiple Files17.2.4.2. Modifying the Files of an Existing Database17.2.5. Planning Multiple Filegroups17.2.5.1. Creating a Database with Filegroups17.2.5.2. Modifying Filegroups17.2.5.3. Dropping a Database17.3. Creating Tables17.3.1. Designing Tables Using Management Studio17.3.2. Working with SQL Scripts17.3.3. Schemas17.3.4. Table and Column Names17.3.5. Filegroups17.4. Creating Primary Keys17.4.1. Primary Keys17.4.1.1. Creating Primary Keys17.4.1.2. Natural Primary Keys17.4.1.3. Identity Column Surrogate Primary Keys17.4.1.4. Using Uniqueidentifier Surrogate Primary Keys17.4.2. Creating Foreign Keys17.4.2.1. Declarative Referential Integrity17.4.2.2. Optional Foreign Keys17.4.2.3. Cascading Deletes and Updates17.5. Creating User-Data Columns17.5.1. Column Data Types17.5.1.1. Character Data Types17.5.1.2. Numeric Data Types17.5.1.3. Date/Time Data Types17.5.1.4. Other Data Types17.5.2. Calculated Columns17.5.3. Column Constraints and Defaults17.5.3.1. Column Nullability17.5.3.2. Unique Constraints17.5.3.3. Check Constraints17.5.3.4. Default Option17.5.4. Data Catalog17.5.4.1. User-Defined Rules17.5.4.2. User-Defined Default17.5.4.3. User-Defined Data Types17.6. DDL Triggers17.6.1. Creating and Altering DDL Triggers17.6.2. EventData()17.6.3. Enabling and Disabling DDL Triggers17.7. Summary
18. Programming with Transact-SQL
18.1. Transact-SQL Fundamentals18.1.1. T-SQL Batches18.1.1.1. Terminating a Batch18.1.1.2. DDL Commands18.1.1.3. Switching Databases18.1.1.4. Executing Batches18.1.1.5. Executing a Stored Procedure18.1.2. T-SQL Formatting18.1.2.1. Statement Termination18.1.2.2. Line Continuation18.1.2.3. Comments18.1.3. Debugging T-SQL18.2. Variables18.2.1. Variable Default and Scope18.2.2. Using the Set and Select Commands18.2.3. Conditional Select18.2.4. Using Variables within SQL Queries18.2.5. Multiple Assignment Variables18.3. Procedural Flow18.3.1. If18.3.1.1. Begin/End18.3.1.2. If Exists()18.3.1.3. If/Else18.3.2. While18.3.3. Goto18.4. Examining SQL Server with Code18.4.1. sp_help18.4.2. Global Variables18.5. Temporary Tables and Table Variables18.5.1. Local Temporary Tables18.5.2. Global Temporary Tables18.5.3. Table Variables18.6. Dynamic SQL18.6.1. Executing Dynamic SQL18.6.2. sp_excecuteSQL18.6.3. Developing Dynamic SQL Code18.7. Error Handling18.7.1. Try...Catch18.7.2. Legacy @@Error Global Variable18.7.3. @@RowCount Global Variable18.7.4. Raiserror18.7.4.1. The Simple Raiserror Form18.7.4.2. The Complete Raiserror Form18.7.4.3. Error Severity18.7.4.4. Adding Variable Parameters to Messages18.7.4.5. Stored Messages18.7.4.6. Logging the Error18.7.4.7. SQL Server Log18.7.5. Catch Block18.7.6. T-SQL Fatal Errors18.8. Summary
19. Performing Bulk Operations
19.1. Bulk Insert19.1.1. Bulk Insert Options19.1.2. BCP19.2. Summary
20. Kill the Cursor!
20.1. Anatomy of a Cursor20.1.1. The Five Steps to Cursoring20.1.2. Managing the Cursor20.1.3. Update Cursors20.1.4. Cursor Scope20.1.5. Cursors and Transactions20.2. Cursor Strategies20.3. Complex-Logic Solutions20.3.1. The Logic Code20.3.2. SQL-92 Cursor with Stored Procedure20.3.3. Fast-Forward Cursor with Stored Procedure20.3.4. Fast-Forward Cursor and User-Defined Function20.3.5. Update Cursor with Stored Procedure20.3.6. Update Query with User-Defined Function20.3.7. Multiple Queries20.3.8. Query with Case Expression20.3.9. Performance Analysis20.4. Denormalizing a List Example20.5. Summary
21. Developing Stored Procedures
21.1. Managing Stored Procedures21.1.1. Create, Alter, and Drop21.1.2. Returning a Record Set21.1.3. Compiling Stored Procedures21.1.4. Stored Procedure Encryption21.1.5. System Stored Procedures21.2. Passing Data to Stored Procedures21.2.1. Input Parameters21.2.2. Parameter Defaults21.3. Returning Data from Stored Procedures21.3.1. Output Parameters21.3.2. Using the Return Command21.3.3. Path and Scope of Returning Data21.4. Using Stored Procedures within Queries21.5. Executing Remote Stored Procedures21.6. The Complete Stored Procedure21.6.1. The pGetPrice Stored Procedure21.6.2. The pOrder_AddNew Stored Procedure21.6.3. The pOrder_AddItem Stored Procedure21.6.4. Adding an Order21.7. Summary
22. Building User-Defined Functions
22.1. Scalar Functions22.1.1. Creating a Scalar Function22.1.2. Calling a Scalar Function22.1.3. Creating Functions with Schema Binding22.2. Inline Table-Valued Functions22.2.1. Creating an Inline Table-Valued Function22.2.2. Calling an Inline Table-Valued Function22.2.3. Using Parameters22.2.4. Correlated User Defined Functions22.3. Multi-Statement Table-Valued Functions22.3.1. Creating a Multi-Statement Table-Valued Function22.3.2. Calling the Function22.4. Summary
23. Implementing Triggers
23.1. Trigger Basics23.1.1. Transaction Flow23.1.2. Creating Triggers23.1.3. After Triggers23.1.4. Instead of Triggers23.1.5. Trigger Limitations23.1.6. Disabling Triggers23.1.7. Listing Triggers23.1.8. Triggers and Security23.2. Working with the Transaction23.2.1. Determining the Updated Columns23.2.2. Inserted and Deleted Logical Tables23.2.3. Developing Multi-Row Enabled–Triggers23.3. Multiple-Trigger Interaction23.3.1. Trigger Organization23.3.2. Nested Triggers23.3.3. Recursive Triggers23.3.4. Instead of and After Triggers23.3.5. Multiple after Triggers23.4. Summary
24. Exploring Advanced T-SQL Solutions
24.1. Complex Business Rule Validation24.2. Complex Referential Integrity24.3. Row-Level Custom Security24.3.1. The Security Table24.3.1.1. Creating the Table24.3.1.2. Security Fetch24.3.1.3. Assigning Security24.3.1.4. Handling Security-Level Updates24.3.2. The Security-Check Stored Procedure24.3.3. The Security-Check Function24.3.4. Using the NT Login24.3.5. The Security-Check Trigger24.4. Auditing Data Changes24.4.1. The Audit Table24.4.2. The Fixed Audit Trail Trigger24.4.3. Rolling Back from the Audit Trail24.4.4. Auditing Complications24.4.4.1. Auditing Related Data24.4.4.2. Date Created and Date Modified24.4.4.3. Auditing Select Statements24.4.4.4. Data Auditing and Security24.4.4.5. Data Auditing and Performance24.4.5. The Dynamic Audit-Trail Trigger and Procedure24.5. Transaction-Aggregation Handling24.5.1. The Inventory-Transaction Trigger24.5.2. The Inventory Trigger24.6. Logically Deleting Data24.6.1. Logical Delete Triggers24.6.2. Undeleting a Logically Deleted Row24.6.3. Filtering Out Logically Deleted Rows24.6.4. Cascading Logical Deletes24.6.4.1. Cascading during the Read24.6.4.2. Cascading during the Write24.6.4.3. Logical Deletes and Referential Integrity24.6.5. Degrees of Inactivity24.7. Archiving Data24.8. Summary
25. Creating Extensibility with a Data Abstraction Layer
25.1. The AddNew Stored Procedure25.2. The Fetch Stored Procedure25.3. The Update Stored Procedure25.3.1. Update with RowVersion25.3.2. Minimal-Update25.4. The Delete Stored Procedure25.5. Summary
26. Developing for SQL Server Everywhere
26.1. An Overview of SQL Server 2005 Everywhere Edition26.1.1. History26.1.2. Concepts26.2. What's New in SQL Server 2005 Everywhere Edition26.3. Getting Started with SQL Everywhere26.3.1. Installing SQL Everywhere26.3.1.1. Development Tools26.3.1.2. Runtime Tools26.3.1.3. Choosing the Correct SQL Everywhere CAB Files26.3.2. Query Analyzer 3.026.3.3. Creating a SQL Everywhere Database26.3.3.1. Create a SQL Everywhere Database with Managed Code26.3.3.2. Create a SQL Everywhere Database from Query Analyzer 3.026.3.3.3. Create a SQL Everywhere Database with Visual Studio 200526.3.3.4. Create a SQL Everywhere Database with SQL Server 2005 Management Studio26.3.3.5. Create a SQL Everywhere Database via Merge Replication26.3.4. Upgrading an Existing SQL CE 2.0 Database26.4. Synchronizing Data26.4.1. Remote Data Access26.4.2. Merge Replication26.4.3. Web Services26.5. Packaging and Deployment26.5.1. Creating the Database Programmatically26.5.2. Deploying a Starter Database with the Mobile Application26.5.3. Creating the Database Dynamically through Merge Replication26.5.4. Other Approaches26.6. Security26.6.1. Password Protection26.6.2. Encryption26.6.3. Secure Data Synchronization26.7. Tuning, Maintenance, and Administration26.7.1. Measuring and Improving Query Performance26.7.2. SQL Everywhere Maintenance26.7.2.1. AutoShrink26.7.2.2. Compact26.7.2.3. AutoFlush26.7.3. Repairing a Damaged SQL Everywhere Database26.7.3.1. Verify26.7.3.2. Repair26.7.4. Keeping Merge Replication Healthy26.8. More Information26.9. Summary
27. Programming CLR Assemblies within SQL Server
27.1. .NET Framework Crash Course27.1.1. Assemblies27.1.2. App Domains27.2. Overview of the CLR SQL Server Types27.2.1. CLR Integration .NET Type Attributes27.2.2. Shared Features of SQL Server CLR Types27.2.3. Data Types27.3. .NET Methods That Support CLR Integration27.4. T-SQL CLR DDL Commands and Catalog Views27.4.1. Assemblies27.4.2. Database Objects27.5. Building Database Types with Visual Studio 200527.5.1. Creating a CLR Project27.5.2. Coding a CLR Stored Procedure27.5.3. CLR Functions27.6. Using the CLR versus Using T-SQL27.7. T-SQL Is Not Going Away27.8. Summary
28. Queueing Data with Service Broker
28.1. Configuring a Message Queue28.2. Working with Dialogs28.2.1. Sending a Message to the Queue28.2.2. Receiving a Message28.3. Monitoring Service Broker28.4. Summary
29. Persisting Custom Data Types
29.1. Creating CLR Integration User-Defined Types29.1.1. Satisfying the Requirements29.1.2. Coding a CLR UDT with Visual Studio29.1.3. Testing and Debugging the UDT29.1.4. Performance Considerations29.2. CLR Integration UDT Deployment Details29.2.1. Strongly Named Assemblies and the GAC29.2.2. Creating Strongly Named .NET Assemblies29.2.3. Maintaining the UDT29.3. Summary
30. Programming with ADO.NET 2.0
30.1. An Overview of ADO.NET30.1.1. ADO30.1.1.1. OLE DB30.1.1.2. ADODB Primary Interop Assembly30.1.2. The ADO Object Model30.1.2.1. OLE DB Data Providers30.1.2.2. Mapping Data Types30.1.2.3. ADO and Scripting30.1.3. ADO.NET30.1.3.1. The ADO.NET Object Model30.1.3.2. Managed Providers30.1.3.3. SQL Native Client30.1.3.4. Data Types30.1.3.5. DataAdapters and DataSets30.2. ADO.NET in Visual Studio 200530.2.1. Server Explorer30.2.2. Debugging ADO.NET30.2.3. Application Tracing30.3. Application Building Basics30.3.1. Connecting to SQL Server30.3.2. Data Adapters30.3.3. DataReaders and Recordsets30.3.4. Streams30.3.5. Asynchronous Execution30.3.6. Using a Single Database Value30.3.7. Data Modification30.3.8. Binding to Controls30.4. Summary
31. Using XML, XPath, and XQuery
31.1. XML Data Type31.1.1. Casting and Converting31.1.2. XML Type Limitations31.1.3. XML Schema Collections31.2. XML Indexes31.3. Querying XML Data31.3.1. XPATH31.3.2. FLWOR Queries31.3.3. Merging XQuery with Select31.4. Decomposing XML SQL Server31.4.1. Reading XML into SQL Server31.4.2. Creating XML with SQL Server 200531.4.2.1. For XML Raw31.4.2.2. For XML Auto31.5. Summary
32. Building an SOA Data Store with Web Services
32.1. HTTP Listening32.1.1. About HTTP.sys32.1.2. Implicit Endpoints32.1.3. Explicit Endpoints32.2. WSDL32.3. Endpoint Security32.4. Summary
33. InfoPath and SQL Server 2005
33.1. Overview of InfoPath 200333.1.1. Automatic Data Validation33.1.2. Offline Form Completion33.1.3. Conditional Formatting33.1.4. Security in InfoPath 200333.1.5. InfoPath Object Model33.1.6. Scripting and .NET Code33.1.7. Note33.2. Creating a Form Template33.3. Other Features of InfoPath33.4. Summary
IV. Enterprise Data Management
34. Configuring SQL Server
34.1. Setting the Options34.1.1. Configuring the Server34.1.2. Configuring the Database34.1.3. Configuring the Connection34.2. Configuration Options34.2.1. Displaying the Advanced Options34.2.2. Start/Stop Configuration Properties34.2.2.1. Startup Parameters34.2.2.2. Startup Stored Procedures34.2.3. Memory-Configuration Properties34.2.3.1. Dynamic Memory34.2.3.2. Reconfigure34.2.3.3. Query Wait34.2.3.4. AWE Memory34.2.3.5. Index Memory34.2.3.6. Lock Memory34.2.3.7. Max Open Objects34.2.4. Processor-Configuration Properties34.2.4.1. Affinity Mask34.2.4.2. Max Worker Threads34.2.4.3. Priority Boost34.2.4.4. Lightweight Pooling34.2.4.5. Parallelism34.2.5. Security-Configuration Properties34.2.5.1. Security-Audit Level34.2.5.2. C2 Security34.2.6. Connection-Configuration Properties34.2.6.1. Maximum Concurrent User Connections34.2.6.2. Remote Access34.2.6.3. Remote Query Timeout34.2.6.4. Enforce DTC34.2.6.5. Network-Packet Size and Timeout34.2.6.6. Max Large-Data-Replication Size34.2.7. Server-Configuration Properties34.2.7.1. Default Message Language34.2.7.2. Full-Text Search Default Language34.2.7.3. Allowing Changes to System Tables34.2.7.4. Query Governor Cost Limit34.2.7.5. Two-Digit-Year Cutoff34.2.8. Index-Configuration Properties34.2.9. Configuring Database Auto Options34.2.9.1. Auto Close34.2.9.2. Auto_Shrink34.2.9.3. Auto Create Statistics34.2.9.4. Auto Update Statistics34.2.10. Cursor-Configuration Properties34.2.10.1. Cursor Threshold34.2.10.2. Cursor Close on Commit34.2.10.3. Cursor Default34.2.11. SQL ANSI–Configuration Properties34.2.11.1. ANSI Null Default34.2.11.2. ANSI NULLs34.2.11.3. ANSI Padding34.2.11.4. ANSI Warnings34.2.11.5. Arithmetic Abort34.2.11.6. Numeric Round Abort34.2.11.7. Concatenation Null Yields Null34.2.11.8. Use Quoted Identifier34.2.12. Trigger Configuration Properties34.2.12.1. Nested Triggers34.2.12.2. Recursive Triggers34.2.13. Database-State-Configuration Properties34.2.13.1. Database-Access Level34.2.13.2. Compatibility Level34.2.14. Recovery-Configuration Properties34.2.14.1. Recovery Model34.2.14.2. Torn-Page Detection34.3. Summary
35. Transferring Databases
35.1. Copy Database Wizard35.2. Working with SQL Script35.3. Detaching and Attaching35.4. Summary
36. Recovery Planning
36.1. Recovery Concepts36.2. Recovery Models36.2.1. Simple Recovery Model36.2.2. The Full Recovery Model36.2.3. Bulk-Logged Recovery Model36.2.4. Setting the Recovery Model36.2.5. Modifying Recovery Models36.3. Backing Up the Database36.3.1. Backup Destination36.3.2. Backup Rotation36.3.3. Performing Backup with Management Studio36.3.4. Backing Up the Database with Code36.3.5. Verifying the Backup with Code36.4. Working with the Transaction Log36.4.1. Inside the Transaction Log36.4.1.1. The Active and Inactive Divide36.4.1.2. Transaction Checkpoints36.4.2. Backing Up the Transaction Log36.4.3. Truncating the Log36.4.4. The Transaction Log and Simple Recovery Model36.5. Recovery Operations36.5.1. Detecting the Problem36.5.2. Recovery Sequences36.5.3. Performing the Restore with Management Studio36.5.4. Restoring with T-SQL Code36.6. System Databases Recovery36.6.1. Master Database36.6.1.1. Backing Up the Master Database36.6.1.2. Recovering the Master Database36.6.2. MSDB System Database36.7. Performing a Complete Recovery36.8. Summary
37. Maintaining the Database
37.1. DBCC Commands37.1.1. Database Integrity37.1.1.1. Repairing the Database37.1.1.2. Multi-User Concerns37.1.1.3. Object-Level Validation37.1.1.4. Data Integrity37.1.2. Index Maintenance37.1.2.1. Database Fragmentation37.1.2.2. Index Statistics37.1.2.3. Index Density37.1.3. Database File Size37.1.3.1. Monitoring Database File Sizes37.1.3.2. Shrinking the Database37.1.3.3. Shrinking the Transaction Log37.1.4. Miscellaneous DBCC Commands37.2. Managing Database Maintenance37.2.1. Planning Database Maintenance37.2.2. Maintenance Plan Wizard37.2.2.1. Adding a Task37.2.2.2. Defining the Schedule37.2.2.3. Creating New Connections37.2.2.4. Logging the Maintenance Progress37.2.3. Command-Line Maintenance37.2.4. Monitoring Database Maintenance37.3. Summary
38. Automating Database Maintenance with SQL Server Agent
38.1. Setting Up SQL Server Agent38.2. Understanding Alerts, Operators, and Jobs38.3. Managing Operators38.4. Managing Alerts38.4.1. Creating User-Defined Errors38.4.2. Creating an Alert38.5. Managing Jobs38.5.1. Creating a Job Category38.5.2. Creating a Job Definition38.5.3. Setting Up the Job Steps38.5.4. Configuring a Job Schedule38.5.5. Handling Completion-, Success-, and Failure-Notification Messages38.6. Summary
39. Replicating Data
39.1. Why Replicate Data?39.1.1. Fault Tolerance/Disaster Recovery39.1.2. Application Requirements39.1.3. Performance Gains39.1.4. Data Distribution39.2. Comparing Options for Distributing Data39.3. The Microsoft Model39.3.1. Publisher39.3.2. Subscriber39.3.3. Distributor39.3.4. Central Publisher39.3.5. Central Subscriber39.3.6. Republishing39.3.7. Peer-to-Peer39.3.8. Article39.3.9. Push Subscriptions39.3.10. Pull Subscriptions39.4. Replication Types39.4.1. Snapshot Replication39.4.2. Snapshot Replication with Immediate Updating39.4.3. Snapshot Replication with Queued Updating39.4.4. Snapshot Replication with Immediate Updating and Queued Failover39.5. Transactional Replication39.5.1. Snapshot Agent39.5.2. Log Reader Agent39.5.3. Distribution Agent39.5.4. Peer-to-Peer Replication39.5.5. Bi-directional Transactional Replication39.5.6. Transactional Replication with Immediate Updating39.5.7. Transactional Replication with Queued Updating39.5.8. Transactional Replication with Immediate Updating and Queued Failover39.5.9. Transactional Replication over the Internet39.5.10. Merge Replication39.5.11. Merge Replication and SQL CE or SQL Mobile Subscribers39.5.12. Merge Replication over the Internet39.6. New in SQL 2005 Replication39.6.1. Restartable Snapshots39.6.2. Oracle Publishing39.6.3. Very Tight Security39.6.4. Peer-to-Peer Replication Model39.6.5. Replicating All DDL39.6.6. Replicating Full-Text Indexes39.6.7. Allowing Anonymous Subscriptions for All Publications39.6.8. Logical Records in Merge Replication39.6.9. Precomputed Partitions39.6.10. Updates to Unique Keys39.6.11. Custom Conflict Handling through SQL RMO39.6.12. Numerous Performance Improvements39.6.13. Latency Tokens39.6.14. Transactional Parallelism39.6.15. Download Only Articles39.6.16. Replication Monitor39.6.17. Merge Replication over HTTPS39.6.18. Merge Replication Performance and Scalability Improvements39.6.19. SQL RMO39.6.20. Simplified Wizards39.6.21. Initializing a Subscriber39.6.21.1. Backup and Restore39.6.21.2. Copy Database39.6.21.3. Dynamic Snapshots39.7. Configuring Replication39.7.1. Using a Local Distributor39.7.2. Using a Remote Distributor39.8. Creating Snapshot Replication Publications39.8.1. Creating Transactional Replication Publications39.8.2. Creating Bi-directional Transactional Replication Publications39.8.3. Creating Oracle Publications39.8.4. Creating Peer-to-Peer Replication Publications39.8.5. Creating Merge Replication Publications39.8.6. Creating Subscriptions39.8.7. Creating Web Synchronizing Subscriptions39.9. Monitoring Your Replication Solution39.9.1. Agent Profiles39.9.2. All Subscriptions39.9.3. Warnings and Agents39.9.4. Modifying Replication Agent Properties39.9.5. Tracer Tokens39.10. Replication Performance39.11. Replication Troubleshooting39.12. Summary
40. Securing Databases
40.1. Security Concepts40.1.1. Server-Level Security40.1.2. Database-Level Security40.1.3. Object Ownership40.2. Windows Security40.2.1. Windows Security40.2.2. SQL Server Login40.3. Server Security40.3.1. SQL Server Authentication Mode40.3.2. Windows Authentication40.3.2.1. Adding a New Windows Login40.3.2.2. Removing a Windows Login40.3.2.3. Denying a Windows Login40.3.2.4. Setting the Default Database40.3.2.5. Orphaned Windows Users40.3.2.6. Security Delegation40.3.3. SQL Server Logins40.3.3.1. Updating a Password40.3.3.2. Removing a Login40.3.3.3. Setting the Default Database40.3.4. Server Roles40.4. Database Security40.4.1. Guest Logins40.4.2. Granting Access to the Database40.4.2.1. Granting Access Using T-SQL Code40.4.3. Fixed Database Roles40.4.3.1. Assigning Fixed Database Roles with Management Studio40.4.3.2. Assigning Fixed Database Roles with T-SQL40.4.4. Securables Permissions40.4.5. Application Roles40.5. Object Security40.5.1. Object Permissions40.5.1.1. Granting Object Permissions with Code40.5.1.2. Revoking and Denying Object Permission with Code40.5.2. Standard Database Roles40.5.2.1. The Public Role40.5.2.2. Managing Roles with Code40.5.2.3. Hierarchical Role Structures40.5.3. Object Security and Management Studio40.5.3.1. From the Object List40.5.3.2. From the User List40.5.3.3. From the Role List40.5.3.4. Ownership Chains40.5.4. A Sample Security Model Example40.6. C2-Level Security40.7. Views and Security40.8. Cryptography40.8.1. Introduction to Cryptography40.8.2. The SQL Server-Crypto Hierarchy40.8.3. Encrypting with a Passphrase40.8.4. Encrypting with a Symmetric Key40.8.4.1. Encryption Algorithms40.8.4.2. Using the Symmetric Key40.8.4.3. Using Asymmetric Keys40.8.4.4. Using Certificates40.9. Preventing SQL Injection40.9.1. Appending Malicious Code40.9.2. Or 1=140.9.3. Password? What Password?40.9.4. Prevention40.10. Summary
41. Administering SQL Server Express
41.1. Installing SQL Server Express41.1.1. Using Command-Line Parameters to Install SSE Silently41.1.2. Using INI Files to Install SSE41.2. SQL Server Management Studio Express Edition41.3. Summary
V. Business Intelligence
42. ETL with Integration Services
42.1. Design Environment42.1.1. Connection Managers42.1.2. Variables42.1.2.1. Variable Usage42.1.2.2. Expressions42.1.3. Configuring Elements42.1.3.1. Control Flow42.1.3.2. Control Flow Precedence42.1.3.3. Data Flow42.1.4. Event Handlers42.1.5. Executing a Package in Development42.2. Integration Services Package Elements42.2.1. Connection Managers42.2.1.1. Database42.2.1.2. Data Source and Data Source Views42.2.1.3. File42.2.1.4. Special42.2.2. Control Flow Elements42.2.2.1. Containers42.2.2.2. Control Flow Tasks42.2.2.3. Maintenance Plan Tasks42.2.3. Data Flow Components42.2.3.1. Sources42.2.3.2. Destinations42.2.3.3. Transformations42.3. Maintainable and Manageable Packages42.3.1. Logging42.3.2. Package Configurations42.3.3. Checkpoint Restart42.4. Deploying Packages42.4.1. Installing Packages42.4.2. Executing Packages42.5. Summary
43. Business Intelligence with Analysis Services
43.1. Data Warehousing43.1.1. Star Schema43.1.2. Consistency43.1.3. Loading Data43.1.3.1. Loading Dimensions43.1.3.2. Loading Fact Tables43.1.4. Analysis Services Quick Start43.2. Analysis Services Architecture43.2.1. Unified Dimensional Model43.2.2. Server43.2.3. Client43.3. Building a Database43.3.1. Business Intelligence Development Studio43.3.2. Data Sources43.3.3. Data Source View43.3.3.1. Creating the Data Source View43.3.3.2. Managing the Data Source View43.3.3.3. Refining the Data Source View43.3.4. Creating a Cube43.4. Dimensions43.4.1. Dimension Designer43.4.1.1. Attributes and Hierarchies43.4.1.2. Attribute Source Columns and Ordering43.4.1.3. Attribute Relationships43.4.1.4. Visibility and Organization43.4.1.5. Basic Setup Checklist43.4.2. Changing Data in Dimensions43.4.3. Beyond Regular Dimensions43.4.3.1. Time Dimension43.4.3.2. Other Dimension Types43.4.3.3. Parent-Child Dimensions43.4.4. Dimension Refinements43.4.4.1. Hierarchy (All) Level and Default Member43.4.4.2. Grouping Dimension Members43.5. Cubes43.5.1. Cube Structure43.5.1.1. Measures43.5.1.2. Cube Dimensions43.5.2. Dimension Usage43.5.2.1. No Relationship43.5.2.2. Regular43.5.2.3. Fact43.5.2.4. Referenced43.5.2.5. Many-to-Many43.5.3. KPIs43.5.4. Actions43.5.5. Partitions43.5.5.1. Partition Sizing43.5.5.2. Creating Partitions43.5.5.3. Aggregation Design43.5.6. Perspectives43.6. Data Storage43.6.1. SQL Server Notifications43.6.2. Client-Initiated Notifications43.6.3. Scheduled Polling Notifications43.7. Data Integrity43.7.1. Null Processing43.7.2. Unknown Member43.7.3. Error Configuration43.8. Summary
44. Data Mining with Analysis Services
44.1. The Data Mining Process44.1.1. Modeling with Analysis Services44.1.1.1. Data Mining Wizard44.1.1.2. Mining Models44.1.1.3. Model Evaluation44.1.1.4. Deploying44.2. Algorithms44.2.1. Decision Trees44.2.2. Linear Regression44.2.3. Clustering44.2.4. Sequence Clustering44.2.5. Neural Network44.2.6. Logistic Regression44.2.7. Naive Bayes44.2.8. Association Rules44.2.9. Time Series44.3. OLAP Integration44.4. Summary
45. Programming MDX Queries
45.1. Basic Select Query45.1.1. Cube Addressing45.1.2. Dimension Structure45.1.2.1. Dimension References45.1.2.2. Tuples and Simple Sets45.1.3. Basic SELECT Statement45.1.3.1. Measures45.1.3.2. Generating Sets from Functions45.1.3.3. Using SQL Server Management Studio45.2. Advanced Select Query45.2.1. Subcubes45.2.2. WITH Clause45.2.2.1. Sets45.2.2.2. Calculated Members45.2.3. Dimension Considerations45.3. MDX Scripting45.3.1. Calculated Members and Named Sets45.3.2. Adding Business Intelligence45.4. Summary
46. Authoring Reports with Reporting Services
46.1. Anatomy of a Report46.1.1. Report Definition Language (RDL)46.1.2. Data Sources46.1.2.1. Data Source Types46.1.2.2. Common Data Source Connection Strings46.1.2.3. Using Expressions in a Connection String46.1.2.4. Setting Data Source Credentials46.1.3. Reporting Services Data Sets46.1.4. Query Parameters and Report Parameters46.1.5. Report Content and Layout46.2. The Report Authoring Process46.2.1. Creating a Reporting Services Project in Visual Studio 200546.2.2. Steps to Create a Report46.2.3. Using the Report Wizard to Create Reports46.2.4. Authoring a Report from Scratch46.2.4.1. Adding a Blank Report46.2.4.2. Creating a Shared Data Source46.2.4.3. Creating a Data Set46.2.4.4. Displaying Data in the Report46.2.4.5. Previewing the Report46.3. Working with Data46.3.1. Working with SQL in the Report Designer46.3.2. Using Query Parameters to Select and Filter Data46.3.2.1. Nested Report Parameters46.3.2.2. Multi-Value Report Parameters46.3.3. Adding Calculated Fields to a Data Set46.3.4. Working with XML Data Sources46.3.5. Working with Expressions46.3.5.1. Expression Scope46.3.5.2. Express Yourself with Common Expressions46.4. Designing the Report Layout46.4.1. Design Basics46.4.1.1. Designing the Report Header46.4.1.2. Designing the Report Footer46.4.1.3. Adding and Formatting a Table Report Item46.4.1.4. Adding and Formatting a List Report Item46.4.2. Using the Table and Matrix Property Pages46.4.3. Grouping and Sorting Data in a Table and Matrix46.4.3.1. Setting the Sort Order for a Table46.4.3.2. Adding Groups to a Table46.4.3.3. Formatting Tables with Groups46.4.4. Illustrating Data with Charts46.4.5. Adding a Pie Chart to a Report46.5. Summary
47. Administering Reports with Reporting Services
47.1. Deploying Reporting Services Reports47.1.1. Deploying Reports Using Visual Studio 200547.1.1.1. Deploying a Single Report or Data Source47.1.1.2. Deploying a Reporting Services Project47.1.2. Deploying Reports Using the Report Manager47.1.3. Deploying Reports Programmatically Using the Reporting Services Web Service47.2. Configuring Reporting Services Using the Report Manager47.2.1. Configuring Reporting Services Site Settings47.2.1.1. Enabling My Reports47.2.1.2. Configuring Report History Settings47.2.1.3. Configuring Report Execution Settings47.2.2. Administering Security47.2.2.1. System-Level Roles47.2.2.2. Granting System Access to Users and Groups47.2.2.3. Item-Level Roles47.2.2.4. Controlling Item-level Security47.2.3. Working with Linked Reports47.2.4. Creating Linked Reports47.2.5. Leveraging the Power of Subscriptions47.2.6. Creating a Data-Driven Subscription47.3. Summary
48. Analyzing Data with Excel and Data Analyzer
48.1. Excel PivotTables48.1.1. Multidimensional Connections48.1.2. Relational Connection48.1.2.1. Query Wizard48.1.2.2. Microsoft Query48.1.3. PivotTable Construction48.1.4. PivotChart Construction48.2. Excel Data Ranges48.3. Data Analyzer48.3.1. Creating a View48.3.2. Formatting a View48.4. Summary
VI. Optimization Strategies
49. Measuring Performance
49.1. Measuring Accuracy49.2. Using Performance Monitor49.2.1. System Monitor49.2.2. Counter Logs49.3. Using SQL Server Profiler49.3.1. Defining a New Trace49.3.2. Selecting Events49.3.3. Filtering Events49.3.4. Organizing Columns49.3.5. Using the Profiler Trace49.3.6. Integrating Performance Monitor Data49.4. Using SQL Trace49.5. Using Transact-SQL49.5.1. Using Dynamic Management Views49.5.2. Using GetDate()49.5.3. Using Statistics49.6. The Database Performance KPI49.6.1. Repeatable Performance Testing49.6.2. Production Performance Data49.6.3. Scalability Performance Load Testing49.7. Summary
50. Query Analysis and Index Tuning
50.1. A Holistic Approach to Index Tuning50.2. Indexing50.2.1. Index Basics50.2.1.1. Clustered Indexes50.2.1.2. Nonclustered Indexes50.2.2. Creating Indexes50.2.2.1. Composite Indexes50.2.2.2. Primary Keys50.2.2.3. Covering Indexes50.2.2.4. Filegroup Location50.2.3. Index Options50.2.3.1. Unique Indexes50.2.3.2. Index Fill Factor and Pad Index50.2.3.3. Limiting Index Locks and Parallelism50.2.3.4. Index Sort Order50.2.3.5. The Ignore Dup Key Index Option50.2.3.6. The Drop Existing Index Option50.2.3.7. The Statistics Norecompute Index Option50.2.3.8. Sort in Tempdb50.2.3.9. Disabling an Index50.2.4. Creating Base Indexes50.3. Query Analysis50.3.1. Viewing Query Execution Plans50.3.2. Using the Showplans50.3.3. Interpreting the Query Execution Plan50.4. Index Tuning50.4.1. Missing Indexes50.4.2. Bookmark Lookup50.4.3. Optimizable SARGs50.4.4. Index Selectivity50.5. Reusing Query Execution Plans50.6. A Comprehensive Index Strategy50.7. Using the Database Engine Tuning Advisor50.8. Summary
51. Managing Transactions, Locking, and Blocking
51.1. Transactional Basics51.2. Transactional Integrity51.2.1. The ACID Properties51.2.1.1. Atomicity51.2.1.2. Consistency51.2.1.3. Isolation51.2.1.4. Durability51.2.2. Transactional Faults51.2.2.1. Dirty Reads51.2.2.2. Non-Repeatable Reads51.2.2.3. Phantom Rows51.2.3. Isolation Levels51.2.3.1. Level 1—Read Uncommitted51.2.3.2. Level 2—Read Committed51.2.3.3. Level 3—Repeatable Read51.2.3.4. Level 4—Serializable51.2.3.5. Snapshot Isolation51.2.3.6. Read Committed Snapshot51.3. Transaction-Log Architecture51.3.1. Transaction Log Sequence51.3.1.1. Database Beginning State51.3.1.2. Data-Modification Command51.3.1.3. Transaction Log Recorded51.3.1.4. Transaction Commit51.3.1.5. Data-File Update51.3.1.6. Transaction Complete51.3.1.7. Transaction-Log Rollback51.3.2. Transaction-Log Recovery51.4. Understanding SQL Server Locking51.4.1. Lock Granularity51.4.2. Lock Mode51.4.2.1. Lock Contention51.4.2.2. Shared Lock (S)51.4.2.3. Exclusive Lock (X)51.4.2.4. Update Lock (U)51.4.2.5. Intent Locks51.4.2.6. Schema Lock (Sch-M, Sch-S)51.4.3. Lock Duration51.4.4. Monitoring Locking and Blocking51.4.4.1. Using Management Studio51.4.4.2. Using Activity Monitor51.4.5. Using Profiler51.5. Controlling SQL Server Locking51.5.1. Setting the Connection Isolation Level51.5.2. Using Database Snapshot Isolation51.5.2.1. Using Snapshot Isolation51.5.2.2. Using Read Committed Snapshot Isolation51.5.2.3. Handling Write Conficts51.5.3. Using Locking Hints51.5.4. Index-Level Locking Restrictions51.5.5. Controlling Lock Timeouts51.5.6. Evaluating Database Concurrency Performance51.5.7. Application Locks51.6. Deadlocks51.6.1. Creating a Deadlock51.6.2. Automatic Deadlock Detection51.6.3. Handling Deadlocks51.6.4. Minimizing Deadlocks51.7. Application Locking Design51.7.1. Implementing Optimistic Locking51.7.2. Lost Updates51.7.2.1. Minimizing Lost Updates51.7.2.2. Preventing Lost Updates51.8. Transaction Performance Strategies51.9. Summary
52. Providing High Availability
52.1. Availability Testing52.1.1. Warm-Standby Availability52.1.2. Log Shipping52.1.2.1. The Servers52.1.2.2. Configuring Log Shipping with Enterprise Manager52.1.2.3. Monitoring Log Shipping52.1.2.4. Switching Roles52.1.2.5. Configuring a Read-Only Standby Query Server52.1.3. Shipping the Users52.1.4. Returning to the Original Primary Server52.2. Failover Servers and Clustering52.3. Failover SQL Server Installs52.4. Configuring52.5. Database Mirroring52.5.1. Prerequisites52.5.2. Configuring52.6. Architecting the Environment52.7. Summary
53. Scaling Very Large Databases
53.1. Optimization Theory and Scalability53.1.1. Scaling Out the Platform53.1.2. Scaling Up the Solution53.2. Partitioned Tables and Indexes53.2.1. Creating the Partition Function53.2.2. Creating Partition Schemes53.2.3. Creating the Partition Table53.2.4. Querying Partition Tables53.2.5. Altering Partition Tables53.2.5.1. Merging Partitions53.2.5.2. Splitting Partitions53.2.6. Switching Tables53.2.6.1. Prepping the New Table53.2.6.2. Prepping the Partition Table53.2.6.3. Performing the Switch53.2.6.4. Switching Out53.2.7. Rolling Partitions53.2.8. Indexing Partitioned Tables53.2.8.1. Creating Partitioned Indexes53.2.8.2. Maintaining Partitioned Indexes53.2.9. Removing Partitioning53.3. Working with Indexed Views53.3.1. Indexed Views and Queries53.3.2. Updating Indexed Views53.4. Summary
54. Designing High-Performance Data Access Providers
54.1. Data Access Concepts54.1.1. Why Good Data Access Matters54.1.2. Defining Requirements54.2. Data Access Objects54.2.1. How the DAO Scenario Works54.2.1.1. DAO Address base class54.2.1.2. AddressSQLDAO.vb54.2.1.3. TestMain.vb54.2.2. The Cons of the DAO Pattern54.3. Factories54.3.1.54.3.1.1. Main Factory54.3.1.2. SqlServerFactory54.3.1.3. MainTester.vb54.3.2. The Pros of the Factory Pattern54.3.3. The Cons of the Factory Pattern54.4. Data Providers54.4.1. How a Data Provider Works54.4.1.1. Determiner Base Class54.4.1.2. SqlOrAccessDeterminer.vb54.4.1.3. ProviderDeterminers.dll.config54.4.1.4. Providers.dll.config configuration file54.4.1.5. DataProvider.vb class54.4.1.6. DpAddress.vb Data Provider54.4.1.7. DpImplementation.vb Base Class54.4.1.8. ImplAddress.vb Base Class54.4.1.9. Address.vb - SQL Server Implementation54.4.1.10. Data Provider MainTester54.4.2. Pros of the Data Provider Pattern54.4.3. Cons of the Data Provider Pattern54.5. Summary
VII. Appendixes
A. SQL Server 2005 Specifications
B. Sample Databases
B.1. The Sample Database FilesB.2. Cape Hatteras Adventures Version 2B.2.1. Application RequirementsB.2.2. Database DesignB.2.2.1. Data ConversionB.2.2.2. CHA2.adp Front EndB.3. OBX KitesB.3.1. Application RequirementsB.3.2. Database DesignB.4. The FamilyB.4.1. Application RequirementsB.4.2. Database DesignB.5. Aesop's FablesB.5.1. Application RequirementsB.5.2. Database Design

Overview

Use this comprehensive tutorial and reference to increase productivity and write stored procedures using the language with which you're most familiar. The revised content covers new features such as XML integration, Web services, the .NET Common Language Runtime (CLR), and security updates, making this book a must for any developer or database administrator transitioning to the new version of SQL Server. You'll learn to develop SQL Server database and data connections, administer SQL Server, and keep databases performing at their peak. In addition, you'll find dozens of specific examples in both a graphical format and as SQL code as well as numerous best practices describing the most effective way to accomplish a given task. A companion Web site provides all of the code examples found in the book.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Professional SQL Server™ 2005 Programming

Publisher Resources

ISBN: 9780764542565Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills