SQL Server MVP Deep Dives

By Paul S. Randal, Kimberly Tripp, Adam Machanic and Paul Nielsen

This is not an ordinary SQL Server Book. SQL Server MVP Deep Dives brings together the world's most highly-regarded SQL Server experts to create a masterful collection of tips, techniques, and experience-driven best practices for SQL Server development and administration. These SQL Server MVPs-53 in all-each selected a topic of great interest to them, and in this unique book, they share their knowledge and passion with you.

SQL Server MVP Deep Dives is organized into five parts: Design and Architecture, Development, Administration, Performance Tuning and Optimization, and Business Intelligence. Within each part, you'll find a collection of brilliantly concise and focused chapters that take on key topics like mobile data strategies, Dynamic Management Views, or query performance. The range of subjects covered is comprehensive, from database design tips to data profiling strategies for BI.

Additionally, the authors of this book have generously donated 100% of their royalties to support War Child International. War Child International is a network of independent organizations, working across the world to help children affected by war. War Child was founded upon a fundamental goal: to advance the cause of peace through investing hope in the lives of children caught up in the horrors of war. War Child works in many different conflict areas around the world, helping hundreds of thousands of children every year. Visit www.warchild.org for more information.

Purchase of the print book comes with an offer of a free PDF, ePub, and Kindle eBook from Manning. Also available is all code from the book.

• Language: English
• Publisher: Manning
• Release date: Oct 31, 2009
• ISBN: 9781638352204

Book preview

Copyright

For online information and ordering of this and other Manning books, please visit www.manning.com. The publisher offers discounts on this book when ordered in quantity. For more information, please contact

    Special Sales Department

    Manning Publications Co.

    Sound View Court 3B

    Greenwich, CT 06830

    Email: [e-mail]

©2010 by Manning Publications Co. All rights reserved.

No part of this publication may be reproduced, stored in a retrieval system, or transmitted, in any form or by means electronic, mechanical, photocopying, or otherwise, without prior written permission of the publisher.

Many of the designations used by manufacturers and sellers to distinguish their products are claimed as trademarks. Where those designations appear in the book, and Manning Publications was aware of a trademark claim, the designations have been printed in initial caps or all caps.

Recognizing the importance of preserving what has been written, it is Manning’s policy to have the books we publish printed on acid-free paper, and we exert our best efforts to that end. Recognizing also our responsibility to conserve the resources of our planet, Manning books are printed on paper that is at least 15 percent recycled and processed without the use of elemental chlorine.

Development editor: Jeff Bleiel

Lead copyeditor: Andy Carroll

Typesetter: Dottie Marsico

Cover designer: Marija Tudor

Printed in the United States of America

1 2 3 4 5 6 7 8 9 10 – MAL – 14 13 12 11 10 09

Dedication

To all children traumatized by the horrors of war and War Child’s efforts to help children be children again

Brief Table of Contents

Copyright

Brief Table of Contents

Table of Contents

List of Figures

List of Tables

List of Listings

MVP contributors and their chapters

Preface

Acknowledgments

About War Child

About this Book

About SQL Server MVPs

1. Database design and architecture

Chapter 1. Louis and Paul’s 10 key relational database design ideas

Chapter 2. SQL Server tools for maintaining data integrity

Chapter 3. Finding functional dependencies

2. Database Development

Chapter 4. Set-based iteration, the third alternative

Chapter 5. Gaps and islands

Chapter 6. Error handling in SQL Server and applications

Chapter 7. Pulling apart the FROM clause

Chapter 8. What makes a bulk insert a minimally logged operation?

Chapter 9. Avoiding three common query mistakes

Chapter 10. Introduction to XQuery on SQL Server

Chapter 11. SQL Server XML frequently asked questions

Chapter 12. Using XML to transport relational data

Chapter 13. Full-text searching

Chapter 14. Simil: an algorithm to look for similar strings

Chapter 15. LINQ to SQL and ADO.NET Entity Framework

Chapter 16. Table-valued parameters

Chapter 17. Build your own index

Chapter 18. Getting and staying connected—or not

Chapter 19. Extending your productivity in SSMS and Query Analyzer

Chapter 20. Why every SQL developer needs a tools database

Chapter 21. Deprecation feature

Chapter 22. Placing SQL Server in your pocket

Chapter 23. Mobile data strategies

3. Database Administration

Chapter 24. What does it mean to be a DBA?

Chapter 25. Working with maintenance plans

Chapter 26. PowerShell in SQL Server

Chapter 27. Automating SQL Server Management using SMO

Chapter 28. Practical auditing in SQL Server 2008

Chapter 29. My favorite DMVs, and why

Chapter 30. Reusing space in a table

Chapter 31. Some practical issues in table partitioning

Chapter 32. Partitioning for manageability (and maybe performance)

Chapter 33. Efficient backups without indexes

Chapter 34. Using database mirroring to become a superhero!

Chapter 35. The poor man’s SQL Server log shipping

Chapter 36. Understated changes in SQL Server 2005 replication

Chapter 37. High-performance transactional replication

Chapter 38. Successfully implementing Kerberos delegation

Chapter 39. Running SQL Server on Hyper-V

4. Performance Tuning and Optimization

Chapter 40. When is an unused index not an unused index?

Chapter 41. Speeding up your queries with index covering

Chapter 42. Tracing the deadlock

Chapter 43. How to optimize tempdb performance

Chapter 44. Does the order of columns in an index matter?

Chapter 45. Correlating SQL Profiler with PerfMon

Chapter 46. Using correlation to improve query performance

Chapter 47. How to use Dynamic Management Views

Chapter 48. Query performance and disk I/O counters

Chapter 49. XEVENT: the next event infrastructure

5. Business intelligence

Chapter 50. BI for the relational guy

Chapter 51. Unlocking the secrets of SQL Server 2008 Reporting Services

Chapter 52. Reporting Services tips and tricks

Chapter 53. SQL Server Audit, change tracking, and change data capture

Chapter 54. Introduction to SSAS 2008 data mining

Chapter 55. To aggregate or not to aggregate—is there really a question?

Chapter 56. Incorporating data profiling in the ETL process

Chapter 57. Expressions in SQL Server Integration Services

Chapter 58. SSIS performance tips

Chapter 59. Incremental loads using T-SQL and SSIS

Index

Table of Contents

Copyright

Brief Table of Contents

Table of Contents

List of Figures

List of Tables

List of Listings

MVP contributors and their chapters

Preface

Acknowledgments

About War Child

About this Book

About SQL Server MVPs

1. Database design and architecture

Chapter 1. Louis and Paul’s 10 key relational database design ideas

1. Denormalization is for wimps

2. Keys are key

3. Generalize, man!

4. Class <> table

5. Data drives design

6. Sets good, cursors bad

7. Properly type data

8. Extensibility through encapsulation

9. Spaghetti is food, not code

10. NOLOCK = no consistency

Summary

About the authors

Chapter 2. SQL Server tools for maintaining data integrity

Protection tools

Data types

NULL specification

Uniqueness constraints

Filtered unique indexes

Foreign key constraints

Check constraints

Triggers

When and why to use what tool

Summary

About the author

Chapter 3. Finding functional dependencies

Interview method

Modeling the sales order

First step: finding single-attribute dependencies

Second step: finding two-attribute dependencies

Further steps: three-and-more-attribute dependencies

What if I have some independent attributes left?

Summary

About the author

2. Database Development

Chapter 4. Set-based iteration, the third alternative

The common methods and their shortcomings

Declarative (set-based) code

Iterative (cursor-based) code

Set-based iteration

The most basic form

Running totals

Bin packing

Summary

About the author

Chapter 5. Gaps and islands

Description of gaps and islands problems

Sample data and desired results

Solutions to gaps problem

Gaps—solution 1 using subqueries

Gaps—solution 2 using subqueries

Gaps—solution 3 using ranking functions

Gaps—solution 4 using cursors

Performance summary for gaps solutions

Solutions to islands problem

Islands—solution 1 using subqueries and ranking calculations

Islands—solution 2 using group identifier based on subqueries

Islands—solution 3 using group identifier based on ranking calculations

Islands—solution 4 using cursors

Variation on the islands problem

Performance summary for islands solutions

Summary

About the author

Chapter 6. Error handling in SQL Server and applications

Handling errors inside SQL Server

Returning information about the error

Generate your own errors using RAISERROR

Nesting TRY...CATCH blocks

TRY...CATCH and transactions

Handling SQL Server errors on the client

Handling SQL Server messages on the client

Summary

About the author

Chapter 7. Pulling apart the FROM clause

JOIN basics

The INNER JOIN

The OUTER JOIN

The CROSS JOIN

Formatting your FROM clause

A sample query

The appearance of most queries

When the pattern doesn’t apply

How to read a FROM clause

When the pattern can’t apply

Writing the FROM clause clearly the first time

Filtering with the ON clause

The different filters of the SELECT statement

Filtering out the matches

JOIN uses and simplification

The four uses of JOINs

Simplification using views

How JOIN uses affect you

Summary

About the author

Chapter 8. What makes a bulk insert a minimally logged operation?

Recovery and locking

Creating the file to import

Creating the tables to store the data

Importing the data

Summary

About the author

Chapter 9. Avoiding three common query mistakes

NULL comparisons

Multiple OUTER JOINS

Incorrect GROUP BY clauses

Summary

About the author

Chapter 10. Introduction to XQuery on SQL Server

What is XQuery?

How XQuery sees your XML

Querying XML

FLWOR expressions

XQuery comparison operators

XML indexes and XQuery performance

Summary

About the author

Chapter 11. SQL Server XML frequently asked questions

XML basics

What’s XML?

What’s well-formed XML?

What’s the prolog?

What’s an entity?

What’s a DTD?

The xml data type

Why does SQL Server remove the DTD from my XML data?

How do I preserve whitespace in my XML?

Why am I getting strange characters in my XML?

How do I query XML data?

How do I query a single value from my XML data?

How do I shred XML data?

Advanced query topics

How do I specify an XML namespace in my XQuery queries?

How do I get all element names and values from my XML document?

How do I load XML documents from the filesystem?

Summary

About the author

Chapter 12. Using XML to transport relational data

Understanding before coding

The concept

The logical model

The physical model

The database

The XML Schema

Enabling and maintaining the data flow

Preparing the inbound data flow

Importing the data

Exporting the data

Preparing the sample data

Homework

Summary

About the author

Chapter 13. Full-text searching

Foundations of full-text searching

Creating and maintaining catalogs

Creating and maintaining full-text indexes

Creating the full-text index

Maintaining full-text indexes

Querying full-text indexes

Basic searches

FORMSOF

Phrases, NEAR, OR, and prefixed terms

Ranking

Custom thesaurus and stopwords

Custom thesaurus

Stopwords and stoplists

Useful system queries

Basic queries to discover what catalogs, indexes, and columns exist

Advanced queries

The keywords

Summary

About the author

Chapter 14. Simil: an algorithm to look for similar strings

Equals (=) and LIKE

SOUNDEX and DIFFERENCE

CONTAINS and FREETEXT

Simil

Algorithm

Implementation in .NET

Installation

Usage

Testing

Summary

About the author

Chapter 15. LINQ to SQL and ADO.NET Entity Framework

LINQ to SQL and performance

Generating SQL that uses projection

Updating in the middle tier

Optimizing the number of database round trips

LINQ to SQL and stored procedures

Tuning and LINQ to SQL queries

Summary

About the author

Chapter 16. Table-valued parameters

What’s the problem?

Table-valued parameters to the rescue!

Another TVP example

Using TVPs from client applications

Using a DataTable

Using a DbDataReader

Using TVPs to enter orders

Summary

About the author

Chapter 17. Build your own index

The database and the table

Plain search and introducing tester_sp

Using the LIKE operator—an important observation

Using a binary collation

Fragments and persons

The fragments_persons table

Writing the search procedure

Keeping the index and the statistics updated

What is the overhead?

Fragments and lists

Building the lists

Unwrapping the lists

The fragments_personlists table

Loading the table

A search procedure

Keeping the lists updated

Using bitmasks

The initial setup

Searching with the bitmask

Adapting the bitmask to the data

Performance and overhead

The big bitmask

Summary

About the author

Chapter 18. Getting and staying connected—or not

What is SQL Server?

Understanding the SQL Server Browser service

Diagnosing a connectivity problem

Testing for network availability

Managing the SQL Server instance state

Finding visible SQL Server instances

What is a connection?

To connect or not to connect...

Connection management

Connection strategies

Establishing a connection

The server key

Trusted or untrusted security?

Using trusted or integrated security

ASP.NET considerations

Using SQL Server authentication

Accepting user login credentials—or not

Accessing user instances

Connection pooling

Closing the connection

Summary

About the author

Chapter 19. Extending your productivity in SSMS and Query Analyzer

Custom keyboard shortcuts

Creating your custom utility to use with keyboard shortcuts

Some ideas for utilities to implement

Summary

About the author

Chapter 20. Why every SQL developer needs a tools database

What belongs in the tools database?

Creating the tools database

Using an auxiliary table of numbers

Generating a calendar on the fly

Splitting strings with a numbers table

Placing common code in the tools database

Formatting

Calling code from a different database

Summary

About the author

Chapter 21. Deprecation feature

A simple usage example

Methods of tracking deprecated features

Summary

About the author

Chapter 22. Placing SQL Server in your pocket

Design goals

Architecture

Deployment

Deploying on a desktop

Deploying on a device

XCOPY deployment

Tool support

Programming support

Summary

About the author

Chapter 23. Mobile data strategies

Microsoft Sync Framework (MSF)

Client synchronization provider

Server synchronization provider

Synchronization adapter

Synchronization agent

Using MSF

Comparison

Summary

About the author

3. Database Administration

Chapter 24. What does it mean to be a DBA?

Typical DBA tasks: A to Z

Application Integration

Archiving Data

Attending Meetings

Auditing

Backup and Recovery

Business Intelligence and Data Warehousing

Capacity Planning

Change Management

Data Modeling and Database Design

Database Application Development

Developing and Maintaining Best Practices

Disaster Recovery

Hardware Setup and Configuration

High Availability

Installing, Configuring, and Upgrading SQL Server Software

Load Balancing

Maintaining Documentation

Managing People

Managing SQL Server–Based Applications

Managing Test Environments

Mentoring

Monitoring

Needs and Requirements Analysis

Negotiating Service Level Agreements

Operating System Setup, Configuration, and Administration

Performance Tuning

Project Management

Replication

Report Writing

Running Jobs

Scripting

Security

SSIS and ETL

Testing

Troubleshooting

Working with Teammates

DBA specialties

DBA System Administrator

DBA Database Architect

DBA Database Designer

DBA Developer

DBA High Availability and Disaster Recovery Specialist

DBA Business Intelligence Specialist

DBA Report Writer

Summary

About the author

Chapter 25. Working with maintenance plans

What is a maintenance plan, and how do I create one?

Versions and service packs

One or several schedules per plan?

Wizard dialogs

Task types

Check database integrity task

Shrink database task

Reorganize index task

Rebuild index task

Update statistics task

History cleanup task

Execute SQL Server Agent job task

Back up database task

Maintenance cleanup task

Select reporting options

Execute T-SQL statement task

Executing and monitoring the plan

Summary

About the author

Chapter 26. PowerShell in SQL Server

PowerShell overview

PowerShell features

PowerShell issues and solutions

PowerShell with SQL Server 2000 and 2005

Data access

Administration

PowerShell with SQL Server 2008

SQLPS

Provider

Cmdlets

Summary

About the author

Chapter 27. Automating SQL Server Management using SMO

Loading required libraries

Backup

Restore

Creating a database

Scripting

Summary

About the author

Chapter 28. Practical auditing in SQL Server 2008

Overview of audit infrastructure

Server audit objects

Server audit specification objects

Database audit specification objects

Server audits

Configuring the Windows Security Log target

Creating a server audit using the Windows Security Log target

Creating a security audit using the Windows Application Log target

Configuring a server audit using the File target

Server audit specifications

Creating server audit specifications

Viewing audit events

Database audit specifications

Creating database audit specifications

Summary

About the author

Chapter 29. My favorite DMVs, and why

What is so great about DMVs, anyway?

A brief list of my favorite DMVs and DMFs

sys.dm_os_sys_info

sys.dm_exec_sessions, sys.dm_exec_requests, and sys.dm_exec_connections

sys.dm_exec_sql_text

sys.dm_exec_query_stats

sys.dm_exec_procedure_stats

sys.dm_db_index_usage_stats

sys.dm_db_missing_index_details, sys.dm_db_missing_index_groups, and sys.dm_db_missing_index_group_stats

Honorable mentions

sys.dm_os_performance_counters

sys.dm_db_partition_stats

sys.dm_db_index_physical_stats

sys.dm_sql_referenced_entities

Setting up a utility database

Some interesting applications of my favorite DMVs

A more refined sp_who or sp_who2

Getting statistics for stored procedures (SQL Server 2008 only)

Finding unused stored procedures (SQL Server 2008 only)

Finding inefficient and unused indexes

Finding inefficient queries

Finding missing indexes

DMV categories in SQL Server

Summary

About the author

Chapter 30. Reusing space in a table

Understanding how SQL Server automatically reuses table space

Recognizing when SQL Server does not reclaim space

Using DBCC CLEANTABLE to reclaim unused table space

Summary

About the author

Chapter 31. Some practical issues in table partitioning

Table partitioning dependencies

Manipulating partitioned data

How the partition function works

Drilling down: using SPLIT and MERGE

Drilling down: using SWITCH

The key: avoiding data movement

Sources for more information

Summary

About the author

Chapter 32. Partitioning for manageability (and maybe performance)

Overview

How to partition

Planning and design considerations

Gotchas and tips

Boundary time values

SPLIT and MERGE performance

Update statistics after SWITCH

Shared partition functions and schemes

Summary

About the author

Chapter 33. Efficient backups without indexes

It’s OK to not back up nonclustered indexes!

A simple example

Default table and index storage behavior

Adding a dedicated filegroup for nonclustered indexes

Moving nonclustered indexes into the new filegroup

Backing up only the PRIMARY filegroup

Restoring the PRIMARY filegroup backup

Restoring for extraction only

Restoring for production use

Restoring for production use—step by step

usp_Manage_NCIX_Filegroup

Planning before moving NCIXs into a dedicated filegroup

Moving NCIXs temporarily requires additional disk space

Moving NCIXs creates empty space in PRIMARY filegroup

Log shipping

Summary

About the author

Chapter 34. Using database mirroring to become a superhero!

Why should I use database mirroring?

How does database mirroring work?

How do you set up database mirroring?

How do you prepare the mirror?

Using database mirroring for routine maintenance

Using database mirroring to upgrade to SQL Server 2008

Using database mirroring to move data seamlessly

Case study of moving data with database mirroring

Lessons learned from case study

Summary

About the author

Chapter 35. The poor man’s SQL Server log shipping

Creating the T-SQL script

Creating a cleanup script

Creating a batch file

Improving the log shipping process

Summary

About the author

Chapter 36. Understated changes in SQL Server 2005 replication

Undocumented or partially documented changes in behavior

Reading the text of hidden replication stored procedures

Creating snapshots without any data—only the schema

Some changed replication defaults

More efficient methodologies

Remove redundant pre-snapshot and post-snapshot scripts

Replace merge -EXCHANGETYPE parameters

Summary

About the author

Chapter 37. High-performance transactional replication

Performance kiss of death factors in transactional replication

Batch updates

Replicating text

Logging

Network latency

Subscriber hardware

Subscriber indexes and triggers

Distributor hardware

Large numbers of push subscriptions

Optimal settings for replication

CommitBatchSize and CommitBatchThreshold

Update proc

SubscriptionStreams

Summary

About the author

Chapter 38. Successfully implementing Kerberos delegation

Understanding the issues that Kerberos delegation resolves

The double hop

A generic infrastructure—our business challenge

Understanding Kerberos delegation

Service principle names

Constrained delegation

Implementing Kerberos delegation step by step

Configuring the Active Directory

Configuring the client tier

Configuring the web tier

Configuring the data tier

Validating delegation from end to end

Downloading and installing DelegConfig

Running DelegConfig from the web tier

Running DelegConfig from the client tier

Resources to assist in more complex infrastructures

Summary

About the author

Chapter 39. Running SQL Server on Hyper-V

Virtualization architecture

Benefits of isolation

Configuring virtual machines

Configuring disks

CPU configuration

Configuring networking

Memory configuration

Addressing clock drift issues

Backup considerations

Advantages of physical to virtual migration

Test environments and virtualization

Summary

About the author

4. Performance Tuning and Optimization

Chapter 40. When is an unused index not an unused index?

Overview of indexing

Unused indexes

Unused indexes that are actually used

How is the unused index being used?

How does this affect me?

Summary

About the author

Chapter 41. Speeding up your queries with index covering

Index covering speeds up selects

Some rules of thumb about indexes aren’t true for covering indexes

Covering indexes usually slow down modifications

One index should cover many queries

One index can both cover queries and implement uniqueness

Summary

About the author

Chapter 42. Tracing the deadlock

What’s a deadlock?

Causes of deadlocks

Deadlock graph

Trace flag 1204

Trace flag 1222

SQL Profiler

Reading the deadlock graph

The process list

The resource list

The big picture

Summary

About the author

Chapter 43. How to optimize tempdb performance

What is tempdb used for?

Tempdb internals

How DBAs can help optimize tempdb

Minimizing the use of tempdb

Preallocating tempdb space and avoiding use of autogrowth

Don’t shrink tempdb if you don’t need to

Dividing tempdb among multiple physical files

Moving tempdb to a disk separate from your other databases

Locating tempdb on a fast I/O subsystem

Adding RAM to your SQL server instance

Using SQL Server 2008 transparent data encryption

Leaving auto create statistics and auto update statistics on

Verifying CHECKSUM for SQL Server 2008

Summary

About the author

Chapter 44. Does the order of columns in an index matter?

Understanding the basics of composite indexes

Finding a specific row

Finding a last name

Finding a first name

Summary

About the author

Chapter 45. Correlating SQL Profiler with PerfMon

What vexes you?

Getting started with PerfMon and Profiler

Best practices using PerfMon

Best practices using Profiler

A correlated view of performance

Summary

About the author

Chapter 46. Using correlation to improve query performance

The purpose of the optimizer

Correlation with the clustered index

Low correlation

When the optimizer does it right

When the optimizer does it right again

When the optimizer gets it wrong

Correcting the optimizer

When to expect correlation

Determining correlation

Summary

About the author

Chapter 47. How to use Dynamic Management Views

Why should I use DMV queries?

Setting up security to run DMV queries

Looking at top waits at the instance level

Looking for CPU pressure and what’s causing it

Finding I/O pressure in SQL Server

SQL Server memory pressure

SQL Server index usage

Detecting blocking in SQL Server

Summary

About the author

Chapter 48. Query performance and disk I/O counters

Expensive I/Os and very expensive I/Os

Disk performance counters

Random or sequential I/Os and disk performance counters

SQL Server operations and I/O sizes

How expensive are small random I/Os, anyway?

Performance scenarios

Scenario 1: constant checkpoints

Scenario 2: NOLOCK and faster query processing

Scenario 3: read-ahead reads

Scenario 4: index fragmentation

Summary

About the author

Chapter 49. XEVENT: the next event infrastructure

Extended Events infrastructure characteristics

XEVENT architecture

SQL Server Extended Events engine

Packages

Events

Targets

Actions

Predicates

Types and maps

Sessions

XEVENT in action

Usage scenarios

System health session

Performance considerations

Summary

About the author

5. Business intelligence

Chapter 50. BI for the relational guy

Business intelligence overview

Terminology

Really, what is so different?

Approach

Dimensional modeling

Cubes, anyone?

Microsoft BI stack

How do I get started?

Summary

About the author

Chapter 51. Unlocking the secrets of SQL Server 2008 Reporting Services

Why should developers care about Reporting Services?

What is Reporting Services?

Using Visual Studio to create an RDL report

Using the Visual Studio 2008 Report Designer

Managing report parameters

Deploying your report

Using the Report Manager

Using the Visual Studio MicrosoftReportViewer control

What’s in Reporting Services 2008 for developers?

Virtual directory changes

Using SQL Server Reporting Services Configuration Manager

Exporting reports

Enabling My Reports

Working with the Report Designer

Summary

About the author

Chapter 52. Reporting Services tips and tricks

Performance tips

Filters versus query parameters

Linked servers

Drillthrough instead of drill-down

Data export

Connection pooling

Design tips

Stored procedures and temp tables

Excel merged cell solution

Excel web queries and reports

HTML or Word documents

Server portability

Embedding T-SQL in a report

User!UserID

Summary

About the author

Chapter 53. SQL Server Audit, change tracking, and change data capture

What are these solutions used for?

What do people do now?

How does SQL Server 2008 solve these problems?

SQL Server Audit

Change tracking

Change data capture

Comparison of features

Summary

About the author

Chapter 54. Introduction to SSAS 2008 data mining

Data mining basics

Data mining projects

Data overview and preparation

SSAS 2008 data mining algorithms

Creating mining models

Harvesting the results

Viewing the models

Evaluating the models

Creating prediction queries

Sources for more information

Summary

About the author

Chapter 55. To aggregate or not to aggregate—is there really a question?

What are aggregations?

Designing aggregations

Influencing aggregations

Attribute relationships

Usage-based optimization

High-level approach

Other considerations

Summary

About the author

Chapter 56. Incorporating data profiling in the ETL process

Why profile data?

Introduction to the Data Profiling task

Types of profiles

Input to the task

Output from the task

Constraints of the Data Profiling task

Making the Data Profiling task dynamic

Changing the database

Altering the profile requests

Setting the ProfileInputXml property

Making data-quality decisions in the ETL

Excluding data based on quality

Adjusting rules dynamically

Consuming the task output

Capturing the output

Using SSIS XML functionality

Using scripts

Incorporating the values in the package

Summary

About the author

Chapter 57. Expressions in SQL Server Integration Services

SSIS packages: a brief review

Expressions: a quick tour

Expressions in the control flow

Expressions and variables

Expressions in the data flow

Expressions and connection managers

Summary

About the author

Chapter 58. SSIS performance tips

SSIS overview

Control flow performance

Data flow performance

Source acquisition performance

Data transformation performance

Destination performance

Lookup transformation performance

General data flow performance

Summary

About the author

Chapter 59. Incremental loads using T-SQL and SSIS

Some definitions

A T-SQL incremental load

Incremental loads in SSIS

Creating the new BIDS project

Defining the lookup transformation

Setting the lookup transformation behavior

Summary

About the author

Index

List of Figures

Chapter 3. Finding functional dependencies

Figure 1. A mocked-up database diagram makes it immediately obvious that this foreign key isn’t allowed.

Figure 2. A sample order confirmation form

Figure 3. Sample order confirmation modified to find which attributes depend on OrderNo

Chapter 7. Pulling apart the FROM clause

Figure 1. Identical query plans demonstrating the breakdown of the view

Figure 2. A much simpler execution plan involving only one table

Figure 3. A nonclustered index being used

Figure 4. The ProductSubcategory table being used again

Figure 5. This execution plan is simpler because NULLs are not being introduced.

Chapter 8. What makes a bulk insert a minimally logged operation?

Figure 1. Bulk Insert Task Editor

Figure 2. Properties window

Chapter 9. Avoiding three common query mistakes

Figure 1. NULL values are included along with the colors that have data.

Figure 2. No results returned

Figure 3. The list of colors not used in the Production.Product table

Figure 4. All rows from the Production.ColorList and the Production.Product rows that match. Non-matching rows return NULL in the ProductID column.

Figure 5. The non-matching rows are lost.

Figure 6. The correct results when the LEFT OUTER JOIN is continued

Figure 7. An extra column in the GROUP BY clause causes unexpected results.

Figure 8. Invalid results because OrderDate was included instead of the expression

Figure 9. The results when the expression is included in the GROUP BY clause

Chapter 10. Introduction to XQuery on SQL Server

Figure 1. XML document viewed as a filesystem hierarchy

Figure 2. XDM representation of an XML document

Figure 3. Retrieving XML via the .query() method

Figure 4. Single scalar value returned by the .value() method

Figure 5. Results of using the .exist() method to check for node existence

Figure 6. Shredding XML data with the .nodes() method

Figure 7. Result of a simple FLWOR expression

Figure 8. Results of a FLWOR expression with the order by clause

Chapter 11. SQL Server XML frequently asked questions

Figure 1. XML tree structure

Figure 2. XML document with a DTD processed by the SQL Server XML parser

Figure 3. Result of single-byte encoding applied to Chinese characters

Figure 4. Result of properly encoding international characters in source XML

Figure 5. Result of executing an XQuery query against XML data

Figure 6. Result of retrieving a scalar value from XML data

Figure 7. Result of shredding XML data

Figure 8. Querying XML with namespaces defined

Figure 9. Retrieving all element names and nodes from an XML document

Figure 10. XML file loaded from filesystem into SQL Server

Chapter 12. Using XML to transport relational data

Figure 1. The physical model

Chapter 14. Simil: an algorithm to look for similar strings

Figure 1. A form showing similar database records

Chapter 16. Table-valued parameters

Figure 1. Results of passing a TVP to a stored procedure with list of Alaska place names

Figure 2. Results of running TVP-stored procedure

Figure 3. Results of passing a DataTable as a TVP

Figure 4. Results of passing a DbDataReader as a TVP

Figure 5. The order entry user interface

Chapter 18. Getting and staying connected—or not

Figure 1. The SQL Server Configuration Manager

Figure 2. Setting SQL Server and Windows Authentication mode in SSMS

Chapter 19. Extending your productivity in SSMS and Query Analyzer

Figure 1. To define custom keyboard shortcuts in Query Analyzer, in the Tools menu, click Customize...

Figure 2. Keyboard shortcuts defined in the Customize window in Query Analyzer

Figure 3. To define custom keyboard shortcuts in SSMS, in the Tools menu, click Options...

Figure 4. Keyboard shortcuts defined in the Options window in SSMS

Figure 1. Sample use of the sp_getcolumns utility

Chapter 21. Deprecation feature

Figure 1. Tracking deprecated features using Performance Monitor

Chapter 22. Placing SQL Server in your pocket

Figure 1. An overview of the architecture of SQL Server Compact Edition showing the various DLLs that make up the database engine. These DLLs are loaded directly into the process space of the client application wanting access to the database.

Figure 2. The Connect to Server dialog box within SQL Server Management Studio allows a developer to connect to a SQL Server Compact Edition database by selecting SQL Server Compact Edition as the server type.

Figure 3. An example session with SQL Server Management Studio demonstrating the Object Explorer and Query panes accessing a SQL Server Compact Edition database.

Chapter 23. Mobile data strategies

Figure 1. Synchronization architecture

Chapter 25. Working with maintenance plans

Figure 1. Defining the check database integrity task

Figure 2. Defining the shrink database task

Figure 3. Defining the reorganize index task

Figure 4. Defining the rebuild index task

Figure 5. Defining the update statistics task

Figure 6. Defining the history cleanup task

Figure 7. Defining the execute SQL Server Agent job task

Figure 8. Defining the back up database task

Figure 9. Defining the maintenance cleanup task

Figure 10. Selecting the reporting options

Figure 11. Executing the T-SQL statement task

Chapter 26. PowerShell in SQL Server

Figure 1. Service dependencies in SQL Server

Chapter 27. Automating SQL Server Management using SMO

Figure 1. The BackupDirectory property is in the server’s Settings collection.

Figure 2. The SMO Backup object

Figure 3. Database file path properties

Figure 4. The SMO Restore object

Figure 5. The SMO Databases collection and Database object

Figure 6. The SMO Scripter object

Chapter 28. Practical auditing in SQL Server 2008

Figure 1. Overview of audit object relationships

Figure 2. Enabling Audit Object Access

Figure 3. Enabling Generate Security Audit for SQL Service account

Figure 4. Creating a new audit using SSMS

Figure 5. Setting server audit properties using SSMS

Figure 6. Enabling a server audit using SSMS

Figure 7. Creating a server audit specification using SSMS

Figure 8. Viewing audit logs using SSMS

Figure 9. Viewing audit logs in the Log File Viewer

Figure 10. Results of listing 11

Figure 11. Creating a database audit specification in SSMS

Figure 12. Configuring a database audit specification in SSMS

Chapter 30. Reusing space in a table

Figure 1. Results from dbo.Test table

Figure 2. Using a DMV to review space used

Figure 3. Deleting half the rows in the dbo.Test table

Figure 4. Examining the space consumed by the dbo.Test table

Figure 5. Reviewing the space used by the dbo.Test table after inserting new rows

Figure 6. Viewing data in the dbo.Test2 table

Figure 7. Space used by the dbo.Test2 table

Figure 8. Reviewing the space used after dropping a column

Figure 9. Inserting rows after dropping a column

Figure 10. The space consumed by dbo.Test2 after running DBCC CLEANTABLE

Figure 11. Inserting rows into reclaimed space

Chapter 31. Some practical issues in table partitioning

Figure 1. Dependency relations of the major table partitioning components

Figure 2. An initially empty partition is swapped with a full staging table.

Figure 3. A full partition can be swapped with an empty staging table to remove data.

Chapter 32. Partitioning for manageability (and maybe performance)

Figure 1. Partitioned heap with two partitions

Figure 2. Partitioned table or b-tree index with two partitions

Figure 3. Rope cuts representing two partition boundaries, resulting in three partitions

Figure 4. Partitions resulting from RANGE LEFT and RANGE RIGHT functions

Figure 5. Partition scheme filegroup mapping for RANGE RIGHT function

Figure 6. SQL Server 2005 aggregate query nonpartitioned plan and statistics

Figure 7. SQL 2005 aggregate query partitioned plan and statistics

Figure 8. SQL Server 2005 detail query nonpartitioned plan and statistics

Figure 9. SQL Server 2005 detail query partitioned plan and statistics

Figure 10. SQL Server 2008 detail query partitioned plan and statistics

Chapter 33. Efficient backups without indexes

Figure 1. List of filegroups from sys.filegroups

Figure 2. Confirm that Table1 was created on the default PRIMARY filegroup.

Figure 3. Confirm that index ncix_Table1 was created within data_space_id 1 (PRIMARY).

Figure 4. Adding the -m startup parameter in SQL Server Configuration Manager

Figure 5. Specifying the Dedicated Administrator Connection using the SSMS connection dialog box

Chapter 35. The poor man’s SQL Server log shipping

Figure 1. Selecting a program to schedule

Figure 2. Naming a task

Figure 3. Entering a start time and day for the task

Figure 4. Entering the user’s name and password

Figure 5. Using Advanced Schedule Options

Chapter 36. Understated changes in SQL Server 2005 replication

Figure 1. In the Snapshot Agent’s job step, the unofficial (unsupported!) /NoBcpData is entered.

Figure 2. Snapshot data from a table is now partitioned across several text files.

Figure 3. Merge replication articles can be marked as download-only to prevent subscriber changes and reduce metadata.

Chapter 37. High-performance transactional replication

Figure 1. The effect of HistoryVerboseLevel and OutputVerboseLevel settings on a workload of 10,000 singleton inserts

Figure 2. The effect of HistoryVerboseLevel and OutputVerboseLevel settings on a workload of 100 transactions of 100 singleton inserts

Figure 3. The effect of worker time with varying settings of ReadBatchSize and ReadBatchThreshold for a workload of 10,000 singleton inserts on the Log Reader Agent

Figure 4. The effect of worker time with varying settings of CommitBatchSize and CommitBatchThreshold for a workload of 10,000 singleton inserts on the Distribution Agent

Figure 5. The effect of worker time with varying settings of CommitBatchSize and CommitBatchThreshold for a workload of 10,000 singleton inserts on the Distribution Agent

Chapter 38. Successfully implementing Kerberos delegation

Figure 1. Our fictitious SSTB.local Active Directory domain

Figure 2. User account tab for Service_SSRS

Figure 3. Dialog box showing that the current domain functional level is set to Windows Server 2003

Figure 4. Checking the domain functional level within the Active Directory Domains and Trusts

Figure 5. Checking the forest functional level within Active Directory Domains and Trusts

Figure 6. Properties of domain user account Joan Rawlyns showing that Account Is Sensitive and Cannot Be Delegated isn’t selected

Figure 7. Confirm that the Automatically Detect Settings option is not selected, and ensure that you Bypass Proxy Server for Local Addresses if you do have a proxy server configured.

Figure 8. 502 Bad Gateway error message appears if Automatically Detect Settings is enabled.

Figure 9. Ensuring that Enable Integrated Windows Authentication* is selected

Figure 10. Ensuring that Automatic Logon Only in Intranet Zone is selected

Figure 11. Accessing the local intranet zone’s security settings

Figure 12. Setting the identity of the application pool

Figure 13. Selecting the application pool you just created for your new virtual directory

Figure 14. Enabling integrated Windows authentication and disabling anonymous access

Figure 15. Enabling the default content page

Figure 16. DelegConfig confirms a successful Kerberos implementation with big green check boxes.

Chapter 39. Running SQL Server on Hyper-V

Figure 1. A virtual machine running on Hyper-V only passes through a thin hypervisor layer to access the physical hardware.

Figure 2. A virtual machine running on Virtual Server 2005 R2 passes its operating system calls to the host operating system for execution, which results in a longer, slower path to the hardware.

Figure 3. A dynamically expanding virtual hard disk is seen by the virtual machine as having 117 GB of free space when there is actually no disk space left on the physical file system. This forces the virtual machine into a paused state..

Figure 4. Task Manager results are local to the partition in which it runs.

Chapter 40. When is an unused index not an unused index?

Figure 1. The structure of sys.dm_db_index_usage_stats

Figure 2. Execution plan in SSMS 2005

Figure 3. Execution plan in SSMS 2008

Figure 4. Execution plan for simple DISTINCT query

Figure 5. Execution plan for second simple DISTINCT query

Figure 6. Querying the DMV

Figure 7. Execution plan with index

Figure 8. Execution plan without index

Chapter 41. Speeding up your queries with index covering

Figure 1. Index seek plan

Chapter 42. Tracing the deadlock

Figure 1. The first and second stages of a deadlock

Chapter 44. Does the order of columns in an index matter?

Figure 1. Query execution plan for listing 1

Figure 2. Query execution plan for listing 4

Figure 3. Query execution plan for listing 5

Figure 4. Query execution plan for listing 6

Figure 5. Reads required for listing 6

Figure 6. Reads required for listing 6 without the ix_Customer_Name index

Figure 7. Query execution plan for listing 6 without the ix_Customer_Name index

Chapter 45. Correlating SQL Profiler with PerfMon

Figure 1. Windows PerfMon in action

Figure 2. Profiler trace while starting a new trace

Figure 3. Overlay of PerfMon and Profiler data

Chapter 46. Using correlation to improve query performance

Figure 1. Low correlation index

Figure 2. High correlation index

Figure 3. Query plan that uses the nonclustered index

Figure 4. Query plan that scans the entire clustered index

Chapter 49. XEVENT: the next event infrastructure

Figure 1. SQL Server Extended Events engine

Figure 2. Extended Events packages

Chapter 50. BI for the relational guy

Figure 1. Reporting evolution

Figure 2. A star schema

Figure 3. MS Excel 2007 Pivot Table sourcing Analysis Services cube

Figure 4. The Microsoft BI stack

Chapter 51. Unlocking the secrets of SQL Server 2008 Reporting Services

Figure 1. SQL Server Reporting Services architecture

Figure 2. Report parameters as generated by the Report Processor

Figure 3. Specifying the report query with a WHERE clause

Figure 4. Specifying the report page, group breaks, and detail elements

Figure 5. Reporting Services Configuration Manager Web Service URLs report

Figure 6. Visual Studio BI project with the newly generated report

Figure 7. The report rendered in the Preview tab

Figure 8. Setting a default value for report parameters

Figure 9. Setting the report deployment properties

Figure 10. Verifying the Report Manager URL

Figure 11. The Report Manager home directory

Figure 12. Setting the TargetServerURL

Figure 13. The Reporting Services Configuration Manager—setting the Report Manager URL

Figure 14. Setting Reporting Services properties

Figure 15. The Report Manager with My Reports enabled

Chapter 54. Introduction to SSAS 2008 data mining

Figure 1. The CRISP-DM standard process for data mining projects

Figure 2. Predictive models project

Figure 3. Decision tree

Figure 4. Lift Chart for models created in this section

Figure 5. Prediction Query builder

Chapter 55. To aggregate or not to aggregate—is there really a question?

Figure 1. The Aggregation Design Wizard

Figure 2. SQL Server 2008 Aggregation Usage designer

Figure 3. The SQL Server 2008 Attribute Relationships designer

Figure 4. SQL Server 2008 Aggregation Design Wizard

Chapter 56. Incorporating data profiling in the ETL process

Figure 1. The Data Profiling Task Editor

Figure 2. Data Profile Viewer

Figure 3. Data flow to reassemble a Column Pattern profile

Chapter 57. Expressions in SQL Server Integration Services

Figure 1. Static task properties

Figure 2. Adding a property expression

Figure 3. Conditional execution with expressions

Figure 4. Precedence constraint with expression

Figure 5. Deployment folders

Figure 6. Exposed data flow component properties

Figure 7. Conditional Split transformation

Figure 8. SQL script variable expression

Figure 9. Product and product category database schema

Figure 10. Flat File export starting point

Figure 11. The finished package

Figure 12. Exported files for each category

Chapter 58. SSIS performance tips

Figure 1. An OLE DB Source component hooked up to a Row Count transformation

Figure 2. The OLE DB Destination fast load panel

Chapter 59. Incremental loads using T-SQL and SSIS

Figure 1. Creating a new BIDS project named SSISIncrementalLoad

Figure 2. Using SSIS to edit the lookup transformation

Figure 3. Using the Lookup Transformation Editor to establish the correct mappings

Figure 4. The Data Flow canvas shows a graphical view of the transformation.

Figure 5. The Advanced Editor shows a representation of the data flow prior to execution.

List of Tables

Chapter 3. Finding functional dependencies

Table 1. Sample data in tabular format

Table 2. Changing the data in the second row to find what attributes depend on OrderNo

Table 3. Another set of changed data, this time to test dependencies for CustomerName

Table 4. Testing functional dependencies for CustomerID

Table 5. Testing functional dependencies for Product

Table 6. Testing functional dependencies for Qty

Table 7. Testing functional dependencies for TotalPrice

Table 8. Testing functional dependencies for OrderTotal

Table 9. Testing functional dependencies for the combination of OrderNo and Product

Table 10. Testing functional dependencies for the combination of CustomerID and OrderTotal

Chapter 5. Gaps and islands

Table 1. Desired result for gaps problem

Table 2. Desired result for islands problem

Table 3. Performance summary of solutions to gaps problem

Table 4. Desired result for variation on the islands problem

Table 5. Performance summary of solutions to islands problem

Chapter 6. Error handling in SQL Server and applications

Table 1. SQLException class properties

Chapter 8. What makes a bulk insert a minimally logged operation?

Table 1. Resultant log file sizes

Chapter 10. Introduction to XQuery on SQL Server

Table 1. XML data type methods summary

Table 2. XQuery comparison operators

Chapter 11. SQL Server XML frequently asked questions

Table 1. Predeclared XML entities

Chapter 12. Using XML to transport relational data

Table 1. Entities, the facts about them, and the roles those facts play in the business case

Table 2. Data management operations supported by our solution

Table 3. XPath expressions used to extract the entities from the Album XML

Table 4. XPath expressions used to extract the entities from the Band XML

Table 5. Retrieving the associative entities

Chapter 13. Full-text searching

Table 1. Simple ranking query results

Table 2. Results for medium-complexity ranking query

Table 3. Query results to list all full-text indexes

Table 4. Query results to list all columns that are full-text indexed

Table 5. Query results for catalog information query

Table 6. Sample of results for query to find keywords

Table 7. Sample of results for query to find keywords and their source row

Table 8. Partial results of expanded query combining keywords with source data

Chapter 14. Simil: an algorithm to look for similar strings

Table 1. Simil results for Pennsylvania

Chapter 18. Getting and staying connected—or not

Table 1. Typical server key settings

Table 2. Connection pooling connection string keywords

Chapter 21. Deprecation feature

Table 1. Deprecated features

Chapter 23. Mobile data strategies

Table 1. SyncAdapter properties

Table 2. SyncAdapter methods

Table 3. Synchronization agent properties

Table 4. TableCreationOption enumerations

Table 5. SyncDirection enumerations

Table 6. Key feature comparison of the three synchronization methodologies

Chapter 26. PowerShell in SQL Server

Table 1. PowerShell issues

Chapter 28. Practical auditing in SQL Server 2008

Table 1. Server Audit configuration settings for Application and Security Log targets

Table 2. Server audit configuration settings for File targets

Chapter 29. My favorite DMVs, and why

Table 1. DMV categories in SQL Server 2005 and 2008

Table 2. New DMV categories in SQL Server 2008

Chapter 38. Successfully implementing Kerberos delegation

Table 1. Free tools for testing and implementing

Table 2. Blog posts

Table 3. Microsoft TechNet articles

Table 4. Microsoft Help and Support articles pertaining to Kerberos delegation

Table 5. Microsoft white papers

Table 6. Microsoft webcasts

Chapter 39. Running SQL Server on Hyper-V

Table 1. Types and examples of virtualization technologies

Chapter 48. Query performance and disk I/O counters

Table 1. Test scripts to see the impact of small random I/Os

Table 2. Performance impact of small random I/Os

Table 3. Test scripts to see the impact of the NOLOCK hint

Table 4. Performance impact of the NOLOCK hint

Table 5. Test scripts to see the impact of the read-ahead operation

Table 6. Performance impact of the NOLOCK hint

Table 7. Performance impact of index fragmentation

Chapter 50. BI for the relational guy

Table 1. OLTP versus reporting environment characteristics

Chapter 53. SQL Server Audit, change tracking, and change data capture

Table 1. Comparing SQL Server Audit, change tracking, and change data capture

Chapter 54. Introduction to SSAS 2008 data mining

Table 1. SSAS 2008 data mining algorithms and usage

Chapter 55. To aggregate or not to aggregate—is there really a question?

Table 1. Aggregation example

List of Listings

Chapter 2. SQL Server tools for maintaining data integrity

Listing 1. Basic template for triggers

Listing 2. Trigger to ensure line item total > 0

Chapter 4. Set-based iteration, the third alternative

Listing 1. Set-based iteration with the TOP clause

Listing 2. Declarative code for calculating running totals

Listing 3. Iterative code for calculating running totals

Listing 4. Set-based iteration for calculating running totals

Listing 5. Set up tables and generate random data for bin packing

Listing 6. Iterative code for bin packing

Listing 7. Creating the numbers table for use in the set-based bin-packing code

Listing 8. Set-based iteration for bin packing

Chapter 5. Gaps and islands

Listing 1. Code creating and populating table NumSeq

Listing 2. Code creating and populating the BigNumSeq table

Listing 3. Gaps—solution 1 using subqueries

Listing 4. Gaps—solution 2 using subqueries

Listing 5. Gaps—solution 3 using ranking functions

Listing 6. Gaps—solution 4 using cursors

Listing 7. Islands—solution 1 using subqueries and ranking calculations

Listing 8. Islands—solution 2 using group identifier based on subqueries

Listing 9. Islands—solution 3 using group identifier based on ranking calculations

Listing 10. Islands—solution 4 using cursors

Listing 11. Code creating and populating table T1

Listing 12. Solution to variation on the islands problem

Chapter 6. Error handling in SQL Server and applications

Listing 1. Error sent to SQL Server Management Studio

Listing 2. T-SQL statements in a TRY...CATCH block

Listing 3. Outputting error properties with system-provided functions

Listing 4. ERROR_LINE and ERROR_PROCEDURE functions in a stored procedure

Listing 5. Returning user-created error messages with RAISERROR

Listing 6. Nesting TRY...CATCH blocks

Listing 7. Error handling with nested TRY...CATCH statements

Listing 8. An error-handling module

Listing 9. Transaction processing in a TRY...CATCH block

Listing 10. Outputting SQL Server–specific error properties with SqlException

Listing 11. Handling multiple errors with the Errors property

Listing 12. Outputting SQL Server messages

Listing 13. Capturing RAISERROR statements

Chapter 7. Pulling apart the FROM clause

Listing 1. Query to return rows with matching product subcategories

Listing 2. A LEFT OUTER JOIN

Listing 3. Beware of COUNT(*) with OUTER JOINs

Listing 4. Using a CROSS JOIN to cover all combinations

Listing 5. A FROM clause from the Timesheet Audit Report

Listing 6. A reformatted version of the FROM clause in listing 5

Listing 7. Placing a predicate in the ON clause of an outer join

Listing 8. View to return products and their subcategories

Listing 9. View to return all products and their subcategories (if they exist)

Listing 10. Query to return products and their subcategory

Listing 11. Using a FULL JOIN

Chapter 8. What makes a bulk insert a minimally logged operation?

Listing 1. SQL scripts to create databases

Listing 2. Error message on running bcp utility from a query window

Listing 3. Script to enable xp_cmdshell

Listing 4. Script to create a database table in six different databases

Listing 5. BULK INSERT statements to import data without the TABLOCK hint

Listing 6. BULK INSERT statement to import data with TABLOCK hint

Listing 7. Query to determine the size of log files

Chapter 9. Avoiding three common query mistakes

Listing 1. Three queries to include NULL

Listing 2. The code to create the Production.ColorList table

Listing 3. Query returns no rows because of NULL values in the subquery

Listing 4. The correct code to find the list of unused colors

Listing 5. How to return all rows even if there isn’t a match

Listing 6. The same results are returned when using a RIGHT OUTER JOIN.

Listing 7. Non-matching rows lost when INNER JOIN follows LEFT OUTER JOIN.

Listing 8. Using LEFT OUTER JOIN down the OUTER JOIN path

Listing 9. Using a RIGHT OUTER JOIN followed by a LEFT OUTER JOIN

Listing 10. Missing the GROUP BY clause

Listing 11. An extra column in the GROUP BY clause

Listing 12. This query runs, but the results are invalid.

Listing 13. Writing the query so that the expression is used in the GROUP BY clause

Chapter 10. Introduction to XQuery on SQL Server

Listing 1. Simple XML document

Listing 2. XML with multiple instances of the same element at the same level

Listing 3. Sample employee XML content

Listing 4. Querying XML data

Listing 5. Retrieving a single scalar value

Listing 6. Confirming existence of a node

Listing 7. Shredding XML with the .nodes() method

Listing 8. Querying XML with a FLWOR expression

Listing 9. Binding tuples to variables with the let clause

Listing 10. Sorting tuples with the order by clause

Listing 11. Restricting results with the where clause

Listing 12. Comparing a sequence with a single value to a scalar value

Listing 13. Comparing with the value comparison operators

Listing 14. Comparing sequences with general comparison operators

Listing 15. Comparing nodes with the node comparison operators

Chapter 11. SQL Server XML frequently asked questions

Listing 1. Sample XML document

Listing 2. Extracting state elements from XML document

Listing 3. Sample prolog

Listing 4. Sample prolog with encoding specifier

Listing 5. Converting XML with a DTD

Listing 6. Applying single-byte encoding to Unicode characters

Listing 7. Eliminating single-byte-to-Unicode conversion problems

Listing 8. Invalid single-byte-to-Unicode conversion

Listing 9. Avoiding single-byte-to-Unicode conversion problems

Listing 10. Querying XML data

Listing 11. Retrieving a single scalar value from XML

Listing 12. Shredding XML data

Listing 13. Shredding XML with OPENXML

Listing 14. Sample XML with namespaces

Listing 15. Querying XML with namespaces

Listing 16. Using WITH XMLNAMESPACES clause

Listing 17. Retrieving all element names and values from XML

Listing 18. Sample state-list.xml file

Listing 19. Loading XML data from the filesystem

Chapter 12. Using XML to transport relational data

Listing 1. The Album XML Schema

Listing 2. The Band XML Schema

Listing 3. Common XML Schema

Listing 4. Extracting the titles

Listing 5. Simplified query with union XPath expression

Listing 6. Extracting the albums

Listing 7. Extracting the tracks

Listing 8. Extracting the persons

Listing 9. Extracting the bands

Listing 10. Extracting the bands

Listing 11. Extracting the persons

Listing 12. To export the Band data from the database

Listing 13. To export the Album data from the database

Chapter 13. Full-text searching

Listing 1. Real-world example using FREETEXTTABLE

Listing 2. Default thesaurus XML file

Listing 3. Full information about tables and full-text searching

Listing 4. List all columns that are full-text indexed

Listing 5. Using FullTextCatalogProperty to get information

Listing 6. Determining the number of fragments for your full-text indexes

Chapter 14. Simil: an algorithm to look for similar strings

Listing 1. Calling the fnSimil() function from a stored procedure

Listing 2. Using the fnSimil() function to search an entire table

Listing 3. Comparing Simil values between a .NET assembly and a classic DLL

Chapter 16. Table-valued parameters

Listing 1. Code to use a DataTable to pass a TVP to a stored procedure

Listing 2. Using a DbDataReader object as a TVP

Listing 3. Code to create database objects to insert orders

Listing 4. Checkout code that creates the order in the database

Chapter 17. Build your own index

Listing 1. Creating the persons table and index on email

Listing 2. The procedure map_search_five

Listing 3. The trigger keeps fragment_persons updated.

Listing 4. Loading the fragments_personlists table

Listing 5. Search procedure using fragments_personlists

Listing 6. Filtering out unchanged fragment-person_id mappings

Listing 7. Search function using the bitmask

Chapter 18. Getting and staying connected—or not

Listing 1. Testing for network availability in Visual Basic.NET

Listing 2. Starting SQL Server and supporting services in a command batch

Listing 3. Capturing the list of visible SQL Server instances

Listing 4. Starting a selected service

Chapter 19. Extending your productivity in SSMS and Query Analyzer

Listing 1. Creating sample utility sp_getcolumns

Chapter 20. Why every SQL developer needs a tools database

Listing 1. Script to create a numbers table

Listing 2. Query to create dates from the numbers table

Listing 3. Abridged result set of dates created from the numbers table.

Listing 4. Query to create dates in the past from the numbers table

Listing 5. Abridged result set of dates created in the past from the numbers table

Listing 6. Query to return the first and last day of every quarter from 2000 to 2024

Listing 7. Abridged result set of query in listing 6

Listing 8. Stored procedure to split delimited strings with the numbers table

Listing 9. Result set of unique characters in a string

Listing 10. Stored procedure with comments

Listing 11. Table for state tax rates

Listing 12. User-defined function to calculate tax

Listing 13. Four example calls and their results

Listing 14. Function to format a date

Chapter 21. Deprecation feature

Listing 1. Reading the SQLServer:Deprecated Features counters

Listing 2. Event notification method

Listing 3. Extended Events method

Listing 4. XML result

Chapter 22. Placing SQL Server in your pocket

Listing 1. Bypassing the query processor

Chapter 23. Mobile data strategies

Listing 1. WCF service IService.cs

Listing 2. WCF service.cs

Listing 3. WCF service App.config

Listing 4. Mobile console app TestSync.cs

Listing 5. Mobile class SyncClient

Chapter 26. PowerShell in SQL Server

Listing 1. Accessing SQL Server data with PowerShell

Listing 2. Accessing SMO with PowerShell

Listing 3. Accessing data with PowerShell cmdlet

Chapter 27. Automating SQL Server Management using SMO

Listing 1. Code to load SMOExtended.dll and SQLWMIManagement.dll

Listing 2. Backing up user databases

Listing 3. Restoring a copy of an existing database from backup

Listing 4. Creating a user database

Listing 5. Scripting all objects in the AdventureWorks database

Chapter 28. Practical auditing in SQL Server 2008

Listing 1. Creating a server audit using the Security Log target

Listing 2. Creating a server audit using the Application Log target

Listing 3. Creating a server audit using the File target

Listing 4. Creating a server audit specification using the Application Log target

Listing 5. Creating events for the server audit specification

Listing 6. Viewing audit events from T-SQL

Listing 7. Viewing details of all server audit specifications

Listing 8. Creating a server audit for a database audit specification

Listing 9. Creating a database audit specification to audit DML activity

Listing 10. Creating DML activity

Listing 11. Viewing DML activity audit events in File target

Listing 12. Additional examples of database audit specifications

Chapter 29. My favorite DMVs, and why

Listing 1. Sample query against sys.dm_db_index_physical_stats

Listing 2. Creating a utility database

Listing 3. An sp_who2 replacement

Listing 4. Table-valued function for procedure statistics

Listing 5. Finding unused stored procedures

Listing 6. Making a system stored procedure

Listing 7. Measuring the usefulness of indexes

Listing 8. Finding inefficient queries

Listing 9. Finding missing indexes

Chapter 30. Reusing space in a table

Listing 1. Creating and populating the dbo.Test table

Listing 2. Querying the dbo.Test table

Listing 3. Examining the space used by the dbo.Test table

Listing 4. Deleting the odd-numbered rows

Listing 5. Adding new rows to the dbo.Test table

Listing 6. Dropping the dbo.Test table

Listing 7. Creating the dbo.Test2 table

Listing 8. Dropping a varchar column in the dbo.Test table

Listing 9. Adding more rows to the dbo.Test2 table

Listing 10. Reclaiming space using DBCC CLEANTABLE

Chapter 32. Partitioning for manageability (and maybe performance)

Listing 1. Creating a partitioned sales transaction table

Figure 11. SQL Server 2008 partitioned index seek predicate including partition range expression

Chapter 33. Efficient backups without indexes

Listing 1. Scripting CREATE INDEX commands to a new filegroup

Chapter 34. Using database mirroring to become a superhero!

Listing 1. Script to create a login with identical SID on the mirror instance

Listing 2. Queries to monitor database mirroring

Chapter 35. The poor man’s SQL Server log shipping

Listing 1. T-SQL script (backupLog.sql) generates transaction log backups

Listing 2. VBScript (deleteTRN.vbs) cleans up the transaction log backups

Listing 3. Batch file (databaseBackupLog.cmd) calls the backup and cleanup scripts

Listing 4. Batch file (logShipRobocopy.cmd) calls the replication and restore scripts

Listing 5. Batch file (restoreLOG.vbs) calls the backup and cleanup scripts

Listing 6. VBScript file (sendEmailSMTP.vbs) sends email notifications

Listing 7. PowerShell translation of deleteTRN.vbs

Chapter 38. Successfully implementing Kerberos delegation

Listing 1. Finding out the SPNs that are currently in place

Chapter 40. When is an unused index not an unused index?

Listing 1. Querying the DMV to review the indexes and the execution plan

Chapter 41. Speeding up your queries with index covering

Listing 1. Restore the AdventureWorks database to AdventureWorks_Copy

Listing 2. Creating a noncovering index

Listing 3. Running two range queries

Listing 4. Comparing covering and noncovering index performance

Listing 5. Update statement performance improved with a covering index

Listing 6. Creating two covering indexes

Chapter 42. Tracing the deadlock

Listing 1. A sample deadlock graph

Chapter 44. Does the order of columns in an index matter?

Listing 1. A sample Customers table

Listing 2. Creating indexes for the Customers table

Listing 3. Finding a specific Customer row by Last_Name, First_Name

Listing 4. Finding a specific Customer row by First_Name, Last_Name

Listing 5. Finding customers by Last_Name

Listing 6. Finding Customers with a first name of Jake

Listing 7. Turning STATISTICS IO on

Listing 8. Using DBCC to drop the procedure cache and free memory

Listing 9. Dropping the ix_Customer_Name index

Chapter 46. Using correlation to improve query performance

Listing 1. Query to select WorkOrders for a 2-day date range

Listing 2. Query to select all WorkOrders except for 2 days

Listing 3. Query to select WorkOrders for a one-month date range

Listing 4. Using an index hint

Chapter 47. How to use Dynamic Management Views

Listing 1. Creating a monitoring login with view server state permission

Listing 2. Top waits query

Listing 3. Checking CPU pressure

Listing 4. Checking the runnable tasks count

Listing 5. Finding the most expensive stored procedures

Listing 6. Finding expensive stored procedures, sorted by average worker time

Listing 7. Checking for I/O pressure

Listing 8. Identifying the highest I/O waits

Listing 9. Checking I/O statistics for a database

Listing 10. Locating physical read I/O pressure

Listing 11. Finding stored procedures with the most write activity

Listing 12. Top 10 consumers of memory from buffer pool

Listing 13. Getting query mix and use counts for each plan

Listing 14. Finding indexes and tables that use the most buffer space

Listing 15. Finding ad hoc queries that are bloating the plan cache

Listing 16. Finding your 25 most expensive queries

Listing 17. Finding tables with the most reads

Listing 18. Finding tables with the most writes

Listing 19. Finding bad indexes

Listing 20. Looking at Index Advantage to find missing indexes

Listing 21. Looking at Last User Seek to find missing indexes

Listing 22. Getting statistics for a table

Listing 23. Missing indexes for a single table

Listing 24. Checking SQL Server schedulers to see if you may have blocking

Listing 25. Detecting blocking

Listing 26. Detecting blocking (a more accurate and complete version)

Listing 27. Looking at locks that are causing problems

Chapter 48. Query performance and disk I/O counters

Listing 1. Creating a test table and populating it with 4 million rows

Listing 2. Output from running DBCC SHOWCONTIG on the test table

Chapter 49. XEVENT: the next event infrastructure

Listing 1. Using Extended Events to monitor long-running queries

Listing 2. Output from running listing 1

Listing 3. Using sys.dm_xe_packages to list packages

Listing 4. Output from running listing 3

Listing 5. Enumerating Extended Events targets

Listing 6. Output from running listing 5

Listing 7. Generating a user dump

Listing 8. Output from running listing 7

Listing 9. Querying sys.dm_xe_sessions to determine oldest session

Listing 10. Getting the system health session active events and filters

Listing 11. Obtaining information from the system health session

Chapter 52. Reporting Services tips and tricks

Listing 1. Queries to compare performance of OPENQUERY and four-part naming

Listing 2. Editing rsreportserver.config to render CSV in ASCII

Listing 3. Script to change URL to include parameters

Listing 4. Macro to add date parameters and refresh the query

Listing 5. CSV alternative to web query wizards

Chapter 53. SQL Server Audit, change tracking, and change data capture

Listing 1. Preparing Employees schema and data

Listing 2. Creating a server and database audit

Listing 3. Updating the Employees table

Listing 4. Event log entry for the UPDATE command in listing 3

Listing 5. Creating a Server Audit with the SUCCESSFUL_LOGIN_GROUP

Listing 6. Cleaning up the audit specification

Listing 7. Enabling change tracking

Listing 8. Determining (and updating) the baseline version of a table

Listing 9. Retrieving changes to the Employees table

Listing 10. Using WITH CHANGE_TRACKING_CONTEXT() in an INSTEAD OF trigger

Listing 11. Calling the CHANGETABLE function

Listing 12. Disabling change tracking

Listing 13. Enabling a database and table for change tracking

Listing 14. Inserting data into the Employees table

Listing 15. Query against (and results from) a change data capture function

Listing 16. Using the get_net_changes function

Listing 17. Viewing the before and after image of each key row

Listing 18. Cleaning up change data capture settings

Chapter 56. Incorporating data profiling in the ETL process

Listing 1. Data profile XML prior to making it dynamic

Listing 2. Data profiling XML after converting to an expression

Listing 3. Script component to check column values against a list of patterns

Chapter 59. Incremental loads using T-SQL and SSIS

Listing 1. Creating the tblSource source

Listing 2. Creating the tblDest destination

Listing 3. Loading data

Listing 4. Viewing new rows

Listing 5. Incrementally loading new rows

Listing 6. Isolating changed rows

Listing 7. Updating the data

Listing 8. Resetting the tables

MVP contributors and their chapters

John Baird 23

Bob Beauchemin 15

Itzik Ben-Gan 5

Glenn Berry 34, 47

Aaron Bertrand 29, 53

Phil Brammer 58

Robert C. Cain 13

Michael Coles 10, 11

John Paul Cook 39

Hilary Cotter 37

Louis Davidson 1, 2

Christopher Fairbairn 22

Rob Farley 7, 40

Denis Gobo 8, 20

Bill Graziano 6

Dan Guzman 32

Paul Ibison 36

Tibor Karaszi 25

Kathi Kellenberger 9

Don Kiely 16

Kevin Kline 45

Hugo Kornelis 3, 4

Alex Kuznetsov 41

Matija Lah 12

Cristian Lefter 21, 49

Andy Leonard 59

Greg Linwood 33

Bruce Loehle-Conger 52

Brad McGehee 24, 43

Paul Nielsen 1

Pawel Potasinski 19

Matthew Roche 57

Dejan Sarka 54

Edwin Sarmiento 35

Gail Shaw 42

Linchi Shea 48

Richard Siddaway 26

Jasper Smith 28

Erland Sommarskog 17

Scott Stauffer 38

Tom van Stiphout 14

Gert-Jan Strik 46

Ron Talmage 31

William R. Vaughn 18, 51

Joe Webb 30,