Woot Makes Me Think

I got an email from Woot today. At the bottom they had this silly graphic:



I've been minimalizing a lot of stuff in my life lately: Possessions, bills, work, Facebook friends (yeah, I got rid of people I didn't really know and it simplified my Facebook experience.) I'm enjoying freedom from life's unnecessary burdens, and it's freeing up my time so I can focus on the things that really matter to me.

I never thought about freedom from knowledge before, but it really makes sense. Freedom from knowing what other people expect me to know. I don't need to know the meaning of every inside joke. What happens if I never watch the most popular TV shows and I don't get references? Will my life be less meaningful? Will I be less happy? What happens if I stop watching the news? Will my vote count less? Will I be less of an American?

I really don't have the answers to these questions, but the woot monkeys made me think about it. I'll let you know if I land on anything brilliant.

Planning Into Minimalism

I'm on day 2 of my journey into Minimalism. I'm planning. I didn't buy a calendar like they recommend, but instead used Microsoft Outlook. I'm thinking Outlook might be the problem. That software is expensive. I'm planning my digital minimalist life. I have a lot of monthly, quarterly, and yearly fees for digital services, when free ones are just as good or nearly just as good. I think I'll spend my free time during Christmas transitioning to the free services, like using GMail instead of Outlook/Exchange.

I pay for source control, when I could just host it myself for free.

I pay for web hosting from several different companies, but I should consolidate and make my expenses cheaper.

I canceled GOTOMeeting (which I use primarily to remote control computers or show someone my screen), because I can just use mikogo for free.

I'm going to comb over my cell phone bill and cancel unneeded phones and services. My phone bill is typically $330 a month. YES, YOU READ THAT RIGHT! I know, I know...it's embarrassing. But I have three sons. Little buggers are expensive.

My Must List for the Next 90 Days

I read Day 1 of the journey from The Minimalist Blog. They mention that I need to turn shoulds into musts and I totally agree.

This is who I am for the next 90 days.

I must do P90X first thing in the morning.

I must write 1000 words a day on my book.

I must complete projects for work.

I must listen to podcasts to and from work.

I must be available for my sons for surfing, golf, and bonding time.

I must eat healthy for every meal.

I must not watch TV, HULU, etc.

I must not go on the Internet unless it's to research or write.

I must not hold on to my fucking phone like I'm Gollum and it's the goddamn One Ring.



I must watch a technology video every day.

I must make time for making good friends.

New BunkSpeed Site is Amazing!

Brad Cunningham just showed me the new website for his company, Bunkspeed. This site was created with Javascript and CSS. I'm flabbergasted how seamless the scroll in and scroll out works without losing any graphical detail. Brad and his team did some breathtaking work here and they should be proud.

www.bunkspeed.com

How To Reconcile Belief in God with Morals

Slides for SQL in the City Presentation on SQL Azure

Here's the promised slide deck:

Developing on SQL Azure

View more presentations from Ike Ellis

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
            

Father of the Year Candidate at Desert Code Camp 2011

As I was walking around the campus at Desert Code Camp last month, a man stopped me. This guy had tattoos riding up his arm, a flat-brim hat, baggy jeans, and looked like he just walked off the construction site...not your typical code camp attendee. Trailing him was a skinny 12-year-old boy wearing a pacman t-shirt.

"Excuse me, I have no idea what my son is saying. Can you please translate?"

His son wanted to go to a specific class. I found one close enough on the schedule and pointed them on their way. Later, as I was running around after my own sons, I saw the man walk out of the class his son was in. He found a chair by the door, sat down, and waited. As I walked around code camp that day, I saw the same sight over and over again. This guy was either walking his son to the next class, or waiting outside the door.

I wonder what he was thinking about with all that free time that day. Perhaps, "why doesn't my boy like to throw the football?" Or, "why can't we make furniture together?" I wonder how many super smart kids are born to parents that are not as generous and thoughtful as that man. I wonder how many brilliant minds the world has lost out on because of bad luck or circumstance.

On the flip side, maybe one of my sons was meant to be an NFL Quarterback, but we'll never know. They're stuck in code camp classes.

Outlook 2011 for Mac (OsX) Won't Open New Email Window and Reply All Doesn't Work

Today, none of my email buttons would work in Outlook. After some digging, I found this solution:

http://jimmyblake.com/2010/11/09/microsoft-outlook-2011-not-opening-e-mail-compose-window/

I reinstalled Safari and it finally fixed it.

Database Design Fundamentals - Louis Davidson's SQL Pass 2010 Talk

I've been watching old SQL Pass talks from last year's conference. Today I watched Louis Davidson's talk, found here:

http://sqlpass.eventpoint.com/topic/details/AD270S


He is an interesting speaker, who talks funny because he's from Tennessee. My favorite is the pronounciation of varchar(max) as vare care (max). That rhymes, by-the-way.

His presentation covers database normalization from first normal form through Boyce/Codd normal form. He also talks about de-normalization and offers tips and techniques for good design. Forty-nine minutes into his presentation, he has a brilliant slide:



He says that if you find a lot of parsing and function calls in queries, particularly on the same column over and over again, you have strong evidence of a design that needs to be revisited. I totally agree. As soon as he said it, it hit me like a ton of bricks. If you see parsing functions over and over again, you have opened yourself up to bad data and inconsistant results. You are overly-relying on report authors and developers to get the parsing formulas right every time they touch that data. That won't happen. The database needs to be refactored so that data is easy to retrieve and store.

I've thought about this specifically, but I've never considered it as a good general rule.

Here are links for Louis Davidson:

His Website
His Blog
His Latest Book on Database Design for SQL Server 2008
His Twitter: @SqlDr

The SQL MCM Might Not Be For Me

I've been thinking a lot about the Microsoft Certified Master Certification recently, trying to decide if it's worth it.

On one hand, I really like being identified as a smart guy, and perhaps MCM will help that effort. I can picture it now - I walk into SQL Pass, standing proud and tall in my scottish kilt, legs hairy, smiling brightly...and I hear hushed whispers of "There's Ike...the Microsoft Certified Master." Reverence on their faces as I stride into the room...right up to the point where I trip, legs sprawling and they realize I'm wearing that kilt as a true scotsman.

Also, I seem to learn better when I have a goal. It gives the learning purpose, context, and urgency, which I (and many people) require in order to learn.

On the other hand, my clients do not care about this certification. I doubt I would ever even mention it. My clients are not SQL DBAs. They are corporations and institutions that don't have great SQL knowledge in-house. Some of them are large software companies that just haven't found the right SQL resource. They've never heard of MCM and therefore, wouldn't add to my bill rate by a single dollar. MCM = time-consuming = expensive and <> cost-justified. Basically, I'd be going against the advice I constantly give my customers. If it doesn't make you more money, it's not worth it.

My clients employ me for one basic reason. I'm always concerned about their profitability, efficiency, and productiveness. Getting better at making them money will make me more money. I'm unconvinced that MCM helps me achieve that.

Is ego enough of a reason to pursue this?

EDIT - 3/31/2011: Joe Sack, acting PM for MCM:SQL, has been talking to me about this post for quite some time. He gave me the details of a study done to prove the value of MCM...the non-ego-driven value. Here is a link to the study:

http://download.microsoft.com/download/7/9/5/795B3672-1B65-49DE-9180-7B7BEB0E1F52/MCM_Research_and_Evidence.pdf

Looks like my SQL MCM journey is going to begin today.

Webcast for DevelopMentor - Top 5 Things for Developers to Know about SQL Azure

Thanks for coming, today. Here's the link for the slide deck:

Top 5 things to know about sql azure for developers

View more presentations from Ike Ellis.

One thing I wanted to cover, and didn't is that you can analyze query plans while using SQL Azure. Here's a photo to jog your memory:



Also, here are the links to the audio portions of the webcast:

Streaming Video (WMV):
http://developmentor.s3.amazonaws.com/webcasts/Top-5-Things-to-Know-About-SQL-Azure-for-Developers-by-Ike-Ellis-Feb-15-2011.wvx

Video Download for WMV:
http://developmentor.s3.amazonaws.com/webcasts/Top-5-Things-to-Know-About-SQL-Azure-for-Developers-by-Ike-Ellis-Feb-15-2011.zip

SSRS 2008 Execution Log - Report Auditing

I wrote this query to tell me how many times a report gets excecuted, who ran it last, and when it was ran last. It's a simple, but effective query:


USE reportserver;

WITH LastExecution
AS
(
   SELECT e.ReportID
       ,  MAX(e.timestart) AS LastExecution
       FROM ExecutionLog  e
       GROUP BY e.ReportID
)
,
ExecutionCount
AS
(
   SELECT e.ReportID
       ,  COUNT(*) AS ExecutionCount
       FROM ExecutionLog  e
       GROUP BY e.ReportID
)
SELECT c.Path
   , c.Name
   , le.LastExecution
   , ec.ExecutionCount
   , e.UserName AS LastExecutingUser
   FROM ExecutionLog e
   JOIN Catalog c
       ON e.ReportID = c.ItemID
   JOIN LastExecution le
       ON e.ReportID = le.ReportID AND e.TimeStart = le.LastExecution
   JOIN ExecutionCount ec
       ON e.ReportID = ec.ReportID;




T-SQL Script: Finding the First Monday of the Month

I recently had a reporting requirement to find the first Monday of the month. I used the dateadd/datediff method introduced to me by Itzik Ben-Gan. This is the single line of code, but it might help to break it down into multiple lines of code.

SELECT DATEADD(wk, DATEDIFF(wk, '20101206', dbo.FormatDateTime( GETDATE(), '[MM]/07/[YYYY]')), '20101206')

1) Working from the inside out, we have this line:

dbo.FormatDateTime(GETDATE(), '[MM]/07/[YYYY]')

This line uses a function that I wrote (or stole off the Internet) called FormatDateTime. Here's a blog post of mine about it: FormatDateTime Script

This line assumes that the first Monday of the month needs to be in the first seven days. It chooses the seventh day of the month of the current month.

2) Next comes this line:

DATEDIFF(wk, '20101206', dbo.FormatDateTime(GETDATE(), '[MM]/07/[YYYY]'))

This line chooses an anchor date that is the first Monday of a chosen month. 12/6/2010 was the first Monday of December 2010. It then takes that seventh day of the current month and finds out how many weeks (wk) are between 12/6/2010 and the seventh of the current month. If today is January 26th, 2011, then the answer will be 5 weeks.

Finally, when you put the whole statement together, it adds five weeks only to 12/6/2010, which will be the first Monday of January 2011 which is 1/3/2011.

If you want to find out the first Monday of next month, or any month, then replace the getdate() call with a dateadd call like this:

DATEADD(m, 1, GETDATE())

which will create this:

SELECT DATEADD(wk, DATEDIFF(wk, '20101206', dbo.FormatDateTime(DATEADD(m, 1, GETDATE()), '[MM]/07/[YYYY]')), '20101206')