Book description
Harness the power of SQL Server, Microsoft’s high-performance database and data analysis software package, by accesing everything you need to know in Microsoft SQL Server 2008 Bible. Learn the best practices, tips, and tricks from this comprehensive tutorial and reference, which includes specific examples and sample code, with nearly every task demonstrated in both a graphical and SQL code method. Understand how to develop SQL Server databases and data connections, how to administer the SQL Server and keep databases performing optimally, and how to navigate all the new features of the 2008 release.
Table of contents
- Copyright
- About Paul Nielsen
- Credits
- Acknowledgments
- Foreword
- Introduction
-
I. Laying the Foundation
- 1. The World of SQL Server
- 2. Data Architecture
- 3. Relational Database Design
-
4. Installing SQL Server 2008
- 4.1. Selecting Server Hardware
- 4.2. Preparing the Server
-
4.3. Performing the Installation
-
4.3.1. Attended installations
- 4.3.1.1. Setup Support Rules and Support Files pages
- 4.3.1.2. Installation Type page
- 4.3.1.3. Product Key and License Terms pages
- 4.3.1.4. Feature Selection page
- 4.3.1.5. Instance Configuration page
- 4.3.1.6. Disk Space Requirements page
- 4.3.1.7. Server Configuration page
- 4.3.1.8. Database Engine Configuration page
- 4.3.1.9. Analysis Services Configuration page
- 4.3.1.10. Reporting Services Configuration page
- 4.3.1.11. Error and Usage Reporting page
- 4.3.1.12. Installation Rules and Ready to Install pages
- 4.3.2. Unattended installations
- 4.3.3. Remote installations
-
4.3.1. Attended installations
- 4.4. Upgrading from Previous Versions
- 4.5. Migrating to SQL Server
- 4.6. Removing SQL Server
- 4.7. Summary
- 5. Client Connectivity
- 6. Using Management Studio
- 7. Scripting with PowerShell
-
II. Manipulating Data with Select
- 8. Introducing Basic Query Flow
- 9. Data Types, Expressions, and Scalar Functions
- 10. Merging Data with Joins and Unions
- 11. Including Data with Subqueries and CTEs
- 12. Aggregating Data
- 13. Windowing and Ranking
- 14. Projecting Data Through Views
- 15. Modifying Data
-
16. Modification Obstacles
- 16.1. Data Type/Length
- 16.2. Primary Key Constraint and Unique Constraint
- 16.3. Deleting Duplicate Rows
- 16.4. Foreign Key Constraints
- 16.5. Null and Default Constraints
- 16.6. Check Constraints
- 16.7. Instead of Triggers
- 16.8. After Triggers
- 16.9. Non-Updateable Views
- 16.10. Views With Check Option
- 16.11. Calculated Columns
- 16.12. Security Constraints
- 16.13. Summary
-
III. Beyond Relational
-
17. Traversing Hierarchies
- 17.1. Adjacency List Pattern
- 17.2. The Materialized-Path Pattern
- 17.3. Using the New HierarchyID
- 17.4. Summary
-
18. Manipulating XML Data
- 18.1. XML Processing in SQL Server 2008
- 18.2. Sample Tables and Data
- 18.3. XML Data Type
- 18.4. Understanding XML Data Type Methods
- 18.5. Generating XML Output Using FOR XML
- 18.6. Understanding XQuery and FLWOR operations
- 18.7. Understanding XQuery Functions
- 18.8. Performing XML Data Modification
- 18.9. Handling Namespaces
- 18.10. Shredding XML Using OPENXML()
- 18.11. XSD and XML Schema Collections
- 18.12. Understanding XML Indexes
- 18.13. XML Best Practices
- 18.14. Summary
- 19. Using Integrated Full-Text Search
-
17. Traversing Hierarchies
-
IV. Developing with SQL Server
-
20. Creating the Physical Database Schema
- 20.1. Designing the Physical Database Schema
- 20.2. Creating the Database
- 20.3. Creating Tables
- 20.4. Creating Keys
- 20.5. Creating User-Data Columns
-
20.6. Creating Indexes
- 20.6.1. Composite indexes
- 20.6.2. Primary keys
- 20.6.3. Filegroup location
- 20.6.4. Index options
- 20.6.5. Include columns
-
20.6.6. Filtered indexes
- 20.6.6.1. Index fill factor and pad index
- 20.6.6.2. Limiting index locks and parallelism
- 20.6.6.3. Index sort order
- 20.6.6.4. The Ignore Dup Key index option
- 20.6.6.5. The Drop Existing index option
- 20.6.6.6. The Statistics Norecompute index option
- 20.6.6.7. Sort in tempdb
- 20.6.6.8. Disabling an index
- 20.7. Summary
- 21. Programming with T-SQL
- 22. Kill the Cursor!
- 23. T-SQL Error Handling
- 24. Developing Stored Procedures
- 25. Building User-Defined Functions
- 26. Creating DML Triggers
- 27. DDL Triggers
- 28. Building Out the Data Abstraction Layer
- 29. Dynamic SQL and Code Generation
-
20. Creating the Physical Database Schema
-
V. Data Connectivity
- 30. Bulk Operations
-
31. Executing Distributed Queries
- 31.1. Distributed Query Concepts
- 31.2. Accessing a Local SQL Server Database
- 31.3. Linking to External Data Sources
- 31.4. Developing Distributed Queries
- 31.5. Distributed Transactions
- 31.6. Summary
-
32. Programming with ADO.NET 3.5
- 32.1. An Overview of ADO.NET
- 32.2. ADO.NET in Visual Studio 2008
-
32.3. Application Building Basics
- 32.3.1. Connecting to SQL Server
- 32.3.2. What's new in ADO.NET 3.5
- 32.3.3. Stored procedures vs. parameterized/ad-hoc queries
- 32.3.4. Data adapters
- 32.3.5. DataReaders and Recordsets
- 32.3.6. Streams
- 32.3.7. Asynchronous execution
- 32.3.8. Using a single database value
- 32.3.9. Data modification
- 32.3.10. Binding to controls
- 32.4. Summary
- 33. Sync Framework
- 34. LINQ
- 35. Asynchronous Messaging with Service Broker
-
36. Replicating Data
- 36.1. Replication Concepts
-
36.2. Configuring Replication
- 36.2.1. Creating a publisher and distributor
- 36.2.2. Creating a snapshot/transactional publication
- 36.2.3. Creating a push subscription to a transactional/snapshot publication
- 36.2.4. Creating a pull subscription to a transactional/snapshot publication
- 36.2.5. Creating a peer-to-peer topology
- 36.2.6. Creating a merge publication
- 36.2.7. Web synchronization
- 36.3. Summary
- 37. Performing ETL with Integration Services
- 38. Access as a Front End to SQL Server
-
VI. Enterprise Data Management
-
39. Configuring SQL Server
- 39.1. Setting the Options
-
39.2. Configuration Options
- 39.2.1. Displaying the advanced options
- 39.2.2. Start/Stop configuration properties
- 39.2.3. Memory-configuration properties
- 39.2.4. Processor-configuration properties
- 39.2.5. Security-configuration properties
- 39.2.6. Connection-configuration properties
- 39.2.7. Advanced server-configuration properties
- 39.2.8. Configuring database auto options
- 39.2.9. Cursor-configuration properties
- 39.2.10. SQL ANSI–configuration properties
- 39.2.11. Trigger configuration properties
- 39.2.12. Database-state-configuration properties
- 39.2.13. Recovery-configuration properties
- 39.3. Summary
- 40. Policy-Based Management
- 41. Recovery Planning
- 42. Maintaining the Database
- 43. Automating Database Maintenance with SQL Server Agent
- 44. Transferring Databases
- 45. Database Snapshots
- 46. Log Shipping
- 47. Database Mirroring
-
48. Clustering
- 48.1. SQL Server 2008 Failover Clustering Basics
- 48.2. Enhancements in SQL Server 2008 Failover Clustering
-
48.3. SQL Server 2008 Failover Clustering Setup
- 48.3.1. Planning SQL Server 2008 failover clustering
- 48.3.2. SQL Server 2008 prerequisites
- 48.3.3. Creating a single-node SQL Server 2008 failover cluster
- 48.3.4. Adding a node to an existing SQL Server 2008 failover cluster
- 48.3.5. Post-installation tasks
- 48.3.6. Uninstalling a SQL Server 2008 failover cluster
- 48.3.7. Installing a failover cluster using a command prompt
- 48.3.8. Rolling upgrade and patching
- 48.3.9. Maintaining a SQL Server 2008 failover cluster
- 48.3.10. Troubleshooting a SQL Server 2008 failover cluster
- 48.4. Summary
-
39. Configuring SQL Server
-
VII. Security
- 49. Authenticating Principals
-
50. Authorizing Securables
- 50.1. Object Ownership
-
50.2. Object Security
- 50.2.1. Standard database roles
- 50.2.2. Object permissions
- 50.2.3. Granting object permissions with code
- 50.2.4. Revoking and denying object permission with code
- 50.2.5. The public role
- 50.2.6. Managing roles with code
- 50.2.7. Hierarchical role structures
- 50.2.8. Object security and Management Studio
- 50.2.9. Stored procedure execute as
- 50.3. A Sample Security Model Example
- 50.4. Views and Security
- 50.5. Summary
- 51. Data Cryptography
- 52. Row-Level Security
-
VIII. Monitoring and Auditing
- 53. Data Audit Triggers
- 54. Schema Audit Triggers
- 55. Performance Monitor
- 56. Tracing and Profiling
- 57. Wait States
- 58. Extended Events
- 59. Change Tracking
- 60. Change Data Capture
- 61. SQL Audit
- 62. Management Data Warehouse
-
IX. Performance Tuning and Optimization
- 63. Interpreting Query Execution Plans
-
64. Indexing Strategies
- 64.1. Zen and the Art of Indexing
- 64.2. Indexing Basics
-
64.3. Path of the Query
- 64.3.1. Query Path 1: Fetch All
- 64.3.2. Query Path 2: Clustered Index Seek
- 64.3.3. Query Path 3: Range Seek Query
- 64.3.4. Query Path 4: Filter by non-key column
- 64.3.5. Query Path 5: Bookmark Lookup
- 64.3.6. Query Path 6: Covering Index
- 64.3.7. Query Path 7: Filter by 2 x NC Indexes
- 64.3.8. Query Path 8: Filter by Ordered Composite Index
- 64.3.9. Query Path 9: Filter by Unordered Composite Index
- 64.3.10. Query Path 10: Non-SARGable Expressions
- 64.4. A Comprehensive Indexing Strategy
- 64.5. Specialty Indexes
- 64.6. Summary
- 65. Query Plan Reuse
-
66. Managing Transactions, Locking, and Blocking
- 66.1. The ACID Properties
- 66.2. Programming Transactions
- 66.3. Default Locking and Blocking Behavior
- 66.4. Monitoring Locking and Blocking
- 66.5. Deadlocks
- 66.6. Understanding SQL Server Locking
- 66.7. Transaction Isolation Levels
- 66.8. Application Locks
- 66.9. Application Locking Design
- 66.10. Transaction-Log Architecture
- 66.11. Transaction Performance Strategies
- 66.12. Summary
- 67. Data Compression
-
68. Partitioning
- 68.1. Partitioning Strategies
- 68.2. Partitioned Views
- 68.3. Partitioned Tables and Indexes
- 68.4. Data-Driven Partitioning
- 68.5. Summary
- 69. Resource Governor
-
X. Business Intelligence
- 70. BI Design
-
71. Building Multidimensional Cubes with Analysis Services
- 71.1. Analysis Services Quick Start
- 71.2. Analysis Services Architecture
- 71.3. Building a Database
- 71.4. Dimensions
- 71.5. Cubes
- 71.6. Data Storage
- 71.7. Data Integrity
- 71.8. Summary
- 72. Programming MDX Queries
-
73. Authoring Reports with Reporting Services
- 73.1. Anatomy of a Report
- 73.2. The Report Authoring Process
- 73.3. Working with Data
- 73.4. Designing the Report Layout
- 73.5. Summary
-
74. Administering Reporting Services
- 74.1. Deploying Reporting Services Reports
- 74.2. Configuring Reporting Services Using Management Studio
- 74.3. Configuring Reporting Services Using Report Manager
- 74.4. Summary
- 75. Analyzing Data with Excel
- 76. Data Mining with Analysis Services
- A. SQL Server 2008 Specifications
- B. Using the Sample Databases
Product information
- Title: Microsoft® SQL Server® 2008 Bible
- Author(s):
- Release date: August 2009
- Publisher(s): Wiley
- ISBN: 9780470257043
You might also like
book
Professional Microsoft® SQL Server® 2008 Administration
SQL Server 2008 represents a sizable jump forward in scalability, performance, and usability for the DBA, …
book
Microsoft® SQL Server™ 2000 Unleashed, Second Edition
Microsoft SQL Server 2000 Unleashed, 2E offers a variety of topics for system and database administrators …
book
Microsoft® SQL Server 2005 Unleashed
Microsoft SQL Server 2005 Unleashed offers comprehensive coverage of SQL Server 2005 that goes beyond the …
book
Microsoft® SQL Server® 2012 Integration Services
Build and manage data integration solutions with expert guidance from the Microsoft SQL Server Integration Services …