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
Seek and You Shall Scan Part II: Ascending Keys 2
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 3
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 9
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 15
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 7
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.
data center
Compute a Trimmed Mean
Use the trimmed mean method to exclude outliers from the computation of an average aggregate.
table data
Learn How to Deduplicate Data 1
Discover a simple method for deduplicating data.
filter
Get Accurate Estimates with Filtered Indexes 1
Use filtered indexes and statistics for improved cardinality estimates.
number values
Find a Minimum Missing Value 5
Discover the most efficient method to identify the minimum missing value after the minimum existing value.
databases
Sequence and Identity Performance
Learn how SQL Server handles caching for sequence and identity, as well as how to determine the optimal cache size for your environment.

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
Sep 15, 2016
Sponsored

Power BI Desktop “Publish to Pyramid Server” Button

Many organizations today cannot use public cloud solutions because of security concerns, administrative challenges and functional limitations. However, they still need a centralized platform where end users can conduct self-service analytics in an IT-enabled environment....More
Jul 6, 2016
Sponsored

Using BI Office Together with Microsoft Power BI Desktop to Unlock Business Insights

It is crucial to move away from data and analytics stored on individual desktop computers. Today’s solutions must promote holistic, collective intelligence. The strong, continued alliance between Microsoft and Pyramid Analytics helps make all this possible....More
Jul 6, 2016
Sponsored

Why It’s Important to Unlock Business Insights Trapped on Individual Desktops

To become a truly data-driven enterprise, many business leaders recognize that they must extend the capabilities of self-service business intelligence (BI) and analytics to more of their business users. Many BI tools tackle part of this need, but they don’t offer a complete enterprise solution....More

Sponsored Introduction Continue on to (or wait seconds) ×