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
First Look at System-Versioned Temporal Tables-Part 2: Querying Data and Optimization Considerations
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 6
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 1

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 2
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 8
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 2
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.
warehouse inventory
T-SQL Challenges: Replenishing and Depleting Quantities 4
Itzik discusses two T-SQL challenges: replenishing quantities and depleting quantities. He presents an efficient solution for replenishing quantities and challenges you to find an efficient solution for depleting quantities.
optimize
Optimization Tips for Multiple Range Predicates, Part 3
Itzik dives even deeper into the problem of using multiple predicates in your query filters.

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 28, 2015
blog

AlwaysOn Availability Groups and SQL Server Jobs, Part 29: Practical Implementation Tips

My initial goal in writing this series of posts was to outline some of the concerns surrounding Availability Groups (AGs) and SQL Server Agent Jobs – and call out how there is virtually no guidance from Microsoft on this front and then detail some of the pitfalls and options available for tackling this problem domain. I initially expected this series of posts to have between 25 and 30 posts – according to some of the early outlines I created ‘way back when’....More
Jul 6, 2015
blog

AlwaysOn Availability Groups and SQL Server Jobs, Part 28: Additional Options for Tackling Jobs Failover

Throughout this series of posts I’ve taken a somewhat pessimistic view of how SQL Server Agent jobs are managed within most organizations – meaning that most of the code and examples I’ve provided up until this point were based on assumptions about how CHANGE to jobs is managed. That pessimism, to date, has come in two forms:...More
Jul 1, 2015
blog

AlwaysOn Availability Groups and SQL Server Jobs, Part 27: Options and Concerns for More Advanced Deployments

In this series of posts I’ve called out some of the concerns related to SQL Server AlwaysOn Availability Groups and their interaction with SQL Server Agent jobs – both in the form of Batch Jobs (see post #3) and backups....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) ×