My Interview with SSWUG

SSWUG just released an interview I did with them back in August. You can see it here:





Thanks, SSWUG! I totally enjoyed the experience and look forward to doing it again

clemens vasters discusses lack of dtc on the azure platform

clemens vasters responded to complaints on the azure nda list this week. he addresses the lack of a distributed transaction coordinator on the azure platform. i thought his response was interesting, told him that, and he turned it into a public blog post.

he still got skewered by the mvps, but he gets an "a" for effort.

http://blogs.msdn.com/b/clemensv/archive/2011/10/06/achieving-transactional-behavior-with-messaging.aspx

below is my favorite part:

"The grand canonical example for 2PC transactions is a bank account transfer. You debit one account and credit another. These two operations need to succeed or fail together because otherwise you are either creating or destroying money (which is illegal, by the way). So that’s the example that’s very commonly used to illustrate 2PC transactions. The catch is – that’s not how it really works, at all. Getting money from one bank account to another bank account is a fairly complicated affair that touches a ton of other accounts. More importantly, it’s not a synchronous fail-together/success-together scenario. Instead, principles of accounting apply (surprise!). When a transfer is initiated, let’s say in online banking, the transfer is recorded in form of a message for submission into the accounting system and the debit is recorded in the account as a ‘pending’ transaction that affects the displayed balance. From the user’s perspective, the transaction is ’done’, but factually nothing has happened, yet. Eventually, the accounting system will get the message and start performing the transfer, which often causes a cascade of operations, many of them yielding further messages, including booking into clearing accounts and notifying the other bank of the transfer. The principle here is that all progress is forward. If an operation doesn’t work for some technical reason it can be retried once the technical reason is resolved. If operation fails for a business reason, the operation can be aborted – but not by annihilating previous work, but by doing the inverse of previous work. If an account was credited, that credit is annulled with a debit of the same amount. For some types of failed transactions, the ‘inverse’ operation may not be fully symmetric but may result in extra actions like imposing penalty fees. In fact, in accounting, annihilating any work is illegal – ‘delete’ and ‘update’ are a great way to end up in prison."

SQL Quick and Easy Tips - SQL Saturday San Diego

Here is the slide deck for two of my SQL Saturday presentations. Enjoy!

Quick & Easy SQL Tips

View more presentations from Ike Ellis

Create a Numbers Table with Buckets Using a Recursive CTE

I needed a numbers table from –400 to 5000. I needed every number to be in a specific bucket for a report. For instance number 55 would be in the bucket "31 – 60".

This code elegantly creates the data for the number ranges (without listing each of them) and for those buckets.


with myNumberCounts as
(
select CAST(-400 as int) NumValue
union all
select NumValue + 1
from mynumberCounts
where numvalue + 1 <= 5000
)
select NumValue
, case
when NumValue <= 30 then '0 - 30'
when NumValue >30 and NumValue <= 60 then '31 - 60'
when NumValue > 60 and NumValue <= 90 then '61 - 90'
when NumValue > 90 and NumValue <= 120 then '91 - 120'
when NumValue > 120 and NumValue <= 150 then '121 - 150'
when NumValue > 150 and NumValue <= 180 then '151 - 180'
when NumValue > 180 and NumValue <= 365 then '181 - 365'
else '365+'
end AS DayBucket
from myNumberCounts
option (maxRecursion 0)

San Diego Code Camp Slides

Here is the link for the slides for the T-SQL Tips and Tricks presentation at code camp:

http://www.slideshare.net/ellisike/quick-easy-sql-tips

A Bug Using INTERSECT and ORDER BY

I was teaching a TSQL course to Total Jobs Group in London, UK, and we stumbled on the following bug. This script uses the Northwind sample database on SQL Server 2008 R2.

use northwind

select contactName, Phone
from customers
intersect
select lastname + ', ' + firstname
, homephone
from employees
order by contactName

If you execute this statement with INTERSECT and ORDER BY, you get the following error:

Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

If you remove the ORDER BY, it works just fine. This is definitely a bug and I found that it was reported on Connect. I'll test this out to see if they've fixed this in Denali.

Find All The Columns in Indexes with SQL Azure

In SQL Server on-premise, this query is often used to find all the columns that are currently participating in an index:

SELECT OBJECT_NAME(sysindexkeys.id) Table_Name,
            syscolumns.name Column_Name,
            sysindexes.name Index_Name,
    CASE WHEN
                INDEXKEY_PROPERTY(sysindexes.id, sysindexes.indid, sysindexkeys.keyno, 'IsDescending')=0
                THEN 'Ascending'
    ELSE    'Descending'
    END SORT_ORDER
    FROM sysindexkeys
            INNER JOIN syscolumns
            ON sysindexkeys.colid=syscolumns.colid
        AND sysindexkeys.id=syscolumns.id
            INNER JOIN sysindexes
            ON sysindexkeys.indid=sysindexes.indid
        AND sysindexkeys.id=sysindexes.id
            


In SQL Azure, we don't have sysindexes or sysindexkeys. Instead, we use sys.indexes and sys.index_columms. It looks like this:

SELECT OBJECT_NAME(sysindexkeys.OBJECT_ID) Table_Name,
            syscolumns.name Column_Name,
            sysindexes.name Index_Name,
    CASE WHEN is_descending_key = '0' THEN 'Ascending'
    ELSE 'Descending'
    END AS SORT_ORDER,
            type_desc AS indexType
    FROM sys.index_columns sysindexkeys
            INNER JOIN syscolumns
            ON sysindexkeys.index_column_id=syscolumns.colid
        AND sysindexkeys.OBJECT_ID=syscolumns.id
            INNER JOIN sys.indexes sysindexes
            ON sysindexkeys.index_id=sysindexes.index_id
        AND sysindexkeys.OBJECT_ID=sysindexes.OBJECT_ID