Book description
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.
Table of contents
- Copyright
- Credits
- About the Author
- Foreword
- Acknowledgments
- Introduction
-
I. Laying the Foundation
-
1. The Information Architecture Principle
- 1.1. Simplicity vs. Complexity
- 1.2. The Usability Objective
- 1.3. Data Integrity
- 1.4. Performance
- 1.5. Availability
- 1.6. Extensibility
- 1.7. Security
- 1.8. Optimization Theory and SQL Server
- 1.9. Summary
-
2. Relational Database Modeling
- 2.1. Modeling Reality
- 2.2. Visible Entities
- 2.3. Identifying Multiple Entities
-
2.4. Relational Patterns
- 2.4.1. Secondary Entities and Foreign Keys
- 2.4.2. Relationship Cardinality
- 2.4.3. Relationship Optionality
- 2.4.4. Data-Model Diagramming
- 2.4.5. One-to-Many Relationships
- 2.4.6. One-to-One Relationships
- 2.4.7. Supertype/Subtype Relationships
- 2.4.8. Many-to-Many Relationships
- 2.4.9. Category Entities
- 2.4.10. Reflexive Relationships
-
2.5. Normalization
- 2.5.1. Entity/Attribute Design Principles
- 2.5.2. Normal Forms
- 2.5.3. Simplicity and Normalization
- 2.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 Algebra
- 2.7. Summary
-
3. Exploring SQL Server 2005 Architecture
- 3.1. Data Access Architectures
-
3.2. SQL Server Services
- 3.2.1. Relational Engine
- 3.2.2. Transact-SQL
- 3.2.3. Visual Studio and the CLR
- 3.2.4. Service Broker
- 3.2.5. Replication Services
- 3.2.6. Full-Text Search
- 3.2.7. Notification Services
- 3.2.8. Server Management Objects
- 3.2.9. SQL Server Agent
- 3.2.10. Distributed Transaction Coordinator (DTC)
- 3.2.11. SQL Mail
- 3.3. Business Intelligence Services
- 3.4. SQL Server 2005 Editions
-
3.5. SQL Server Tools and Components
- 3.5.1. SQL Server Management Studio
- 3.5.2. SQL Server Configuration Manager
- 3.5.3. Surface Area Configuration Tool
- 3.5.4. Business Intelligence Development Studio
- 3.5.5. SQL Integrated Help
- 3.5.6. SQL Profiler
- 3.5.7. Performance Monitor
- 3.5.8. Database Tuning Advisor
- 3.5.9. Command-Line Utilities: SQLCmd, Bulk Copy
- 3.6. SQL Server 2005 Feature Pack
- 3.7. AdventureWorks
- 3.8. Exploring the Metadata
- 3.9. Summary
- 4. Installing SQL Server 2005
- 5. Client Software Connectivity
- 6. Using Management Studio
-
1. The Information Architecture Principle
-
II. Manipulating Data with Select
- 7. Understanding Basic Query Flow
- 8. Using Expressions and Scalar Functions
- 9. Merging Data with Joins and Unions
- 10. Including Data with Subqueries and CTEs
- 11. Aggregating Data
- 12. Navigating Hierarchical Data
- 13. Using Full-Text Search
- 14. Creating Views
-
15. Working with Distributed Queries
- 15.1. Distributed Query Concepts
- 15.2. Accessing a Local SQL Server Database
- 15.3. Linking to External Data Sources
- 15.4. Developing Distributed Queries
- 15.5. Distributed Transactions
- 15.6. Summary
-
16. Modifying Data
- 16.1. Inserting Data
- 16.2. Updating Data
- 16.3. Deleting Data
- 16.4. Returning Modified Data
-
16.5. Potential Data-Modification Obstacles
- 16.5.1. Data Type/Length Obstacles
- 16.5.2. Primary Key Obstacles
- 16.5.3. Foreign Key Obstacles
- 16.5.4. Unique Index Obstacles
- 16.5.5. Null and Default Obstacles
- 16.5.6. Check Constraint Obstacles
- 16.5.7. Instead of Trigger Obstacles
- 16.5.8. After Trigger Obstacles
- 16.5.9. Calculated Columns
- 16.5.10. Non-Updateable View Obstacles
- 16.5.11. Views with-check-option Obstacles
- 16.5.12. Security Obstacles
- 16.6. Summary
-
III. Developing with SQL Server
-
17. Implementing the Physical Database Schema
- 17.1. Designing the Physical Database Schema
- 17.2. Creating the Database
- 17.3. Creating Tables
- 17.4. Creating Primary Keys
- 17.5. Creating User-Data Columns
- 17.6. DDL Triggers
- 17.7. Summary
-
18. Programming with Transact-SQL
- 18.1. Transact-SQL Fundamentals
- 18.2. Variables
- 18.3. Procedural Flow
- 18.4. Examining SQL Server with Code
- 18.5. Temporary Tables and Table Variables
- 18.6. Dynamic SQL
- 18.7. Error Handling
- 18.8. Summary
- 19. Performing Bulk Operations
-
20. Kill the Cursor!
- 20.1. Anatomy of a Cursor
- 20.2. Cursor Strategies
-
20.3. Complex-Logic Solutions
- 20.3.1. The Logic Code
- 20.3.2. SQL-92 Cursor with Stored Procedure
- 20.3.3. Fast-Forward Cursor with Stored Procedure
- 20.3.4. Fast-Forward Cursor and User-Defined Function
- 20.3.5. Update Cursor with Stored Procedure
- 20.3.6. Update Query with User-Defined Function
- 20.3.7. Multiple Queries
- 20.3.8. Query with Case Expression
- 20.3.9. Performance Analysis
- 20.4. Denormalizing a List Example
- 20.5. Summary
- 21. Developing Stored Procedures
- 22. Building User-Defined Functions
- 23. Implementing Triggers
-
24. Exploring Advanced T-SQL Solutions
- 24.1. Complex Business Rule Validation
- 24.2. Complex Referential Integrity
- 24.3. Row-Level Custom Security
- 24.4. Auditing Data Changes
- 24.5. Transaction-Aggregation Handling
- 24.6. Logically Deleting Data
- 24.7. Archiving Data
- 24.8. Summary
- 25. Creating Extensibility with a Data Abstraction Layer
-
26. Developing for SQL Server Everywhere
- 26.1. An Overview of SQL Server 2005 Everywhere Edition
- 26.2. What's New in SQL Server 2005 Everywhere Edition
-
26.3. Getting Started with SQL Everywhere
- 26.3.1. Installing SQL Everywhere
- 26.3.2. Query Analyzer 3.0
-
26.3.3. Creating a SQL Everywhere Database
- 26.3.3.1. Create a SQL Everywhere Database with Managed Code
- 26.3.3.2. Create a SQL Everywhere Database from Query Analyzer 3.0
- 26.3.3.3. Create a SQL Everywhere Database with Visual Studio 2005
- 26.3.3.4. Create a SQL Everywhere Database with SQL Server 2005 Management Studio
- 26.3.3.5. Create a SQL Everywhere Database via Merge Replication
- 26.3.4. Upgrading an Existing SQL CE 2.0 Database
- 26.4. Synchronizing Data
- 26.5. Packaging and Deployment
- 26.6. Security
- 26.7. Tuning, Maintenance, and Administration
- 26.8. More Information
- 26.9. Summary
-
27. Programming CLR Assemblies within SQL Server
- 27.1. .NET Framework Crash Course
- 27.2. Overview of the CLR SQL Server Types
- 27.3. .NET Methods That Support CLR Integration
- 27.4. T-SQL CLR DDL Commands and Catalog Views
- 27.5. Building Database Types with Visual Studio 2005
- 27.6. Using the CLR versus Using T-SQL
- 27.7. T-SQL Is Not Going Away
- 27.8. Summary
- 28. Queueing Data with Service Broker
- 29. Persisting Custom Data Types
- 30. Programming with ADO.NET 2.0
- 31. Using XML, XPath, and XQuery
- 32. Building an SOA Data Store with Web Services
- 33. InfoPath and SQL Server 2005
-
17. Implementing the Physical Database Schema
-
IV. Enterprise Data Management
-
34. Configuring SQL Server
- 34.1. Setting the Options
-
34.2. Configuration Options
- 34.2.1. Displaying the Advanced Options
- 34.2.2. Start/Stop Configuration Properties
- 34.2.3. Memory-Configuration Properties
- 34.2.4. Processor-Configuration Properties
- 34.2.5. Security-Configuration Properties
- 34.2.6. Connection-Configuration Properties
- 34.2.7. Server-Configuration Properties
- 34.2.8. Index-Configuration Properties
- 34.2.9. Configuring Database Auto Options
- 34.2.10. Cursor-Configuration Properties
- 34.2.11. SQL ANSI–Configuration Properties
- 34.2.12. Trigger Configuration Properties
- 34.2.13. Database-State-Configuration Properties
- 34.2.14. Recovery-Configuration Properties
- 34.3. Summary
- 35. Transferring Databases
- 36. Recovery Planning
- 37. Maintaining the Database
- 38. Automating Database Maintenance with SQL Server Agent
-
39. Replicating Data
- 39.1. Why Replicate Data?
- 39.2. Comparing Options for Distributing Data
- 39.3. The Microsoft Model
- 39.4. Replication Types
-
39.5. Transactional Replication
- 39.5.1. Snapshot Agent
- 39.5.2. Log Reader Agent
- 39.5.3. Distribution Agent
- 39.5.4. Peer-to-Peer Replication
- 39.5.5. Bi-directional Transactional Replication
- 39.5.6. Transactional Replication with Immediate Updating
- 39.5.7. Transactional Replication with Queued Updating
- 39.5.8. Transactional Replication with Immediate Updating and Queued Failover
- 39.5.9. Transactional Replication over the Internet
- 39.5.10. Merge Replication
- 39.5.11. Merge Replication and SQL CE or SQL Mobile Subscribers
- 39.5.12. Merge Replication over the Internet
-
39.6. New in SQL 2005 Replication
- 39.6.1. Restartable Snapshots
- 39.6.2. Oracle Publishing
- 39.6.3. Very Tight Security
- 39.6.4. Peer-to-Peer Replication Model
- 39.6.5. Replicating All DDL
- 39.6.6. Replicating Full-Text Indexes
- 39.6.7. Allowing Anonymous Subscriptions for All Publications
- 39.6.8. Logical Records in Merge Replication
- 39.6.9. Precomputed Partitions
- 39.6.10. Updates to Unique Keys
- 39.6.11. Custom Conflict Handling through SQL RMO
- 39.6.12. Numerous Performance Improvements
- 39.6.13. Latency Tokens
- 39.6.14. Transactional Parallelism
- 39.6.15. Download Only Articles
- 39.6.16. Replication Monitor
- 39.6.17. Merge Replication over HTTPS
- 39.6.18. Merge Replication Performance and Scalability Improvements
- 39.6.19. SQL RMO
- 39.6.20. Simplified Wizards
- 39.6.21. Initializing a Subscriber
- 39.7. Configuring Replication
-
39.8. Creating Snapshot Replication Publications
- 39.8.1. Creating Transactional Replication Publications
- 39.8.2. Creating Bi-directional Transactional Replication Publications
- 39.8.3. Creating Oracle Publications
- 39.8.4. Creating Peer-to-Peer Replication Publications
- 39.8.5. Creating Merge Replication Publications
- 39.8.6. Creating Subscriptions
- 39.8.7. Creating Web Synchronizing Subscriptions
- 39.9. Monitoring Your Replication Solution
- 39.10. Replication Performance
- 39.11. Replication Troubleshooting
- 39.12. Summary
-
40. Securing Databases
- 40.1. Security Concepts
- 40.2. Windows Security
- 40.3. Server Security
- 40.4. Database Security
- 40.5. Object Security
- 40.6. C2-Level Security
- 40.7. Views and Security
- 40.8. Cryptography
- 40.9. Preventing SQL Injection
- 40.10. Summary
- 41. Administering SQL Server Express
-
34. Configuring SQL Server
-
V. Business Intelligence
- 42. ETL with Integration Services
-
43. Business Intelligence with Analysis Services
- 43.1. Data Warehousing
- 43.2. Analysis Services Architecture
- 43.3. Building a Database
- 43.4. Dimensions
- 43.5. Cubes
- 43.6. Data Storage
- 43.7. Data Integrity
- 43.8. Summary
- 44. Data Mining with Analysis Services
- 45. Programming MDX Queries
-
46. Authoring Reports with Reporting Services
- 46.1. Anatomy of a Report
- 46.2. The Report Authoring Process
- 46.3. Working with Data
- 46.4. Designing the Report Layout
- 46.5. Summary
-
47. Administering Reports with Reporting Services
- 47.1. Deploying Reporting Services Reports
- 47.2. Configuring Reporting Services Using the Report Manager
- 47.3. Summary
- 48. Analyzing Data with Excel and Data Analyzer
-
VI. Optimization Strategies
- 49. Measuring Performance
-
50. Query Analysis and Index Tuning
- 50.1. A Holistic Approach to Index Tuning
-
50.2. Indexing
- 50.2.1. Index Basics
- 50.2.2. Creating Indexes
-
50.2.3. Index Options
- 50.2.3.1. Unique Indexes
- 50.2.3.2. Index Fill Factor and Pad Index
- 50.2.3.3. Limiting Index Locks and Parallelism
- 50.2.3.4. Index Sort Order
- 50.2.3.5. The Ignore Dup Key Index Option
- 50.2.3.6. The Drop Existing Index Option
- 50.2.3.7. The Statistics Norecompute Index Option
- 50.2.3.8. Sort in Tempdb
- 50.2.3.9. Disabling an Index
- 50.2.4. Creating Base Indexes
- 50.3. Query Analysis
- 50.4. Index Tuning
- 50.5. Reusing Query Execution Plans
- 50.6. A Comprehensive Index Strategy
- 50.7. Using the Database Engine Tuning Advisor
- 50.8. Summary
-
51. Managing Transactions, Locking, and Blocking
- 51.1. Transactional Basics
- 51.2. Transactional Integrity
- 51.3. Transaction-Log Architecture
- 51.4. Understanding SQL Server Locking
- 51.5. Controlling SQL Server Locking
- 51.6. Deadlocks
- 51.7. Application Locking Design
- 51.8. Transaction Performance Strategies
- 51.9. Summary
-
52. Providing High Availability
- 52.1. Availability Testing
- 52.2. Failover Servers and Clustering
- 52.3. Failover SQL Server Installs
- 52.4. Configuring
- 52.5. Database Mirroring
- 52.6. Architecting the Environment
- 52.7. Summary
-
53. Scaling Very Large Databases
- 53.1. Optimization Theory and Scalability
- 53.2. Partitioned Tables and Indexes
- 53.3. Working with Indexed Views
- 53.4. Summary
-
54. Designing High-Performance Data Access Providers
- 54.1. Data Access Concepts
- 54.2. Data Access Objects
- 54.3. Factories
-
54.4. Data Providers
-
54.4.1. How a Data Provider Works
- 54.4.1.1. Determiner Base Class
- 54.4.1.2. SqlOrAccessDeterminer.vb
- 54.4.1.3. ProviderDeterminers.dll.config
- 54.4.1.4. Providers.dll.config configuration file
- 54.4.1.5. DataProvider.vb class
- 54.4.1.6. DpAddress.vb Data Provider
- 54.4.1.7. DpImplementation.vb Base Class
- 54.4.1.8. ImplAddress.vb Base Class
- 54.4.1.9. Address.vb - SQL Server Implementation
- 54.4.1.10. Data Provider MainTester
- 54.4.2. Pros of the Data Provider Pattern
- 54.4.3. Cons of the Data Provider Pattern
-
54.4.1. How a Data Provider Works
- 54.5. Summary
- VII. Appendixes
Product information
- Title: SQL Server™ 2005 Bible
- Author(s):
- Release date: November 2006
- Publisher(s): Wiley
- ISBN: 9780764542565
You might also like
book
SQL Server 2005 Practical Troubleshooting: The Database Engine
Never-Before-Published Insiders’ Information for Troubleshooting SQL Server 2005. This is the definitive guide to troubleshooting the …
book
SQL Server 2005 Distilled
Need to get your arms around Microsoft SQL Server 2005 fast, without getting buried in the …
book
Professional SQL Server™ 2005 Programming
Professional SQL Server 2005 Programming shows experienced developers how to master the substantially revamped feature set …
book
A Developer’s Guide to Data Modeling for SQL Server: Covering SQL Server 2005 and 2008
“ explains the concepts and practice of data modeling with a clarity that makes the technology …