Reversing a string in .NET
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 --...
Stored procedures are not parameterized views
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...
T-SQL Variables: Multiple Value Assignment
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...
Running sums yet again: SQLCLR saves the day!
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...
Running sums, redux
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...
Swinging From Tree to Tree Using CTEs, Part 2: Adjacency to Nested Intervals
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...
Swinging From Tree to Tree Using CTEs, Part 1: Adjacency to Nested Sets
I'm not sure how many times over the last several years I've seen the same tired article titles... "Climbing Trees in SQL," "Climbing Up the SQL Tree," or maybe, "Naked Coeds Playing in the Trees!" ... Oh wait, I think that last one might be something else. But anyway, the point is, I'm...
Looping over routines using sp_foreachroutine
Of all of the undocumented stored procedures shipped with SQL Server, there are two in particular that I constantly use: sp_MSforeachtable and sp_MSforeachdb. These procedures internally loop over each non-Microsoft shipped (i.e. user-defined) table in the current database, or each database on the current server, respectively. During this loop, the procedures perform whatever action(s)...
SQL Server 2005 T-SQL: Aggregates and the OVER clause
A new feature added to SQL Server 2005 for the sake of the windowing functions is the OVER clause. Using this clause, you can specify ordering or partitioning for the windowing functions. For instance, to enumerate the names of all of the products in the AdventureWorks database that have a list price, along with...
Tokenize UDF
Yes, another string splitting UDF from a guy who's obvioiusly become obsessed with TSQL string splitting. This time we delve into a mysterious world that I call, "Tokenization." So what is Tokenization? It's a word I made up for this problem. But what is it, really? It's splitting up a string based...