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...

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.  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...

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...

Over in the Simple-Talk forums, there is a good thread going about how best to reverse a string in .NET, since no string reverse method is included in the BCL. A few suggestions were made, and someone implied that they were too complex and that simplicity is the most important factor.  Personally, I wonder --...

Peter van Ooijen over at CodeBetter.com posted in his blog about some observations he had when working with stored procedures in a recent project. What I found to be interesting about his post was his comment that a stored procedure can be, "a view with parameters."  I've run into this assertion before, and it's...

Tony Rogerson brings us an interesting blog post about T-SQL variable assignment and SET vs. SELECT.  The issue?  With SELECT you can assign values to multiple variables simultaneously.  But with SET, you can set up your assignment such that you get an exception if more than one row is assigned to the variable.  Both...

Back again!  Fourth post for the month of February, making this my best posting month in, well, months.  Expect this trend to continue. After yesterday's post on running sums and the evils of cursors, Jamie Thompson came up with a faster solution than the curser I posted.  Alas, Jamie's solution uses an undocumented form of UPDATE...

Siddhartha Gautama, the Buddha, taught us to understand that the key to enlightenment is following the Middle Path.  And today I learned a valuable lesson in extremes.  You can file this one in the "Doh!  Wrong again!" category... A fairly common question on SQL Server forums is, "how can I get the running...

In our previous installment, we saw how to convert Adjacency Lists into Nested Sets using a CTE. In this episode, we will convert the Adjacency List into a Nested Intervals encoding.  Specifically, this encoding will make use of the Nested Intervals with Continued Fractions technique that Tropashko presented in a later paper. The key to this...