I replied, “You are absolutely right that there is a better way: you can use Recursive CTEs!”

The CTE, Recursed

To quote SQL Server Books Online, “A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.” So what makes a CTE recursive? It references itself over and over, building subsets until it has returned the whole results set. A bit of a warning: you can only use CTEs with SQL Server 2005 or later.

To start, let’s use TempDB and create some data so we can play. We’re creating a single table, dbo.Family, and it is self-referencing in that both Parents and Children are stored inside of it. You can tell the top-most Parent when a row doesn’t have an ID listed in the ParentID field. If it does, it means that the row is the child of another, and the ID listed is the Parent’s MainID. (I know, it’s not at all confusing.) Once you create the table and populate the data, you can run the SELECT statement and see the normal output.

USE tempdb;
GO

CREATE TABLE dbo.Family (
	MainID INT NOT NULL IDENTITY PRIMARY KEY
	,ParentID INT NULL
	,Name VARCHAR(35) NOT NULL
);

ALTER TABLE dbo.Family
ADD CONSTRAINT FK_ParentChild FOREIGN KEY (ParentID)
	REFERENCES dbo.Family (MainID);
GO

INSERT INTO dbo.Family (ParentID, Name)
VALUES (NULL,'Parent A')
,(1,'Child A')
,(2,'Grandchild A1')
,(2,'Grandchild A2')
,(NULL,'Parent B')
,(NULL,'Parent C')
,(5,'Child B1')
,(6,'Child C')
,(5,'Child B2')
,(8,'Grandchild C')
,(10,'Great-Grandchild C');
GO

SELECT MainID, ParentID, Name
FROM dbo.Family;

SELECT * FROM dbo.Family

The Boring Syntax Part

As you’ll see in the examples below, the CTE part of the query is the SELECT statement wrapped in parathesis and setting on top of the normal SELECT statement. This simple query must begin with the keyword WITH, and then you’d name your CTE. I’ve named mine reCTE. Another warning: because of the keyword WITH, all previous statements must be closed with a semi-colon (;). You may often see others write their CTEs as “;WITH reCTE” with the semi-colon preceding the keyword. It’s a safety measure as the whole query will fail if the previous statement hasn’t been closed yet. Additionally, I like to include explicit column names, though you don’t have to.

As for the CTE, it is your basic SELECT statement. It’s used to build the temporary set that the final SELECT statement can use; it’s similar to using a temp table. To make a CTE recursive, you simply add a UNION ALL. When using UNION ALL, the number of columns must be equal between the two sets, and they must be in the same order and share the same data type in order for the UNION to function properly.

As you’ll see in the example queries below, the first SELECT query is called the Anchor Member. It returns a valid table that’s used as the basis of the recursion. The query that follows the UNION ALL is termed the Recursive Member. It’s the query that is run against the anchor until the complete set has been returned.

Power of Recursion

As before, this functionality is available to users of SQL Server 2005 and later. What about the times before SQL 2005? Well, you had to rely on some seconday table in order to recurse through a results set. Using CTEs, you can encapsulate this within a single query. One common request is to see how “deep” a family is. This might come in the guise of managers to subordinates in displaying how flat (or deep) an organizational chart is.

WITH reCTE (MainID, ParentID, Name, Depth) AS (
	--Anchor Member
	SELECT f.MainID, f.ParentID, f.Name, 1 AS Depth
	FROM dbo.Family as f
	WHERE f.ParentID IS NULL
	UNION ALL
	--Recursive Member
	SELECT f.MainID, f.ParentID, f.Name, Depth + 1
	FROM dbo.Family as f
	INNER JOIN reCTE
		ON f.ParentID = reCTE.MainID
)
SELECT reCTE.MainID, reCTE.ParentID, reCTE.Name, reCTE.Depth
FROM reCTE
ORDER BY reCTE.Depth, reCTE.MainID;

How Low Can You Go?

As above, the Anchor Member is used to set the basic result. In this case, we’re querying for the absolute Parents in the set. The Recursive Member follows the UNION ALL, and we actually reference the CTE in a JOIN while still defining reCTE as a whole. This is what makes it recursive.

The outer query returns the set we’ve created with the CTE. For the above set, there are four levels of depth. This could translate to an organizational chart, or in discovering one’s secondary and tertiary managers.

Another common use for the recursive CTE is to find all relationships to a single Parent. With a little modification, we can use nearly the same query but aim for finding all the subordinates to Parent A by changing the WHERE clause in our Anchor to a specific MainID.

WITH reCTE (MainID, ParentID, Name) AS (
	--Anchor Member
	SELECT f.MainID, f.ParentID, f.Name
	FROM dbo.Family as f
	WHERE f.MainID = 1
	UNION ALL
	--Recursive Member
	SELECT f.MainID, f.ParentID, f.Name
	FROM dbo.Family as f
	INNER JOIN reCTE
		ON f.ParentID = reCTE.MainID
)
SELECT reCTE.MainID, reCTE.ParentID, reCTE.Name
FROM reCTE
ORDER BY reCTE.MainID;

Who is in this Family?

That’s all I’ve got on CTEs for now. If you want to learn even more, check out Audrey Hammond’s talk on the subject. It’s number 18 on the list, and it’ll require a PASS login, but it’s well worth it. She’s quite the awesome T-SQL Wizard.

Microblog… Something Else… Profit!

A Big, Wooden Twitter!

I didn’t get Twitter at first. I thought that it was a service for self-involved folks to talk incessantly about what they ate at each meal. I sometimes fail in having the best confidence, and I thought, “Who would even care to learn about what I ate?”

But I didn’t get Twitter; I didn’t understand how people were using it. Sure, there are plenty of people who Tweet about what they ate for lunch, what they bought at the store, what location they just checked in to (and that you are free to rob their houses while they’re away) – and even I’m guilty of such behavior from time to time – but this isn’t want Twitter is about. Twitter isn’t about broadcasting; it’s about having conversations.

Twitter is a public, global chat room, except it’s cooler than AOL ever was and you can tune in to just the people you want to hear.

How I Use Twitter

When I was unceremoniously handed the role of Accidental DBA, I was alone. I had no one to go to with questions. I didn’t even have the support of the IT department. What I had was a SQL Server database, the knowledge of how to restart IIS via Command Line (seriously, don’t ask…), and a lot of questions around, “How would I even know if something were going wrong?”

Like any good geek, I turned to Google for answers. But it wasn’t until I ran across Brent Ozar’s blog and found his Simple Twitter book that I was sold enough to give Twitter a try.

Twitter saved me. Twitter became my support system at work. Twitter became a way to ask questions, double check assumptions, verify functionality, and blow off steam. Twitter became a way to find new blogs to read, and to share new blogs that I’ve written. Simply watching the #SQLHelp hashtag through the day opened me to new and often-times esoteric knowledge that I probably wouldn’t have encountered on my own.

How Should You Use Twitter?

Well, you first have to sign up for an account! After that, download a Twitter client to help you manage it. In the past I would’ve easily recommended TweetDeck, but they’ve made some changes to their app recently that I don’t fully approve but at least you can run it in a browser. (Yeah, I know: it’s free so get over it.) Do some searches for #SQLServer, #SQLHelp, #SQLPeople and #SQLPass to find some interesting and knowledgeable people to follow. And then…

Jump into the conversation! Offer your opinion, help in answering a question. Don’t hesitate, don’t become a lurker. Like I mentioned, Twitter is about conversation, and sure, you’ll often times jump in right in the middle of things, but that’s how the SQL Community rolls on Twitter. And it’s funny how a conversation can thread from clustering to coffee to bacon in a matter of minutes.

Come and join in!

Beginning today, you can access the newly released video library from SQLInspire NYC 2011. Seven inspirational talks, fourteen videos filled with wonderful stories and knowledge. Go and consume. Change your life and the SQL community.

When everyone is working towards the same goal, there is no time to assign blame. If even one person does, the whole team fails.

That said, I don’t understand all the smoke, mirrors, and general misdirection in trying to hide wrong in a client situation. Sure, they expect that we know what we’re doing. (And in the SQL Server sphere, they can sometimes expect a bit too much: “What? You aren’t an expert in administration, development, a tuning ninja, a database designer, IS/AS/RS, Warehousing, and an ETL architect?”) I also understand that we like to appear as superheroes to our clients, especially since that helps to keep the money flowing in.

But I think there comes a point in a project when the energy expended in dodging responsibility becomes greater than the energy required to fix the problem. And the whole situation rots into a stinking mess when multiple contractors are involved.

But you know what? When “Whose fault?” becomes more important than “How can we help?” everyone has failed. We may not like the other contractors or vendors that we have to work with, but positive outcomes for the client should never be put into jeopardy due to internal positioning and bickering.

If you want to keep your clients, follow these three simple steps: First, step up. Second, take responsibility. Third, keep your promises and deliver. You will become that superhero the client falls in love with and continues to utilize.

In the Beginning

Darkness, light, creation, and all that jazz. I’ve blogged before I came to the SQL Server community, and before I identified as a technologist at all. My blogging was a mix between the personal and the DC art world. Those sites are gone now, save for in the Way Back Machine. (Asides: I tried to grab a screenshot, but they didn’t have any nice looking ones.)

And Forever Continuing

Analog Blogs

I blog primarily because I like to share things. Sometimes it’s personal information about what’s happening in my life. Now I tend to focus on sharing knowledge about SQL Server and design topics. But I’m always sharing.

I blog because I enjoy writing. While I do have periods of writer’s block, I don’t have trouble with the blank page. I’ve learned to turn off my inner editor. I’ve learned to have fun in communicating, because if you don’t get enjoyment out of the activity, why the hell do it? (Not always true, of course, but especially true for blogging to a community that has many fantastic resources already.)

I blog because I think it’s helped to open up opportunities to me. It’s drawn me into becoming a presenter for the community. I’ve been approached about writing articles. It hasn’t helped to land me that Dream Gig (yet), but I don’t feel it’s hurt me either. I don’t think Andy and Brian would have asked me to join SQLPeople if I hadn’t been an active writer.

Lesson: It Really is About You

Like many others, I don’t blog for the page views or the visitor stats. I don’t blog because I make any money. I’ll admit that sometimes the analytics psych me out, but in comparison to my pre-SQL Server blogging, it’s the difference between night and day.

One of the main reasons I continue to blog is I feel that I’ve found the right audience. Despite all the great resources available, this community hungers for more. We all share the same voracious appetite for information. And there’s enough depth to the technology that there’s plenty of room for all kinds of content ranging from novice to expert level topics.

For all those writers out there, and for those that would like to begin writing, here is an important lesson: work on style and content, yes, but also find the right audience for your work. It’s critical that you do. The right audience will love the hard work you’re doing for them, and they’ll return the favor by spreading your work further. More, those people make all that work a pleasure.

T-SQL Tuesday

This month’s T-SQL Tuesday is hosted by Allen White. Allen has asked about T-SQL tips that make our work easier. I would like to share one around development.

Everyone knows that both Primary and Foreign Keys must share a few common attributes, such as sharing the same data type. But they do not need to share the same name, and this is something that I believe can hinder query development. Here’s an example:

CREATE TABLE dbo.Donors(
	Donor_ID	INT			IDENTITY(1,1)	PRIMARY KEY,
	First_Name	NVARCHAR(20)	NOT NULL,
	Last_Name	NVARCHAR(40)	NOT NULL
);

CREATE TABLE dbo.Gifts(
	Gift_Key	INT			IDENTITY(1,1)	PRIMARY KEY,
	Person_ID	INT			NOT NULL,
	Amount		MONEY		NOT NULL,
	Tender		VARCHAR(13)	NOT NULL,
	Gift_Date	DATE		NOT NULL,
	Campaign	VARCHAR(14)	NOT NULL
);

ALTER TABLE dbo.Gifts
ADD CONSTRAINT FK_Gift_Donor FOREIGN KEY (Person_ID)
	REFERENCES dbo.Donors (Donor_ID);

In this example, a foreign key relationship exists between Donor_ID and Person_ID. And in a simple, two table example, this isn’t likely to create any issues. But I’m certain that you don’t have to stretch your imagination to see how this may become a development issue when you are working against a whole database. And the problem is exacerbated once you begin to work on dozens of databases all at once.

What problem am I referencing? The problem of memory.

The Problem of Memory

Here is the previous code rewritten following my tip. You’ll notice that both columns referenced in the Foreign Key Constraint have the same name.

CREATE TABLE dbo.Donors(
	Donor_ID	INT			IDENTITY(1,1)	PRIMARY KEY,
	First_Name	NVARCHAR(20)	NOT NULL,
	Last_Name	NVARCHAR(40)	NOT NULL
);

CREATE TABLE dbo.Gifts(
	Gift_Key	INT			IDENTITY(1,1)	PRIMARY KEY,
	Donor_ID	INT			NOT NULL,
	Amount		MONEY		NOT NULL,
	Tender		VARCHAR(13)	NOT NULL,
	Gift_Date	DATE		NOT NULL,
	Campaign	VARCHAR(14)	NOT NULL
);

ALTER TABLE dbo.Gifts
ADD CONSTRAINT FK_Gift_Donor FOREIGN KEY (Donor_ID)
	REFERENCES dbo.Donors (Donor_ID);

And here’s a query written against both examples. They’ll each return the same results with the same performance… but which do you think was less taxing mentally to write?

SELECT d.Donor_ID, d.First_Name, g.Gift_Date, g.Gift_Amount
FROM dbo.Donors AS d
LEFT OUTER JOIN dbo.Gifts AS g
	ON d.Donor_ID = g.Person_ID
WHERE g.Gift_Amount >= 10

--versus

SELECT d.Donor_ID, d.First_Name, g.Gift_Date, g.Gift_Amount
FROM dbo.Donors AS d
LEFT OUTER JOIN dbo.Gifts AS g
	ON d.Donor_ID = g.Donor_ID
WHERE g.Gift_Amount >= 10

Personally, it’s the second. I don’t have to take a split second to think “Did I join those tables on the right columns?” I don’t have to take thirty seconds to look up that information if I’ve guessed wrong. Naming your constraints the same takes the guess work out of query writing. It may not be a game-changer, but this has helped me in a number of occasions, and I hope it helps you too.

We’ve made it impossible to be unbusy.

When you subscribe [like I do] to the Godin-ian mindset of fighting your inner Resistance in order to do important work, Busy becomes the easiest way to placate yourself into thinking that you’ll eventually get all that stuff done. You aren’t giving in. You aren’t giving up. You are simply Busy.

Done with Busy

A few days ago, I shared a link (which was shared with me by Tom LaRock) about The Cult of Done Manifesto. While many of its precepts rely and build upon each other, the very last rule, lucky number thirteen, wraps up the whole philosophy and is the most important.

13. Done is the engine of more.

I’d like to think that Done is good habit that can be built. That it can help fight The Resistance, and banish Busy. I hope that by recognizing Busy as a prime excuse, I can stop using it; I can remain focused on producing great work for my family, friends, and the SQL Server community. If Busy is one of your excuses as well, we can support each other in getting the work done for the betterment of all.

So who’s busy?

Standard Disclaimers Apply

Of course, working yourself up into a frothy mix of anxiety from all the work you aren’t getting done isn’t useful either. The standard life-work balancing act applies in all things. It’s making choices and prioritizing what’s important. Your family needs you today and every day. So do your friends and co-workers. But maybe you can TiVo that episode of N.C.I.S. and build something that is life-changing.