I’ll never forget my very first day as a philosophy TA. I’d been assigned to”Introduction to Ethics” and Professor Oldenquist was describing his expectations and class mechanics. After explaining that, “no, ethical theory is not just a matter of opinion” and “yes, we can give you an objective grade”, he walked to the edge of the stage, smiled wryly, and said: “There’s one more thing I’d like to clear up: you may hear some of your professors say that ‘there’s no such thing as a stupid question’. They’re wrong. That’s rubbish. There are stupid questions, please don’t ask them in my class.”

He paused for effect and continued, “A stupid question is one whose sole purpose is to make the speaker look bad or the questioner look good.  Please, don’t ask them in my class”.

We still got our share of “stupid” questions (embarrassing new TAs is a Big 10 sport, after all) but I think that bit of administrivia was his most important lesson of the quarter.

Which brings me to “Stupid SQL Questions”, the subject of this month’s MemeMonday creative writing assignment.

Whenever I’m at a user group or seminar and the speaker says “there’s no such thing as a stupid question.”   I remember Professor Oldenquist.  Yes, there are stupid questions.  In a professional context, a stupid question is one that’s designed to make the speaker look bad, the questioner look good, or one that derails the speaker from the presentation’s topic to “a problem I encountered this week.”

Yes, there are stupid SQL questions: please, don’t ask them.

I’ll never forget my very first day as a philosophy TA. I’d been assigned to”Introduction to Ethics” and Professor Oldenquist was describing his expectations and class mechanics. After explaining that, “no, ethical theory is not just a matter of opinion” and “yes, we can give you an objective grade”, he walked to the edge of the stage, smiled wryly, and said: “There’s one more thing I’d like to clear up: you may hear some of your professors say that ‘there’s no such thing as a stupid question’. They’re wrong. That’s rubbish. There are stupid questions, please don’t ask them in my class.”

He paused for effect and continued, “A stupid question is one whose sole purpose is to make the speaker look bad or the questioner look good.  Please, don’t ask them in my class”.

We still got our share of “stupid” questions (embarrassing new TAs is a Big 10 sport, after all) but I think that bit of administrivia was his most important lesson of the quarter.

Which brings me to “Stupid SQL Questions”, the subject of this month’s MemeMonday creative writing assignment.

Whenever I’m at a user group or seminar and the speaker says “there’s no such thing as a stupid question.”   I remember Professor Oldenquist.  Yes, there are stupid questions.  In a professional context, a stupid question is one that’s designed to make the speaker look bad, the questioner look good, or one that derails the speaker from the presentation’s topic to “a problem I encountered this week.”

Yes, there are stupid SQL questions: please, don’t ask them.

Today on Twitter #SqlHelp, someone asked how to find the definition of an expression in a graphic execution plan. I thought I’d pass along my answer in case it helps someone else.

Consider a very simple query, which includes a scalar operator, such as a SUM():

It produces a simple graphical execution plan like this:

Examining the Hash Match operator shows an output named “EXPR1002”. It’s a calculated expression, but what’s its definition?

To find a calculated expression’s definition, select the operator and hit F4 to bring up the Properties window.  The definition is in the Defined Properties entry.  Here, it’s the SUM() of the OrderQuantity.

In a more complex query the output of one operator may be used by another operator, resulting in expressions operating on expressions, so you may need to check more than one step to find the fields involved.

Today on Twitter #SqlHelp, someone asked how to find the definition of an expression in a graphic execution plan. I thought I’d pass along my answer in case it helps someone else.

Consider a very simple query, which includes a scalar operator, such as a SUM():

It produces a simple graphical execution plan like this:

Examining the Hash Match operator shows an output named “EXPR1002”. It’s a calculated expression, but what’s its definition?

To find a calculated expression’s definition, select the operator and hit F4 to bring up the Properties window.  The definition is in the Defined Properties entry.  Here, it’s the SUM() of the OrderQuantity.

In a more complex query the output of one operator may be used by another operator, resulting in expressions operating on expressions, so you may need to check more than one step to find the fields involved.

This month’s TSQL-Tuesday topic is so much fun to write about that I’ve decided to create a second post.

When Recursive CTEs were introduced in SQL Server 2005, many people gave examples of how they can be used to generate a “numbers” table.  (I’ll ignore the debate about whether it’s the best way to generate the table, or if we should even care).   Well, once you have a numbers table it’s easy to create a Calendar table or Date Dimension.

The script consists of three parts.  The first section is a recursive CTE, using a method attributed to Itzik Ben-Gan, that generates a numbers table.  In the second section, each number in the table is added to your starting date to generate a list of days between the start and end dates.  Each date is also associated with the first date of its fiscal year using a creative approach I learned from this forum post.  This “first date” of a fiscal year will be used to perform the fiscal date calculations in the final step. Finally, the days CTE is used with a series of date functions to populate the calendar table or date dimension.

Here’s a sample of the output:

Do I get extra credit for writing two posts?

This month’s TSQL-Tuesday topic is so much fun to write about that I’ve decided to create a second post.

When Recursive CTEs were introduced in SQL Server 2005, many people gave examples of how they can be used to generate a “numbers” table.  (I’ll ignore the debate about whether it’s the best way to generate the table, or if we should even care).   Well, once you have a numbers table it’s easy to create a Calendar table or Date Dimension.

The script consists of three parts.  The first section is a recursive CTE, using a method attributed to Itzik Ben-Gan, that generates a numbers table.  In the second section, each number in the table is added to your starting date to generate a list of days between the start and end dates.  Each date is also associated with the first date of its fiscal year using a creative approach I learned from this forum post.  This “first date” of a fiscal year will be used to perform the fiscal date calculations in the final step. Finally, the days CTE is used with a series of date functions to populate the calendar table or date dimension.

Here’s a sample of the output:

Do I get extra credit for writing two posts?

A common beginner’s misconception about Common Table Expressions (CTEs) is that they are a real result set, like those produced by a temporary table or table variable. In fact, the opposite is true: they’re really just a way to simplify and encapsulate your code. For this month’s TSQL-Tuesday, focused on CTEs, I want to illustrate this difference with an example of how a (non-recursive) CTE can be both more and less efficient than a temporary table at accessing data.

Let’s start with a simple query comparing the sales volume and average price between a given year and the previous year expressed as both a CTE and a derived table:

Both the CTE and the derived table generate the same execution plan:

Statistics IO for both queries is also identical:

So, from an execution standpoint, the derived table and CTE are essentially the same. Now, consider the same query using a temporary table:

Along with its query plan:

And Statistics IO:

Notice that in both the CTE and derived table versions, SalesOrderHeader and SalesOrderDetail are each accessed twice and the aggregations are each calculated twice.  The temporary table version accesses each table once, aggregates the results, and then uses the smaller temporary table to produce the final results.  As a result, the CTE version is nearly twice as expensive as the temporary table.   If a CTE were truly a “results set” (as some authors and speakers have presented it), then we should only see SalesOrderHeader  and SalesOrderDetail accessed once, just as with the temporary table, and it should have a similar IO cost.  They’re not.  Conclusion: a CTE is not a temp table or stored results set.

In this particular case, I’ve structured my queries so that the CTE was a less efficient way to access the data. There are times, however, that the compiler can take advantage of the CTE structure and create a more efficient way to access the data.

Here are two (oversimplified) queries to illustrate this point:

Along with their Statistics IO:

The temporary table approach still pays the cost of aggregating all the data first and it has the same IO cost as the first sample query.  With the CTE, however, the compiler is able to take advantage of the WHERE clause when it’s expanding the query.  As a result, it aggregates less data and uses about half the IO that’s involved in creating and reading from the unfiltered temporary table.   The text showplan reveals this application of the WHERE clause:

So which approach is better, the CTE or temporary table?  As always, it depends on your data and your use.  For a little help deciding, why not check out the rest of the posts in this month’s TSQL-Tuesday?

A common beginner’s misconception about Common Table Expressions (CTEs) is that they are a real result set, like those produced by a temporary table or table variable. In fact, the opposite is true: they’re really just a way to simplify and encapsulate your code. For this month’s TSQL-Tuesday, focused on CTEs, I want to illustrate this difference with an example of how a (non-recursive) CTE can be both more and less efficient than a temporary table at accessing data.

Let’s start with a simple query comparing the sales volume and average price between a given year and the previous year expressed as both a CTE and a derived table:

Both the CTE and the derived table generate the same execution plan:

Statistics IO for both queries is also identical:

So, from an execution standpoint, the derived table and CTE are essentially the same. Now, consider the same query using a temporary table:

Along with its query plan:

And Statistics IO:

Notice that in both the CTE and derived table versions, SalesOrderHeader and SalesOrderDetail are each accessed twice and the aggregations are each calculated twice.  The temporary table version accesses each table once, aggregates the results, and then uses the smaller temporary table to produce the final results.  As a result, the CTE version is nearly twice as expensive as the temporary table.   If a CTE were truly a “results set” (as some authors and speakers have presented it), then we should only see SalesOrderHeader  and SalesOrderDetail accessed once, just as with the temporary table, and it should have a similar IO cost.  They’re not.  Conclusion: a CTE is not a temp table or stored results set.

In this particular case, I’ve structured my queries so that the CTE was a less efficient way to access the data. There are times, however, that the compiler can take advantage of the CTE structure and create a more efficient way to access the data.

Here are two (oversimplified) queries to illustrate this point:

Along with their Statistics IO:

The temporary table approach still pays the cost of aggregating all the data first and it has the same IO cost as the first sample query.  With the CTE, however, the compiler is able to take advantage of the WHERE clause when it’s expanding the query.  As a result, it aggregates less data and uses about half the IO that’s involved in creating and reading from the unfiltered temporary table.   The text showplan reveals this application of the WHERE clause:

So which approach is better, the CTE or temporary table?  As always, it depends on your data and your use.  For a little help deciding, why not check out the rest of the posts in this month’s TSQL-Tuesday?

In this month’s Meme Monday, Tom LaRock challenges us to list nine things that can go wrong with SQL that aren’t a result of disk issues.  Here are mine:

1. Selecting from a view returns an error because an underlying table was changed.
2. “String or binary data would be truncated” error on insert.
3. Parameter sniffing.
4. High CXPACKET activity because of an incorrect MAXDOP setting.
5. Unnecessary query compilation/recompilation because code was written as Database..Table instead of explicitly specifying a schema. (e.g. Databse.dbo.Schema).
6. Deadlocks.
7. ODBC Timeout in the client.
8. “Cannot generate SSPI context”.
9. Using IDENTITY_INSERT  on an identity column creating duplicate values (because the column doesn’t have a unique constraint).

I recently started using PsPad as a general purpose text editor. It’s freeware, portable, and offers a lot of great editing features such as syntax highlighting, bracket matching, and regular expressions.  It also has a macro recorder and can be scripted in VBScript and JScript.

One thing I didn’t like was that the application defaulted to opening the last open file or project on startup. It’s not obvious how to change the setting because the it isn’t listed as a startup setting. Here’s where to find it:

On the Settings menu, click Program Settings to open the Program Settings property sheet.

Select the Files and Dirs settings to open the file preferences:

Change the Save Work/Desktop setting from “In PsPad opened files only” to “Don’t restore files”.

Click OK and restart PsPad.  It will now open to an empty workspace.