String Replacement Puzzle

Courtesy of SQL Server Pro. You can find the original article here.

My dad, Gabriel Ben-Gan, passed away recently. He loved numbers, logic and puzzles, and used to solve problems in his own unique way. This article is about a puzzle that incorporates the above ingredients. Dad, this one’s for you, and is in your memory.


Packing Intervals

Packing Intervals

SQL Server doesn’t support a native temporal interval type. A temporal interval has a starting point in time and duration. Therefore, when you want to represent intervals in your database, you need to come up with your own solution. Most use two attributes representing the start and end points in time. But then you also need to implement your own solutions to fundamental operations on intervals like packing, unpacking, and others. Packing intervals involves merging intersecting intervals. So far, set-based  solutions to packing intervals were very slow, and often people resorted to use of cursors. This article covers new set-based techniques to handle intervals in a highly efficient manner. The new techniques utilize window functions and are optimized with close to linear scaling. (more…)