Microsoft® SQL Server® 2008 Bible

Microsoft® SQL Server® 2008 Bible

Released August 2009
Publisher(s): Wiley
ISBN: 9780470257043

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

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

  1. Copyright
  2. About Paul Nielsen
    1. About the Contributing Authors
    2. About the Technical Reviewers
  3. Credits
  4. Acknowledgments
  5. Foreword
  6. Introduction
    1. Who Should Read This Book
    2. How This Book Is Organized
    3. SQL Server Books Online
    4. Conventions and Features
      1. Tips, Notes, Cautions, and Cross-References
      2. What's New and Best Practice Sidebars
    5. www.SQLServerBible.com
    6. Where to Go from Here
  7. I. Laying the Foundation
    1. 1. The World of SQL Server
      1. 1.1. A Great Choice
      2. 1.2. SQL Server Database Engine
        1. 1.2.1. Database Engine
        2. 1.2.2. Transact-SQL
        3. 1.2.3. Policy-Based Management
        4. 1.2.4. .NET Common Language Runtime
        5. 1.2.5. Service Broker
        6. 1.2.6. Replication services
        7. 1.2.7. Integrated Full-Text Search
        8. 1.2.8. Server management objects
        9. 1.2.9. Filestream
      3. 1.3. SQL Server Services
        1. 1.3.1. SQL Server Agent
        2. 1.3.2. Database Mail
        3. 1.3.3. Distributed Transaction Coordinator (DTC)
      4. 1.4. Business Intelligence
        1. 1.4.1. Business Intelligence Development Studio
        2. 1.4.2. Integration Services
        3. 1.4.3. Analysis Services
          1. 1.4.3.1. OLAP
          2. 1.4.3.2. Data Mining
        4. 1.4.4. Reporting Services
      5. 1.5. UI and Tools
        1. 1.5.1. SQL Server Management Studio
        2. 1.5.2. SQL Server Configuration Manager
        3. 1.5.3. SQL Profiler/Trace
        4. 1.5.4. Performance Monitor
        5. 1.5.5. Command-line utilities
        6. 1.5.6. Books Online
      6. 1.6. SQL Server Editions
      7. 1.7. Exploring the Metadata
        1. 1.7.1. System databases
        2. 1.7.2. Metadata views
      8. 1.8. What's New?
      9. 1.9. Summary
    2. 2. Data Architecture
      1. 2.1. Information Architecture Principle
      2. 2.2. Database Objectives
        1. 2.2.1. Usability
        2. 2.2.2. Extensibility
        3. 2.2.3. Data integrity
        4. 2.2.4. Performance/scalability
        5. 2.2.5. Availability
        6. 2.2.6. Security
      3. 2.3. Smart Database Design
        1. 2.3.1. Database system
        2. 2.3.2. Physical schema
        3. 2.3.3. Set-based queries
        4. 2.3.4. Indexing
        5. 2.3.5. Concurrency
        6. 2.3.6. Advanced scalability
        7. 2.3.7. A performance framework
        8. 2.3.8. Issues and objections
      4. 2.4. Summary
    3. 3. Relational Database Design
      1. 3.1. Database Basics
        1. 3.1.1. Benefits of a digital database
        2. 3.1.2. Tables, rows, columns
        3. 3.1.3. Database design phases
        4. 3.1.4. Normalization
        5. 3.1.5. The three "Rules of One"
        6. 3.1.6. Identifying entities
        7. 3.1.7. Generalization
        8. 3.1.8. Primary keys
        9. 3.1.9. Foreign keys
        10. 3.1.10. Cardinality
        11. 3.1.11. Optionality
      2. 3.2. Data Design Patterns
        1. 3.2.1. One-to-many pattern
        2. 3.2.2. One-to-one pattern
        3. 3.2.3. Many-to-many pattern
        4. 3.2.4. Supertype/subtype pattern
        5. 3.2.5. Domain integrity lookup pattern
        6. 3.2.6. Recursive pattern
        7. 3.2.7. Database design layers
      3. 3.3. Normal Forms
        1. 3.3.1. First normal form (1NF)
        2. 3.3.2. Second normal form (2NF)
        3. 3.3.3. Third normal form (3NF)
        4. 3.3.4. The Boyce-Codd normal form (BCNF)
        5. 3.3.5. Fourth normal form (4NF)
        6. 3.3.6. Fifth normal form (5NF)
      4. 3.4. Summary
    4. 4. Installing SQL Server 2008
      1. 4.1. Selecting Server Hardware
        1. 4.1.1. CPU planning
        2. 4.1.2. Copious memory
        3. 4.1.3. Disk-drive subsystems
        4. 4.1.4. Network performance
      2. 4.2. Preparing the Server
        1. 4.2.1. Dedicated server
        2. 4.2.2. Operating system
        3. 4.2.3. Service accounts
        4. 4.2.4. Server instances
      3. 4.3. Performing the Installation
        1. 4.3.1. Attended installations
          1. 4.3.1.1. Setup Support Rules and Support Files pages
          2. 4.3.1.2. Installation Type page
          3. 4.3.1.3. Product Key and License Terms pages
          4. 4.3.1.4. Feature Selection page
          5. 4.3.1.5. Instance Configuration page
          6. 4.3.1.6. Disk Space Requirements page
          7. 4.3.1.7. Server Configuration page
          8. 4.3.1.8. Database Engine Configuration page
          9. 4.3.1.9. Analysis Services Configuration page
          10. 4.3.1.10. Reporting Services Configuration page
          11. 4.3.1.11. Error and Usage Reporting page
          12. 4.3.1.12. Installation Rules and Ready to Install pages
        2. 4.3.2. Unattended installations
        3. 4.3.3. Remote installations
      4. 4.4. Upgrading from Previous Versions
        1. 4.4.1. Upgrading from SQL Server 2005
      5. 4.5. Migrating to SQL Server
        1. 4.5.1. Migrating from Access
        2. 4.5.2. Migration Assistant
          1. 4.5.2.1. Assessment
          2. 4.5.2.2. Schema conversion
          3. 4.5.2.3. Data migration
          4. 4.5.2.4. Business logic conversion
          5. 4.5.2.5. Validation and Integration
      6. 4.6. Removing SQL Server
      7. 4.7. Summary
    5. 5. Client Connectivity
      1. 5.1. Enabling Server Connectivity
        1. 5.1.1. Server Configuration Manager
        2. 5.1.2. SQL Native Client Connectivity (SNAC)
      2. 5.2. SQL Server Native Client Features
        1. 5.2.1. Requirements
        2. 5.2.2. Asynchronous operations
        3. 5.2.3. Multiple Active Result Sets (MARS)
        4. 5.2.4. XML data types
        5. 5.2.5. User-defined types
        6. 5.2.6. Large value types
        7. 5.2.7. Handling expired passwords
        8. 5.2.8. Snapshot isolation
      3. 5.3. Summary
    6. 6. Using Management Studio
      1. 6.1. Organizing the Interface
        1. 6.1.1. Window placement
        2. 6.1.2. The Context Menu
      2. 6.2. Registered Servers
        1. 6.2.1. Managing Servers
        2. 6.2.2. Server Groups
      3. 6.3. Object Explorer
        1. 6.3.1. Navigating the tree
        2. 6.3.2. Filtering Object Explorer
        3. 6.3.3. Object Explorer Details
        4. 6.3.4. The Table Designer
        5. 6.3.5. Building database diagrams
        6. 6.3.6. The Query Designer
        7. 6.3.7. Object Explorer reports
      4. 6.4. Using the Query Editor
        1. 6.4.1. Opening a query connecting to a server
        2. 6.4.2. Opening a .sql file
        3. 6.4.3. Shortcuts and bookmarks
        4. 6.4.4. Query options
        5. 6.4.5. Executing SQL batches
        6. 6.4.6. Results!
        7. 6.4.7. Viewing query execution plans
      5. 6.5. Using the Solution Explorer
      6. 6.6. Jump-Starting Code with Templates
        1. 6.6.1. Using templates
        2. 6.6.2. Managing templates
      7. 6.7. Summary
    7. 7. Scripting with PowerShell
      1. 7.1. Why Use PowerShell?
      2. 7.2. Basic PowerShell
        1. 7.2.1. Language features
        2. 7.2.2. Creating scripts
      3. 7.3. Communicating with SQL Server
        1. 7.3.1. SQL Server Management Objects
        2. 7.3.2. ADO.NET
      4. 7.4. Scripting SQL Server Tasks
        1. 7.4.1. Administrative tasks
        2. 7.4.2. Data-based tasks
      5. 7.5. SQL Server PowerShell Extensions
        1. 7.5.1. SQLPS.exe
        2. 7.5.2. The SQL PSDrive—SQLSERVER:
        3. 7.5.3. SQL cmdlets
      6. 7.6. Summary
  8. II. Manipulating Data with Select
    1. 8. Introducing Basic Query Flow
      1. 8.1. Understanding Query Flow
        1. 8.1.1. Syntactical flow of the query statement
        2. 8.1.2. A graphical view of the query statement
        3. 8.1.3. Logical flow of the query statement
        4. 8.1.4. Physical flow of the query statement
      2. 8.2. From Clause Data Sources
        1. 8.2.1. Possible data sources
        2. 8.2.2. Table aliases
        3. 8.2.3. [Table Name]
        4. 8.2.4. Fully qualified names
      3. 8.3. Where Conditions
        1. 8.3.1. Using the between search condition
        2. 8.3.2. Comparing with a list
        3. 8.3.3. Using the like search condition
        4. 8.3.4. Multiple where conditions
        5. 8.3.5. Select...where
      4. 8.4. Columns, Stars, Aliases, and Expressions
        1. 8.4.1. The star
        2. 8.4.2. Aliases
        3. 8.4.3. Qualified columns
      5. 8.5. Ordering the Result Set
        1. 8.5.1. Specifying the order by using column names
        2. 8.5.2. Specifying the order by using expressions
        3. 8.5.3. Specifying the order by using column aliases
        4. 8.5.4. Using the column ordinal position
        5. 8.5.5. Order by and collation
      6. 8.6. Select Distinct
      7. 8.7. Top ()
        1. 8.7.1. The with ties option
        2. 8.7.2. Selecting a random row
      8. 8.8. Summary
    2. 9. Data Types, Expressions, and Scalar Functions
      1. 9.1. Building Expressions
        1. 9.1.1. Operators
        2. 9.1.2. Bitwise operators
          1. 9.1.2.1. Boolean and
          2. 9.1.2.2. Boolean or
          3. 9.1.2.3. Boolean exclusive or
          4. 9.1.2.4. Bitwise not
        3. 9.1.3. Case expressions
          1. 9.1.3.1. Simple case
          2. 9.1.3.2. Boolean case
        4. 9.1.4. Working with nulls
          1. 9.1.4.1. Testing for null
          2. 9.1.4.2. Handling nulls
          3. 9.1.4.3. Using the COALESCE() function
          4. 9.1.4.4. Using the ISNULL() function
          5. 9.1.4.5. Using the NULLIF() function
      2. 9.2. Scalar Functions
        1. 9.2.1. User information functions
        2. 9.2.2. Date and time functions
      3. 9.3. String Functions
      4. 9.4. Soundex Functions
        1. 9.4.1. Using the SOUNDEX() function
        2. 9.4.2. Using the DIFFERENCE() Soundex function
      5. 9.5. Data-Type Conversion Functions
      6. 9.6. Server Environment Information
      7. 9.7. Summary
    3. 10. Merging Data with Joins and Unions
      1. 10.1. Using Joins
      2. 10.2. Inner Joins
        1. 10.2.1. Building inner joins with the Query Designer
        2. 10.2.2. Creating inner joins within SQL code
        3. 10.2.3. Number of rows returned
        4. 10.2.4. ANSI SQL 89 joins
        5. 10.2.5. Multiple data source joins
      3. 10.3. Outer Joins
        1. 10.3.1. Using the Query Designer to create outer joins
        2. 10.3.2. T-SQL code and outer joins
        3. 10.3.3. Outer joins and optional foreign keys
        4. 10.3.4. Full outer joins
        5. 10.3.5. Red thing blue thing
        6. 10.3.6. Placing the conditions within outer joins
        7. 10.3.7. Multiple outer joins
      4. 10.4. Self-Joins
      5. 10.5. Cross (Unrestricted) Joins
      6. 10.6. Exotic Joins
        1. 10.6.1. Multiple-condition joins
        2. 10.6.2. Θ (theta) joins
        3. 10.6.3. Non-key joins
      7. 10.7. Set Difference Queries
        1. 10.7.1. Left set difference query
        2. 10.7.2. Full set difference queries
      8. 10.8. Using Unions
        1. 10.8.1. Union [All]
        2. 10.8.2. Intersection union
        3. 10.8.3. Difference union/except
      9. 10.9. Summary
    4. 11. Including Data with Subqueries and CTEs
      1. 11.1. Methods and Locations
      2. 11.2. Simple Subqueries
        1. 11.2.1. Common table expressions
        2. 11.2.2. Using scalar subqueries
        3. 11.2.3. Using subqueries as lists
        4. 11.2.4. Using subqueries as tables
        5. 11.2.5. Row constructors
        6. 11.2.6. All, some, and any
      3. 11.3. Correlated Subqueries
        1. 11.3.1. Correlating in the where clause
        2. 11.3.2. Correlating a derived table using apply
      4. 11.4. Relational Division
        1. 11.4.1. Relational division with a remainder
        2. 11.4.2. Exact relational division
      5. 11.5. Composable SQL
      6. 11.6. Summary
    5. 12. Aggregating Data
      1. 12.1. Simple Aggregations
        1. 12.1.1. Basic aggregations
        2. 12.1.2. Aggregates, averages, and nulls
        3. 12.1.3. Using aggregate functions within the Query Designer
        4. 12.1.4. Beginning statistics
      2. 12.2. Grouping within a Result Set
        1. 12.2.1. Simple groupings
        2. 12.2.2. Grouping sets
        3. 12.2.3. Filtering grouped results
      3. 12.3. Aggravating Queries
        1. 12.3.1. Including group by descriptions
        2. 12.3.2. Including all group by values
        3. 12.3.3. Nesting aggregations
        4. 12.3.4. Including detail descriptions
      4. 12.4. OLAP in the Park
        1. 12.4.1. Rollup subtotals
        2. 12.4.2. Cube queries
      5. 12.5. Building Crosstab Queries
        1. 12.5.1. Pivot method
        2. 12.5.2. Case expression method
        3. 12.5.3. Dynamic crosstab queries
        4. 12.5.4. Unpivot
      6. 12.6. Cumulative Totals (Running Sums)
        1. 12.6.1. Correlated subquery solution
        2. 12.6.2. T-SQL cursor solution
        3. 12.6.3. Multiple assignment variable solution
      7. 12.7. Summary
    6. 13. Windowing and Ranking
      1. 13.1. Windowing
        1. 13.1.1. The Over() clause
        2. 13.1.2. Partitioning within the window
      2. 13.2. Ranking Functions
        1. 13.2.1. Row number() function
        2. 13.2.2. Rank() and dense_rank() functions
        3. 13.2.3. Ntile() function
        4. 13.2.4. Aggregate Functions
      3. 13.3. Summary
    7. 14. Projecting Data Through Views
      1. 14.1. Why Use Views?
      2. 14.2. The Basic View
        1. 14.2.1. Creating views using the Query Designer
        2. 14.2.2. Creating views with DDL code
        3. 14.2.3. Executing views
        4. 14.2.4. Altering and dropping a view
      3. 14.3. A Broader Point of View
        1. 14.3.1. Column aliases
        2. 14.3.2. Order by and views
        3. 14.3.3. View restrictions
        4. 14.3.4. Nesting views
        5. 14.3.5. Updating through views
        6. 14.3.6. Views and performance
      4. 14.4. Locking Down the View
        1. 14.4.1. Unchecked data
        2. 14.4.2. Protecting the data
        3. 14.4.3. Protecting the view
        4. 14.4.4. Encrypting the view's select statement
        5. 14.4.5. Application metadata
      5. 14.5. Using Synonyms
      6. 14.6. Summary
    8. 15. Modifying Data
      1. 15.1. Inserting Data
        1. 15.1.1. Inserting simple rows of values
        2. 15.1.2. Inserting a result set from select
        3. 15.1.3. Inserting the result set from a stored procedure
        4. 15.1.4. Creating a default row
        5. 15.1.5. Creating a table while inserting data
      2. 15.2. Updating Data
        1. 15.2.1. Updating a single table
        2. 15.2.2. Performing global search and replace
        3. 15.2.3. Referencing multiple tables while updating data
      3. 15.3. Deleting Data
        1. 15.3.1. Referencing multiple data sources while deleting
        2. 15.3.2. Cascading deletes
        3. 15.3.3. Alternatives to physically deleting data
      4. 15.4. Merging Data
      5. 15.5. Returning Modified Data
        1. 15.5.1. Returning data from an insert
        2. 15.5.2. Returning data from an update
        3. 15.5.3. Returning data from a delete
        4. 15.5.4. Returning data from a merge
        5. 15.5.5. Returning data into a table
      6. 15.6. Summary
    9. 16. Modification Obstacles
      1. 16.1. Data Type/Length
      2. 16.2. Primary Key Constraint and Unique Constraint
        1. 16.2.1. Identity columns
        2. 16.2.2. Globally unique identifiers (GUIDs)
      3. 16.3. Deleting Duplicate Rows
        1. 16.3.1. Deleting duplicate rows using windowing
        2. 16.3.2. Deleting duplicate rows using a surrogate key
        3. 16.3.3. Deleting duplicate rows using select distant into
      4. 16.4. Foreign Key Constraints
      5. 16.5. Null and Default Constraints
      6. 16.6. Check Constraints
      7. 16.7. Instead of Triggers
      8. 16.8. After Triggers
      9. 16.9. Non-Updateable Views
      10. 16.10. Views With Check Option
      11. 16.11. Calculated Columns
      12. 16.12. Security Constraints
      13. 16.13. Summary
  9. III. Beyond Relational
    1. 17. Traversing Hierarchies
      1. 17.1. Adjacency List Pattern
        1. 17.1.1. Single-level queries
        2. 17.1.2. Subtree queries
          1. 17.1.2.1. Recursive CTE down the hierarchy
          2. 17.1.2.2. User-defined function down the hierarchy
          3. 17.1.2.3. Recursive CTE looking up the hierarchy
          4. 17.1.2.4. Searching up the hierarchy with a user-defined function
        3. 17.1.3. Is the node an ancestor?
        4. 17.1.4. Determining the node's level
        5. 17.1.5. Reparenting the adjacency list
        6. 17.1.6. Indexing an adjacency list
        7. 17.1.7. Cyclic errors
        8. 17.1.8. Adjacency list variations
          1. 17.1.8.1. Bills of materials/multiple cardinalities
        9. 17.1.9. Adjacency list pros and cons
      2. 17.2. The Materialized-Path Pattern
        1. 17.2.1. Subtree queries
          1. 17.2.1.1. Searching down the hierarchy with materialized path
          2. 17.2.1.2. Searching up the hierarchy with materialized path
        2. 17.2.2. Is the node in the subtree?
        3. 17.2.3. Determining the node level
        4. 17.2.4. Single-level queries
        5. 17.2.5. Reparenting the materialized path
        6. 17.2.6. Indexing the materialized path
        7. 17.2.7. Materialized path pros and cons
      3. 17.3. Using the New HierarchyID
        1. 17.3.1. Selecting a single node
        2. 17.3.2. Scanning for ancestors
        3. 17.3.3. Performing a subtree search
        4. 17.3.4. Single-level searches
        5. 17.3.5. Inserting new nodes
        6. 17.3.6. Performance
        7. 17.3.7. HierarchyID pros and cons
      4. 17.4. Summary
    2. 18. Manipulating XML Data
      1. 18.1. XML Processing in SQL Server 2008
        1. 18.1.1. Generating XML documents
        2. 18.1.2. Querying XML documents
        3. 18.1.3. Validating XML documents
      2. 18.2. Sample Tables and Data
      3. 18.3. XML Data Type
        1. 18.3.1. Typed and untyped XML
        2. 18.3.2. Creating and using XML columns
        3. 18.3.3. Declaring and using XML variables
        4. 18.3.4. Using XML parameters and return values
        5. 18.3.5. Loading/querying XML documents from disk files
        6. 18.3.6. Limitations of the XML data type
      4. 18.4. Understanding XML Data Type Methods
        1. 18.4.1. XPath
        2. 18.4.2. value()
        3. 18.4.3. nodes()
        4. 18.4.4. exist()
        5. 18.4.5. query()
        6. 18.4.6. modify()
        7. 18.4.7. Joining XML nodes with relational tables
        8. 18.4.8. Using variables and filters in XQuery expressions
        9. 18.4.9. Accessing the parent node
      5. 18.5. Generating XML Output Using FOR XML
        1. 18.5.1. FOR XML AUTO
        2. 18.5.2. FOR XML RAW
        3. 18.5.3. FOR XML EXPLICIT
        4. 18.5.4. FOR XML PATH
        5. 18.5.5. TYPE directive
        6. 18.5.6. XSINIL Directive
        7. 18.5.7. Generating XML Schema information
        8. 18.5.8. Generating XML namespaces
      6. 18.6. Understanding XQuery and FLWOR operations
        1. 18.6.1. Simple queries
        2. 18.6.2. FLWOR operation
        3. 18.6.3. What's new for XQuery in SQL Server 2008
      7. 18.7. Understanding XQuery Functions
        1. 18.7.1. String functions
        2. 18.7.2. Numeric and aggregate functions
        3. 18.7.3. Other functions
      8. 18.8. Performing XML Data Modification
        1. 18.8.1. Insert operation
        2. 18.8.2. Update operation
        3. 18.8.3. Delete operation
        4. 18.8.4. What's new for XML DML operations in SQL Server 2008
      9. 18.9. Handling Namespaces
      10. 18.10. Shredding XML Using OPENXML()
      11. 18.11. XSD and XML Schema Collections
        1. 18.11.1. Creating an XML Schema collection
        2. 18.11.2. Creating typed XML columns and variables
        3. 18.11.3. Performing validation
        4. 18.11.4. XML DOCUMENT and CONTENT
        5. 18.11.5. Altering XML Schema collections
        6. 18.11.6. What's in the "collection"?
        7. 18.11.7. What's new in SQL Server 2008 for XSD
      12. 18.12. Understanding XML Indexes
      13. 18.13. XML Best Practices
      14. 18.14. Summary
    3. 19. Using Integrated Full-Text Search
      1. 19.1. Configuring Full-Text Search Catalogs
        1. 19.1.1. Creating a catalog with the wizard
        2. 19.1.2. Creating a catalog with T-SQL code
        3. 19.1.3. Pushing data to the full-text index
        4. 19.1.4. Maintaining a catalog with Management Studio
        5. 19.1.5. Maintaining a catalog in T-SQL code
      2. 19.2. Word Searches
        1. 19.2.1. The Contains function
        2. 19.2.2. The ContainsTable function
      3. 19.3. Advanced Search Options
        1. 19.3.1. Multiple-word searches
        2. 19.3.2. Searches with wildcards
        3. 19.3.3. Phrase searches
        4. 19.3.4. Word-proximity searches
        5. 19.3.5. Word-inflection searches
        6. 19.3.6. Thesaurus searches
        7. 19.3.7. Variable-word-weight searches
      4. 19.4. Fuzzy Searches
        1. 19.4.1. Freetext
        2. 19.4.2. FreetextTable
      5. 19.5. Performance
      6. 19.6. Summary
  10. IV. Developing with SQL Server
    1. 20. Creating the Physical Database Schema
      1. 20.1. Designing the Physical Database Schema
        1. 20.1.1. Logical to physical options
        2. 20.1.2. Refining the data patterns
        3. 20.1.3. Designing for performance
        4. 20.1.4. Responsible denormalization
        5. 20.1.5. Designing for extensibility
      2. 20.2. Creating the Database
        1. 20.2.1. The Create DDL command
        2. 20.2.2. Database-file concepts
        3. 20.2.3. Configuring file growth
        4. 20.2.4. Using multiple files
          1. 20.2.4.1. Creating a database with multiple files
          2. 20.2.4.2. Modifying the files of an existing database
        5. 20.2.5. Planning multiple filegroups
          1. 20.2.5.1. Creating a database with filegroups
          2. 20.2.5.2. Modifying filegroups
          3. 20.2.5.3. Dropping a database
      3. 20.3. Creating Tables
        1. 20.3.1. Designing tables using Management Studio
        2. 20.3.2. Working with SQL scripts
        3. 20.3.3. Schemas
        4. 20.3.4. Column names
        5. 20.3.5. Filegroups
      4. 20.4. Creating Keys
        1. 20.4.1. Primary keys
        2. 20.4.2. The surrogate debate: pros and cons
        3. 20.4.3. Database design layers
          1. 20.4.3.1. Creating primary keys
          2. 20.4.3.2. Identity column surrogate primary keys
          3. 20.4.3.3. Using uniqueidentifier surrogate primary keys
        4. 20.4.4. Creating foreign keys
          1. 20.4.4.1. Declarative referential integrity
          2. 20.4.4.2. Optional foreign keys
          3. 20.4.4.3. Cascading deletes and updates
      5. 20.5. Creating User-Data Columns
        1. 20.5.1. Column data types
          1. 20.5.1.1. Character data types
          2. 20.5.1.2. Numeric data types
          3. 20.5.1.3. Date/Time data types
          4. 20.5.1.4. Other data types
        2. 20.5.2. Calculated columns
        3. 20.5.3. Sparse columns
        4. 20.5.4. Column constraints and defaults
          1. 20.5.4.1. Column nullability
      6. 20.6. Creating Indexes
        1. 20.6.1. Composite indexes
        2. 20.6.2. Primary keys
        3. 20.6.3. Filegroup location
        4. 20.6.4. Index options
          1. 20.6.4.1. Unique indexes
        5. 20.6.5. Include columns
        6. 20.6.6. Filtered indexes
          1. 20.6.6.1. Index fill factor and pad index
          2. 20.6.6.2. Limiting index locks and parallelism
          3. 20.6.6.3. Index sort order
          4. 20.6.6.4. The Ignore Dup Key index option
          5. 20.6.6.5. The Drop Existing index option
          6. 20.6.6.6. The Statistics Norecompute index option
          7. 20.6.6.7. Sort in tempdb
          8. 20.6.6.8. Disabling an index
      7. 20.7. Summary
    2. 21. Programming with T-SQL
      1. 21.1. Transact-SQL Fundamentals
        1. 21.1.1. T-SQL batches
          1. 21.1.1.1. Terminating a batch
          2. 21.1.1.2. DDL commands
          3. 21.1.1.3. Switching databases
          4. 21.1.1.4. Executing batches
        2. 21.1.2. T-SQL formatting
          1. 21.1.2.1. Statement termination
          2. 21.1.2.2. Line continuation
          3. 21.1.2.3. Comments
      2. 21.2. Variables
        1. 21.2.1. Variable default and scope
        2. 21.2.2. Using the set and select commands
        3. 21.2.3. Incrementing variables
        4. 21.2.4. Conditional select
        5. 21.2.5. Using variables within SQL queries
        6. 21.2.6. Multiple assignment variables
      3. 21.3. Procedural Flow
        1. 21.3.1. If
          1. 21.3.1.1. Begin/end
          2. 21.3.1.2. If exists()
          3. 21.3.1.3. If/else
        2. 21.3.2. While
        3. 21.3.3. Goto
      4. 21.4. Examining SQL Server with Code
        1. 21.4.1. Dynamic Management Views
        2. 21.4.2. sp_help
        3. 21.4.3. System functions
      5. 21.5. Temporary Tables and Table Variables
        1. 21.5.1. Local temporary tables
        2. 21.5.2. Global temporary tables
        3. 21.5.3. Table variables
      6. 21.6. Summary
    3. 22. Kill the Cursor!
      1. 22.1. Anatomy of a Cursor
        1. 22.1.1. The five steps to cursoring
        2. 22.1.2. Managing the cursor
        3. 22.1.3. Watching the cursor
        4. 22.1.4. Cursor options
        5. 22.1.5. Update cursor
        6. 22.1.6. Cursor scope
        7. 22.1.7. Cursors and transactions
      2. 22.2. Cursor Strategies
      3. 22.3. Refactoring Complex-Logic Cursors
        1. 22.3.1. Update query with user-defined function
        2. 22.3.2. Multiple queries
        3. 22.3.3. Query with case expression
      4. 22.4. Summary
    4. 23. T-SQL Error Handling
      1. 23.1. Legacy Error Handling
        1. 23.1.1. @@error system function
        2. 23.1.2. @@rowcount system function
      2. 23.2. Raiserror
        1. 23.2.1. The simple raiserror form
        2. 23.2.2. The improved raiserror form
        3. 23.2.3. Error severity
          1. 23.2.3.1. Adding variable parameters to messages
        4. 23.2.4. Stored messages
          1. 23.2.4.1. Logging the error
          2. 23.2.4.2. SQL Server log
      3. 23.3. Try...Catch
        1. 23.3.1. Catch block
        2. 23.3.2. Nested try/catch and rethrown errors
      4. 23.4. T-SQL Fatal Errors
      5. 23.5. Summary
    5. 24. Developing Stored Procedures
      1. 24.1. Managing Stored Procedures
        1. 24.1.1. Create, alter, and drop
        2. 24.1.2. Executing a stored procedure
        3. 24.1.3. Returning a record set
        4. 24.1.4. Compiling stored procedures
        5. 24.1.5. Stored procedure encryption
        6. 24.1.6. System stored procedures
        7. 24.1.7. Using stored procedures within queries
        8. 24.1.8. Executing remote stored procedures
      2. 24.2. Passing Data to Stored Procedures
        1. 24.2.1. Input parameters
        2. 24.2.2. Parameter defaults
        3. 24.2.3. Table-valued parameters
      3. 24.3. Returning Data from Stored Procedures
        1. 24.3.1. Output parameters
        2. 24.3.2. Using the Return Command
        3. 24.3.3. Path and scope of returning data
      4. 24.4. Summary
    6. 25. Building User-Defined Functions
      1. 25.1. Scalar Functions
        1. 25.1.1. Limitations
        2. 25.1.2. Creating a scalar function
        3. 25.1.3. Calling a scalar function
      2. 25.2. Inline Table-Valued Functions
        1. 25.2.1. Creating an inline table-valued function
        2. 25.2.2. Calling an inline table-valued function
        3. 25.2.3. Using parameters
        4. 25.2.4. Correlated user-defined functions
        5. 25.2.5. Creating functions with schema binding
      3. 25.3. Multi-Statement Table-Valued Functions
        1. 25.3.1. Creating a multi-statement table-valued function
        2. 25.3.2. Calling the function
      4. 25.4. Summary
    7. 26. Creating DML Triggers
      1. 26.1. Trigger Basics
        1. 26.1.1. Transaction flow
        2. 26.1.2. Creating triggers
        3. 26.1.3. After triggers
        4. 26.1.4. Instead of triggers
        5. 26.1.5. Trigger limitations
        6. 26.1.6. Disabling triggers
        7. 26.1.7. Listing triggers
        8. 26.1.8. Triggers and security
      2. 26.2. Working with the Transaction
        1. 26.2.1. Determining the updated columns
        2. 26.2.2. Inserted and deleted logical tables
        3. 26.2.3. Developing multi-row-enabled triggers
      3. 26.3. Multiple-Trigger Interaction
        1. 26.3.1. Trigger organization
        2. 26.3.2. Nested triggers
        3. 26.3.3. Recursive triggers
        4. 26.3.4. Instead of and after triggers
        5. 26.3.5. Multiple after triggers
      4. 26.4. Transaction-Aggregation Handling
        1. 26.4.1. The inventory-transaction trigger
        2. 26.4.2. The inventory trigger
      5. 26.5. Summary
    8. 27. DDL Triggers
      1. 27.1. Managing DDL Triggers
        1. 27.1.1. Creating and altering DDL triggers
        2. 27.1.2. Trigger scope
        3. 27.1.3. DDL triggers and security
        4. 27.1.4. Enabling and disabling DDL triggers
        5. 27.1.5. Removing DDL triggers
      2. 27.2. Developing DDL Triggers
        1. 27.2.1. EventData()
        2. 27.2.2. Preventing database object changes
      3. 27.3. Summary
    9. 28. Building Out the Data Abstraction Layer
      1. 28.1. CRUD Stored Procedures
      2. 28.2. Google-Style Search Procedure
      3. 28.3. Summary
    10. 29. Dynamic SQL and Code Generation
      1. 29.1. Executing Dynamic SQL
        1. 29.1.1. sp_executeSQL
        2. 29.1.2. Parameterized queries
        3. 29.1.3. Developing dynamic SQL code
        4. 29.1.4. Code generation
      2. 29.2. Preventing SQL Injection
        1. 29.2.1. Appending malicious code
        2. 29.2.2. Or 1=1
        3. 29.2.3. Password? What password?
        4. 29.2.4. Preventing SQL Server injection attacks
      3. 29.3. Summary
  11. V. Data Connectivity
    1. 30. Bulk Operations
      1. 30.1. Bulk Insert
      2. 30.2. Bulk Insert Options
      3. 30.3. BCP
      4. 30.4. Summary
    2. 31. Executing Distributed Queries
      1. 31.1. Distributed Query Concepts
      2. 31.2. Accessing a Local SQL Server Database
      3. 31.3. Linking to External Data Sources
        1. 31.3.1. Linking to SQL Server with Management Studio
          1. 31.3.1.1. Selecting the server
          2. 31.3.1.2. Configuring the logins
          3. 31.3.1.3. Configuring the options
        2. 31.3.2. Linking to SQL Server with T-SQL
          1. 31.3.2.1. Establishing the link
          2. 31.3.2.2. Distributed security and logins
          3. 31.3.2.3. Linked server options
        3. 31.3.3. Linking with non–SQL Server data sources
          1. 31.3.3.1. Linking to Excel
          2. 31.3.3.2. Linking to MS Access
      4. 31.4. Developing Distributed Queries
        1. 31.4.1. Distributed queries and Management Studio
        2. 31.4.2. Distributed views
        3. 31.4.3. Local-distributed queries
          1. 31.4.3.1. Using the four-part name
          2. 31.4.3.2. OpenDataSource()
        4. 31.4.4. Pass-through distributed queries
          1. 31.4.4.1. Using the four-part name
          2. 31.4.4.2. OpenQuery()
          3. 31.4.4.3. OpenRowSet()
      5. 31.5. Distributed Transactions
        1. 31.5.1. Distributed Transaction Coordinator
        2. 31.5.2. Developing distributed transactions
        3. 31.5.3. Monitoring distributed transactions
      6. 31.6. Summary
    3. 32. Programming with ADO.NET 3.5
      1. 32.1. An Overview of ADO.NET
        1. 32.1.1. ADO
          1. 32.1.1.1. OLEDB
          2. 32.1.1.2. ADODB primary interop assembly
        2. 32.1.2. The ADO object model
          1. 32.1.2.1. OLEDB data providers
          2. 32.1.2.2. Mapping data types
          3. 32.1.2.3. ADO and scripting
        3. 32.1.3. ADO.NET
          1. 32.1.3.1. The ADO.NET object model
          2. 32.1.3.2. Managed providers
          3. 32.1.3.3. SQL Native Client
          4. 32.1.3.4. Data types
          5. 32.1.3.5. DataAdapters and DataSets
      2. 32.2. ADO.NET in Visual Studio 2008
        1. 32.2.1. Server Explorer
        2. 32.2.2. Debugging ADO.NET
        3. 32.2.3. Application tracing
      3. 32.3. Application Building Basics
        1. 32.3.1. Connecting to SQL Server
        2. 32.3.2. What's new in ADO.NET 3.5
          1. 32.3.2.1. LINQ
          2. 32.3.2.2. Table-valued parameters
        3. 32.3.3. Stored procedures vs. parameterized/ad-hoc queries
        4. 32.3.4. Data adapters
        5. 32.3.5. DataReaders and Recordsets
        6. 32.3.6. Streams
        7. 32.3.7. Asynchronous execution
        8. 32.3.8. Using a single database value
        9. 32.3.9. Data modification
        10. 32.3.10. Binding to controls
      4. 32.4. Summary
    4. 33. Sync Framework
      1. 33.1. Sync Framework example
        1. 33.1.1. Sync Framework overview
          1. 33.1.1.1. Sync architecture
          2. 33.1.1.2. Sync knowledge
          3. 33.1.1.3. Sync fundamentals
        2. 33.1.2. Sync services for ADO.NET 2.0
          1. 33.1.2.1. What's new in Sync Services 2.0
          2. 33.1.2.2. Offline scenarios
          3. 33.1.2.3. Collaboration scenarios
      2. 33.2. Summary
    5. 34. LINQ
      1. 34.1. LINQ Overview
        1. 34.1.1. What Is LINQ?
        2. 34.1.2. Standard Query Operators
        3. 34.1.3. Query expression syntax
          1. 34.1.3.1. Query syntax and method syntax
          2. 34.1.3.2. Choosing a query syntax
      2. 34.2. LINQ to SQL
        1. 34.2.1. Example 1: Manually applying the mappings
        2. 34.2.2. Example 2: The easy way
      3. 34.3. LINQ to XML
        1. 34.3.1. LINQ to XML example
        2. 34.3.2. Traversing XML
      4. 34.4. LINQ to DataSet
        1. 34.4.1. Querying a DataSet using LINQ to DataSet
        2. 34.4.2. Data binding with LINQ to DataSet
      5. 34.5. LINQ to Entities
        1. 34.5.1. Creating and querying entities using LINQ
        2. 34.5.2. Querying multiple tables using LINQ to Entities and the Entity Framework
      6. 34.6. Summary
    6. 35. Asynchronous Messaging with Service Broker
      1. 35.1. Configuring a Message Queue
      2. 35.2. Working with Conversations
        1. 35.2.1. Sending a message to the queue
        2. 35.2.2. Receiving a message
      3. 35.3. Monitoring Service Broker
      4. 35.4. Summary
    7. 36. Replicating Data
      1. 36.1. Replication Concepts
        1. 36.1.1. Types of replication
        2. 36.1.2. Replication agents
        3. 36.1.3. Transactional consistency
      2. 36.2. Configuring Replication
        1. 36.2.1. Creating a publisher and distributor
        2. 36.2.2. Creating a snapshot/transactional publication
        3. 36.2.3. Creating a push subscription to a transactional/snapshot publication
        4. 36.2.4. Creating a pull subscription to a transactional/snapshot publication
        5. 36.2.5. Creating a peer-to-peer topology
        6. 36.2.6. Creating a merge publication
        7. 36.2.7. Web synchronization
      3. 36.3. Summary
    8. 37. Performing ETL with Integration Services
      1. 37.1. Design Environment
        1. 37.1.1. Connection managers
        2. 37.1.2. Variables
          1. 37.1.2.1. Variable usage
          2. 37.1.2.2. Expressions
        3. 37.1.3. Configuring elements
          1. 37.1.3.1. Control flow
          2. 37.1.3.2. Control flow precedence
          3. 37.1.3.3. Data flow
        4. 37.1.4. Event handlers
        5. 37.1.5. Executing a package in development
      2. 37.2. Integration Services Package Elements
        1. 37.2.1. Connection managers
          1. 37.2.1.1. Database
          2. 37.2.1.2. File
          3. 37.2.1.3. Special
        2. 37.2.2. Control flow elements
          1. 37.2.2.1. Containers
          2. 37.2.2.2. Control flow tasks
          3. 37.2.2.3. Maintenance Plan tasks
        3. 37.2.3. Data flow components
          1. 37.2.3.1. Sources
          2. 37.2.3.2. Destinations
          3. 37.2.3.3. Transformations
      3. 37.3. Maintainable and Manageable Packages
        1. 37.3.1. Logging
        2. 37.3.2. Package configurations
        3. 37.3.3. Checkpoint restart
      4. 37.4. Deploying Packages
        1. 37.4.1. Installing packages
        2. 37.4.2. Executing packages
      5. 37.5. Summary
    9. 38. Access as a Front End to SQL Server
      1. 38.1. Access–SQL Server Use Case Scenarios
        1. 38.1.1. Access projects or ODBC linked tables?
      2. 38.2. Migrating from Access to SQL Server
      3. 38.3. Designing Your Access Front End
      4. 38.4. Connecting to SQL Server
        1. 38.4.1. Linking to tables and views
        2. 38.4.2. Caching data in local tables using pass-through queries
        3. 38.4.3. Extending the power of pass-through queries using table-valued parameters (TVPs)
      5. 38.5. Monitoring and Troubleshooting
      6. 38.6. Ad Hoc Querying and Reporting
        1. 38.6.1. Pre-aggregating data on the server
        2. 38.6.2. Sorting and filtering data
        3. 38.6.3. Creating forms and reports
        4. 38.6.4. Exporting and publishing data
        5. 38.6.5. Managing your SQL Server databases
      7. 38.7. Summary
  12. VI. Enterprise Data Management
    1. 39. Configuring SQL Server
      1. 39.1. Setting the Options
        1. 39.1.1. Configuring the server
        2. 39.1.2. Configuring the database
        3. 39.1.3. Configuring the connection
      2. 39.2. Configuration Options
        1. 39.2.1. Displaying the advanced options
        2. 39.2.2. Start/Stop configuration properties
          1. 39.2.2.1. Startup parameters
          2. 39.2.2.2. Startup stored procedures
        3. 39.2.3. Memory-configuration properties
          1. 39.2.3.1. Dynamic memory
          2. 39.2.3.2. Fixed memory
          3. 39.2.3.3. Minimum query memory
          4. 39.2.3.4. Query wait
          5. 39.2.3.5. AWE enabled
          6. 39.2.3.6. Index create memory
        4. 39.2.4. Processor-configuration properties
          1. 39.2.4.1. Processor affinity
          2. 39.2.4.2. Max worker threads
          3. 39.2.4.3. Priority boost
          4. 39.2.4.4. Lightweight pooling
          5. 39.2.4.5. Parallelism
        5. 39.2.5. Security-configuration properties
          1. 39.2.5.1. Server authentication mode
          2. 39.2.5.2. Security-audit level
          3. 39.2.5.3. C2 audit tracing
          4. 39.2.5.4. Cross-database ownership chaining
        6. 39.2.6. Connection-configuration properties
          1. 39.2.6.1. Maximum concurrent user connections
          2. 39.2.6.2. Query governor cost limit
          3. 39.2.6.3. Remote access
          4. 39.2.6.4. Remote login timeout
          5. 39.2.6.5. Remote query timeout
          6. 39.2.6.6. Enforce DTC
          7. 39.2.6.7. Network packet size
        7. 39.2.7. Advanced server-configuration properties
          1. 39.2.7.1. Filestream access level
          2. 39.2.7.2. Extensible Key Management
          3. 39.2.7.3. Default full-text language
          4. 39.2.7.4. Default language
          5. 39.2.7.5. Two-digit-year cutoff
          6. 39.2.7.6. Max text replication size
        8. 39.2.8. Configuring database auto options
          1. 39.2.8.1. Auto close
          2. 39.2.8.2. Auto shrink
          3. 39.2.8.3. Auto create statistics
          4. 39.2.8.4. Auto update statistics
          5. 39.2.8.5. Auto update statistics asynchronously
        9. 39.2.9. Cursor-configuration properties
          1. 39.2.9.1. Cursor threshold
          2. 39.2.9.2. Cursor close on commit
          3. 39.2.9.3. Cursor default
        10. 39.2.10. SQL ANSI–configuration properties
          1. 39.2.10.1. ANSI defaults
          2. 39.2.10.2. ANSI null default
          3. 39.2.10.3. ANSI NULLs
          4. 39.2.10.4. ANSI padding
          5. 39.2.10.5. ANSI warnings
          6. 39.2.10.6. Arithmetic abort
          7. 39.2.10.7. Arithmetic ignore
          8. 39.2.10.8. Numeric round abort
          9. 39.2.10.9. Concatenation null yields null
          10. 39.2.10.10. Use quoted identifier
        11. 39.2.11. Trigger configuration properties
          1. 39.2.11.1. Nested triggers
          2. 39.2.11.2. Recursive triggers
        12. 39.2.12. Database-state-configuration properties
          1. 39.2.12.1. Database-access level
          2. 39.2.12.2. Compatibility level
        13. 39.2.13. Recovery-configuration properties
          1. 39.2.13.1. Recovery model
          2. 39.2.13.2. Page Verify
          3. 39.2.13.3. Media retention
          4. 39.2.13.4. Backup compression
          5. 39.2.13.5. Recovery interval
      3. 39.3. Summary
    2. 40. Policy-Based Management
      1. 40.1. Defining Policies
        1. 40.1.1. Management facets
        2. 40.1.2. Health conditions
        3. 40.1.3. Policies
      2. 40.2. Evaluating Policies
      3. 40.3. Summary
    3. 41. Recovery Planning
      1. 41.1. Recovery Concepts
      2. 41.2. Recovery Models
        1. 41.2.1. Simple recovery model
        2. 41.2.2. The full recovery model
        3. 41.2.3. Bulk-logged recovery model
        4. 41.2.4. Setting the recovery model
        5. 41.2.5. Modifying recovery models
      3. 41.3. Backing Up the Database
        1. 41.3.1. Backup destination
        2. 41.3.2. Backup rotation
        3. 41.3.3. Performing backup with Management Studio
        4. 41.3.4. Backing up the database with code
        5. 41.3.5. Verifying the backup with code
      4. 41.4. Working with the Transaction Log
        1. 41.4.1. Inside the transaction log
          1. 41.4.1.1. The active and inactive divide
          2. 41.4.1.2. Transaction checkpoints
        2. 41.4.2. Backing up the transaction log
        3. 41.4.3. Truncating the log
        4. 41.4.4. The transaction log and simple recovery model
      5. 41.5. Recovery Operations
        1. 41.5.1. Detecting the problem
        2. 41.5.2. Recovery sequences
        3. 41.5.3. Performing the restore with Management Studio
        4. 41.5.4. Restoring with T-SQL code
      6. 41.6. System Databases Recovery
        1. 41.6.1. Master database
          1. 41.6.1.1. Backing up the master database
          2. 41.6.1.2. Recovering the master database
        2. 41.6.2. MSDB system database
      7. 41.7. Performing a Complete Recovery
      8. 41.8. Summary
    4. 42. Maintaining the Database
      1. 42.1. DBCC Commands
        1. 42.1.1. Database integrity
          1. 42.1.1.1. Repairing the database
          2. 42.1.1.2. Multi-user concerns
          3. 42.1.1.3. Object-level validation
          4. 42.1.1.4. Data integrity
        2. 42.1.2. Index maintenance
          1. 42.1.2.1. Database fragmentation
          2. 42.1.2.2. Index statistics
          3. 42.1.2.3. Index density
        3. 42.1.3. Database file size
          1. 42.1.3.1. Monitoring database file sizes
          2. 42.1.3.2. Shrinking the database
          3. 42.1.3.3. Shrinking the transaction log
        4. 42.1.4. Miscellaneous DBCC commands
      2. 42.2. Managing Database Maintenance
        1. 42.2.1. Planning database maintenance
        2. 42.2.2. Maintenance plan
          1. 42.2.2.1. Adding a task
          2. 42.2.2.2. Defining the schedule
          3. 42.2.2.3. Creating new connections
          4. 42.2.2.4. Logging the maintenance progress
        3. 42.2.3. Command-line maintenance
        4. 42.2.4. Monitoring database maintenance
      3. 42.3. Summary
    5. 43. Automating Database Maintenance with SQL Server Agent
      1. 43.1. Setting Up SQL Server Agent
      2. 43.2. Understanding Alerts, Operators, and Jobs
      3. 43.3. Managing Operators
      4. 43.4. Managing Alerts
        1. 43.4.1. Creating user-defined errors
        2. 43.4.2. Creating an alert
      5. 43.5. Managing Jobs
        1. 43.5.1. Creating a job category
        2. 43.5.2. Creating a job definition
        3. 43.5.3. Setting up the job steps
        4. 43.5.4. Configuring a job schedule
        5. 43.5.5. Handling completion-, success-, and failure-notification messages
      6. 43.6. Database Mail
        1. 43.6.1. Configuring database mail
          1. 43.6.1.1. Using the Database Mail Configuration Wizard
      7. 43.7. Summary
    6. 44. Transferring Databases
      1. 44.1. Copy Database Wizard
      2. 44.2. Working with SQL Script
      3. 44.3. Detaching and Attaching
      4. 44.4. Import and Export Wizard
      5. 44.5. Summary
    7. 45. Database Snapshots
      1. 45.1. How Database Snapshots Work
      2. 45.2. Creating a Database Snapshot
      3. 45.3. Using Your Database Snapshots
      4. 45.4. Performance Considerations and Best Practices
      5. 45.5. Summary
    8. 46. Log Shipping
      1. 46.1. Availability Testing
      2. 46.2. Warm Standby Availability
      3. 46.3. Defining Log Shipping
        1. 46.3.1. Configuring log shipping
          1. 46.3.1.1. Pre-log shipping configuration
          2. 46.3.1.2. Configuring log shipping using Management Studio
          3. 46.3.1.3. Configuring log shipping using Transact-SQL
          4. 46.3.1.4. Post-log shipping configuration
      4. 46.4. Checking Log Shipping Configuration
      5. 46.5. Monitoring Log Shipping
      6. 46.6. Modifying or Removing Log Shipping
      7. 46.7. Switching Roles
        1. 46.7.1. Returning to the original primary server
      8. 46.8. Summary
    9. 47. Database Mirroring
      1. 47.1. Database Mirroring Overview
      2. 47.2. Defining Database Mirroring
        1. 47.2.1. Configuring database mirroring
          1. 47.2.1.1. Pre-database mirroring configuration
          2. 47.2.1.2. Configuring database mirroring using Management Studio
          3. 47.2.1.3. Configuring database mirroring using Transact-SQL
          4. 47.2.1.4. Post-database mirroring configuration
      3. 47.3. Checking a Database Mirroring Configuration
      4. 47.4. Monitoring Database Mirroring
        1. 47.4.1. Monitoring using Database Mirroring Monitor
        2. 47.4.2. Monitoring using System Monitor
        3. 47.4.3. Monitoring using SQL Server Profiler
      5. 47.5. Pausing or Removing Database Mirroring
      6. 47.6. Role Switching
      7. 47.7. Summary
    10. 48. Clustering
      1. 48.1. SQL Server 2008 Failover Clustering Basics
        1. 48.1.1. How SQL Server 2008 failover clustering works
        2. 48.1.2. SQL Server 2008 failover clustering topologies
      2. 48.2. Enhancements in SQL Server 2008 Failover Clustering
      3. 48.3. SQL Server 2008 Failover Clustering Setup
        1. 48.3.1. Planning SQL Server 2008 failover clustering
        2. 48.3.2. SQL Server 2008 prerequisites
        3. 48.3.3. Creating a single-node SQL Server 2008 failover cluster
        4. 48.3.4. Adding a node to an existing SQL Server 2008 failover cluster
        5. 48.3.5. Post-installation tasks
        6. 48.3.6. Uninstalling a SQL Server 2008 failover cluster
        7. 48.3.7. Installing a failover cluster using a command prompt
        8. 48.3.8. Rolling upgrade and patching
          1. 48.3.8.1. Patching a SQL Server 2008 failover cluster
        9. 48.3.9. Maintaining a SQL Server 2008 failover cluster
        10. 48.3.10. Troubleshooting a SQL Server 2008 failover cluster
      4. 48.4. Summary
  13. VII. Security
    1. 49. Authenticating Principals
      1. 49.1. Server-Level Security
      2. 49.2. Database-Level Security
      3. 49.3. Windows Security
        1. 49.3.1. Using Windows Security
        2. 49.3.2. SQL Server login
      4. 49.4. Server Security
        1. 49.4.1. SQL Server authentication mode
        2. 49.4.2. Windows Authentication
          1. 49.4.2.1. Adding a new Windows login
          2. 49.4.2.2. Removing a Windows login
          3. 49.4.2.3. Denying a Windows login
          4. 49.4.2.4. Setting the default database
          5. 49.4.2.5. Orphaned Windows users
          6. 49.4.2.6. Security delegation
        3. 49.4.3. SQL Server logins
          1. 49.4.3.1. Updating a password
          2. 49.4.3.2. Removing a login
          3. 49.4.3.3. Setting the default database
          4. 49.4.3.4. Server roles
      5. 49.5. Database Security
        1. 49.5.1. Guest logins
        2. 49.5.2. Granting access to the database
          1. 49.5.2.1. Granting access using T-SQL code
        3. 49.5.3. Fixed database roles
        4. 49.5.4. Assigning fixed database roles with Management Studio
          1. 49.5.4.1. Assigning fixed database roles with T-SQL
        5. 49.5.5. Application roles
      6. 49.6. Summary
    2. 50. Authorizing Securables
      1. 50.1. Object Ownership
      2. 50.2. Object Security
        1. 50.2.1. Standard database roles
        2. 50.2.2. Object permissions
        3. 50.2.3. Granting object permissions with code
        4. 50.2.4. Revoking and denying object permission with code
        5. 50.2.5. The public role
        6. 50.2.6. Managing roles with code
        7. 50.2.7. Hierarchical role structures
        8. 50.2.8. Object security and Management Studio
          1. 50.2.8.1. From the object list
          2. 50.2.8.2. From the user list
          3. 50.2.8.3. From the role list
          4. 50.2.8.4. Ownership chains
        9. 50.2.9. Stored procedure execute as
      3. 50.3. A Sample Security Model Example
      4. 50.4. Views and Security
      5. 50.5. Summary
    3. 51. Data Cryptography
      1. 51.1. Introduction to Cryptography
        1. 51.1.1. Types of encryption
        2. 51.1.2. The hierarchy of keys
      2. 51.2. Encrypting Data
        1. 51.2.1. Encrypting with a passphrase
        2. 51.2.2. Encrypting with a symmetric key
          1. 51.2.2.1. Encryption algorithms
          2. 51.2.2.2. Using the symmetric key
        3. 51.2.3. Using asymmetric keys
        4. 51.2.4. Using certificates
      3. 51.3. Transparent Data Encryption
      4. 51.4. Summary
    4. 52. Row-Level Security
      1. 52.1. The Security Table
      2. 52.2. Assigning Permissions
        1. 52.2.1. Assigning security
        2. 52.2.2. Handling security-level updates
      3. 52.3. Checking Permissions
        1. 52.3.1. The security-check stored procedure
        2. 52.3.2. The security-check function
        3. 52.3.3. Using the NT login
        4. 52.3.4. The security-check trigger
      4. 52.4. Summary
  14. VIII. Monitoring and Auditing
    1. 53. Data Audit Triggers
      1. 53.1. AutoAudit
        1. 53.1.1. Installing AutoAudit
        2. 53.1.2. The audit table
        3. 53.1.3. Running AutoAudit
        4. 53.1.4. _Modified trigger
        5. 53.1.5. Auditing changes
        6. 53.1.6. Viewing and undeleting deleted rows
        7. 53.1.7. Viewing row history
        8. 53.1.8. Backing out AutoAudit
      2. 53.2. Auditing Complications
        1. 53.2.1. Auditing related data
        2. 53.2.2. Auditing select statements
        3. 53.2.3. Data auditing and security
        4. 53.2.4. Data auditing and performance
      3. 53.3. Summary
    2. 54. Schema Audit Triggers
      1. 54.1. SchemaAudit Table
      2. 54.2. SchemaAudit Trigger
      3. 54.3. Summary
    3. 55. Performance Monitor
      1. 55.1. Using Performance Monitor
        1. 55.1.1. System monitor
        2. 55.1.2. Counter Logs
      2. 55.2. Summary
    4. 56. Tracing and Profiling
      1. 56.1. Running Profiler
        1. 56.1.1. Defining a new trace
        2. 56.1.2. Selecting events and data columns
        3. 56.1.3. Filtering events
        4. 56.1.4. Organizing columns
        5. 56.1.5. Running the trace
        6. 56.1.6. Using the trace file
        7. 56.1.7. Integrating Performance Monitor data
      2. 56.2. Using SQL Trace
        1. 56.2.1. Preconfigured traces
      3. 56.3. Summary
    5. 57. Wait States
      1. 57.1. Observing Wait State Statistics
        1. 57.1.1. Querying wait states
        2. 57.1.2. Activity Monitor
      2. 57.2. Analyzing Wait States
      3. 57.3. Summary
    6. 58. Extended Events
      1. 58.1. XE Components
        1. 58.1.1. Packages
        2. 58.1.2. Objects
      2. 58.2. XE Sessions
      3. 58.3. Summary
    7. 59. Change Tracking
      1. 59.1. Configuring Change Tracking
        1. 59.1.1. Enabling the database
        2. 59.1.2. Auto cleanup
        3. 59.1.3. Enabling tables
        4. 59.1.4. Enabling all tables
        5. 59.1.5. Internal tables
      2. 59.2. Querying Change Tracking
        1. 59.2.1. Version numbers
        2. 59.2.2. Changes by the row
        3. 59.2.3. Coding a synchronization
      3. 59.3. Change Tracking Options
        1. 59.3.1. Column tracking
        2. 59.3.2. Determining latest version per row
        3. 59.3.3. Capturing application context
      4. 59.4. Removing Change Tracking
      5. 59.5. Summary
    8. 60. Change Data Capture
      1. 60.1. Enabling CDC
        1. 60.1.1. Enabling the database
        2. 60.1.2. Enabling tables
      2. 60.2. Working with Change Data Capture
        1. 60.2.1. Examining the log sequence numbers
        2. 60.2.2. Querying the change tables
        3. 60.2.3. Querying net changes
        4. 60.2.4. Walking through the change tables
      3. 60.3. Removing Change Data Capture
      4. 60.4. Summary
    9. 61. SQL Audit
      1. 61.1. SQL Audit Technology Overview
      2. 61.2. Creating an Audit
        1. 61.2.1. Defining the target
        2. 61.2.2. Using T-SQL
        3. 61.2.3. Enabling/disabling the audit
      3. 61.3. Server Audit Specifications
        1. 61.3.1. Adding actions
        2. 61.3.2. Creating with T-SQL
        3. 61.3.3. Modifying Server Audit Specifications
      4. 61.4. Database Audit Specifications
      5. 61.5. Viewing the Audit Trail
      6. 61.6. Summary
    10. 62. Management Data Warehouse
      1. 62.1. Configuring MDW
        1. 62.1.1. Configuring a data warehouse
        2. 62.1.2. Configuring a data collection
      2. 62.2. The MDW Data Warehouse
      3. 62.3. Summary
  15. IX. Performance Tuning and Optimization
    1. 63. Interpreting Query Execution Plans
      1. 63.1. Viewing Query Execution Plans
        1. 63.1.1. Estimated query execution plans
        2. 63.1.2. The Query Editor's execution plan
        3. 63.1.3. Returning the plan with showplans
        4. 63.1.4. SQL Profiler's execution plans
        5. 63.1.5. Examining plans using dynamic management views
      2. 63.2. Interpreting the Query Execution Plan
      3. 63.3. Summary
    2. 64. Indexing Strategies
      1. 64.1. Zen and the Art of Indexing
      2. 64.2. Indexing Basics
        1. 64.2.1. The b-tree index
        2. 64.2.2. Clustered indexes
        3. 64.2.3. Non-clustered indexes
        4. 64.2.4. Composite indexes
        5. 64.2.5. Unique indexes and constraints
        6. 64.2.6. The page split problem
        7. 64.2.7. Index selectivity
        8. 64.2.8. Unordered heaps
        9. 64.2.9. Query operations
      3. 64.3. Path of the Query
        1. 64.3.1. Query Path 1: Fetch All
        2. 64.3.2. Query Path 2: Clustered Index Seek
        3. 64.3.3. Query Path 3: Range Seek Query
        4. 64.3.4. Query Path 4: Filter by non-key column
        5. 64.3.5. Query Path 5: Bookmark Lookup
        6. 64.3.6. Query Path 6: Covering Index
        7. 64.3.7. Query Path 7: Filter by 2 x NC Indexes
        8. 64.3.8. Query Path 8: Filter by Ordered Composite Index
        9. 64.3.9. Query Path 9: Filter by Unordered Composite Index
        10. 64.3.10. Query Path 10: Non-SARGable Expressions
      4. 64.4. A Comprehensive Indexing Strategy
        1. 64.4.1. Identifying key queries
        2. 64.4.2. Table CRUD analysis
        3. 64.4.3. Selecting the clustered index
        4. 64.4.4. Creating base indexes
      5. 64.5. Specialty Indexes
        1. 64.5.1. Filtered indexes
        2. 64.5.2. Indexed views
          1. 64.5.2.1. Indexed Views and Queries
          2. 64.5.2.2. Updating indexed views
      6. 64.6. Summary
    3. 65. Query Plan Reuse
      1. 65.1. Query Compiling
        1. 65.1.1. The Query Optimizer
        2. 65.1.2. Viewing the Plan Cache
        3. 65.1.3. Plan lifetime
        4. 65.1.4. Query plan execution
      2. 65.2. Query Recompiles
      3. 65.3. Summary
    4. 66. Managing Transactions, Locking, and Blocking
      1. 66.1. The ACID Properties
        1. 66.1.1. Atomicity
        2. 66.1.2. Consistency
        3. 66.1.3. Isolation
        4. 66.1.4. Durability
      2. 66.2. Programming Transactions
        1. 66.2.1. Logical transactions
        2. 66.2.2. Xact_State()
        3. 66.2.3. Xact_Abort
        4. 66.2.4. Nested transactions
        5. 66.2.5. Implicit transactions
        6. 66.2.6. Save points
      3. 66.3. Default Locking and Blocking Behavior
      4. 66.4. Monitoring Locking and Blocking
        1. 66.4.1. Viewing blocking with Management Studio reports
        2. 66.4.2. Viewing blocking with Activity Monitor
        3. 66.4.3. Using Profiler
        4. 66.4.4. Querying locks with DMVs
      5. 66.5. Deadlocks
        1. 66.5.1. Creating a deadlock
        2. 66.5.2. Automatic deadlock detection
        3. 66.5.3. Handling deadlocks
        4. 66.5.4. Minimizing deadlocks
      6. 66.6. Understanding SQL Server Locking
        1. 66.6.1. Lock granularity
        2. 66.6.2. Lock mode
          1. 66.6.2.1. Lock contention
          2. 66.6.2.2. Shared lock (S)
          3. 66.6.2.3. Exclusive lock (X)
          4. 66.6.2.4. Update lock (U)
          5. 66.6.2.5. Intent locks (various)
          6. 66.6.2.6. Schema lock (Sch-M, Sch-S)
        3. 66.6.3. Controlling lock timeouts
        4. 66.6.4. Lock duration
        5. 66.6.5. Index-level locking restrictions
      7. 66.7. Transaction Isolation Levels
        1. 66.7.1. Setting the transaction isolation level
        2. 66.7.2. Level 1—Read uncommitted and the dirty read
        3. 66.7.3. Level 2—Read committed
        4. 66.7.4. Level 3—Repeatable read
          1. 66.7.4.1. Nonrepeatable read fault
          2. 66.7.4.2. Preventing the fault
        5. 66.7.5. Level 4—Serializable
          1. 66.7.5.1. Phantom rows
          2. 66.7.5.2. Serialized transaction isolation level
        6. 66.7.6. Snapshot isolations
          1. 66.7.6.1. Enabling row versioning
          2. 66.7.6.2. Using read committed snapshot isolation
          3. 66.7.6.3. Handling write conficts
        7. 66.7.7. Using locking hints
      8. 66.8. Application Locks
      9. 66.9. Application Locking Design
        1. 66.9.1. Implementing optimistic locking
        2. 66.9.2. Lost updates
          1. 66.9.2.1. Minimizing lost updates
          2. 66.9.2.2. Preventing lost updates
      10. 66.10. Transaction-Log Architecture
        1. 66.10.1. Transaction log sequence
          1. 66.10.1.1. Database beginning state
          2. 66.10.1.2. Data-modification command
          3. 66.10.1.3. Transaction log recorded
          4. 66.10.1.4. Transaction commit
          5. 66.10.1.5. Data-file update
          6. 66.10.1.6. Transaction complete
          7. 66.10.1.7. Transaction-log rollback
        2. 66.10.2. Transaction log recovery
      11. 66.11. Transaction Performance Strategies
        1. 66.11.1. Evaluating database concurrency performance
      12. 66.12. Summary
    5. 67. Data Compression
      1. 67.1. Understanding Data Compression
        1. 67.1.1. Data compression pros and cons
        2. 67.1.2. Row compression
        3. 67.1.3. Page compression
        4. 67.1.4. Compression sequence
      2. 67.2. Applying Data Compression
        1. 67.2.1. Determining the current compression setting
        2. 67.2.2. Estimating data compression
        3. 67.2.3. Enabling data compression
        4. 67.2.4. Data compression strategies
      3. 67.3. Summary
    6. 68. Partitioning
      1. 68.1. Partitioning Strategies
      2. 68.2. Partitioned Views
        1. 68.2.1. Local-partition views
          1. 68.2.1.1. Segmenting the data
          2. 68.2.1.2. Creating the partition view
          3. 68.2.1.3. Selecting through the partition view
          4. 68.2.1.4. Updating through the partition view
          5. 68.2.1.5. Moving data
        2. 68.2.2. Distributed-partition views
          1. 68.2.2.1. Updating and moving data with distributed-partition views
          2. 68.2.2.2. Highly scalable distributed-partition views
      3. 68.3. Partitioned Tables and Indexes
        1. 68.3.1. Creating the partition function
        2. 68.3.2. Creating partition schemes
        3. 68.3.3. Creating the partition table
        4. 68.3.4. Querying partition tables
        5. 68.3.5. Altering partition tables
          1. 68.3.5.1. Merging partitions
          2. 68.3.5.2. Splitting partitions
        6. 68.3.6. Switching tables
          1. 68.3.6.1. Prepping the new table
          2. 68.3.6.2. Prepping the partition table
          3. 68.3.6.3. Performing the switch
          4. 68.3.6.4. Switching out
        7. 68.3.7. Rolling partitions
        8. 68.3.8. Indexing partitioned tables
          1. 68.3.8.1. Creating partitioned indexes
          2. 68.3.8.2. Maintaining partitioned indexes
        9. 68.3.9. Removing partitioning
      4. 68.4. Data-Driven Partitioning
      5. 68.5. Summary
    7. 69. Resource Governor
      1. 69.1. Configuring Resource Governor
        1. 69.1.1. Resource pools
        2. 69.1.2. Workload groups
        3. 69.1.3. Classifier functions
      2. 69.2. Monitoring Resource Governor
      3. 69.3. Summary
  16. X. Business Intelligence
    1. 70. BI Design
      1. 70.1. Data Warehousing
        1. 70.1.1. Star schema
        2. 70.1.2. Snowflake schema
        3. 70.1.3. Surrogate keys
        4. 70.1.4. Consistency
        5. 70.1.5. Loading data
          1. 70.1.5.1. Loading dimensions
          2. 70.1.5.2. Loading fact tables
        6. 70.1.6. Changing data in dimensions
      2. 70.2. Summary
    2. 71. Building Multidimensional Cubes with Analysis Services
      1. 71.1. Analysis Services Quick Start
      2. 71.2. Analysis Services Architecture
        1. 71.2.1. Unified Dimensional Model
        2. 71.2.2. Server
        3. 71.2.3. Client
      3. 71.3. Building a Database
        1. 71.3.1. Business Intelligence Development Studio
        2. 71.3.2. Data sources
        3. 71.3.3. Data source view
          1. 71.3.3.1. Creating the data source view
          2. 71.3.3.2. Managing the data source view
          3. 71.3.3.3. Refining the data source view
        4. 71.3.4. Creating a cube
      4. 71.4. Dimensions
        1. 71.4.1. Dimension Designer
          1. 71.4.1.1. Attributes
          2. 71.4.1.2. Attribute source columns and ordering
          3. 71.4.1.3. Hierarchies and attribute relationships
          4. 71.4.1.4. Creating user hierarchies
          5. 71.4.1.5. Establishing attribute relationships
          6. 71.4.1.6. Visibility and organization
          7. 71.4.1.7. Basic setup checklist
        2. 71.4.2. Beyond regular dimensions
          1. 71.4.2.1. Time dimension
          2. 71.4.2.2. Other dimension types
          3. 71.4.2.3. Parent-child dimensions
        3. 71.4.3. Dimension refinements
          1. 71.4.3.1. Hierarchy (All) level and default member
          2. 71.4.3.2. Grouping dimension members
      5. 71.5. Cubes
        1. 71.5.1. Cube structure
          1. 71.5.1.1. Measures
          2. 71.5.1.2. Cube dimensions
        2. 71.5.2. Dimension usage
          1. 71.5.2.1. No relationship
          2. 71.5.2.2. Regular
          3. 71.5.2.3. Fact
          4. 71.5.2.4. Referenced
          5. 71.5.2.5. Many-to-Many
        3. 71.5.3. Calculations
        4. 71.5.4. KPIs
        5. 71.5.5. Actions
        6. 71.5.6. Partitions
          1. 71.5.6.1. Partition sizing
          2. 71.5.6.2. Creating partitions
        7. 71.5.7. Aggregation design
          1. 71.5.7.1. Aggregation Design Wizard
          2. 71.5.7.2. Aggregations tab
        8. 71.5.8. Perspectives
      6. 71.6. Data Storage
        1. 71.6.1. Proactive caching
        2. 71.6.2. SQL Server notifications
        3. 71.6.3. Client-initiated notifications
        4. 71.6.4. Scheduled polling notifications
      7. 71.7. Data Integrity
        1. 71.7.1. Null processing
        2. 71.7.2. Unknown member
        3. 71.7.3. Error Configuration
      8. 71.8. Summary
    3. 72. Programming MDX Queries
      1. 72.1. Basic Select Query
        1. 72.1.1. Cube addressing
        2. 72.1.2. Dimension structure
          1. 72.1.2.1. Dimension references
          2. 72.1.2.2. Tuples and simple sets
        3. 72.1.3. Basic SELECT statement
          1. 72.1.3.1. Measures
          2. 72.1.3.2. Generating sets from functions
          3. 72.1.3.3. Using SQL Server Management Studio
      2. 72.2. Advanced Select Query
        1. 72.2.1. Subcubes
        2. 72.2.2. WITH clause
          1. 72.2.2.1. Sets
          2. 72.2.2.2. Calculated Members
        3. 72.2.3. Dimension considerations
      3. 72.3. MDX Scripting
        1. 72.3.1. Calculated members and named sets
        2. 72.3.2. Adding Business Intelligence
      4. 72.4. Summary
    4. 73. Authoring Reports with Reporting Services
      1. 73.1. Anatomy of a Report
        1. 73.1.1. Report Definition Language (RDL)
        2. 73.1.2. Data Sources
          1. 73.1.2.1. Data Source types
          2. 73.1.2.2. Data source connection strings
          3. 73.1.2.3. Using expressions in a connection string
          4. 73.1.2.4. Setting data source credentials
        3. 73.1.3. Reporting Services datasets
        4. 73.1.4. Query parameters and report parameters
        5. 73.1.5. Report content and layout
      2. 73.2. The Report Authoring Process
        1. 73.2.1. Creating a Reporting Services project
        2. 73.2.2. Creating a report
        3. 73.2.3. Using the Report Wizard to create reports
        4. 73.2.4. Authoring a report from scratch
          1. 73.2.4.1. Creating a shared data source
          2. 73.2.4.2. Adding a blank report
          3. 73.2.4.3. Creating a dataset
          4. 73.2.4.4. Displaying data in the report
          5. 73.2.4.5. Previewing the report
      3. 73.3. Working with Data
        1. 73.3.1. Working with SQL in the Query Designer
        2. 73.3.2. Using query parameters to select and filter data
          1. 73.3.2.1. Nested report parameters
          2. 73.3.2.2. Multi-value report parameters
        3. 73.3.3. Adding calculated fields to a dataset
        4. 73.3.4. Working with XML data sources
        5. 73.3.5. Working with expressions
          1. 73.3.5.1. Expression scope
          2. 73.3.5.2. Expressing yourself with common expressions
      4. 73.4. Designing the Report Layout
        1. 73.4.1. Design basics
          1. 73.4.1.1. Designing the report header
          2. 73.4.1.2. Designing the report footer
          3. 73.4.1.3. Adding and formatting a Table report item
          4. 73.4.1.4. Adding and formatting a List report item
        2. 73.4.2. Using the Tablix property pages
        3. 73.4.3. Grouping and sorting data in a Tablix
          1. 73.4.3.1. Setting the sort order for a Tablix
          2. 73.4.3.2. Formatting tables with groups
        4. 73.4.4. Illustrating data with charts and gauges
      5. 73.5. Summary
    5. 74. Administering Reporting Services
      1. 74.1. Deploying Reporting Services Reports
        1. 74.1.1. Deploying reports using BIDS
          1. 74.1.1.1. Deploying a single report or data source
          2. 74.1.1.2. Deploying a Reporting Services project
        2. 74.1.2. Deploying reports using the Report Manager
        3. 74.1.3. Deploying reports programmatically using the Reporting Services Web Service
      2. 74.2. Configuring Reporting Services Using Management Studio
        1. 74.2.1. Configuring Reporting Services server properties
          1. 74.2.1.1. General
          2. 74.2.1.2. Configuring report execution settings
          3. 74.2.1.3. Configuring report history settings
        2. 74.2.2. Security: managing roles
          1. 74.2.2.1. System-level roles
          2. 74.2.2.2. Item-level roles
      3. 74.3. Configuring Reporting Services Using Report Manager
        1. 74.3.1. Managing security
          1. 74.3.1.1. Granting system access to users and groups
          2. 74.3.1.2. Item-level permissions
          3. 74.3.1.3. Controlling item-level security
        2. 74.3.2. Working with linked reports
        3. 74.3.3. Creating linked reports
        4. 74.3.4. Leveraging the power of subscriptions
        5. 74.3.5. Creating a data-driven subscription
      4. 74.4. Summary
    6. 75. Analyzing Data with Excel
      1. 75.1. Data Connections
        1. 75.1.1. Data Connection Wizard
        2. 75.1.2. Microsoft Query
        3. 75.1.3. Connection file types
      2. 75.2. Basic Data Analysis
        1. 75.2.1. Data tables
        2. 75.2.2. PivotTables
        3. 75.2.3. PivotCharts
      3. 75.3. Advanced Data Analysis
        1. 75.3.1. Installing the data mining add-ins
        2. 75.3.2. Exploring and preparing data
          1. 75.3.2.1. Explore Data
          2. 75.3.2.2. Clean Data
          3. 75.3.2.3. Partition Data
        3. 75.3.3. Table analysis tools
          1. 75.3.3.1. Analyze Key Influencers
          2. 75.3.3.2. Detect Categories
          3. 75.3.3.3. Highlight Exceptions
          4. 75.3.3.4. Fill from Example
          5. 75.3.3.5. Forecasting
          6. 75.3.3.6. Scenario Analysis
          7. 75.3.3.7. Prediction Calculator
          8. 75.3.3.8. Shopping Basket Analysis
        4. 75.3.4. Data mining client
      4. 75.4. Summary
    7. 76. Data Mining with Analysis Services
      1. 76.1. The Data Mining Process
      2. 76.2. Modeling with Analysis Services
        1. 76.2.1. Data Mining Wizard
        2. 76.2.2. Mining Models view
        3. 76.2.3. Model evaluation
          1. 76.2.3.1. Lift charts and scatter plots
          2. 76.2.3.2. Classification matrix
          3. 76.2.3.3. Cross validation
          4. 76.2.3.4. Troubleshooting models
          5. 76.2.3.5. Deploying
      3. 76.3. Algorithms
        1. 76.3.1. Decision tree
        2. 76.3.2. Linear regression
        3. 76.3.3. Clustering
        4. 76.3.4. Sequence clustering
        5. 76.3.5. Neural Network
        6. 76.3.6. Logistic regression
        7. 76.3.7. Naive Bayes
        8. 76.3.8. Association rules
        9. 76.3.9. Time series
      4. 76.4. Cube Integration
      5. 76.5. Summary
  17. A. SQL Server 2008 Specifications
    1. A.1. SQL Server 2008 Edition Features Table
  18. B. Using the Sample Databases
    1. B.1. The Sample Database Files
    2. B.2. Cape Hatteras Adventures Version 2
      1. B.2.1. Application requirements
      2. B.2.2. Database design
        1. B.2.2.1. Data conversion
        2. B.2.2.2. CHA2.adp front end
    3. B.3. OBX Kites
      1. B.3.1. Application requirements
      2. B.3.2. Database design
    4. B.4. The Family
      1. B.4.1. Application requirements
      2. B.4.2. Database design
    5. B.5. Aesop's Fables
      1. B.5.1. Application requirements
      2. B.5.2. Database design

Product information

  • Title: Microsoft® SQL Server® 2008 Bible
  • Author(s):
  • Release date: August 2009
  • Publisher(s): Wiley
  • ISBN: 9780470257043