Itzik Ben-Gan

Itzik
Ben-Gan

Itzik Ben-Gan is a T-SQL Trainer and a Co-Founder of SolidQ. A Data Platform Microsoft MVP (Most Valuable Professional) since 1999, Itzik has delivered numerous training events around the world focused on T-SQL Querying, Query Tuning and Programming. Itzik is the author of several books including T-SQL Fundamentals Third Edition and T-SQL Querying. Itzik is the author of SolidQ’s Advanced T-SQL Querying, Programming and Tuning and T-SQL Fundamentals courses along with being a primary resource within the company for their T-SQL related activities.

Twitter: @ItzikBenGan

Articles
T-SQL Feature Request: Add RESET WHEN Clause to Reset Window Partition 4
Window functions allow you to solve a wide variety of T-SQL querying tasks elegantly and efficiently. Still, as of SQL Server 2017, there are some tasks that are difficult to solve with efficient set-based solutions that could be handled easily and efficiently if T-SQL added support for a windowing clause called RESET WHEN.
What You Need to Know about Distinct Windowed Aggregate Calculations 3
T-SQL supports distinct grouped aggregate calculations like COUNT(DISTINCT ), but as of SQL Server 2017 CTP2, it doesn’t yet support distinct windowed aggregate calculations. There’s an open connect item requesting this feature. In this article, I explain what distinct aggregate calculations are, provide an example for a task that requires such calculations, show the desirable yet missing syntax that would solve the task, and present four supported workarounds.
What You Need to Know about Adaptive Joins over Rowstore 2
SQL Server 2017 CTP 2.0 introduces support for adaptive query processing capabilities. Traditionally, the query optimizer made all of its plan choices ahead of query execution, and SQL Server wasn’t able to change those during execution. With adaptive query processing, SQL Server is able to dynamically adapt its optimization choices to actual run time conditions such as cardinality misestimations.
Ordered Set Functions: What's New--and Missing--in SQL Server vNext 4
This article provides a reminder of what ordered set functions are, what’s new in vNext, and what’s still missing.
How to Compute Date and Time Difference in Parts 6
Sometimes you need to use T-SQL to compute the time passed between two events whose date and time values you get as inputs. For some purposes, it’s sufficient to compute the difference as a duration in terms of some part. What’s significantly trickier is to compute the difference as a combination of parts.
Identifying Existence of Intersections in Intervals
Identifying the existence of intersections in intervals is a classic task where, given a table with a set of intervals, you need to check whether any intersections exist. This is often done to verify the validity of data that holds intervals that are not supposed to have any intersections between them. For example, when you keep history of changes to rows by maintaining multiple versions of a row, the same row is not supposed to have intersecting versions.
Tips Concerning Temporal Tables
Here are a few tips on working with temporal tables, which were introduced in SQL Server 2016 to enable tracking history of changes to data.
What You Need to Know about DATETIMEOFFSET and SQL Server 2016's AT TIME ZONE Function 4
Time zone related calculations with DATETIMEOFFSET values can get quite tricky. Prior to the introduction of the AT TIME ZONE function the two main tools that we had for time zone conversions were the TODATETIMEOFFSET and SWITCHOFFSET functions.
Logical Query Processing Part 8 : SELECT and ORDER BY 2
Logical query processing defines the conceptual interpretation of queries. A good understanding of this topic is key to writing correct and robust queries.
Logical Query Processing Part 7: GROUP BY and HAVING
This article continues the series about logical query processing, which describes the logical, or conceptual, interpretation of queries.
Logical Query Processing Part 6: The WHERE Clause
Logical query processing describes the conceptual interpretation of SQL queries. This article is the sixth part in a series on the topic.
What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 3
The series concludes with coverage of aggregate window functions with a frame, as well as offset window functions.
What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 2 2
SQL Server 2016 introduces the batch mode Window Aggregate operator, which dramatically improves the performance of calculating window functions. Besides the general performance advantages of batch mode processing compared to row mode processing, this operator uses a dedicated code path for each window function. Many inefficiencies in the original row mode optimization are removed.
What You Need to Know about the Batch Mode Window Aggregate Operator in SQL Server 2016: Part 1
This month I was scheduled to cover Part 6 of the Logical Query Processing series, but now that SQL Server 2016 has been released, I feel like I have to interrupt that series with a three-part series about an exciting new feature: the batch mode Window Aggregate operator.
Logical Query Processing Part 5: The FROM Clause and UNPIVOT
This article continues the series covering logical query processing, which describes the conceptual interpretation of a query.

Digital Magazine Archives

Browse back issues of SQL Server Pro, from January 2007 through the last issue published in April 2014. Find the back issues here.

 

From the Blogs
Jul 17, 2017
Sponsored

How Inferred Linking in Matched Records Digs Deeper into Your Data

Duplicate records clutter databases and render the data within them unclear. This kind of problem is very common, and it’s the main reason that deduping software exists. But there’s another benefit to deduplication software: the ability to infer connections between individual records from various data sets....More
May 22, 2017
Sponsored

Chief Data Officer Playbook: How to Be Successful in This Ground-Breaking Role

Companies looking to grow and extract value from their data are increasingly turning to Chief Data Officers (CDOs) to execute their data strategy. The role is new, and a playbook is necessary to address the many challenges CDOs face....More
May 12, 2017
blog

5 Common Challenges to Building BI in the Cloud

After spending 20 years building analytics, BI and database solutions, I've focused on Cloud data solutions over the past 2 years. I've chosen 5 common challenges that I face every day with Cloud migrations and that you'll face in your Cloud BI projects....More

Sponsored Introduction Continue on to (or wait seconds) ×