Voice of the DBA

Author: way0utwest

  • Renewed

    I received my fourth (or fifth?) renewal notice as a Microsoft MVP this morning. I am proud to be recognized as contributing to the SQL Server community. Thanks to everyone that reads my work or comes to see me speak and I’m glad if I can help a few of you in your SQL Server work and career.

  • The Data Resolutions

    Happy New Year

    Making a new year’s resolution is an annual tradition for many people on January 1. Breaking those resolutions seems to be another annual tradition that often takes place during the month of January as we struggle to live up to the changes that many of us often want to make in our lives.

    It’s the last Friday of the year, and rather than ask you about the resolutions you are making overall, I wanted to ask this question:

    What would you like to do differently in your career in 2012?

    It’s easy, and I hope it makes you think a little about where you want to go ni the future. Are you looking to move to a new job, a new kind of work with SQL Server? Do you want to learn more about some aspect of SQL Server? Do you want to get out of technology?

    As you think about what you might do differently in 2012, it’s not always about growth. Perhaps 2012 is a year where you slow down a bit and maintain some skills, or even reduce your hours as you spend time on something outside of work.

    For me 2012 will be a slower year. After attending over a dozen events in 2011, I am planning on many fewer in 2012. My wife’s career will take a little more precedence over mine, and I’ll concentrate on writing more, and bringing you more news, knowledge, and training as SQL Server 2012 is released.

    Happy New Year, be safe and responsible over the weekend and I will see you in 2012.

    Steve Jones

    The Voice of the DBA Podcasts

  • How Many Calls? Part 2

    I wrote recently about a report that a friend of mine needed from his Access database. In the first post, I used a CTE and an outer join to cull together some data, however since I wasn’t sure Access would support any of that, I came up with another way.

    If you read the last post, you’ll have my setup code and sample data. In this one I decided to move to sub-queries as the first part of my work. I started with this:

    SELECT a.date_sent 'date_sent' , ISNULL( a.five_day_call, 0) 'five_day_calls' , ISNULL( b.ten_day_call, 0) 'ten_day_calls' FROM ( SELECT date_sent
       , COUNT(*) 'five_day_call' FROM calls
       WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 6
       GROUP BY date_sent
) a
inner JOIN ( SELECT date_sent
  , COUNT(*) 'ten_day_call' FROM calls
   WHERE DATEDIFF(DAY, date_sent, acc_call_date) < 11
   GROUP BY date_sent
 ) b
   ON a.date_sent = b.date_sent
 go

    I know this doesn’t work because both queries don’t have all the dates. However there was a better way, and one I should have thought of immediately. A few people noticed this in the comments to the previous post. We are looking to count records, according to some criteria. The SUM function will do this. If I run this, I get a count of calls in my 5 day window.

    SELECT a.date_sent 'date_sent' , SUM( CASE WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 6 THEN 1 ELSE 0)

    FROM calls GROUP BY date_sent

    That worked, and it’s easy to understand. What SQL Server does is look through each row, and if there is a row that matches the DATEDIFF function, meaning the call back date is less than 6 days from the call date, then it returns a 1 for that row. Otherwise a 0 is returned. If I then sum the results for each day, I essentially get a count for the date since each call counts as one (or zero).

    To get the final query, I can then do this, adding a column for each type of row I am looking for.

    SELECT a.date_sent
 , SUM( CASE WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 6 THEN 1 ELSE 0) 'five_day_calls' 
 , SUM( CASE WHEN DATEDIFF(DAY, date_sent, acc_call_date) < 11 THEN 1 ELSE 0) 'ten_day_calls'
    FROM dbo.Calls

    GROUP BY date_sent

    That produced the correct results, and it was easy to convert to Access.

    Again, I’m not sure this is the most efficient way to do this, but it worked and it was an interesting problem. If you have a better way to do it, let me know.

  • The Year in Review – 2011

    Big Data and The Cloud were a large part of 2011

    It’s the end of the year, and I’m taking a look back at 2011 from the data perspective. As we close out the year, it’s apparent that “big data” has been catching on with many companies. It seems that every week we have some headline that talks about “big data” in some way. Even Microsoft has joined in with  their work with Hadoop and SQL Server.

    The other big push in 2011 seems to have been “the cloud”. It’s everywhere, from consumer Windows 7 commercials to the updates to the SQL Server tools to support the SQL Azure platform. I haven’t found many people that are excited about moving their databases to the cloud, but more and more people are testing the platform, and I suspect we’ll start to see some apps that don’t require high security or extremely high performance appearing in the cloud in 2012. I have embraced the cloud in many ways over the last couple years (Evernote, Dropbox, Live Mesh) and I find it to be an advantage in managing a busy life.

    We had our share of high profile data breaches as well, with some very high profile ones, including RSA, the security vendor. We could add in the Carrier IQ fiasco, which showed a little abuse of consumer trust with the debugging code they developed.

    A few other notable items in the data world for me. The first was the death of Steve Jobs. While most of us working with SQL Server don’t use Apple products, the changes that Steve Jobs brought to the world, most recently the iPhone and iPad, have altered the way we consume data in many facets of our lives. The second item, was the split of Netflix into two businesses, and the failed idea that splitting our online and physical DVD data into separate sites wouldn’t be an issue. The reversal of that decision showed me just how valuable centralization is for many consumers.

    There were many stories that touched data in our lives in 2011, and it seemed to be a busier year than ever before for me. However one large milestone for me was the 100th SQL Saturday event in Brazil. It’s hard to believe there have been 100 events, with 45 events in 2011. Seeing the SQL Server community come together to embrace, help, and inspire each other, both online and off, has been amazing. If you haven’t tried to interact with others through LinkedIn, Twitter, Google Plus, or some other social media, I urge you to try in 2012.

    I hope that you have had a great 2011, and I look forward to an even better 2012.

    Steve Jones

    The Voice of the DBA Podcasts