SQL Server Magazine

Logic Puzzle – Dice Substitute

Itzik provides a logic puzzle involving finding a substitute for dice.

CLR-Based Solution for Running Aggregates

If performance is your main goal, you should use the set-based solution only with very small partition sizes; otherwise, you should use the CLR-based solution.

Ordered UPDATE and Set-Based Solutions to Running Aggregates

Itzik responds to a reader comment about the article “Set-Based vs. Cursor-Based Solutions for Running Aggregates.”

Tip Regarding Cursor Variables

Itzik describes a tip regarding working with cursor variables in the local batch.

Optimizing Aggregates with DISTINCT

Itzik demonstrates how you can optimize queries that use both regular aggregates and ones with DISTINCT.

Set-Based vs. Cursor-Based Solutions for Running Aggregates

Although set-based solutions to running aggregations have many advantages over cursor-based solutions, cursor-based solutions can provide better performance in certain cases, such as when dealing with very large partitions.

Caching of Temporary Objects

Itzik demonstrates causes that prevent caching of temporary objects across invocations of a routine.

Tip Regarding Cached Plans

Itzik provides a tip to help you identify plans in cache.

Subqueries and Joins for Running Aggregates

Both the subquery and join solutions perform reasonably well for calculating running aggregates when you have very small partitions, but the join approach is best for calculating multiple aggregates.

Subqueries and Joins for Running Aggregates

Optimization and Caching

Itzik describes findings of Will Alber concerning the article Optimizing a Suboptimal Query Plan.

New Book – Inside Microsoft SQL Server 2008: T-SQL Querying

Itzik introduces his new book – Inside Microsoft SQL Server 2008: T-SQL Querying.

More SQL Server 2008 T-SQL Improvements

Itzik discusses four enhancements, including composable DML, the ability to declare and initialize variables in the same statement, compound assignment operators, and more reliable object dependency information.

Sums Puzzle

Itzik describes a puzzle involving sums of values.

Calculating Month Difference

Itzik addresses a reader request to calculate the difference in terms of months between two dates.

SQL Server 2008’s T-SQL Features

SQL Server 2008 provides several small T-SQL features, such as an enhanced VALUES clause, improved ISO week number calculation, and the ability to convert character to binary and binary to character values while preserving the hexadecimal digits.

T-SQL Puzzle Solution – Custom Sequence

Itzik provides a solution to the T-SQL challenge to write a custom sequence generator that is based on a single statement.

T-SQL Puzzle – Custom Sequence

Itzik provides a T-SQL challenge involving a custom sequence generator.

More Options for Handling Arrays as Inputs

Use a Common Language Runtime (CLR) split user-defined function (UDF) or SQL Server 2008’s new table-valued parameters to handle arrays as inputs.

Ordered Common Language Runtime Table-Valued User-Defined Functions

Enhancements to CLR table-valued user-defined functions in SQL Server 2008