Inside Microsoft SQL Server 2008 : T-SQL querying / Itzik Ben-Gan ... [et al.].

Format:

Book

Author/Creator:

Lubor Kollar Itzik Ben-Gan Dejan Sarka, and Steve, author.

Contributor:

Ben-Gan, Itzik.

Language:

English

Subjects (All):

SQL server.

Client/server computing.

Relational databases.

Physical Description:

1 online resource (xxiv, 798 p. ) ill.

Edition:

1st edition

Place of Publication:

Redmond, Wash. : Microsoft Press, c2009.

System Details:

text file

Summary:

Tackle the toughest set-based querying and query tuning problems—guided by an author team with in-depth, inside knowledge of T-SQL. Deepen your understanding of architecture and internals—and gain practical approaches and advanced techniques to optimize your code’s performance. Discover how to: Move from procedural programming to the language of sets and logic Optimize query tuning with a top-down methodology Assess algorithmic complexity to predict performance Compare data-aggregation techniques, including new grouping sets Manage data modification—insert, delete, update, merge—for performance Write more efficient queries against partitioned tables Work with graphs, trees, hierarchies, and recursive queries Plus—Use pure-logic puzzles to sharpen your problem-solving skills

Contents:

Intro

Inside Microsoft® SQL Server® 2008: T-SQL Querying

Acknowledgments

Introduction

Companion Content and Sample Database

Find Additional Content Online

Support for These Books

1. Logical Query Processing

Sample Query Based on Customers/Orders Scenario

Logical Query Processing Phase Details

Step 1-J2: Apply ON Filter (Join Condition)

Step 1-J3: Add Outer Rows

Step 2: The WHERE Phase

Step 3: The GROUP BY Phase

Step 4: The HAVING Phase

Step 5: The SELECT Phase

Step 5-2: Apply the DISTINCT Clause

Step 5-3: Apply the TOP Option

Step 6: The Presentation ORDER BY Phase

Further Aspects of Logical Query Processing

PIVOT

UNPIVOT

OVER Clause

Set Operators

Conclusion

2. Set Theory and Predicate Logic

Undefined Terms

Equality, Identity, and Sameness

Mathematical Conventions

Numbers

Context

Alphabetical Order

Functions, Parameters, and Variables

Instructions and Algorithms

Set Theory

Set-Builder Notation

Well-Definedness of Sets

Domains of Discourse

Domains and Modeling

Faithfulness

Russell's Paradox

Ordered Pairs, Tuples, and Cartesian Products

The Cartesian Product

The Empty Set(s)

The Characteristic Function of a Set

Cardinality

Order

Trichotomy

Induced Order

Ordinal Numbers

Set Difference

Set Partitions

Generalizations of Set Theory

Predicate Logic

Propositions and Predicates

Proposition or Predicate?

Creating Propositions from Predicates

The Law of Excluded Middle

And, Or, and Not

When And Means Or

Exclusive Or

Logical Equivalence

Logical Implication

The Contrapositive

Vacuous Truths

Quantification

Multiple Quantification

Alternatives and Generalizations

Three-Valued Logic

Fuzzy Logic

Relations.

A Practical Application

3. The Relational Model

Views (and Other Virtual Relations)

Naming Conventions

The Relational Model: A Quick Summary

Relational Algebra and Relational Calculus

Relational Algebra

Additional Relational Algebra Operators

Primitive Relational Algebra Operators

Relational Calculus

T-SQL Support

Data Integrity

Referential Integrity

Domain Integrity

Other Means of Enforcing Integrity

Normalization and Other Design Topics

Second Normal Form

Third Normal Form

Boyce-Codd Normal Form

Higher Normal Forms

Fifth Normal Form

Additional Normal Forms

Denormalization

Generalization and Specialization

4. Query Tuning

Tuning Methodology

Collecting Wait Information

Correlate Waits with Queues

Determine Course of Action

Drill Down to the Database/File Level

Drill Down to the Process Level

Analyze Trace Data

Query Statistics

Tune Indexes and Queries

Tools for Query Tuning

Clearing the Cache

Dynamic Management Objects

STATISTICS IO

Measuring the Run Time of Queries

Analyzing Execution Plans

Textual Showplans

XML Showplans

Hints

Traces/Profiler

Database Engine Tuning Advisor

Data Collection and Management Data Warehouse

Using SMO to Clone Statistics

Index Tuning

Table Organization

Heap

Clustered Index

Nonclustered Index on a Heap

Nonclustered Index on a Clustered Table

Index Access Methods

Unordered Covering Nonclustered Index Scan

Ordered Clustered Index Scan

Ordered Covering Nonclustered Index Scan

The Storage Engine's Treatment of Scans

Allocation Order Scans

Index Order Scans

Nonclustered Index Seek + Ordered Partial Scan + Lookups

Unordered Nonclustered Index Scan + Lookups

Clustered Index Seek + Ordered Partial Scan.

Covering Nonclustered Index Seek + Ordered Partial Scan

Index Intersection

Filtered Indexes and Statistics

Indexed Views

Analysis of Indexing Strategies

Clustered Index Seek + Ordered Partial Scan

Covering Nonclustered Index Seek + Ordered Partial Scan

Summary of Analysis of Indexing Strategy

Fragmentation

Partitioning

Preparing Sample Data

TABLESAMPLE

An Examination of Set-Based vs. Iterative/Procedural Approaches and a Tuning Exercise

5. Algorithms and Complexity

Sometimes the Jar Has No Quarters

How Algorithms Scale

An Algorithm with Linear Complexity

Exponential and Superexponential Complexity

Sublinear Complexity

Constant Complexity

Technical Definitions of Complexity

Polynomial and Nonpolynomial Complexity

Comparing Complexities

Classic Algorithms and Algorithmic Strategies

Selection sort

O(n log n) Sorting Algorithms

Quick sort

Faster Sorting Algorithms

String Searching

A Practical Application

The Algorithmic Complexity of LISLP

An Algorithm for LISLP with Θ(n log n) Complexity

Solving the Longest Increasing Subsequence Length Problem in T-SQL

6. Subqueries, Table Expressions, and Ranking Functions

Correlated Subqueries

EXISTS

NOT EXISTS vs. NOT IN

Minimum Missing Value

Reverse Logic Applied to Relational Division Problems

Misbehaving Subqueries

Uncommon Predicates

Table Expressions

Using Arguments

Nesting

Multiple References

Common Table Expressions

Multiple CTEs

Modifying Data

CTEs in View and Inline Function Definitions

Recursive CTEs

Analytical Ranking Functions.

Partitioning

Using Subqueries to Calculate Row Numbers

Nonunique Sort Column and Tiebreaker

Nonunique Sort Column Without a Tiebreaker

Cursor-Based Solution

IDENTITY-Based Solution

Partitioned

Performance Comparisons

Paging

Multipage Access

Rank and Dense Rank

Solutions Based on Subqueries

Tile Number

Other Solutions to Tile Number

Auxiliary Table of Numbers

Missing and Existing Ranges (Also Known as Gaps and Islands)

Gaps, Solution 2: Using Subqueries

Gaps, Solution 3: Using Ranking Functions

Gaps, Solution 4: Using Cursors

Returning Individual Missing Values

Existing Ranges (Islands)

Islands, Solution 2: Using Group Identifier Based on Subqueries

Islands, Solution 3: Using Group Identifier Based on Ranking Calculations

Islands, Solution 4: Using Cursors

A Variation of the Islands Problem

7. Joins and Set Operations

Fundamental Join Types

INNER

OUTER

Nonsupported Join Types

Further Examples of Joins

Multiple Joins

Controlling the Logical Join Evaluation Order

Bushy Plans

Semi Joins

Sliding Total of Previous Year

Join Algorithms

Merge

Hash

Forcing a Join Strategy

Separating Elements

Set Operations

UNION ALL

EXCEPT

EXCEPT ALL

INTERSECT

INTERSECT ALL

Precedence of Set Operations

Using INTO with Set Operations

Circumventing Unsupported Logical Phases

8. Aggregating and Pivoting Data

Tiebreakers

Running Aggregations

Sliding Aggregations

Year-to-Date (YTD)

Pivoting

Relational Division

Aggregating Data

Unpivoting

Custom Aggregations

Aggregate Product Using Pivoting

User Defined Aggregates (UDA)

Specialized Solutions

Specialized Solution for Aggregate Product

Specialized Solutions for Aggregate Bitwise Operations.

Aggregate Bitwise AND

Aggregate Bitwise XOR

Median

Mode

Histograms

Grouping Factor

Grouping Sets

The GROUPING SETS Subclause

The CUBE Subclause

The ROLLUP Subclause

Grouping Sets Algebra

Division

Addition

The GROUPING_ID Function

Materialize Grouping Sets

Sorting

9. TOP and APPLY

TOP and Input Expressions

TOP and Modifications

TOP on Steroids

APPLY

Solutions to Common Problems Using TOP and APPLY

Matching Current and Previous Occurrences

Next Page

Previous Page

Random Rows

Logical Transformations

10. Data Modification

SELECT INTO

BULK Rowset Provider

Minimally Logged Operations

Testing Insert Scenarios

Scenario 2: SELECT INTO, Non-FULL Recovery

Scenario 3: INSERT SELECT, Empty Heap, TABLOCK

Scenario 4: INSERT SELECT, Nonempty Heap, TABLOCK

Scenario 5: INSERT SELECT, Empty Heap, Without TABLOCK

Scenario 6: INSERT SELECT, Empty B-Tree, TABLOCK

Scenario 7: INSERT SELECT, Nonempty B-Tree, TABLOCK, TF-610 Off, New Key Range

Scenario 8: INSERT SELECT, Nonempty B-Tree, TABLOCK, TF-610 On, New Key Range

Scenario 9: INSERT SELECT, Nonempty B-Tree, TABLOCK, Merged Key Range

Scenario 10: INSERT SELECT, Empty B-Tree, Without TABLOCK, TF-610 Off

Scenario 11: INSERT SELECT, Empty B-Tree, Without TABLOCK, TF-610 On

Scenario 12: INSERT SELECT, Nonempty B-Tree, without TABLOCK, TF-610 Off, New Key Range

Scenario 13: INSERT SELECT, Nonempty B-Tree, without TABLOCK, TF-610 On, New Key Range

Scenario 14: INSERT SELECT, Nonempty B-Tree, without TABLOCK, Merged Key Range

Summary of Minimal Logging

INSERT EXEC

Sequence Mechanisms

Custom Sequences

Single Sequence Value

Block of Sequence Values

Nonblocking Sequences

GUIDs

Deleting Data

Removing Rows with Duplicate Data.

DELETE Using Joins.

Notes:

Includes index.

Description based on publisher supplied metadata and other sources.

OCLC:

501191354