book

Microsoft® SQL Server® 2008 Bible

Name: Microsoft® SQL Server® 2008 Bible
ISBN: 9780470257043

by Paul Nielsen, Mike White, Uttam Parui

August 2009

Beginner to intermediate

1680 pages

42h 30m

English

Wiley

Read now

Unlock full access

Copyright
About Paul Nielsen
About the Contributing AuthorsAbout the Technical Reviewers
Credits
Acknowledgments
Foreword
Introduction
Who Should Read This BookHow This Book Is OrganizedSQL Server Books OnlineConventions and FeaturesTips, Notes, Cautions, and Cross-ReferencesWhat's New and Best Practice Sidebarswww.SQLServerBible.comWhere to Go from Here
I. Laying the Foundation
1. The World of SQL Server
1.1. A Great Choice1.2. SQL Server Database Engine1.2.1. Database Engine1.2.2. Transact-SQL1.2.3. Policy-Based Management1.2.4. .NET Common Language Runtime1.2.5. Service Broker1.2.6. Replication services1.2.7. Integrated Full-Text Search1.2.8. Server management objects1.2.9. Filestream1.3. SQL Server Services1.3.1. SQL Server Agent1.3.2. Database Mail1.3.3. Distributed Transaction Coordinator (DTC)1.4. Business Intelligence1.4.1. Business Intelligence Development Studio1.4.2. Integration Services1.4.3. Analysis Services1.4.3.1. OLAP1.4.3.2. Data Mining1.4.4. Reporting Services1.5. UI and Tools1.5.1. SQL Server Management Studio1.5.2. SQL Server Configuration Manager1.5.3. SQL Profiler/Trace1.5.4. Performance Monitor1.5.5. Command-line utilities1.5.6. Books Online1.6. SQL Server Editions1.7. Exploring the Metadata1.7.1. System databases1.7.2. Metadata views1.8. What's New?1.9. Summary
2. Data Architecture
2.1. Information Architecture Principle2.2. Database Objectives2.2.1. Usability2.2.2. Extensibility2.2.3. Data integrity2.2.4. Performance/scalability2.2.5. Availability2.2.6. Security2.3. Smart Database Design2.3.1. Database system2.3.2. Physical schema2.3.3. Set-based queries2.3.4. Indexing2.3.5. Concurrency2.3.6. Advanced scalability2.3.7. A performance framework2.3.8. Issues and objections2.4. Summary
3. Relational Database Design
3.1. Database Basics3.1.1. Benefits of a digital database3.1.2. Tables, rows, columns3.1.3. Database design phases3.1.4. Normalization3.1.5. The three "Rules of One"3.1.6. Identifying entities3.1.7. Generalization3.1.8. Primary keys3.1.9. Foreign keys3.1.10. Cardinality3.1.11. Optionality3.2. Data Design Patterns3.2.1. One-to-many pattern3.2.2. One-to-one pattern3.2.3. Many-to-many pattern3.2.4. Supertype/subtype pattern3.2.5. Domain integrity lookup pattern3.2.6. Recursive pattern3.2.7. Database design layers3.3. Normal Forms3.3.1. First normal form (1NF)3.3.2. Second normal form (2NF)3.3.3. Third normal form (3NF)3.3.4. The Boyce-Codd normal form (BCNF)3.3.5. Fourth normal form (4NF)3.3.6. Fifth normal form (5NF)3.4. Summary

4. Installing SQL Server 2008
4.1. Selecting Server Hardware4.1.1. CPU planning4.1.2. Copious memory4.1.3. Disk-drive subsystems4.1.4. Network performance4.2. Preparing the Server4.2.1. Dedicated server4.2.2. Operating system4.2.3. Service accounts4.2.4. Server instances4.3. Performing the Installation4.3.1. Attended installations4.3.1.1. Setup Support Rules and Support Files pages4.3.1.2. Installation Type page4.3.1.3. Product Key and License Terms pages4.3.1.4. Feature Selection page4.3.1.5. Instance Configuration page4.3.1.6. Disk Space Requirements page4.3.1.7. Server Configuration page4.3.1.8. Database Engine Configuration page4.3.1.9. Analysis Services Configuration page4.3.1.10. Reporting Services Configuration page4.3.1.11. Error and Usage Reporting page4.3.1.12. Installation Rules and Ready to Install pages4.3.2. Unattended installations4.3.3. Remote installations4.4. Upgrading from Previous Versions4.4.1. Upgrading from SQL Server 20054.5. Migrating to SQL Server4.5.1. Migrating from Access4.5.2. Migration Assistant4.5.2.1. Assessment4.5.2.2. Schema conversion4.5.2.3. Data migration4.5.2.4. Business logic conversion4.5.2.5. Validation and Integration4.6. Removing SQL Server4.7. Summary
5. Client Connectivity
5.1. Enabling Server Connectivity5.1.1. Server Configuration Manager5.1.2. SQL Native Client Connectivity (SNAC)5.2. SQL Server Native Client Features5.2.1. Requirements5.2.2. Asynchronous operations5.2.3. Multiple Active Result Sets (MARS)5.2.4. XML data types5.2.5. User-defined types5.2.6. Large value types5.2.7. Handling expired passwords5.2.8. Snapshot isolation5.3. Summary
6. Using Management Studio
6.1. Organizing the Interface6.1.1. Window placement6.1.2. The Context Menu6.2. Registered Servers6.2.1. Managing Servers6.2.2. Server Groups6.3. Object Explorer6.3.1. Navigating the tree6.3.2. Filtering Object Explorer6.3.3. Object Explorer Details6.3.4. The Table Designer6.3.5. Building database diagrams6.3.6. The Query Designer6.3.7. Object Explorer reports6.4. Using the Query Editor6.4.1. Opening a query connecting to a server6.4.2. Opening a .sql file6.4.3. Shortcuts and bookmarks6.4.4. Query options6.4.5. Executing SQL batches6.4.6. Results!6.4.7. Viewing query execution plans6.5. Using the Solution Explorer6.6. Jump-Starting Code with Templates6.6.1. Using templates6.6.2. Managing templates6.7. Summary
7. Scripting with PowerShell
7.1. Why Use PowerShell?7.2. Basic PowerShell7.2.1. Language features7.2.2. Creating scripts7.3. Communicating with SQL Server7.3.1. SQL Server Management Objects7.3.2. ADO.NET7.4. Scripting SQL Server Tasks7.4.1. Administrative tasks7.4.2. Data-based tasks7.5. SQL Server PowerShell Extensions7.5.1. SQLPS.exe7.5.2. The SQL PSDrive—SQLSERVER:7.5.3. SQL cmdlets7.6. Summary
II. Manipulating Data with Select
8. Introducing Basic Query Flow
8.1. Understanding Query Flow8.1.1. Syntactical flow of the query statement8.1.2. A graphical view of the query statement8.1.3. Logical flow of the query statement8.1.4. Physical flow of the query statement8.2. From Clause Data Sources8.2.1. Possible data sources8.2.2. Table aliases8.2.3. [Table Name]8.2.4. Fully qualified names8.3. Where Conditions8.3.1. Using the between search condition8.3.2. Comparing with a list8.3.3. Using the like search condition8.3.4. Multiple where conditions8.3.5. Select...where8.4. Columns, Stars, Aliases, and Expressions8.4.1. The star8.4.2. Aliases8.4.3. Qualified columns8.5. Ordering the Result Set8.5.1. Specifying the order by using column names8.5.2. Specifying the order by using expressions8.5.3. Specifying the order by using column aliases8.5.4. Using the column ordinal position8.5.5. Order by and collation8.6. Select Distinct8.7. Top ()8.7.1. The with ties option8.7.2. Selecting a random row8.8. Summary
9. Data Types, Expressions, and Scalar Functions
9.1. Building Expressions9.1.1. Operators9.1.2. Bitwise operators9.1.2.1. Boolean and9.1.2.2. Boolean or9.1.2.3. Boolean exclusive or9.1.2.4. Bitwise not9.1.3. Case expressions9.1.3.1. Simple case9.1.3.2. Boolean case9.1.4. Working with nulls9.1.4.1. Testing for null9.1.4.2. Handling nulls9.1.4.3. Using the COALESCE() function9.1.4.4. Using the ISNULL() function9.1.4.5. Using the NULLIF() function9.2. Scalar Functions9.2.1. User information functions9.2.2. Date and time functions9.3. String Functions9.4. Soundex Functions9.4.1. Using the SOUNDEX() function9.4.2. Using the DIFFERENCE() Soundex function9.5. Data-Type Conversion Functions9.6. Server Environment Information9.7. Summary
10. Merging Data with Joins and Unions
10.1. Using Joins10.2. Inner Joins10.2.1. Building inner joins with the Query Designer10.2.2. Creating inner joins within SQL code10.2.3. Number of rows returned10.2.4. ANSI SQL 89 joins10.2.5. Multiple data source joins10.3. Outer Joins10.3.1. Using the Query Designer to create outer joins10.3.2. T-SQL code and outer joins10.3.3. Outer joins and optional foreign keys10.3.4. Full outer joins10.3.5. Red thing blue thing10.3.6. Placing the conditions within outer joins10.3.7. Multiple outer joins10.4. Self-Joins10.5. Cross (Unrestricted) Joins10.6. Exotic Joins10.6.1. Multiple-condition joins10.6.2. Θ (theta) joins10.6.3. Non-key joins10.7. Set Difference Queries10.7.1. Left set difference query10.7.2. Full set difference queries10.8. Using Unions10.8.1. Union [All]10.8.2. Intersection union10.8.3. Difference union/except10.9. Summary
11. Including Data with Subqueries and CTEs
11.1. Methods and Locations11.2. Simple Subqueries11.2.1. Common table expressions11.2.2. Using scalar subqueries11.2.3. Using subqueries as lists11.2.4. Using subqueries as tables11.2.5. Row constructors11.2.6. All, some, and any11.3. Correlated Subqueries11.3.1. Correlating in the where clause11.3.2. Correlating a derived table using apply11.4. Relational Division11.4.1. Relational division with a remainder11.4.2. Exact relational division11.5. Composable SQL11.6. Summary
12. Aggregating Data
12.1. Simple Aggregations12.1.1. Basic aggregations12.1.2. Aggregates, averages, and nulls12.1.3. Using aggregate functions within the Query Designer12.1.4. Beginning statistics12.2. Grouping within a Result Set12.2.1. Simple groupings12.2.2. Grouping sets12.2.3. Filtering grouped results12.3. Aggravating Queries12.3.1. Including group by descriptions12.3.2. Including all group by values12.3.3. Nesting aggregations12.3.4. Including detail descriptions12.4. OLAP in the Park12.4.1. Rollup subtotals12.4.2. Cube queries12.5. Building Crosstab Queries12.5.1. Pivot method12.5.2. Case expression method12.5.3. Dynamic crosstab queries12.5.4. Unpivot12.6. Cumulative Totals (Running Sums)12.6.1. Correlated subquery solution12.6.2. T-SQL cursor solution12.6.3. Multiple assignment variable solution12.7. Summary
13. Windowing and Ranking
13.1. Windowing13.1.1. The Over() clause13.1.2. Partitioning within the window13.2. Ranking Functions13.2.1. Row number() function13.2.2. Rank() and dense_rank() functions13.2.3. Ntile() function13.2.4. Aggregate Functions13.3. Summary
14. Projecting Data Through Views
14.1. Why Use Views?14.2. The Basic View14.2.1. Creating views using the Query Designer14.2.2. Creating views with DDL code14.2.3. Executing views14.2.4. Altering and dropping a view14.3. A Broader Point of View14.3.1. Column aliases14.3.2. Order by and views14.3.3. View restrictions14.3.4. Nesting views14.3.5. Updating through views14.3.6. Views and performance14.4. Locking Down the View14.4.1. Unchecked data14.4.2. Protecting the data14.4.3. Protecting the view14.4.4. Encrypting the view's select statement14.4.5. Application metadata14.5. Using Synonyms14.6. Summary
15. Modifying Data
15.1. Inserting Data15.1.1. Inserting simple rows of values15.1.2. Inserting a result set from select15.1.3. Inserting the result set from a stored procedure15.1.4. Creating a default row15.1.5. Creating a table while inserting data15.2. Updating Data15.2.1. Updating a single table15.2.2. Performing global search and replace15.2.3. Referencing multiple tables while updating data15.3. Deleting Data15.3.1. Referencing multiple data sources while deleting15.3.2. Cascading deletes15.3.3. Alternatives to physically deleting data15.4. Merging Data15.5. Returning Modified Data15.5.1. Returning data from an insert15.5.2. Returning data from an update15.5.3. Returning data from a delete15.5.4. Returning data from a merge15.5.5. Returning data into a table15.6. Summary
16. Modification Obstacles
16.1. Data Type/Length16.2. Primary Key Constraint and Unique Constraint16.2.1. Identity columns16.2.2. Globally unique identifiers (GUIDs)16.3. Deleting Duplicate Rows16.3.1. Deleting duplicate rows using windowing16.3.2. Deleting duplicate rows using a surrogate key16.3.3. Deleting duplicate rows using select distant into16.4. Foreign Key Constraints16.5. Null and Default Constraints16.6. Check Constraints16.7. Instead of Triggers16.8. After Triggers16.9. Non-Updateable Views16.10. Views With Check Option16.11. Calculated Columns16.12. Security Constraints16.13. Summary
III. Beyond Relational
17. Traversing Hierarchies
17.1. Adjacency List Pattern17.1.1. Single-level queries17.1.2. Subtree queries17.1.2.1. Recursive CTE down the hierarchy17.1.2.2. User-defined function down the hierarchy17.1.2.3. Recursive CTE looking up the hierarchy17.1.2.4. Searching up the hierarchy with a user-defined function17.1.3. Is the node an ancestor?17.1.4. Determining the node's level17.1.5. Reparenting the adjacency list17.1.6. Indexing an adjacency list17.1.7. Cyclic errors17.1.8. Adjacency list variations17.1.8.1. Bills of materials/multiple cardinalities17.1.9. Adjacency list pros and cons17.2. The Materialized-Path Pattern17.2.1. Subtree queries17.2.1.1. Searching down the hierarchy with materialized path17.2.1.2. Searching up the hierarchy with materialized path17.2.2. Is the node in the subtree?17.2.3. Determining the node level17.2.4. Single-level queries17.2.5. Reparenting the materialized path17.2.6. Indexing the materialized path17.2.7. Materialized path pros and cons17.3. Using the New HierarchyID17.3.1. Selecting a single node17.3.2. Scanning for ancestors17.3.3. Performing a subtree search17.3.4. Single-level searches17.3.5. Inserting new nodes17.3.6. Performance17.3.7. HierarchyID pros and cons17.4. Summary
18. Manipulating XML Data
18.1. XML Processing in SQL Server 200818.1.1. Generating XML documents18.1.2. Querying XML documents18.1.3. Validating XML documents18.2. Sample Tables and Data18.3. XML Data Type18.3.1. Typed and untyped XML18.3.2. Creating and using XML columns18.3.3. Declaring and using XML variables18.3.4. Using XML parameters and return values18.3.5. Loading/querying XML documents from disk files18.3.6. Limitations of the XML data type18.4. Understanding XML Data Type Methods18.4.1. XPath18.4.2. value()18.4.3. nodes()18.4.4. exist()18.4.5. query()18.4.6. modify()18.4.7. Joining XML nodes with relational tables18.4.8. Using variables and filters in XQuery expressions18.4.9. Accessing the parent node18.5. Generating XML Output Using FOR XML18.5.1. FOR XML AUTO18.5.2. FOR XML RAW18.5.3. FOR XML EXPLICIT18.5.4. FOR XML PATH18.5.5. TYPE directive18.5.6. XSINIL Directive18.5.7. Generating XML Schema information18.5.8. Generating XML namespaces18.6. Understanding XQuery and FLWOR operations18.6.1. Simple queries18.6.2. FLWOR operation18.6.3. What's new for XQuery in SQL Server 200818.7. Understanding XQuery Functions18.7.1. String functions18.7.2. Numeric and aggregate functions18.7.3. Other functions18.8. Performing XML Data Modification18.8.1. Insert operation18.8.2. Update operation18.8.3. Delete operation18.8.4. What's new for XML DML operations in SQL Server 200818.9. Handling Namespaces18.10. Shredding XML Using OPENXML()18.11. XSD and XML Schema Collections18.11.1. Creating an XML Schema collection18.11.2. Creating typed XML columns and variables18.11.3. Performing validation18.11.4. XML DOCUMENT and CONTENT18.11.5. Altering XML Schema collections18.11.6. What's in the "collection"?18.11.7. What's new in SQL Server 2008 for XSD18.12. Understanding XML Indexes18.13. XML Best Practices18.14. Summary
19. Using Integrated Full-Text Search
19.1. Configuring Full-Text Search Catalogs19.1.1. Creating a catalog with the wizard19.1.2. Creating a catalog with T-SQL code19.1.3. Pushing data to the full-text index19.1.4. Maintaining a catalog with Management Studio19.1.5. Maintaining a catalog in T-SQL code19.2. Word Searches19.2.1. The Contains function19.2.2. The ContainsTable function19.3. Advanced Search Options19.3.1. Multiple-word searches19.3.2. Searches with wildcards19.3.3. Phrase searches19.3.4. Word-proximity searches19.3.5. Word-inflection searches19.3.6. Thesaurus searches19.3.7. Variable-word-weight searches19.4. Fuzzy Searches19.4.1. Freetext19.4.2. FreetextTable19.5. Performance19.6. Summary
IV. Developing with SQL Server
20. Creating the Physical Database Schema
20.1. Designing the Physical Database Schema20.1.1. Logical to physical options20.1.2. Refining the data patterns20.1.3. Designing for performance20.1.4. Responsible denormalization20.1.5. Designing for extensibility20.2. Creating the Database20.2.1. The Create DDL command20.2.2. Database-file concepts20.2.3. Configuring file growth20.2.4. Using multiple files20.2.4.1. Creating a database with multiple files20.2.4.2. Modifying the files of an existing database20.2.5. Planning multiple filegroups20.2.5.1. Creating a database with filegroups20.2.5.2. Modifying filegroups20.2.5.3. Dropping a database20.3. Creating Tables20.3.1. Designing tables using Management Studio20.3.2. Working with SQL scripts20.3.3. Schemas20.3.4. Column names20.3.5. Filegroups20.4. Creating Keys20.4.1. Primary keys20.4.2. The surrogate debate: pros and cons20.4.3. Database design layers20.4.3.1. Creating primary keys20.4.3.2. Identity column surrogate primary keys20.4.3.3. Using uniqueidentifier surrogate primary keys20.4.4. Creating foreign keys20.4.4.1. Declarative referential integrity20.4.4.2. Optional foreign keys20.4.4.3. Cascading deletes and updates20.5. Creating User-Data Columns20.5.1. Column data types20.5.1.1. Character data types20.5.1.2. Numeric data types20.5.1.3. Date/Time data types20.5.1.4. Other data types20.5.2. Calculated columns20.5.3. Sparse columns20.5.4. Column constraints and defaults20.5.4.1. Column nullability20.6. Creating Indexes20.6.1. Composite indexes20.6.2. Primary keys20.6.3. Filegroup location20.6.4. Index options20.6.4.1. Unique indexes20.6.5. Include columns20.6.6. Filtered indexes20.6.6.1. Index fill factor and pad index20.6.6.2. Limiting index locks and parallelism20.6.6.3. Index sort order20.6.6.4. The Ignore Dup Key index option20.6.6.5. The Drop Existing index option20.6.6.6. The Statistics Norecompute index option20.6.6.7. Sort in tempdb20.6.6.8. Disabling an index20.7. Summary
21. Programming with T-SQL
21.1. Transact-SQL Fundamentals21.1.1. T-SQL batches21.1.1.1. Terminating a batch21.1.1.2. DDL commands21.1.1.3. Switching databases21.1.1.4. Executing batches21.1.2. T-SQL formatting21.1.2.1. Statement termination21.1.2.2. Line continuation21.1.2.3. Comments21.2. Variables21.2.1. Variable default and scope21.2.2. Using the set and select commands21.2.3. Incrementing variables21.2.4. Conditional select21.2.5. Using variables within SQL queries21.2.6. Multiple assignment variables21.3. Procedural Flow21.3.1. If21.3.1.1. Begin/end21.3.1.2. If exists()21.3.1.3. If/else21.3.2. While21.3.3. Goto21.4. Examining SQL Server with Code21.4.1. Dynamic Management Views21.4.2. sp_help21.4.3. System functions21.5. Temporary Tables and Table Variables21.5.1. Local temporary tables21.5.2. Global temporary tables21.5.3. Table variables21.6. Summary
22. Kill the Cursor!
22.1. Anatomy of a Cursor22.1.1. The five steps to cursoring22.1.2. Managing the cursor22.1.3. Watching the cursor22.1.4. Cursor options22.1.5. Update cursor22.1.6. Cursor scope22.1.7. Cursors and transactions22.2. Cursor Strategies22.3. Refactoring Complex-Logic Cursors22.3.1. Update query with user-defined function22.3.2. Multiple queries22.3.3. Query with case expression22.4. Summary
23. T-SQL Error Handling
23.1. Legacy Error Handling23.1.1. @@error system function23.1.2. @@rowcount system function23.2. Raiserror23.2.1. The simple raiserror form23.2.2. The improved raiserror form23.2.3. Error severity23.2.3.1. Adding variable parameters to messages23.2.4. Stored messages23.2.4.1. Logging the error23.2.4.2. SQL Server log23.3. Try...Catch23.3.1. Catch block23.3.2. Nested try/catch and rethrown errors23.4. T-SQL Fatal Errors23.5. Summary
24. Developing Stored Procedures
24.1. Managing Stored Procedures24.1.1. Create, alter, and drop24.1.2. Executing a stored procedure24.1.3. Returning a record set24.1.4. Compiling stored procedures24.1.5. Stored procedure encryption24.1.6. System stored procedures24.1.7. Using stored procedures within queries24.1.8. Executing remote stored procedures24.2. Passing Data to Stored Procedures24.2.1. Input parameters24.2.2. Parameter defaults24.2.3. Table-valued parameters24.3. Returning Data from Stored Procedures24.3.1. Output parameters24.3.2. Using the Return Command24.3.3. Path and scope of returning data24.4. Summary
25. Building User-Defined Functions
25.1. Scalar Functions25.1.1. Limitations25.1.2. Creating a scalar function25.1.3. Calling a scalar function25.2. Inline Table-Valued Functions25.2.1. Creating an inline table-valued function25.2.2. Calling an inline table-valued function25.2.3. Using parameters25.2.4. Correlated user-defined functions25.2.5. Creating functions with schema binding25.3. Multi-Statement Table-Valued Functions25.3.1. Creating a multi-statement table-valued function25.3.2. Calling the function25.4. Summary
26. Creating DML Triggers
26.1. Trigger Basics26.1.1. Transaction flow26.1.2. Creating triggers26.1.3. After triggers26.1.4. Instead of triggers26.1.5. Trigger limitations26.1.6. Disabling triggers26.1.7. Listing triggers26.1.8. Triggers and security26.2. Working with the Transaction26.2.1. Determining the updated columns26.2.2. Inserted and deleted logical tables26.2.3. Developing multi-row-enabled triggers26.3. Multiple-Trigger Interaction26.3.1. Trigger organization26.3.2. Nested triggers26.3.3. Recursive triggers26.3.4. Instead of and after triggers26.3.5. Multiple after triggers26.4. Transaction-Aggregation Handling26.4.1. The inventory-transaction trigger26.4.2. The inventory trigger26.5. Summary
27. DDL Triggers
27.1. Managing DDL Triggers27.1.1. Creating and altering DDL triggers27.1.2. Trigger scope27.1.3. DDL triggers and security27.1.4. Enabling and disabling DDL triggers27.1.5. Removing DDL triggers27.2. Developing DDL Triggers27.2.1. EventData()27.2.2. Preventing database object changes27.3. Summary
28. Building Out the Data Abstraction Layer
28.1. CRUD Stored Procedures28.2. Google-Style Search Procedure28.3. Summary
29. Dynamic SQL and Code Generation
29.1. Executing Dynamic SQL29.1.1. sp_executeSQL29.1.2. Parameterized queries29.1.3. Developing dynamic SQL code29.1.4. Code generation29.2. Preventing SQL Injection29.2.1. Appending malicious code29.2.2. Or 1=129.2.3. Password? What password?29.2.4. Preventing SQL Server injection attacks29.3. Summary
V. Data Connectivity
30. Bulk Operations
30.1. Bulk Insert30.2. Bulk Insert Options30.3. BCP30.4. Summary
31. Executing Distributed Queries
31.1. Distributed Query Concepts31.2. Accessing a Local SQL Server Database31.3. Linking to External Data Sources31.3.1. Linking to SQL Server with Management Studio31.3.1.1. Selecting the server31.3.1.2. Configuring the logins31.3.1.3. Configuring the options31.3.2. Linking to SQL Server with T-SQL31.3.2.1. Establishing the link31.3.2.2. Distributed security and logins31.3.2.3. Linked server options31.3.3. Linking with non–SQL Server data sources31.3.3.1. Linking to Excel31.3.3.2. Linking to MS Access31.4. Developing Distributed Queries31.4.1. Distributed queries and Management Studio31.4.2. Distributed views31.4.3. Local-distributed queries31.4.3.1. Using the four-part name31.4.3.2. OpenDataSource()31.4.4. Pass-through distributed queries31.4.4.1. Using the four-part name31.4.4.2. OpenQuery()31.4.4.3. OpenRowSet()31.5. Distributed Transactions31.5.1. Distributed Transaction Coordinator31.5.2. Developing distributed transactions31.5.3. Monitoring distributed transactions31.6. Summary
32. Programming with ADO.NET 3.5
32.1. An Overview of ADO.NET32.1.1. ADO32.1.1.1. OLEDB32.1.1.2. ADODB primary interop assembly32.1.2. The ADO object model32.1.2.1. OLEDB data providers32.1.2.2. Mapping data types32.1.2.3. ADO and scripting32.1.3. ADO.NET32.1.3.1. The ADO.NET object model32.1.3.2. Managed providers32.1.3.3. SQL Native Client32.1.3.4. Data types32.1.3.5. DataAdapters and DataSets32.2. ADO.NET in Visual Studio 200832.2.1. Server Explorer32.2.2. Debugging ADO.NET32.2.3. Application tracing32.3. Application Building Basics32.3.1. Connecting to SQL Server32.3.2. What's new in ADO.NET 3.532.3.2.1. LINQ32.3.2.2. Table-valued parameters32.3.3. Stored procedures vs. parameterized/ad-hoc queries32.3.4. Data adapters32.3.5. DataReaders and Recordsets32.3.6. Streams32.3.7. Asynchronous execution32.3.8. Using a single database value32.3.9. Data modification32.3.10. Binding to controls32.4. Summary
33. Sync Framework
33.1. Sync Framework example33.1.1. Sync Framework overview33.1.1.1. Sync architecture33.1.1.2. Sync knowledge33.1.1.3. Sync fundamentals33.1.2. Sync services for ADO.NET 2.033.1.2.1. What's new in Sync Services 2.033.1.2.2. Offline scenarios33.1.2.3. Collaboration scenarios33.2. Summary
34. LINQ
34.1. LINQ Overview34.1.1. What Is LINQ?34.1.2. Standard Query Operators34.1.3. Query expression syntax34.1.3.1. Query syntax and method syntax34.1.3.2. Choosing a query syntax34.2. LINQ to SQL34.2.1. Example 1: Manually applying the mappings34.2.2. Example 2: The easy way34.3. LINQ to XML34.3.1. LINQ to XML example34.3.2. Traversing XML34.4. LINQ to DataSet34.4.1. Querying a DataSet using LINQ to DataSet34.4.2. Data binding with LINQ to DataSet34.5. LINQ to Entities34.5.1. Creating and querying entities using LINQ34.5.2. Querying multiple tables using LINQ to Entities and the Entity Framework34.6. Summary
35. Asynchronous Messaging with Service Broker
35.1. Configuring a Message Queue35.2. Working with Conversations35.2.1. Sending a message to the queue35.2.2. Receiving a message35.3. Monitoring Service Broker35.4. Summary
36. Replicating Data
36.1. Replication Concepts36.1.1. Types of replication36.1.2. Replication agents36.1.3. Transactional consistency36.2. Configuring Replication36.2.1. Creating a publisher and distributor36.2.2. Creating a snapshot/transactional publication36.2.3. Creating a push subscription to a transactional/snapshot publication36.2.4. Creating a pull subscription to a transactional/snapshot publication36.2.5. Creating a peer-to-peer topology36.2.6. Creating a merge publication36.2.7. Web synchronization36.3. Summary
37. Performing ETL with Integration Services
37.1. Design Environment37.1.1. Connection managers37.1.2. Variables37.1.2.1. Variable usage37.1.2.2. Expressions37.1.3. Configuring elements37.1.3.1. Control flow37.1.3.2. Control flow precedence37.1.3.3. Data flow37.1.4. Event handlers37.1.5. Executing a package in development37.2. Integration Services Package Elements37.2.1. Connection managers37.2.1.1. Database37.2.1.2. File37.2.1.3. Special37.2.2. Control flow elements37.2.2.1. Containers37.2.2.2. Control flow tasks37.2.2.3. Maintenance Plan tasks37.2.3. Data flow components37.2.3.1. Sources37.2.3.2. Destinations37.2.3.3. Transformations37.3. Maintainable and Manageable Packages37.3.1. Logging37.3.2. Package configurations37.3.3. Checkpoint restart37.4. Deploying Packages37.4.1. Installing packages37.4.2. Executing packages37.5. Summary
38. Access as a Front End to SQL Server
38.1. Access–SQL Server Use Case Scenarios38.1.1. Access projects or ODBC linked tables?38.2. Migrating from Access to SQL Server38.3. Designing Your Access Front End38.4. Connecting to SQL Server38.4.1. Linking to tables and views38.4.2. Caching data in local tables using pass-through queries38.4.3. Extending the power of pass-through queries using table-valued parameters (TVPs)38.5. Monitoring and Troubleshooting38.6. Ad Hoc Querying and Reporting38.6.1. Pre-aggregating data on the server38.6.2. Sorting and filtering data38.6.3. Creating forms and reports38.6.4. Exporting and publishing data38.6.5. Managing your SQL Server databases38.7. Summary
VI. Enterprise Data Management
39. Configuring SQL Server
39.1. Setting the Options39.1.1. Configuring the server39.1.2. Configuring the database39.1.3. Configuring the connection39.2. Configuration Options39.2.1. Displaying the advanced options39.2.2. Start/Stop configuration properties39.2.2.1. Startup parameters39.2.2.2. Startup stored procedures39.2.3. Memory-configuration properties39.2.3.1. Dynamic memory39.2.3.2. Fixed memory39.2.3.3. Minimum query memory39.2.3.4. Query wait39.2.3.5. AWE enabled39.2.3.6. Index create memory39.2.4. Processor-configuration properties39.2.4.1. Processor affinity39.2.4.2. Max worker threads39.2.4.3. Priority boost39.2.4.4. Lightweight pooling39.2.4.5. Parallelism39.2.5. Security-configuration properties39.2.5.1. Server authentication mode39.2.5.2. Security-audit level39.2.5.3. C2 audit tracing39.2.5.4. Cross-database ownership chaining39.2.6. Connection-configuration properties39.2.6.1. Maximum concurrent user connections39.2.6.2. Query governor cost limit39.2.6.3. Remote access39.2.6.4. Remote login timeout39.2.6.5. Remote query timeout39.2.6.6. Enforce DTC39.2.6.7. Network packet size39.2.7. Advanced server-configuration properties39.2.7.1. Filestream access level39.2.7.2. Extensible Key Management39.2.7.3. Default full-text language39.2.7.4. Default language39.2.7.5. Two-digit-year cutoff39.2.7.6. Max text replication size39.2.8. Configuring database auto options39.2.8.1. Auto close39.2.8.2. Auto shrink39.2.8.3. Auto create statistics39.2.8.4. Auto update statistics39.2.8.5. Auto update statistics asynchronously39.2.9. Cursor-configuration properties39.2.9.1. Cursor threshold39.2.9.2. Cursor close on commit39.2.9.3. Cursor default39.2.10. SQL ANSI–configuration properties39.2.10.1. ANSI defaults39.2.10.2. ANSI null default39.2.10.3. ANSI NULLs39.2.10.4. ANSI padding39.2.10.5. ANSI warnings39.2.10.6. Arithmetic abort39.2.10.7. Arithmetic ignore39.2.10.8. Numeric round abort39.2.10.9. Concatenation null yields null39.2.10.10. Use quoted identifier39.2.11. Trigger configuration properties39.2.11.1. Nested triggers39.2.11.2. Recursive triggers39.2.12. Database-state-configuration properties39.2.12.1. Database-access level39.2.12.2. Compatibility level39.2.13. Recovery-configuration properties39.2.13.1. Recovery model39.2.13.2. Page Verify39.2.13.3. Media retention39.2.13.4. Backup compression39.2.13.5. Recovery interval39.3. Summary
40. Policy-Based Management
40.1. Defining Policies40.1.1. Management facets40.1.2. Health conditions40.1.3. Policies40.2. Evaluating Policies40.3. Summary
41. Recovery Planning
41.1. Recovery Concepts41.2. Recovery Models41.2.1. Simple recovery model41.2.2. The full recovery model41.2.3. Bulk-logged recovery model41.2.4. Setting the recovery model41.2.5. Modifying recovery models41.3. Backing Up the Database41.3.1. Backup destination41.3.2. Backup rotation41.3.3. Performing backup with Management Studio41.3.4. Backing up the database with code41.3.5. Verifying the backup with code41.4. Working with the Transaction Log41.4.1. Inside the transaction log41.4.1.1. The active and inactive divide41.4.1.2. Transaction checkpoints41.4.2. Backing up the transaction log41.4.3. Truncating the log41.4.4. The transaction log and simple recovery model41.5. Recovery Operations41.5.1. Detecting the problem41.5.2. Recovery sequences41.5.3. Performing the restore with Management Studio41.5.4. Restoring with T-SQL code41.6. System Databases Recovery41.6.1. Master database41.6.1.1. Backing up the master database41.6.1.2. Recovering the master database41.6.2. MSDB system database41.7. Performing a Complete Recovery41.8. Summary
42. Maintaining the Database
42.1. DBCC Commands42.1.1. Database integrity42.1.1.1. Repairing the database42.1.1.2. Multi-user concerns42.1.1.3. Object-level validation42.1.1.4. Data integrity42.1.2. Index maintenance42.1.2.1. Database fragmentation42.1.2.2. Index statistics42.1.2.3. Index density42.1.3. Database file size42.1.3.1. Monitoring database file sizes42.1.3.2. Shrinking the database42.1.3.3. Shrinking the transaction log42.1.4. Miscellaneous DBCC commands42.2. Managing Database Maintenance42.2.1. Planning database maintenance42.2.2. Maintenance plan42.2.2.1. Adding a task42.2.2.2. Defining the schedule42.2.2.3. Creating new connections42.2.2.4. Logging the maintenance progress42.2.3. Command-line maintenance42.2.4. Monitoring database maintenance42.3. Summary
43. Automating Database Maintenance with SQL Server Agent
43.1. Setting Up SQL Server Agent43.2. Understanding Alerts, Operators, and Jobs43.3. Managing Operators43.4. Managing Alerts43.4.1. Creating user-defined errors43.4.2. Creating an alert43.5. Managing Jobs43.5.1. Creating a job category43.5.2. Creating a job definition43.5.3. Setting up the job steps43.5.4. Configuring a job schedule43.5.5. Handling completion-, success-, and failure-notification messages43.6. Database Mail43.6.1. Configuring database mail43.6.1.1. Using the Database Mail Configuration Wizard43.7. Summary
44. Transferring Databases
44.1. Copy Database Wizard44.2. Working with SQL Script44.3. Detaching and Attaching44.4. Import and Export Wizard44.5. Summary
45. Database Snapshots
45.1. How Database Snapshots Work45.2. Creating a Database Snapshot45.3. Using Your Database Snapshots45.4. Performance Considerations and Best Practices45.5. Summary
46. Log Shipping
46.1. Availability Testing46.2. Warm Standby Availability46.3. Defining Log Shipping46.3.1. Configuring log shipping46.3.1.1. Pre-log shipping configuration46.3.1.2. Configuring log shipping using Management Studio46.3.1.3. Configuring log shipping using Transact-SQL46.3.1.4. Post-log shipping configuration46.4. Checking Log Shipping Configuration46.5. Monitoring Log Shipping46.6. Modifying or Removing Log Shipping46.7. Switching Roles46.7.1. Returning to the original primary server46.8. Summary
47. Database Mirroring
47.1. Database Mirroring Overview47.2. Defining Database Mirroring47.2.1. Configuring database mirroring47.2.1.1. Pre-database mirroring configuration47.2.1.2. Configuring database mirroring using Management Studio47.2.1.3. Configuring database mirroring using Transact-SQL47.2.1.4. Post-database mirroring configuration47.3. Checking a Database Mirroring Configuration47.4. Monitoring Database Mirroring47.4.1. Monitoring using Database Mirroring Monitor47.4.2. Monitoring using System Monitor47.4.3. Monitoring using SQL Server Profiler47.5. Pausing or Removing Database Mirroring47.6. Role Switching47.7. Summary
48. Clustering
48.1. SQL Server 2008 Failover Clustering Basics48.1.1. How SQL Server 2008 failover clustering works48.1.2. SQL Server 2008 failover clustering topologies48.2. Enhancements in SQL Server 2008 Failover Clustering48.3. SQL Server 2008 Failover Clustering Setup48.3.1. Planning SQL Server 2008 failover clustering48.3.2. SQL Server 2008 prerequisites48.3.3. Creating a single-node SQL Server 2008 failover cluster48.3.4. Adding a node to an existing SQL Server 2008 failover cluster48.3.5. Post-installation tasks48.3.6. Uninstalling a SQL Server 2008 failover cluster48.3.7. Installing a failover cluster using a command prompt48.3.8. Rolling upgrade and patching48.3.8.1. Patching a SQL Server 2008 failover cluster48.3.9. Maintaining a SQL Server 2008 failover cluster48.3.10. Troubleshooting a SQL Server 2008 failover cluster48.4. Summary
VII. Security
49. Authenticating Principals
49.1. Server-Level Security49.2. Database-Level Security49.3. Windows Security49.3.1. Using Windows Security49.3.2. SQL Server login49.4. Server Security49.4.1. SQL Server authentication mode49.4.2. Windows Authentication49.4.2.1. Adding a new Windows login49.4.2.2. Removing a Windows login49.4.2.3. Denying a Windows login49.4.2.4. Setting the default database49.4.2.5. Orphaned Windows users49.4.2.6. Security delegation49.4.3. SQL Server logins49.4.3.1. Updating a password49.4.3.2. Removing a login49.4.3.3. Setting the default database49.4.3.4. Server roles49.5. Database Security49.5.1. Guest logins49.5.2. Granting access to the database49.5.2.1. Granting access using T-SQL code49.5.3. Fixed database roles49.5.4. Assigning fixed database roles with Management Studio49.5.4.1. Assigning fixed database roles with T-SQL49.5.5. Application roles49.6. Summary
50. Authorizing Securables
50.1. Object Ownership50.2. Object Security50.2.1. Standard database roles50.2.2. Object permissions50.2.3. Granting object permissions with code50.2.4. Revoking and denying object permission with code50.2.5. The public role50.2.6. Managing roles with code50.2.7. Hierarchical role structures50.2.8. Object security and Management Studio50.2.8.1. From the object list50.2.8.2. From the user list50.2.8.3. From the role list50.2.8.4. Ownership chains50.2.9. Stored procedure execute as50.3. A Sample Security Model Example50.4. Views and Security50.5. Summary
51. Data Cryptography
51.1. Introduction to Cryptography51.1.1. Types of encryption51.1.2. The hierarchy of keys51.2. Encrypting Data51.2.1. Encrypting with a passphrase51.2.2. Encrypting with a symmetric key51.2.2.1. Encryption algorithms51.2.2.2. Using the symmetric key51.2.3. Using asymmetric keys51.2.4. Using certificates51.3. Transparent Data Encryption51.4. Summary
52. Row-Level Security
52.1. The Security Table52.2. Assigning Permissions52.2.1. Assigning security52.2.2. Handling security-level updates52.3. Checking Permissions52.3.1. The security-check stored procedure52.3.2. The security-check function52.3.3. Using the NT login52.3.4. The security-check trigger52.4. Summary
VIII. Monitoring and Auditing
53. Data Audit Triggers
53.1. AutoAudit53.1.1. Installing AutoAudit53.1.2. The audit table53.1.3. Running AutoAudit53.1.4. _Modified trigger53.1.5. Auditing changes53.1.6. Viewing and undeleting deleted rows53.1.7. Viewing row history53.1.8. Backing out AutoAudit53.2. Auditing Complications53.2.1. Auditing related data53.2.2. Auditing select statements53.2.3. Data auditing and security53.2.4. Data auditing and performance53.3. Summary
54. Schema Audit Triggers
54.1. SchemaAudit Table54.2. SchemaAudit Trigger54.3. Summary
55. Performance Monitor
55.1. Using Performance Monitor55.1.1. System monitor55.1.2. Counter Logs55.2. Summary
56. Tracing and Profiling
56.1. Running Profiler56.1.1. Defining a new trace56.1.2. Selecting events and data columns56.1.3. Filtering events56.1.4. Organizing columns56.1.5. Running the trace56.1.6. Using the trace file56.1.7. Integrating Performance Monitor data56.2. Using SQL Trace56.2.1. Preconfigured traces56.3. Summary
57. Wait States
57.1. Observing Wait State Statistics57.1.1. Querying wait states57.1.2. Activity Monitor57.2. Analyzing Wait States57.3. Summary
58. Extended Events
58.1. XE Components58.1.1. Packages58.1.2. Objects58.2. XE Sessions58.3. Summary
59. Change Tracking
59.1. Configuring Change Tracking59.1.1. Enabling the database59.1.2. Auto cleanup59.1.3. Enabling tables59.1.4. Enabling all tables59.1.5. Internal tables59.2. Querying Change Tracking59.2.1. Version numbers59.2.2. Changes by the row59.2.3. Coding a synchronization59.3. Change Tracking Options59.3.1. Column tracking59.3.2. Determining latest version per row59.3.3. Capturing application context59.4. Removing Change Tracking59.5. Summary
60. Change Data Capture
60.1. Enabling CDC60.1.1. Enabling the database60.1.2. Enabling tables60.2. Working with Change Data Capture60.2.1. Examining the log sequence numbers60.2.2. Querying the change tables60.2.3. Querying net changes60.2.4. Walking through the change tables60.3. Removing Change Data Capture60.4. Summary
61. SQL Audit
61.1. SQL Audit Technology Overview61.2. Creating an Audit61.2.1. Defining the target61.2.2. Using T-SQL61.2.3. Enabling/disabling the audit61.3. Server Audit Specifications61.3.1. Adding actions61.3.2. Creating with T-SQL61.3.3. Modifying Server Audit Specifications61.4. Database Audit Specifications61.5. Viewing the Audit Trail61.6. Summary
62. Management Data Warehouse
62.1. Configuring MDW62.1.1. Configuring a data warehouse62.1.2. Configuring a data collection62.2. The MDW Data Warehouse62.3. Summary
IX. Performance Tuning and Optimization
63. Interpreting Query Execution Plans
63.1. Viewing Query Execution Plans63.1.1. Estimated query execution plans63.1.2. The Query Editor's execution plan63.1.3. Returning the plan with showplans63.1.4. SQL Profiler's execution plans63.1.5. Examining plans using dynamic management views63.2. Interpreting the Query Execution Plan63.3. Summary
64. Indexing Strategies
64.1. Zen and the Art of Indexing64.2. Indexing Basics64.2.1. The b-tree index64.2.2. Clustered indexes64.2.3. Non-clustered indexes64.2.4. Composite indexes64.2.5. Unique indexes and constraints64.2.6. The page split problem64.2.7. Index selectivity64.2.8. Unordered heaps64.2.9. Query operations64.3. Path of the Query64.3.1. Query Path 1: Fetch All64.3.2. Query Path 2: Clustered Index Seek64.3.3. Query Path 3: Range Seek Query64.3.4. Query Path 4: Filter by non-key column64.3.5. Query Path 5: Bookmark Lookup64.3.6. Query Path 6: Covering Index64.3.7. Query Path 7: Filter by 2 x NC Indexes64.3.8. Query Path 8: Filter by Ordered Composite Index64.3.9. Query Path 9: Filter by Unordered Composite Index64.3.10. Query Path 10: Non-SARGable Expressions64.4. A Comprehensive Indexing Strategy64.4.1. Identifying key queries64.4.2. Table CRUD analysis64.4.3. Selecting the clustered index64.4.4. Creating base indexes64.5. Specialty Indexes64.5.1. Filtered indexes64.5.2. Indexed views64.5.2.1. Indexed Views and Queries64.5.2.2. Updating indexed views64.6. Summary
65. Query Plan Reuse
65.1. Query Compiling65.1.1. The Query Optimizer65.1.2. Viewing the Plan Cache65.1.3. Plan lifetime65.1.4. Query plan execution65.2. Query Recompiles65.3. Summary
66. Managing Transactions, Locking, and Blocking
66.1. The ACID Properties66.1.1. Atomicity66.1.2. Consistency66.1.3. Isolation66.1.4. Durability66.2. Programming Transactions66.2.1. Logical transactions66.2.2. Xact_State()66.2.3. Xact_Abort66.2.4. Nested transactions66.2.5. Implicit transactions66.2.6. Save points66.3. Default Locking and Blocking Behavior66.4. Monitoring Locking and Blocking66.4.1. Viewing blocking with Management Studio reports66.4.2. Viewing blocking with Activity Monitor66.4.3. Using Profiler66.4.4. Querying locks with DMVs66.5. Deadlocks66.5.1. Creating a deadlock66.5.2. Automatic deadlock detection66.5.3. Handling deadlocks66.5.4. Minimizing deadlocks66.6. Understanding SQL Server Locking66.6.1. Lock granularity66.6.2. Lock mode66.6.2.1. Lock contention66.6.2.2. Shared lock (S)66.6.2.3. Exclusive lock (X)66.6.2.4. Update lock (U)66.6.2.5. Intent locks (various)66.6.2.6. Schema lock (Sch-M, Sch-S)66.6.3. Controlling lock timeouts66.6.4. Lock duration66.6.5. Index-level locking restrictions66.7. Transaction Isolation Levels66.7.1. Setting the transaction isolation level66.7.2. Level 1—Read uncommitted and the dirty read66.7.3. Level 2—Read committed66.7.4. Level 3—Repeatable read66.7.4.1. Nonrepeatable read fault66.7.4.2. Preventing the fault66.7.5. Level 4—Serializable66.7.5.1. Phantom rows66.7.5.2. Serialized transaction isolation level66.7.6. Snapshot isolations66.7.6.1. Enabling row versioning66.7.6.2. Using read committed snapshot isolation66.7.6.3. Handling write conficts66.7.7. Using locking hints66.8. Application Locks66.9. Application Locking Design66.9.1. Implementing optimistic locking66.9.2. Lost updates66.9.2.1. Minimizing lost updates66.9.2.2. Preventing lost updates66.10. Transaction-Log Architecture66.10.1. Transaction log sequence66.10.1.1. Database beginning state66.10.1.2. Data-modification command66.10.1.3. Transaction log recorded66.10.1.4. Transaction commit66.10.1.5. Data-file update66.10.1.6. Transaction complete66.10.1.7. Transaction-log rollback66.10.2. Transaction log recovery66.11. Transaction Performance Strategies66.11.1. Evaluating database concurrency performance66.12. Summary
67. Data Compression
67.1. Understanding Data Compression67.1.1. Data compression pros and cons67.1.2. Row compression67.1.3. Page compression67.1.4. Compression sequence67.2. Applying Data Compression67.2.1. Determining the current compression setting67.2.2. Estimating data compression67.2.3. Enabling data compression67.2.4. Data compression strategies67.3. Summary
68. Partitioning
68.1. Partitioning Strategies68.2. Partitioned Views68.2.1. Local-partition views68.2.1.1. Segmenting the data68.2.1.2. Creating the partition view68.2.1.3. Selecting through the partition view68.2.1.4. Updating through the partition view68.2.1.5. Moving data68.2.2. Distributed-partition views68.2.2.1. Updating and moving data with distributed-partition views68.2.2.2. Highly scalable distributed-partition views68.3. Partitioned Tables and Indexes68.3.1. Creating the partition function68.3.2. Creating partition schemes68.3.3. Creating the partition table68.3.4. Querying partition tables68.3.5. Altering partition tables68.3.5.1. Merging partitions68.3.5.2. Splitting partitions68.3.6. Switching tables68.3.6.1. Prepping the new table68.3.6.2. Prepping the partition table68.3.6.3. Performing the switch68.3.6.4. Switching out68.3.7. Rolling partitions68.3.8. Indexing partitioned tables68.3.8.1. Creating partitioned indexes68.3.8.2. Maintaining partitioned indexes68.3.9. Removing partitioning68.4. Data-Driven Partitioning68.5. Summary
69. Resource Governor
69.1. Configuring Resource Governor69.1.1. Resource pools69.1.2. Workload groups69.1.3. Classifier functions69.2. Monitoring Resource Governor69.3. Summary
X. Business Intelligence
70. BI Design
70.1. Data Warehousing70.1.1. Star schema70.1.2. Snowflake schema70.1.3. Surrogate keys70.1.4. Consistency70.1.5. Loading data70.1.5.1. Loading dimensions70.1.5.2. Loading fact tables70.1.6. Changing data in dimensions70.2. Summary
71. Building Multidimensional Cubes with Analysis Services
71.1. Analysis Services Quick Start71.2. Analysis Services Architecture71.2.1. Unified Dimensional Model71.2.2. Server71.2.3. Client71.3. Building a Database71.3.1. Business Intelligence Development Studio71.3.2. Data sources71.3.3. Data source view71.3.3.1. Creating the data source view71.3.3.2. Managing the data source view71.3.3.3. Refining the data source view71.3.4. Creating a cube71.4. Dimensions71.4.1. Dimension Designer71.4.1.1. Attributes71.4.1.2. Attribute source columns and ordering71.4.1.3. Hierarchies and attribute relationships71.4.1.4. Creating user hierarchies71.4.1.5. Establishing attribute relationships71.4.1.6. Visibility and organization71.4.1.7. Basic setup checklist71.4.2. Beyond regular dimensions71.4.2.1. Time dimension71.4.2.2. Other dimension types71.4.2.3. Parent-child dimensions71.4.3. Dimension refinements71.4.3.1. Hierarchy (All) level and default member71.4.3.2. Grouping dimension members71.5. Cubes71.5.1. Cube structure71.5.1.1. Measures71.5.1.2. Cube dimensions71.5.2. Dimension usage71.5.2.1. No relationship71.5.2.2. Regular71.5.2.3. Fact71.5.2.4. Referenced71.5.2.5. Many-to-Many71.5.3. Calculations71.5.4. KPIs71.5.5. Actions71.5.6. Partitions71.5.6.1. Partition sizing71.5.6.2. Creating partitions71.5.7. Aggregation design71.5.7.1. Aggregation Design Wizard71.5.7.2. Aggregations tab71.5.8. Perspectives71.6. Data Storage71.6.1. Proactive caching71.6.2. SQL Server notifications71.6.3. Client-initiated notifications71.6.4. Scheduled polling notifications71.7. Data Integrity71.7.1. Null processing71.7.2. Unknown member71.7.3. Error Configuration71.8. Summary
72. Programming MDX Queries
72.1. Basic Select Query72.1.1. Cube addressing72.1.2. Dimension structure72.1.2.1. Dimension references72.1.2.2. Tuples and simple sets72.1.3. Basic SELECT statement72.1.3.1. Measures72.1.3.2. Generating sets from functions72.1.3.3. Using SQL Server Management Studio72.2. Advanced Select Query72.2.1. Subcubes72.2.2. WITH clause72.2.2.1. Sets72.2.2.2. Calculated Members72.2.3. Dimension considerations72.3. MDX Scripting72.3.1. Calculated members and named sets72.3.2. Adding Business Intelligence72.4. Summary
73. Authoring Reports with Reporting Services
73.1. Anatomy of a Report73.1.1. Report Definition Language (RDL)73.1.2. Data Sources73.1.2.1. Data Source types73.1.2.2. Data source connection strings73.1.2.3. Using expressions in a connection string73.1.2.4. Setting data source credentials73.1.3. Reporting Services datasets73.1.4. Query parameters and report parameters73.1.5. Report content and layout73.2. The Report Authoring Process73.2.1. Creating a Reporting Services project73.2.2. Creating a report73.2.3. Using the Report Wizard to create reports73.2.4. Authoring a report from scratch73.2.4.1. Creating a shared data source73.2.4.2. Adding a blank report73.2.4.3. Creating a dataset73.2.4.4. Displaying data in the report73.2.4.5. Previewing the report73.3. Working with Data73.3.1. Working with SQL in the Query Designer73.3.2. Using query parameters to select and filter data73.3.2.1. Nested report parameters73.3.2.2. Multi-value report parameters73.3.3. Adding calculated fields to a dataset73.3.4. Working with XML data sources73.3.5. Working with expressions73.3.5.1. Expression scope73.3.5.2. Expressing yourself with common expressions73.4. Designing the Report Layout73.4.1. Design basics73.4.1.1. Designing the report header73.4.1.2. Designing the report footer73.4.1.3. Adding and formatting a Table report item73.4.1.4. Adding and formatting a List report item73.4.2. Using the Tablix property pages73.4.3. Grouping and sorting data in a Tablix73.4.3.1. Setting the sort order for a Tablix73.4.3.2. Formatting tables with groups73.4.4. Illustrating data with charts and gauges73.5. Summary
74. Administering Reporting Services
74.1. Deploying Reporting Services Reports74.1.1. Deploying reports using BIDS74.1.1.1. Deploying a single report or data source74.1.1.2. Deploying a Reporting Services project74.1.2. Deploying reports using the Report Manager74.1.3. Deploying reports programmatically using the Reporting Services Web Service74.2. Configuring Reporting Services Using Management Studio74.2.1. Configuring Reporting Services server properties74.2.1.1. General74.2.1.2. Configuring report execution settings74.2.1.3. Configuring report history settings74.2.2. Security: managing roles74.2.2.1. System-level roles74.2.2.2. Item-level roles74.3. Configuring Reporting Services Using Report Manager74.3.1. Managing security74.3.1.1. Granting system access to users and groups74.3.1.2. Item-level permissions74.3.1.3. Controlling item-level security74.3.2. Working with linked reports74.3.3. Creating linked reports74.3.4. Leveraging the power of subscriptions74.3.5. Creating a data-driven subscription74.4. Summary
75. Analyzing Data with Excel
75.1. Data Connections75.1.1. Data Connection Wizard75.1.2. Microsoft Query75.1.3. Connection file types75.2. Basic Data Analysis75.2.1. Data tables75.2.2. PivotTables75.2.3. PivotCharts75.3. Advanced Data Analysis75.3.1. Installing the data mining add-ins75.3.2. Exploring and preparing data75.3.2.1. Explore Data75.3.2.2. Clean Data75.3.2.3. Partition Data75.3.3. Table analysis tools75.3.3.1. Analyze Key Influencers75.3.3.2. Detect Categories75.3.3.3. Highlight Exceptions75.3.3.4. Fill from Example75.3.3.5. Forecasting75.3.3.6. Scenario Analysis75.3.3.7. Prediction Calculator75.3.3.8. Shopping Basket Analysis75.3.4. Data mining client75.4. Summary
76. Data Mining with Analysis Services
76.1. The Data Mining Process76.2. Modeling with Analysis Services76.2.1. Data Mining Wizard76.2.2. Mining Models view76.2.3. Model evaluation76.2.3.1. Lift charts and scatter plots76.2.3.2. Classification matrix76.2.3.3. Cross validation76.2.3.4. Troubleshooting models76.2.3.5. Deploying76.3. Algorithms76.3.1. Decision tree76.3.2. Linear regression76.3.3. Clustering76.3.4. Sequence clustering76.3.5. Neural Network76.3.6. Logistic regression76.3.7. Naive Bayes76.3.8. Association rules76.3.9. Time series76.4. Cube Integration76.5. Summary
A. SQL Server 2008 Specifications
A.1. SQL Server 2008 Edition Features Table
B. Using the Sample Databases
B.1. The Sample Database FilesB.2. Cape Hatteras Adventures Version 2B.2.1. Application requirementsB.2.2. Database designB.2.2.1. Data conversionB.2.2.2. CHA2.adp front endB.3. OBX KitesB.3.1. Application requirementsB.3.2. Database designB.4. The FamilyB.4.1. Application requirementsB.4.2. Database designB.5. Aesop's FablesB.5.1. Application requirementsB.5.2. Database design

Content preview from Microsoft® SQL Server® 2008 Bible

Chapter 70. BI Design

IN THIS CHAPTER

Differences between OLTP and OLAP
Data warehousing concepts
Warehouse structures and relationships
Loading dimensions and fact tables
Managing changing dimension data

Having worked with various organizations and data systems, over time I've noticed a progression of reporting and analysis solutions. First queries are run directly against the online transactional processing (OLTP) database, but this approach conflicts with production use of the database and generally limits access to a very few staff due to security concerns.

Often the next step is to make a copy of the OLTP database for the express purpose of running analytical or reporting queries. These attempts at using an OLTP database for online analytical processing (OLAP) are problematic on a number of fronts:

OLTP data structures are optimized for single, atomic transactions, whereas OLAP queries summarize large volumes of data. Thus, queries are painfully slow.
OLTP data may reflect limited history, whereas OLAP tends to be interested in historical trends.
OLTP data structures are understood by a relatively small population of experts in the organization, whereas OLAP is most effective when exposed to the widest possible audience.

A common refinement on querying the OLTP database is to create a new database that contains tables of summary data. When done carefully, this approach can address some speed and history issues, but it is still understood by a relatively small population. Consistent interpretation ...

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

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

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

Julian F.

Head of Cybersecurity

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

Addison B.

Field Engineer

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

Amir M.

Data Platform Tech Lead

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

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780470257043Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Microsoft® SQL Server® 2008 Bible

by Paul Nielsen, Mike White, Uttam Parui

Chapter 70. BI Design

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