Getting Feedback from Stored Procedures

I'm one of those people who puts as much business logic in the database. Stored procedures, views and functions are my "middle tier". That can sometimes lead to long running procedures with little feedback from the UI. I've often wished that you could return interim status information back from SQL.


Well, it turns out you can. The MSDN blog post, "Getting feedback / progress from batches and stored procedures", details how using RAISERROR.

BTW, the article also notes why there's a missing "E" in RAISERROR.

Putting SQL to Sleep

Have you ever wanted to test how your application would handle a long running stored procedure, but didn't want to create a multi-gigabyte database to do so?  Add a WAITFOR statement. 

WAITFOR is similar to .NET's Thread.Sleep() in which you can stop a thread from processing for a specified period of time.  You can either pass WAITFOR a delay time for the batch to pause for or a specific time for the batch to continue afterwards as follows:

WAITFOR DELAY '00:00:45' --Delays the batch for 45 seconds.

WAITFOR TIME '12:30'  --Restarts the batch at the next line at 12:30.

Note that there is no date specified in the WAITFOR TIME statement.  You can only delay it within a 24 hour period using the TIME clause.

One thing to watch out for are transactions.  If you put a WAITFOR within a transaction, any locked resources will remain so for the duration of the delay, potentially causing timeouts and deadlocks.

WAITFOR is also used for waiting for Service Broker messages, but that's a whole other blog entry.

I'm sure there are other uses for WAITFOR but for now, happy napping!

SQL Connection Kill Switch

Aaaarrrrg……

OK, so a few SSMS functions, such as Detach Database, have a checkbox letting you kill all current connections before performing the requested operation.  But some, say Restore, don’t (could someone give me a good reason why?), and end up failing if someone else (or yourself in another query window) has an open connection.  Your solution is to open the Activity Monitor, sort by database and kill each of the connections and hope that no new ones are opened in the mean time.  And what if you want to do this all in a script anyway?

To solve the above problems you can run the following script:

USE master

DECLARE @SQLStatement VARCHAR(MAX)
SET @SQLStatement = ''

SELECT
        @SQLStatement = @SQLStatement
            + 'KILL '
            + convert(VARCHAR(10), spid)
            + CHAR(13) + CHAR(10)
    FROM master.dbo.sysprocesses
    WHERE DB_NAME(dbid) = 'MyDatabase'
        AND dbid <> 0
        AND spid <> @@SPID

EXEC(@SQLStatement)

GO

So what exactly is this doing?  The SELECT statement is building a collection of KILL n statements where n is a process ID of a process connected to the specified database (in this case “MyDatabase”).  The CHAR(13) + CHAR(10) are really only there for aesthetics and debugging purposes.  Note that the WHERE clause explicitly excludes the connection of the current process.

Once this collection of KILL statements is built it executes them.  By using @SQLStatement = @SQLStatement + {next KILL statement}, it’s continually appending the next KILL statement to itself and the resulting value in the @SQLStatement variable is the entire list of KILL statements.

Giving credit where credit is due, I would like to note that I copied and tweaked the above statement from the Aaquagua blog.  In that blog, it is noted that the following statements will have the same effect:

ALTER DATABASE MyDatabase
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE

ALTER DATABASE MyDatabase
    SET MULTI_USER WITH ROLLBACK IMMEDIATE

What we’re doing here is setting the database to single user mode, which immediately kills any current connections, and then setting back to multi-user mode.  Although this will in fact have the same effect, there’s something hackish about it that makes me a tad uncomfortable.

But that’s just my neuroses speaking.  You can pick whichever one works for you.

UPDATE:

OK.  So after writing this, I was thinking about the method of setting the database to single user mode first.  My issue with using it to kill connections is that your using a statement that does one thing, set a database to single user mode, to perform something else, kill all the connections.  Yes it works, but if someone were to come along and review your script, they may wonder what you’re doing.

Anyway, upon further pondering, I decided that setting the database to single user mode before performing an action, such as a restore or major update, is probably a good idea anyway by preventing any other connections from interfering with the action.  The idea would be to wrap your action inside the two set statements.

My Shiny New Blog

Welcome to my new DataGeek blog.  New you say?  What's with all these old posts?  Did you create some sort of time machine?

Don't we all wish.  I've retired (at least temporarily) my prior blog, DynamicsGeek, to focus (or not) on my real passion, data.  Databases, database development, data warehouses, data cubes, data marts, data analysis...pineapple shrimp, lemon shrimp, coconut shrimp, pepper shrimp, shrimp soup, shrimp stew...You get the picture.  Since I seemed to write a number of non-dynamics GP specific articles in DynamicsGeek anyway, I thought I'd move them over (and make myself look a little more productive)

Just like I veered off the subject of Dynamics GP, I'm sure I'll veer off the subject of data as well but hopefully it will be relevant to someone!

Strip Time from SQL Date

Often, particularly when saving dates to Dynamics GP, we need to strip the time out of a SQL date value. I found a neat little trick for doing that recently in an article on SQLServerCentral.com by Seth Phelabaum.

SELECT DATEADD(dd, DATEDIFF(dd,0,GETDATE()), 0).

This works by getting the number of days since date 0, which truncates the time, and then adding it back on to date 0.

In his article he has a few other DATEADD/DATEDIFF tricks. It’s well worth a read.

King Me

It appears that I had a bit of a hiatus from blogging.  This was primarily due to a whirlwind of a successful job search, followed by a brief transition.   The search itself was quite enlightening as the last time I formally interviewed for a job was 1997 and the last time I actually got a job from the resume/interview process was 1987.

Anyway, during one of my interviews I was asked what I would do as a manager if three of my developers came up with equally good but different solutions to a problem but couldn’t agree on which one to implement.  My answer was something to the affect that, although I prefer to be a consensus builder, management by committee is no management at all, the buck would stop with me and I would have to pick one.

My interviewer had another interesting idea.  If remember it correctly, he called it his “King Theory”.  What he would do is pick one of the developers and make him or her “The King”.  It would be his or her job to pick one of the solutions and be responsible for it’s success.  They would be able to pick any or any combination of the solutions but the buck would stop with them.

Now the interesting question is what would be the best solution for the developer to choose and what would they?  The natural instinct would probably be to pick their own but might it not be better to pick someone else’s or a combination to invest the other team members in the success of the implementation?

I thought it was a pretty interesting solution to the problem.  Has anyone else run across this?  What do you think of it?

SQL Server Recursive Common Table Expressions…And Other Ways to Generate a Stack Overflow in SQL Server

OK, so won’t actually generate a stack overflow error in SQL Server, but it makes for a good title. Some time ago I was asked to write a custom deferred revenue integration into GP. There were some quirks to their recognition schedule that prevented them from using the standard deferred revenue module.

For those of you not familiar with deferred revenue, here’s a quick primer. If you pay $24 for a 12 month magazine subscription, the publisher is not allowed to recognize and report to their stockholders the entire $24 when they receive it. They can only recognize revenue as they’ve delivered the service, in this case each magazine, to you. As such, they will recognize and report $2 in revenue each month.

OK, so how do you write a SQL statement that takes a set of rows and creates multiple rows from each individual row.  Let’s say that we have a sales table, tblSales, that has an amount to be recognized, a starting month and a number of months to be recognized over as follows:

OrderNumber	Amount	StartingMonth	RecognitionMonths
1	$120.00	3/2010	3
2	$257.00	2/2010	7

To make this easy, we’ll recognize the revenue equally over the number of months. Often revenue needs to be recognized according to the number of days in the month and take into account starting and ending dates that are mid-month. In this case we’ll use a simple model and simply need to return a dataset of 10 rows as follows:

OrderNumber	AmountRecognized	MonthRecognized
1	$40.00	3/2010
1	$40.00	4/2010
1	$40.00	5/2010
2	$36.71	2/2010
2	$36.71	3/2010
2	$36.71	4/2010
2	$36.71	5/2010
2	$36.71	6/2010
2	$36.71	7/2010
2	$36.74	8/2010

So how, in a SQL statement, can we get from 2 rows to 10? As with any problem, I’m sure we could come up with any number of solutions using WHILE loops and table variables, but I’m going to use the opportunity to introduce recursion in SQL.

Recursion is a feature of the T-SQL Common Table Expression. It is essentially a common table expression (CTE) that references itself. Let’s look at the following statement:

WITH DeferredRevenueEntries(
OrderNumber
,AmountRecognized
,MonthRecognized
,RecognitionMonths
,CurrentMonthIndex) AS
(
-- Anchor member
-- The anchor member creates the entry for the first month.
SELECT
OrderNumber
,AmountRecognized = Amount / RecognitionMonths
,MonthRecognized = StartingMonth
,RecognitionMonths
,1 AS CurrentMonthIndex
FROM tblSales
UNION ALL
-- Recursive member
-- Recurse one time for each additional month to be recognized
SELECT
OrderNumber
,AmountRecognized
,MonthRecognized = DATEADD(month, 1, MonthRecognized)
,RecognitionMonths
,CurrentMonthIndex = CurrentMonthIndex + 1
FROM DeferredRevenueEntries
WHERE CurrentMonthIndex < RecognitionMonths
)

SELECT
OrderNumber
,AmountRecognized
,MonthRecognized
FROM DeferredRevenueEntries
ORDER BY
OrderNumber
,MonthRecognized

Here we’re defining a CTE, DeferredRevenueEntries. This is a UNION query. the first part of the union is called the anchor member. This is a SELECT statement against our source table, tblSales. This part of will return the first month rows for each of the sales records, with the amount to be recognized being the total amount divided by the number of months.

The second part is the recursive member. Note that the FROM clause is referencing the CTE itself. This will take the initial two records from the first part of the union, and keep returning them, adding one month each time. To make sure that we only return the number of records matching the number of months to be recognized, we add the field CurrentMonthIndex, increment it each time we recurse and make sure we only return records where the CurrentMonthIndex is less than the number of months to be recognized.

OK, so what happens if we forget to put in the WHERE clause in the recursive member? Will we in fact cause the aforementioned stack overflow? The answer is no. SQL is too smart for that. Instead you will recieve the following error:

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

By default, SQL Server limits you to a maximum recursion level of 100. It is possible to override that limit by using the MAXRECURSION option in the final SELECT statement such that it looks as follows:

SELECT
OrderNumber
,AmountRecognized
,MonthRecognized
FROM DeferredRevenueEntries
ORDER BY
OrderNumber
,MonthRecognized
OPTION (MAXRECURSION 1000)

Now you might say, well what if I put in a MAXRECURSION of 10000000000? Is there some way I can create a stack overflow? Again, SQL will outwit you. The maximum MAXRECURSION limit is 32767.

OK, so there’s one final issue. You’ll note in my results example above that the recognition amount for order 2 was $36.71 except for the last entry where it’s $36.74. This is because never met an accountant who could deal with as much as a single penny off no matter how high the total. To fix that I create the following modified version of the statement:

WITH DeferredRevenueEntries(
OrderNumber
,AmountRecognized
,MonthRecognized
,RecognitionMonths
,CurrentMonthIndex) AS
(
-- Anchor member
-- The anchor member creates the entry for the first month.
SELECT
OrderNumber
,AmountRecognized = ROUND((Amount / RecognitionMonths), 2)
,MonthRecognized = StartingMonth
,RecognitionMonths
,1 AS CurrentMonthIndex
FROM tblSales
UNION ALL
-- Recursive member
-- Recurse one time for each additional month to be recognized
SELECT
OrderNumber
,AmountRecognized
,MonthRecognized = DATEADD(month, 1, MonthRecognized)
,RecognitionMonths
,CurrentMonthIndex = CurrentMonthIndex + 1
FROM DeferredRevenueEntries
WHERE CurrentMonthIndex < (RecognitionMonths - 1)
)

SELECT
OrderNumber
,AmountRecognized
,MonthRecognized
FROM DeferredRevenueEntries

UNION ALL

-- Create last entry manually to adjust for rounding errors.

SELECT
tblSales.OrderNumber
,AmountRecognized = ROUND(tblSales.Amount - DeferredRevenueEntryTotals.TotalAmountRecognized, 2)
,MonthRecognized = DATEADD(month, 1, DeferredRevenueEntryTotals.LastMonthRecognized)
FROM tblSales
INNER JOIN (
SELECT
OrderNumber
,TotalAmountRecognized = SUM(AmountRecognized)
,LastMonthRecognized = MAX(MonthRecognized)
FROM DeferredRevenueEntries
GROUP BY OrderNumber) AS DeferredRevenueEntryTotals
ON tblSales.OrderNumber = DeferredRevenueEntryTotals.OrderNumber

ORDER BY
OrderNumber
,MonthRecognized

You’ll notice a few changes here. The first is that I round the AmountRecognized value to 2 digits. The second is that the CTE is now only going to recurse through the number of months – 1. This is because we’re create the final entry in the last select in the UNION query by taking the total sales amount from tblSales and then subtracting the total recognized prior to the final month, giving us an amount that is adjusted for any rounding issues.

I hope that this has given you a brief look at the power of the recursive CTE and that you are reassured that you will not break SQL Server by using it. There are certainly many other ways to use the, such as traversing hierarchical lists. Go ahead, recurse away!

Wither the SQL Developer? Is T-SQL the Next Assembly Language?

So today I’m going to veer completely away from GP and talk about SQL in general. The reality is that I’m a data and database geek first and a Dynamics GP geek second; and my (current) database language of choice is T-SQL.

But what is the future of that technology?  Is T-SQL a data dinosaur?  Years ago I actually programmed in assembly language, the language of the CPU itself (ok so technically that would be machine code but let’s not split hairs).  Today, processing power and memory have gotten to the point that you can run a language like PHP or C# so many layers above the CPU as to make it invisible.  You have an JIT compiled language running on a CLR on top of an operating system on a virtual processor on a hypervisor on a physical processor.  Only the most determined driver developer would even consider assembly.

So what of T-SQL?  With Visual Studio 2010, Microsoft will be releasing their latest iteration of the ADO.NET Entity Framework, the latest in a long line of tools to abstract away the data layer and hide the dirty underwear of SQL from the developer.  The Entity Framework essentially allows you to design, build and implement your database without ever having to get your hands dirty in SQL Management Studio.

Tools like the Entity Framework, LINQ and even the old standby, ADO.NET datasets allow you to do some pretty cool things like being able to join data and enforce referential integrity across disparate data sources and in-memory data structures.  They make it easier to make your applications database agnostic.

To be fair, there has always been significant proportion of the developer community who have lived by the philosophy, for some very valid reasons, that the database should be simply a dumb data store.  Even GP was built with that philosophy when it supported multiple back end technologies.

I have tended towards embedding as much logic as possible in the database itself; to use the power of functions, views, referential integrity and constraints; to make make stored procedures the middle tier.  I have also not found anything quite like T-SQL for querying and analyzing data.  The ability to twist and munge and aggregate your data in a single statement of sub-queries (or better yet, Common Table Expressions)

So one day soon will T-SQL be standing among the assemblers and punch card readers in the dusty closet of computer history? Or will the it continue to have a place in our data toolbox?

Common Table Expressions – Dispensing with the Sub-Query

The Common Table Expression (CTE) is a construct that was introduced in SQL Server 2005. It allows you to define a SELECT statement outside of your main query and then reference it within the query. It’s a great replacement for sub-queries, which can be difficult to debug and maintain, especially once their nested. You can think of CTEs as SQL views that exist only within the scope of a particular SQL statement.

Let’s start with an query example similar to one we were asked to do recently. Let’s imagine you’re asked to write an inventory analysis report that requires the following columns:

• Item ID
• Item Description
• Item Class
• Standard Cost
• Quantity On Hand
• Count Sold – The total number of items sold.
• Average Unit Price – The average unit price that the
• Unique Customers Count – The count if individual customers who have purchased the item
• Percentage of Customers – The percentage of all customers who have purchased the item

The report will have start and end date range parameters (Quantity On Hand will be the current regardless of the date range.

Item ID, Item Description, Item Class and Standard Cost come from the item master table. Quantity On Hand comes from the item quantity master table. Count Sold, Average Unit Price, Count of Unique Customers and Percentage of Customers come from a combination of the Sales Order and Customer Master tables.

Now I suspect there may be a way to do this without sub-queries using aggregates and CASE statements but that’s a challenge for another day. When solving a problem I like to break it into manageable chunks. In this case I’d looking at breaking this into two pieces:

• Returning the core item information, Item ID, Item Description, Standard Cost and Quantity On Hand.
• Returning the sales information, Count Sold and Average Unit Price, Unique Customer Count and Percentage of Customers.

Let’s start with the core item info. That query would look as follows:

SELECT
        ItemNumber = ISNULL(ItemMaster.ITEMNMBR, QuantityMaster.ITEMNMBR)
        ,ItemDescription = ItemMaster.ITEMDESC
        ,ItemClass = ItemMaster.ITMCLSCD
        ,StandardCost = ItemMaster.STNDCOST
        ,QuantityOnHand = ISNULL(QuantityMaster.QTYONHND, 0)
    FROM IV00101 AS ItemMaster
        FULL JOIN IV00102 AS QuantityMaster
            ON ItemMaster.ITEMNMBR = QuantityMaster.ITEMNMBR
    WHERE ISNULL(QuantityMaster.RCRDTYPE, 1) = 1

Here we’re joining the item master (IV00101) with the item quantity master (IV00102). A couple of notes:

• We’re using a FULL JOIN to ensure we pick up all items. I have found examples where there are records in the item master or quantity master but not a corresponding record in the other.
• We’re restricting the quantity master records to a record type (RCRDTYPE) of 1, which only returns quantities that apply to all sites.

Next we’ll look at the statistics. The query would look as follows:

SELECT
        ItemNumber = SOPLineItems.ITEMNMBR
        ,CountSold = SUM(SOPLineItems.QUANTITY)
        ,AverageUnitPrice =
            CASE
                WHEN SUM(SOPLineItems.QUANTITY) = 0 THEN 0.00
                ELSE SUM(SOPLineItems.QUANTITY * SOPLineItems.UNITPRCE) / SUM(SOPLineItems.QUANTITY)
            END
        ,CustomerCount = COUNT(DISTINCT CUSTNMBR)
        ,CustomerPercentage = CAST(COUNT(DISTINCT CUSTNMBR) AS float) /
            CAST ((SELECT COUNT(*) FROM RM00101) AS float)
    FROM SOP30300 AS SOPLineItems
        INNER JOIN SOP30200 AS SOPHeaders
            ON SOPLineItems.SOPTYPE = SOPHeaders.SOPTYPE
                AND SOPLineItems.SOPNUMBE = SOPHeaders.SOPNUMBE
    WHERE SOPHeaders.SOPTYPE = 3
        AND SOPHeaders.GLPOSTDT BETWEEN @StartDate AND @EndDate
    GROUP BY SOPLineItems.ITEMNMBR

Here we’re pulling the data from SOP line items and SOP headers with a total count from the customer master. A couple of notes:

• We’re using a CASE statement in the Average Unit Price to ensure we don’t get a divide by zero error.
• We’re filtering the rows on the SOP header GL posting date (GLPOSTDT).
• We’re getting the item customer count using the COUNT(DISTINCT ) construct.
• While getting the customer percentage we’re explicitly casting the counts as floats so that it doesn’t perform integer division and only return 0 and 1.

So traditionally, we would combine these together as two sub-queries as below:

SELECT
        ItemNumber = Items.ItemNumber
        ,ItemClass = Items.ItemClass
        ,ItemDescription = Items.ItemDescription
        ,StandardCost = Items.StandardCost
        ,QuantityOnHand = Items.QuantityOnHand
        ,CountSold = ISNULL(ItemStatistics.CountSold, 0)
        ,AverageUnitPrice = ISNULL(ItemStatistics.AverageUnitPrice, 0)
        ,CustomerCount = ISNULL(ItemStatistics.CustomerCount, 0)
        ,CustomerPercentage = ISNULL(ItemStatistics.CustomerPercentage, 0)
    FROM (
            SELECT
                    ItemNumber = ISNULL(ItemMaster.ITEMNMBR, QuantityMaster.ITEMNMBR)
                    ,ItemDescription = ItemMaster.ITEMDESC
                    ,ItemClass = ItemMaster.ITMCLSCD
                    ,StandardCost = ItemMaster.STNDCOST
                    ,QuantityOnHand = ISNULL(QuantityMaster.QTYONHND, 0)
                FROM IV00101 AS ItemMaster
                    FULL JOIN IV00102 AS QuantityMaster
                        ON ItemMaster.ITEMNMBR = QuantityMaster.ITEMNMBR
                WHERE ISNULL(QuantityMaster.RCRDTYPE, 1) = 1) AS Items
        LEFT JOIN (
                SELECT
                        ItemNumber = SOPLineItems.ITEMNMBR
                        ,CountSold = SUM(SOPLineItems.QUANTITY)
                        ,AverageUnitPrice =
                            CASE
                                WHEN SUM(SOPLineItems.QUANTITY) = 0 THEN 0.00
                                ELSE SUM(SOPLineItems.QUANTITY * SOPLineItems.UNITPRCE) / SUM(SOPLineItems.QUANTITY)
                            END
                        ,CustomerCount = COUNT(DISTINCT CUSTNMBR)
                        ,CustomerPercentage = CAST(COUNT(DISTINCT CUSTNMBR) AS float) /
                            CAST ((SELECT COUNT(*) FROM RM00101) AS float)
                    FROM SOP30300 AS SOPLineItems
                        INNER JOIN SOP30200 AS SOPHeaders
                            ON SOPLineItems.SOPTYPE = SOPHeaders.SOPTYPE
                                AND SOPLineItems.SOPNUMBE = SOPHeaders.SOPNUMBE
                    WHERE SOPHeaders.SOPTYPE = 3
                        AND SOPHeaders.GLPOSTDT BETWEEN @StartDate AND @EndDate
                    GROUP BY SOPLineItems.ITEMNMBR) AS ItemStatistics
                        ON Items.ItemNumber = ItemStatistics.ItemNumber

Although this works, it’s starting to get a little difficult to read. If there were additional sub-queries, particularly nested ones, it would be much harder to follow and debug. If we’re using CTEs we would create the query below:

-- Items common table expression returns item list with quantities
-- for all sites.
WITH Items
AS (
    SELECT
            ItemNumber = ISNULL(ItemMaster.ITEMNMBR, QuantityMaster.ITEMNMBR)
            ,ItemDescription = ItemMaster.ITEMDESC
            ,ItemClass = ItemMaster.ITMCLSCD
            ,StandardCost = ItemMaster.STNDCOST
            ,QuantityOnHand = ISNULL(QuantityMaster.QTYONHND, 0)
        FROM IV00101 AS ItemMaster
            FULL JOIN IV00102 AS QuantityMaster
                ON ItemMaster.ITEMNMBR = QuantityMaster.ITEMNMBR
        WHERE ISNULL(QuantityMaster.RCRDTYPE, 1) = 1
),

-- Item statistics common table expression returns the relevant
-- statistics for each item.
ItemStatistics
AS (
    SELECT
            ItemNumber = SOPLineItems.ITEMNMBR
            ,CountSold = SUM(SOPLineItems.QUANTITY)
            ,AverageUnitPrice =
                CASE
                    WHEN SUM(SOPLineItems.QUANTITY) = 0 THEN 0.00
                    ELSE SUM(SOPLineItems.QUANTITY * SOPLineItems.UNITPRCE) / SUM(SOPLineItems.QUANTITY)
                END
            ,CustomerCount = COUNT(DISTINCT CUSTNMBR)
            ,CustomerPercentage = CAST(COUNT(DISTINCT CUSTNMBR) AS float) /
                CAST ((SELECT COUNT(*) FROM RM00101) AS float)
        FROM SOP30300 AS SOPLineItems
            INNER JOIN SOP30200 AS SOPHeaders
                ON SOPLineItems.SOPTYPE = SOPHeaders.SOPTYPE
                    AND SOPLineItems.SOPNUMBE = SOPHeaders.SOPNUMBE
        WHERE SOPHeaders.SOPTYPE = 3
            AND SOPHeaders.GLPOSTDT BETWEEN @StartDate AND @EndDate
        GROUP BY SOPLineItems.ITEMNMBR
)

-- Final query joins the two CTEs together.
SELECT
        ItemNumber = Items.ItemNumber
        ,ItemClass = Items.ItemClass
        ,ItemDescription = Items.ItemDescription
        ,StandardCost = Items.StandardCost
        ,QuantityOnHand = Items.QuantityOnHand
        ,CountSold = ISNULL(ItemStatistics.CountSold, 0)
        ,AverageUnitPrice = ISNULL(ItemStatistics.AverageUnitPrice, 0)
        ,CustomerCount = ISNULL(ItemStatistics.CustomerCount, 0)
        ,CustomerPercentage = ISNULL(ItemStatistics.CustomerPercentage, 0)
    FROM Items
        LEFT JOIN ItemStatistics
            ON Items.ItemNumber = ItemStatistics.ItemNumber

In the query above we have two common table expressions, Items and ItemStatistics. We declare the first CTE using the WITH clause. Subsequent CTEs are separated with a comma. Note that all CTEs must be declared at the beginning of the query. The final query simply joins the two CTEs together

The biggest advantage to CTEs is their readability, manageability and the ease of troubleshooting. It’s much easier to break the query down and troubleshoot its individual components than to do the same with nested sub-queries. Other things to note about CTEs:

• A CTE can reference other CTEs defined above it.
• A query can join to a CTE multiple times. i.e., wherever you might use the same sub-query multiple times you can now define it once in a CTE and simply reference the CTE multiple times.
• You can reference parameters and variables in a CTE and thus optimize your queries by filtering out records prior to joining them in your main query.
• If you have any statements above a CTE, such as DECLARE or SET statements in a stored procedure, you must terminate those prior statements using a semicolon.

So let us bid farewell to the much vaunted and occasionally overused sub-query and hello to our new friend, the Common Table Expression.

Next up, the Recursive Common Table Expression.

Excellent Consulting Tips Article

I’m veering off the technical path here, but I came across this great article on the things NOT to do as a consultant.

10 things you should never do on a consulting job (http://blogs.techrepublic.com.com/10things/?p=1290&tag=nl.e550)

SSRS Expressions Tips & Tricks

Wow!  Time flies when you’re having fun.  Anyway, between taking some time off for the holidays, preparing to take time off for the holidays and digging out of the email from taking time off for the holidays, I guess I’ve neglected the blog a bit.

This will just be a short post.  A colleague of mine, Liz Pfahler, passed this link along to me.  It’s a good comprehensive list of tips and tricks for SSRS expressions.  Everything from how to set alternating row colors, to returning the first or last day of the month. 

There’s a few things I’ve used in the past but there’s definitely some new ones and it’s great to have them all in one place.

Check it out.

http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/67660/