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...
“Reflect” a TSQL routine
Ever want to see the text of a stored procedure, function, or trigger -- or manipulate the text in some way? sp_helptext works, sort of. But I really don't like the way it handles large procedures (> 4000 characters). They seem to end up with some strangely wrapped lines and other side-effects that...
Script out PKs/UNIQUE constraints and referencing FKs
In the course of my work, I occasionally need to cluster a primary key that's nonclustered, or go the other way, or make some other modification to a primary key... But it's a hassle! All of the foreign keys need to be dropped, the PK needs to be dropped, and then everything needs to...
Pattern-based split string
"hickymanz" asked in the SQL Server Central forums for a method of counting unique words in a text column. Wayne Lawton recommended using a string split function, which was a good idea, but not quite adequate for the job in my opinion. Typical string split functions, like this one that I wrote can...
Bitmask Handling, part 4: Left-shift and right-shift
Quick installment this time. Left-shift and right-shift operators. Left-shift and right-shift are integral to binary mathematical operations as they have two important qualities: Left-shifting a bitmask once multiplies by two. Right-shifting once divides by two. For example: 0011 (base 2) = 1 + 2 = 3
3 << 1 = 0110 (base 2)...
Bitmask Handling, part 3: Logical operators
It's been longer than I hoped since my last installment on bitmask / big number handling. Life caught up with me and I've had many thankless tasks to catch up on. But that's over now and I'm back to the general slacking that typifies my days, so welcome to Part 3, handling logical operators....
Bitmask Handling, part 2: Bitmask reconstitution
Posting the first part of my series on bitmasks (yes, this is now officially a series) taught me a lot about my readers: You don't care about handling bitmasks in the database. And I respect you for that! I'm overjoyed, as a matter of fact! That article has received the least hits of anything...