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,...
"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...
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...
Replacing xp_execresultset in SQL Server 2005
SQL Server 2000 included a very useful extended stored procedure called xp_execresultset. This XP had two parameters: @cmd and @dbname. @cmd was expected to be a SELECT statement that would produce a single column of output, each row of which would produce a valid query. @dbname was used to specify the database that both...
Exploring the secrets of intermediate materialization
When working with SQL Server 2000, I used to have this little trick I'd pull out after exhausting all other ideas for tuning a query. And I thought that my little trick was dead in SQL Server 2005, but thanks to fellow SQL Server MVP Rob Farley, I am officially reviving my trick from...
Scalar functions, inlining, and performance: An entertaining title for a boring post
Scalar. Function.Wow.Could any other combination of words evoke the same feeling of encapsulation, information hiding, and simplification of client code? After years spent developing software in the procedural and OO worlds, it can be difficult--perhaps, even impossible--to migrate over to working with SQL Server and not consider how to architect your data access logic...
More on string reversal!
In the last installment, I showed a potentially fastest method using Array.Reverse.After finding and fixing a bug in method #3 posted in my last installment (it is, in fact, quite a bit faster than method #1 when you don't have a big huge bug in the code <g>) creating a new method, and hearing...