The Hidden Costs of INSERT EXEC
INSERT and EXEC: relational brothers in arms, helping you get your data and then put it somewhere. But like all brothers, their relationship has its ups and downs and sometimes you need to look just below the surface to see the real issues. In this post I will cover hidden penalties that a decision--seemingly...
SQLCLR String Splitting Part 2: Even Faster, Even More Scalable
Two days ago, after posting what I thought was a pretty solid SQLCLR string splitting method, I received a comment telling me about a big thread on SQLServerCentral dedicated to the question of how best to split strings. So I jumped in, and went back and forth, and back and forth, and back... and...
Faster, More Scalable SQLCLR String Splitting
It seems like every couple of months we see yet another post on SQLCLR string splitting routines. Many bloggers, I suppose, are still struggling, even three years later, to find that "perfect" use case for SQLCLR. Is string splitting it? Probably not. And with SQL Server 2008 table-valued parameters now available, SQLCLR string splitting...
Who’s On First? Solving the Top per Group Problem (Part 1: Technique)
Relative comparison is a simple matter of human nature. From early childhood we compare and contrast what we see in the world around us, building a means by which to rate what we experience. And as it turns out, this desire to discover top and bottom, rightmost and leftmost, or best and worst happens...
A Gift of Script for 2008: Who’s Active, What Are They Doing, and Who is Blocked?
As with all of the blog posts I keep meaning to write -- I keep a list and given the infrequency with which I've been posting lately, it's getting quite large -- this script has been on the queue for quite some time. So here I find myself with a spare moment right on...
Anti-Patterns and Malpractices, Volume 1: Tumbling Data
"Lonely but free I'll be found
Drifting along with the tumbling tumbleweeds"
- Supremes, "Tumbling Tumble Weeds"
Welcome to the first installment of what I hope will be a regular feature on this blog, Anti-Patterns and Malpractices. As a consultant, I get the honor of seeing a lot of different systems, with a lot of different code....
“Cursors Run Just Fine”
I found Linchi's recent post on use of cursors in the TPC-E test to be quite interesting. The question is, why are cursors used in the test when the commonly accepted notion within the SQL Server community is that cursors are a bad thing?
I've posted in the past about situations where cursors were actually...
Programmatic Concurrency Control: Do Simultaneous Updates to Different Columns Constitute a Collision?
In college, as part of my philosophy degree, I took a course on metaphysics. I can still vividly remember the first class. The professor presented us with a simple enough scenario:Consider a wooden boat, whose construction has just finished. Now fast-forward several months. The boat has been well used, and one of the planks...
Service Broker: Messages are queued, but how about readers?
The central database object that supports SQL Service Broker (SSB) is a queue. Messages sent between SSB services are queued and are delivered in order within a conversation. And that's exactly what one would expect from a queued solution. But how about other parts of the SSB equation? Do they also follow a...
Medians, ROW_NUMBERs, and performance
A couple of days ago, Aaron Bertrand posted about a method for calculating medians in SQL Server 2005 using the ROW_NUMBER function in conjunction with the COUNT aggregate. This method (credited to Itzik Ben-Gan) is interesting, but I discovered an even better way to attack the problem in Joe Celko's Analytics and OLAP in...