Itzik Ben-Gan

Itzik
Ben-Gan

Itzik Ben-Gan is a contributing editor to SQL Server Pro and a cofounder of SolidQ. He teaches, lectures, and consults internationally. He's a SQL Server MVP and is the author of several books about T-SQL, including Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Microsoft Press).

Check out Itzik's Puzzled by T-SQL blog.
Twitter: @ItzikBenGan

Articles
Logical Query Processing: What It Is And What It Means to You 5
If I had to name a foundational topic in T-SQL that I felt that was the most important for T-SQL practitioners to know, without a doubt I would say “logical query processing.” A solid understanding of this topic is key to writing correct and robust code.
How To: Previous and Next with Condition 2
There’s a common T-SQL need that involves computing for each current row a value from a previous or next row. For this purpose, T-SQL has the LAG and LEAD window functions. Things get trickier, though, when you need to add a condition. For example, suppose you need to compute the last col1 value that was greater than x; or, based on col1 order, compute the last col2 value that was greater than x. In this article I’ll explain how you can achieve such tasks.
Packing Intervals with Priorities 1
Packing intervals is a classic T-SQL task that involves packing groups of intervals that intersect into single continuous intervals.
SQL Server Query Optimization: No Unknown Unknowns 1
In this article, I’ll provide the hard-coded guesses that the optimizer uses with the optimize-for-unknown technique so that at least you know what the optimizer guesses it doesn’t know. Good query tuning, in great part, starts with being able to explain cardinality estimations—especially ones that are inaccurate.
Seek and You Shall Scan Part I: When the Optimizer Doesn't Optimize 2
Index Seek and Index (or Table) Scan are two of the most common operators that you see in query execution plans. A common misconception is that a scan is bad and a seek is good. The reality is that each is optimal under different circumstances. In some cases the optimizer chooses between the two based on which is indeed more optimal in the given situation. In those cases, other than appreciating the optimizer's ability to come up with the truly optimal plan, there's nothing special that we need to do. However, what’s interesting to us as people who tune queries is to identify cases, or patterns, in which the optimizer doesn’t make the optimal choice and act to fix them.
Seek and You Shall Scan Part II: Ascending Keys
In another column I cover cases in which the optimizer uses table or index scans versus ones in which it uses index seeks. I explained when the optimizer’s choices were efficient by default and when they weren’t (and provided solutions for when they weren’t). In this column I continue the discussion by covering a problem known as the ascending key problem.
New Solution to the Packing Intervals Problem

Packing intervals is a classic T-SQL problem that involves packing groups of intersecting intervals into their respective continuous intervals. I set a challenge to myself to try and find an elegant solution that can achieve the task by using only one supporting index and a single scan of the data, and I found one.

First Look at System-Versioned Temporal Tables-Part 2: Querying Data and Optimization Considerations 2
This article is the second part in a two-part series about system-versioned temporal tables—a new feature introduced in Microsoft SQL Server 2016. Part 1 covered what system-versioned temporal tables are, how to create them and how to modify data in them. This article focuses on querying data and optimization considerations.
First Look at System-Versioned Temporal Tables-Part 1: Creating Tables and Modifying Data 7
SQL Server 2016 introduces support for system-versioned temporal tables based on the ISO/ANSI SQL:2011 standard. A table without system versioning enabled holds only the current, most recent, state of its rows. You cannot query past, deleted or pre-updated states of rows. For the purpose of our discussion, I’m ignoring row-versioning capabilities related to concurrency control, like the multi-versioning concurrency control (MVCC) support of the In Memory OLTP engine, and the row versioning support of the snapshot and read committed snapshot isolation levels for disk-based tables.
Improvements in Table Variables and Temporary Tables in SQL Server 2014 3

SQL Server 2014 introduces a number of gems that can make your solutions faster: support for inline index definitions, memory optimized table types and table valued parameters (TVPs), parallel SELECT INTO, relaxed eager writes and improved cardinality estimates for table variables. The last two improvements were also backported to SQL Server 2012. Some of the new features target specifically temporary objects, whereas others are more general and just happen to effect temporary objects as well.

Puzzle Me This: String Replacement 4
If you love numbers, logic and puzzles, this one is for you.
table data
Table Variable Tip 2
One great reason to use table variables rather than temporary tables is that table variables aren't affected when a user transaction rolls back.
missing puzzle piece
The Last non NULL Puzzle 10
Returning the last non NULL value is a common and simple need, but there's no straightforward solution.
question mark key
Use the TOP Filter's WITH TIES Option 4
Take a little quiz to see if you know what the TOP filter's WITH TIES option does.
database
Avoid Unnecessary Lookups when Using ROW_NUMBER for Paging
Eliminate unnecessary lookups to improve the performance of the typical solution for paging using row numbers.

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
Feb 5, 2016
blog

The Solution to Large Quantities of Data

High-quality data is key to uncovering meaningful insights and new opportunities. Melissa Data has recently released a new tool, Data Profiler, to help companies that are dealing with large quantities of data and need a full spectrum of solutions that work....More
SQL Server 2016 Reporting Services
Jan 25, 2016
Article

Top Takeaways from Microsoft's Reporting Roadmap

The future for on-premises Microsoft business intelligence looks a whole lot brighter these days. Here are the top five takeaways from Microsoft's recent business intelligence announcements....More
Microsoft Power BI
Dec 8, 2015
Article

It is Time to Migrate Power BI for Office 365 1

Don't forget to migrate Power BI for Office 365 content before you leave for the holidays. Deprecation of Power BI for Office 365 starts at the end of December 2015. Here is what you need to know to successfully move your business intelligence content to the latest and greatest version of Microsoft Power BI....More
SQL Server Pro Forums

Get answers to questions, share tips, and engage with the SQL Server community in our Forums.

Sponsored Introduction Continue on to (or wait seconds) ×