Inside Microsoft SQL Server 2008 : T-SQL querying

Inside Microsoft SQL Server 2008 T-SQL querying

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...

Full description

Bibliographic Details
Other Authors: Lubor Kollar Itzik Ben-Gan Dejan Sarka, and Steve, author (author), Ben-Gan, Itzik (-)
Format: eBook
Language:Inglés
Published: Redmond, Wash. : Microsoft Press c2009.
Edition:1st edition
Subjects:
SQL server.
Client/server computing.
Relational databases.
See on Biblioteca Universitat Ramon Llull:https://discovery.url.edu/permalink/34CSUC_URL/1im36ta/alma991009627909706719
Table of 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
  • Alphabetical Order
  • Trichotomy
  • Induced Order
  • Ordinal Numbers
  • Set Operators
  • 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
  • Conclusion
  • 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
  • Conclusion
  • 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
  • Unordered Covering Nonclustered Index Scan
  • Unordered Nonclustered Index Scan + Lookups
  • Nonclustered Index Seek + Ordered Partial Scan + Lookups
  • 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
  • Conclusion
  • 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
  • Conclusion
  • 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
  • Using Arguments
  • Multiple CTEs
  • Multiple References
  • 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
  • Partitioning
  • 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
  • Conclusion
  • 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
  • Conclusion
  • 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
  • Conclusion
  • 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
  • Paging
  • Next Page
  • Previous Page
  • Random Rows
  • Median
  • Logical Transformations
  • Conclusion
  • 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.

Similar Items