Tag goats

iPhones, Robots, and Cookies

I traded my iPhone for a batch of cookies. That’s right: one white iPhone 3GS was traded for a batch of cookies. This has been a long time coming.

My Growing Disappointment

I’ve had an iPhone since Apple first unleashed them on unsuspecting consumers. I was happy with the first generation iPhone. I was even happier with my iPhone 3GS. It was fast, I could download applications, and everything integrated smoothly with the software on my MacBook.

Over the last six months, my disappointment has grown. There was no good way to sync my contacts between the iPhone and Google without some irritating third party add-ons for the Apple Address Book on my Mac. The same problems applied to my calendars – I could either have up to date calendars on Google or on my laptop. It was possible to sync everything using webcal (WebDAV for calendars), but it was not an optimal solution. Heck, I couldn’t even open a calendar invitation sent to my phone even though Apple wrote the original specification.

In order to sync third party applications with the apps on my computer, I had to manually sync each application. That’s not that bad when you only have one application, but once it starts growing you start to forget which apps you have and haven’t synced up – maintaining your external brain shouldn’t be a huge trial. Smart phones are supposed to make our lives easier. Instead I found myself doing more work to keep things up to date than I really liked. It wasn’t a ton of work, but it was still extra work.

Enter John

What does John have to do with this? Well, John Keyes (twitter) and I were talking on Friday. He pulled out his phone and I started asking him questions. John had an HTC Incredible. I’d never used an Android phone before and I wanted to take a look.

After just a few minutes I was really impressed with John’s phone. To be honest, I called around to a number of Verizon stores just to make sure I could get my stinky little hands on an HTC Incredible.

Fancy Robots

First impressions of Android on the Incredible – this is really nice. Some of the earlier Android phones were rough around the edges and I didn’t expect things to be as polished an iPhone. I was right: things weren’t quite as polished as the iPhone but they were really close. The icons were consistent, things were fairly smooth scrolling between the main application screens. Things got a little bit slow scrolling through the list of all programs, but it was nowhere near as bad as I’ve witnessed on other Droid phones.

Multitasking was a really nice change from the iPhone. I was able to stream music through last.fm, check my email, and chat on gtalk at the same time. Sure, I don’t normally use my phone like a computer, but because of the multitasking it was easy to accomplish some pretty cool things – I streamed music through last.fm while using my phone to get directions.

Getting Set Up

In order to get all of my calendar appointments on my phone, I exported them from iCal and then imported them into Google calendar. In a few minutes, everything was down on my phone. Completely and totally. I split everything into separate calendars. I even sent myself a meeting invitation from my work email to my phone and I was able to accept the appointment and it showed up on my calendar.

For three years I haven’t been able to accept a simple appointment on my phone. It was such a welcome relief to be able to see and accept an appointment on my phone and then have it show up in my calendar.

Contacts syncing was a bit hairier because Google doesn’t support contact synchronization from an exteral source (my address book) if there are multiple contacts with the same primary email address. This process has been manual but well worth it. I’ve culled about 300 contacts from my contact list. You know how Google will create a contact for anybody you’ve ever emailed or who has ever emailed you? Yeah, I have about 1,000 contacts like that. It makes you laugh and think you’re popular until you try to dial a phone number have to scroll through 14 pages of ‘\/1agra peni5 c0cks’ before you get to someone named Aaron. Thankfully it’s pretty easy to delete a giant pile of dong from Gmail’s contact manager (one of the few things it gets right), so my contact list was free of unwanted penises in no time.

The Rest of the Internet

One of the coolest things that I’ve found so far is that the phone has synced up my contacts from Facebook as well as the contacts I have in Google. I’ve been able to link them up so that I don’t have three or four entries for a single person in my phone. I can just tap their name and have all of their contact information immediately available.

Browsing has been great. Since Verizon’s 3G network is allegedly the best in the universe, I decided to put Verizon’s tubes to the test. I streamed last.fm in my car between Cinci and Columbus. I would routinely lose any form of signal on AT&T’s network. Instead of losing signal, I was able to listen to some of my favorite music while finding new music. Uncanny. The experience of browsing the internet has been remarkably similar. I suspect that both run similar builds of WebKit optimized for a mobile device. Either way, it works and it works well.

It Works and It Works Well

At the end of the day, that’s what I have to say about this HTC Incredible: It works and it works well.

Is it as polished as the iPhone? I’m not sure. As I get used to the phone, I think that it might be as polished as the iPhone, just different. Things work in a way that makes sense to me. Settings are accessed through each application and not a global settings menu. Applications are consistent.

I am, on the whole, quite pleased.

Speaking is so done

Take three steps to your right to advance to the next slide


Honestly, I’m tired of speaking. PowerPoint is overrated. People want more or less bullet points. It’s never enough. More graphs. More pie charts. Less funny images. More dinosaurs. Stop showing pictures of Zeus sexing up a goat. I don’t know what you people want!

Demos – you can shove those, too. People complain when the code doesn’t fit on the screen or when my resolution is too low or when they have some kind of vitamin deficiency and they can’t look at the color #0f0015 for more than 32 seconds without suffering from a migraine. I’ve spent hours coming up with the perfect demo code only to have someone poke holes in it over and over again. I see this all the time on speaker feedback.

No more. You will no longer have this opportunity. PowerPoints are done. Demos are over. Speaking? That’s right out, too. Greek pornography… The jury’s still out on that one.

So here’s a note to everyone who is expecting me to speak at their event in the future: I won’t. I’ll be there, but I will not speak. I need you to secure velvet ropes for my “room”. I will be available for hushed conversations, but I want my work to speak for itself. I need dim, dramatic, lighting and access to numerous power sources so I can light my “slides” in a variety of ways.

Speaking of slides… They have been carefully painted on using oil or egg tempera paints. A few paintings contain other, non-volatile, material when I was experimenting with a different style of art work, but I cannot promise that everyone will like this.

Presenting is an artform and this is art. You don’t have to like it. You don’t have to understand it.

This is art, goddamnit!

T-SQL Tuesday 4: Io, Io, it’s off to disk we go

Fact: the earliest recorded use of cloud computing was ancient Greek porn

Io was a nymph. True story. Apparently, her father was some kind of river god. In modern times that means you’re likely to catch fire. Back in the days when the Greeks were in charge of things being a river god meant that you were somebody (the Greeks thought the earth was a giant brass plate floating a huge river, all of which was created by perverts who lived on top of a mountain). So, apparently Io’s dad was important.

Anyway, it is rumored that Io was attractive. So attractive, in fact, that Zeus, lord of the perverts, saw her taking a bath and got more than a little bit aroused. Zeus then behaved in a way that would end up in a savage beating and restraining order back where I come from – he pestered Io for nookie until her father drove her out of the house – probably because some horny lunatic who could shoot children out of his forehead was bothering his daughter. Io, being a bit strange in the head, relented. Or something. My records aren’t 100% clear seeing as how they’ve been written on pottery. The point is that Zeus turned into a giant cloud and turned Io into a cow (no, your hooves don’t make you look fat).

Somehow Zeus’s wife got involved and there was bondage involving a cow tied to a tree or something. Eventually Io gets turned back into a real live girl and gives birth to Zeus’s son. Which brought about an ethics probe into cross-species cloning.

Disk… disk… oh yeah I already mentioned that the ancient Greeks were clearly insane and thought that the world was a giant metal plate floating on a huge river name Oceanus all of it encased in a hemisphere with clouds and the sun and the moon and stuff painted all over the inside of the hemisphere.

What’s outside of the hemisphere? Shut up, that’s what. It’s turtles all the way down.

There’s a bit of humor thrown into your T-SQL Tuesday

How the Hell Did I Get Here?

Paul Randal started this chain post. He tagged Steve Jones who, in turn, tagged Jack Corbett who finally tagged me. I’m pretty sure everyone who nominally makes sense has already been tagged at this point thus leaving Jack to scrape the bottom of the barrel.

Like Jack, I think I could approach this in a number of different ways. I think I’ve already answered the professional route that I took. If I haven’t, it’s probably because my professional route isn’t all that interesting and it’s also all available on LinkedIn. You can, and should, fill in the job changes on my resume with something interesting like “After being attacked by a bear in the janitor’s closet at CareWorks Technologies, Jeremiah decided to take a safer job at HMB (they have no bears on staff as janitors).”

Anyway, you asked for it, you got it: how the hell did I get here?

I’m a Rock and Roll Machine

I love being on stage and in front of people, even though it terrifies the crap out of me. Apparently, I like that adrenaline surge. I’ve been playing guitar since I was 13 years old. When I was 23 I answered an advertisement and auditioned for a band. I got the job after 5 minutes.

Being a musician takes a lot of hard work, dedication, and practice. You work for hours and hours as a group, and hours and hours on your own preparing for a show. At that show, you’re going to walk up on stage and try to steal the attention of a room full of people who would, frankly, rather be doing one of a million other things – playing pool, talking to friends, hitting on that girl across the bar – than listening to you. As musicians, it was our job to get their attention, hold it for an hour, and make sure that they were happy about it. That job gets even harder when you’re in a band that only plays original material.

What seemed really fun – being in a band – turned out to be a lot of work – practicing three nights a week for four hours a night with the band and then practicing even more on my own. I learned a lot about myself – my tolerance for bullshit, how to get attention, and how to act in front of a crowd of people – while I was in the band. I also learned a lot about how to budget scarce resources – money and time – while still getting the job done – getting to the show.

The band eventually fell apart, as most do, but I learned many valuable lessons that I carry with me – time and resource management, performing skills, and how to make an ass of yourself and be okay with it. Most importantly, I learned that passion alone isn’t enough. You have to work for something if you really want to be good at it.

Everything to Everyone

The fact is, I’m not everything to everyone. But for a long time, I thought that I could do it.

Before and while I was in the band, I was married. The band took up a lot of time and it took a lot of time away from my marriage. While being in a band didn’t cause my eventual divorce, I’m sure it contributed to it (I quit the band about a year before my ex-wife and I quit the marriage). I remarried pretty quickly and that marriage ended almost as fast as it started.

Throughout all of this, though, there’s a huge undercurrent – I was trying to make everyone happy. I was trying to be a good husband, musician, developer, friend, son, brother, step-father, and about a million other things. I stretched myself thin and I broke.

These days, I know that I can only be me and that I’m the only person I need to make happy. There’s a reason why I work with SQL Server but I program with Ruby, why I listen to old school hardcore punk but I play a bizarre blend of folk and country, why I devote more time to my friends and family than I have before – these things all make me happy. If it doesn’t make me happy and I don’t need to do it to live, I don’t do it.

Self-Fulfilling Catastrophe

A couple of paragraphs ago I said “I stretched myself thin and I broke.” I really do mean that. During the first divorce, I moved into a tiny house on the ass end of Columbus, stopped paying most of my bills, and ended up living on as little as $20 a week. The funny part, though, is that I always found the cash to go out and party, or to stay in and party. This became a bad habit even once the divorce was done and I should have been back on my feet. Over the next 4 years everything spiralled completely out of control.

I’m pretty sure there were more than a few times I nearly lost my job. I frequently called in “sick” from the crowded patio of a bar at 1:30AM, had my car repossessed, racked up so much debt that people were calling my family members to find out where I was, and I partied seven nights a week. I lost a lot of my friends and damaged most of my remaining friendships irrepairably in the process. Throughout this ordeal, a few of my friends stood by me. They didn’t give up on me despite my ardent attempts to turn myself into a drooling train wreck of a human being.

In June of 2008, I gave it all up. I realized that I was a total train wreck and that everything around me was completely out of control. My career was stagnant. I was sliding backwards as a person. I wasn’t meeting any of my goals for myself because I was too busy slowly killing myself.

I stopped drinking. I got the help I needed and I began the long, painful, process of pulling myself up by my shoelaces. I dried out.

By August, I had completed the SQL Server 2005 MCITP: Database Developer certification. I started the Columbus chapter of PASS in October. I started paying back all of my bad debt (only a few months left to go). Nine months after I quit drinking, I quit my one to two pack a day smoking habit (sorry about that one, Mom and Dad) – I never would have thought I could end my 13 year addiction to nicotine.

I have a great relationship with my family and friends now, it’s better than anything I could ever hope for.

I learned a lot of things from this.

  1. I can be horribly selfish.
  2. There’s nothing better than not being that selfish.
  3. I can do damn near anything I want to do if I put my mind to it.
  4. There are some things in life that are so important you can’t afford to overlook them.

Afterward

This summer, one of my friends (someone who met me at my lowest and stuck by me through everything) is giving me the greatest honor I could ever hope for: on June 19th I’ll be officiating his wedding. Like a lot of people, I wouldn’t change a thing about my life.

Knowing and not Knowing

In the IT field, people have the expectation that we’ll always have an answer or a solution. The problem is that we usually don’t have the answer. A lot of the time, we don’t even have the beginnings of a clue. Your reaction when you don’t have an answer speaks volumes. I’m going to use a story to illustrate this point.

The Story

Adam and Bill work together at Amalgamated Spats. During a design meeting Adam mentions a product that could solve some of the problems the developers at Amalgamated Spats are facing. Although Adam is a specialist, he has a great deal of work on his plate and Bill is designated to develop the features. Bill isn’t a specialist, he’s a generalist. Bill is a great developer, but he’s unfamiliar with this specific product.

Over the course of development, Bill makes great strides. Unfortunately, there are some features that he isn’t able to solve programmatically even though they are included in the product. These features are features that were sold as part of reason to use the product. When Adam and Bill’s manager talks to Bill about his progress, Bill tells the manager that he wasn’t able to get the features done because it isn’t possible using the product. The problem is that the features do exist in the product, they just weren’t available the way Bill was using it.

Adam and Bill’s manager is upset that they’ve put so much faith in this product. While the manager trusts Adam, Bill has been working with the product day in and day out, trying to implement these features – why wouldn’t Bill tell the truth? As a result, Adam loses credibility. He can get that credibility back, and certainly will, but for a while it’s gone.

The Problem

There are a couple of problems with this situation:

  1. Bill has effectively thrown Adam under the bus (we call this bussing). Adam has lost credibility with the manager because he gave “wrong” advice.
  2. Bill said “No” when he should have said “I don’t know.”

Of these, the second is far worse. Ultimately, the first problem will go away and Adam will gain that respect back and everyone will be happy again. But the second problem speaks volumes.

It’s Not Okay to Say “I don’t know”

But I just said that Bill should have said “I don’t know,” right? Wrong. Saying “I don’t know” and ending it at that is not acceptable. In this crazy software development world, it’s our job to find the solutions to problems. Did you read that correctly? I didn’t say that it’s our job to code the solutions to problems, it’s our job to find the solutions to problems. Sometimes the solution is to use an existing solution or feature which you can only find through research. Do you see what I’m getting at yet? “I don’t know” isn’t acceptable but saying “I don’t know, but I’ll find out” is perfectly acceptable.

What’s the difference?

When you say “I don’t know” and you stop there you’re effectively throwing your hands in the air and giving up. You’re not only admitting that you don’t know, but that your lack of knowing is the end of it. It ends the conversation

If you say “I don’t know, but let’s find out,” you’re telling the other person that you don’t know the answer and it bothers you. It bothers you so much that you’re going to find out the answer. You’re advertising your inquisitive mindset and the way you solve problems.

What Should I Do?

The next time someone asks you a question where you don’t know the answer, tell them you don’t know. Be completely upfront about it. Follow that up with “but I’ll find out.” Then, actually find out the answer. Research the problem, research the product, and consult with experts – even if you think you are one.

Goals for 2010

Thomas LaRock thought it would be fun to tag me in yet another round of blogging bingo, this time to answer the question “What are you Goals and Theme Word for 2010?”. To tell the truth, I have not made any kind of New Year’s Resolution for a number of years. I usually review my goals on a regular basis, but let’s put them out here for everyone to see.

Theme word? I don’t have one, “f*%#ing rad” is two words, thank you very much.

Communication

Become a Better Writer

A long time ago, I went to college for four years, took out a bunch of loans, and got a degree in English, Non-Fiction Writing. I did exceptionally well in my English classes and did even better in the writing classes. I love writing and, frankly, the quality of my writing has not been up to par recently. I want to change my focus as a writer this year. I want to switch from writing short, highly technical, blog posts and change to creating longer article and essay length pieces. There’s nothing wrong with shorter, technical, posts but that is not where my interests lie. I want to focus on improving my writing so I can effectively teach more advanced concepts through writing as well as through public speaking. I was a good writer before and I’ll be a good writer again.

Become a Better Presenter

I have no doubts about my abilities as a presenter – I have a lot of room for improvement. Over the course of the year I’m going to team up with a number of people to improve my presentations. I want to get better at better content and delivery as well as meticulously practicing my presentations until I can deliver them in my sleep. I know that a lot of improvement comes from repetition and I would like to speak at least six times this year. With my current upcoming speaking schedule, that shouldn’t be difficult at all, but we’ll see.

Self-Employment

I don’t intend to be self-employed by the end of 2010, but I plan on being well on my way. How am I going to get there?

Writing

I’m writing stronger blog content. That’s not going to be enough. I want to get my name in a more places – magazines, guest blog posts, paid content, white papers, and a book. I’ve been shopping an idea around to various publishers and I’m hoping to have a book written and finished by the end of 2010. None of these things pay big bucks, but they all add to the bottom line.

Consulting

I haven’t done much consulting in the past, of course it was difficult when my day job was being a consultant – there’s an expectation that you will bring the business back to the company. Things have changed, I’m a full time employee now. My employer and I have had the talk – I’m allowed to do consulting work as long as I don’t help out our competitors.

This year I will take on several clients that require a small amount of time (10 – 20 hours each) every month. This isn’t to replace my existing job, this is to supplement it and provide additional income. Before I go completely solo, I want to have a substantial savings buffer built up and doing work on the side makes this a lot easier. I don’t plan on going at it alone – I’ve discussed this several co-conspirators and we have plans to get started this year.

Personal Ventures

I have an idea for a business that will make me tens, maybe even hundreds, of dollars. I plan on fully pursuing this over the course of 2010. Honestly, I have incredibly high hopes for this business and we have already thought of multiple ways to monetize our business. That’s not to say that it’s sure to happen, I have a lot of hard work ahead me, but I’m looking forward to it.

PASS

As many of you know, I was elected to the PASS Board of Directors and subsequently put in charge of the Summit program portfolio. This is the heart and soul of what I want to do over the next 12 months. I am going to make sure that the 2010 Summit program committee has my full support and that we’re can make the summit the best event possible.

Inky Mess

This is a personal one: I want to finish both full arm tattoo sleeves this year and, hopefully, start on my legs.

Once again, I think I’m supposed to pick victims. I’m going to pick Matt Nowack, Rick Kierner, Jeff Blankenburg, and Jorge Segarra

I also want a chainsaw on my hand like Bruce Campbell in Army of Darkness. That would be f*%#ing rad.

Getting the ISO Week and Year

Long story short: I need the ISO Week. We need to be able to make sure that when we’re reporting, we’re pulling data for the full week (which happens to coincide with ISO Week at my employer).

The problem with using an existing function is that the existing functions just return the week and not the full year + week combination. I used the function from the Less Than Dot wiki as a starting point and added my own code to create the full YYYYWW string to give me an ISO Week. This isn’t actually in the ISO standard format for reporting the ISO week, but I also don’t care so long as I can use the ISOWeek for effective querying.

Enjoy

ALTER FUNCTION ISOweek (@DATE DATETIME)
RETURNS VARCHAR(6)
AS 
  BEGIN
    DECLARE @ISOweek INT,
      @year INT,
      @rVal VARCHAR(6) ;
      
  
    SET @ISOweek = DATEPART(wk, @DATE) + 1 - DATEPART(wk, CAST(DATEPART(yyyy, @DATE) AS CHAR(4)) + '0104') ;
    SET @year = DATEPART(yyyy, @DATE) ;
    
    SET @rVal = CAST(@year AS VARCHAR(4)) + RIGHT('00' + CAST(@ISOWeek AS VARCHAR(2)), 2)

    --Special cases: Jan 1-3 may belong to the previous year
    IF (@ISOweek = 0) 
      BEGIN
        SET @rVal = dbo.ISOweek(CAST(DATEPART(yyyy, @DATE) - 1 AS CHAR(4))
                                   + '12'
                                   + CAST(24 + DATEPART(DAY, @DATE) AS CHAR(2)))
                                   + 1 ;
      END
    
    --Special case: Dec 29-31 may belong to the next year
    IF (
        (DATEPART(mm, @DATE) = 12)
        AND ((DATEPART(dd, @DATE) - DATEPART(dw, @DATE)) >= 28)
       ) 
      BEGIN
        SET @ISOweek = 1 ;
        SET @year = DATEPART(yyyy, @DATE) + 1 ;
        
        SET @rVal = CAST(DATEPART(yyyy, @DATE) + 1 AS VARCHAR(4)) + '01'
      END
 
    RETURN @rVal ;
  END
GO

This lets me do some snazzy reporting hackery like this:

SELECT  MIN(c2.[Date]) AS StartDate,
        MAX(c2.[Date]) AS EndDate
FROM    dbo.Calendar AS c
        INNER JOIN dbo.Calendar AS c2 ON c.ISOWeek = c2.ISOWeek
WHERE   c.[Date] = '20091229' ;

/*
StartDate               EndDate
----------------------- -----------------------
2009-12-27 00:00:00.000 2010-01-02 00:00:00.000
*/

Which is then used to feed report parameters.

Links for the Week of 2009.12.04

SQL Server

  • Kendal Van Dyke: Delegation: What It Is And How To Set It Up – The title says it all, folks.
  • wmarow’s disk & disk array calculator – Storage calculator. This wouldn't be terribly fascinating, except I know the performance characteristics of our I/O subsystem and when I plug in the variables, this is pretty close to real life performance. (Not directly SQL Server related, but it’s my blog so it goes where I wants it.)
  • A Loan At Last! – Brad Schulz thoroughly describes how to create a loan payment schedule stored procedure using nothing but T-SQL. The end result is beautiful to behold.

Development

Stuff & Things

Only 4 days left!

You know what? I don’t actually know how many days are left in the contest. All I know is that Bill Graziano just sent me an important email that I need to pass on to all 5 of my readers who aren’t my mom. (Hi mom)

There isn’t a lot of time left, but you still have a chance to win your PASS Summit registration, hotel, or one of a number of other cool prizes. If you don’t win, you can still go to PASS and spend time with me, or Brent Ozar, or Tim Ford, or Thomas LaRock or Blythe Morrow (Blythe is part of why PASS works). But, if eating waffles, bacon, or having treadmill races isn’t your cup of tea, there are a number of other reasons you might want to go to PASS this year.

Allow me to enumerate:

  • More than 160 top-quality sessions scheduled across 5 tracks and 14 pre/post-conference seminars (I’m an alternate presenter, bet you didn’t know that!)
  • Great chances to network with awesome people. There are even sessions to help you improve your networking skills like this one with Don Gabor.
  • A great lineup of top experts, authors, presenters, thought-leaders, and community influencers, including Kalen Delaney, Paul Randal, Kimberly Tripp, Itzik Ben-Gan, Kevin Kline, Greg Low, Louis Davidson, Steve Jones, Andy Warren, Brian Knight… and the list goes on and on (look, I stole that last line from some PASS copy, it’s 12:05 AM Eastern time, I’m not going to be all that original).

So, here’s how the contest works:

  1. Write 250 words or less about the best thing you learned at PASS.
  2. Send your submission or a link to your blog to contact@sqlpass.org with the subject line ‘Best Thing I Learned at PASS Summit

It’s really that easy. Check it out if you don’t believe me.

Point is, I’m going to be there.

Now, to find out how much time you have left, you need to

  1. open up SQL Server Management Studio (Express Edition works just fine).
  2. Open up a new window and paste the following code into the new query window:
    SELECT DATEDIFF(dd, GETDATE(), '20090701');
  3. Press F5 or CTRL + E or click Execute

You can enter three times, so enter three times. Three chances to win!

If you need help convincing your boss to send you, check out this great ROI or send them a letter or just send them the link to the conference agenda.

One thing I learned at PASS: it’s possible to include references to Krull, ballet, ice cream, and bacon in the same conversation.

When Less Reading is a Good Thing

Less reading is a good thing when you’re reading from a physical disk.

During some recent performance tuning, I stumbled across a little gem of a stored procedure that was producing between 4,000 and 11,000 physical reads every time it was called. The name? GetStateList.

It returns a list of state abbreviations. I’m not proud to say that I wrote it, but I have to come clean: I wrote it.

So, how did I fix this travesty of my youthful ignorance?

Well, it’s just a list of states, right? And it’s just all of the states from a couple of different tables. So, the easiest thing to do was to create an indexed, or materialized, view. Basically an indexed view is a view with a clustered index. Since a clustered index defines the physical order of data on the disk, the view is persisted to disk. When the underlying data changes, the data that is persisted to disk will change. Plus, you can supply other indexes on the view, just like it’s a table, which can make your queries run even faster.

Imagine, if you will, that you have a Location table full of data, as opposed to the empty one that we’re about to create.

CREATE TABLE Locations (
  LocationID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
  ClientID INT NOT NULL,
  Name VARCHAR(34) NOT NULL,
  State VARCHAR(2) NOT NULL
);

So, we have a lot of locations. Each location is belongs to a client. Each location has a state. I want a list of allowed states for each client.

SELECT l.ClientID,
       l.[State]
FROM Locations AS l
GROUP BY l.ClientId, l.[State]

Well, that’s great, but that’s also the query that results in WAY too many reads. So, how do we go about fixing this situation?

The indexed view that I mentioned earlier, that’s how!

CREATE VIEW dbo.LocationStates
WITH SCHEMABINDING -- you need this, can't very well persist changes if the table changes
AS
SELECT l.ClientID,
       l.[State],
       COUNT_BIG(*) AS the_count
FROM dbo.Locations AS l
GROUP BY l.ClientId, l.[State]
GO

CREATE UNIQUE CLUSTERED INDEX CIX_Locations_ClientID_State ON LocationStates (ClientID, State);
GO

What’s up with that COUNT_BIG(*) in there? Well, any time you use an aggregate function, or aggregation, in an indexed view, you also need to make use of COUNT_BIG(*) (see the MSDN Link above for more info).

Once I created the indexed view, I changed my queries to use the indexed view:

SELECT State
   FROM dbo.LocationStates AS x WITH(NOEXPAND)
  WHERE ClientId = @client_id;

The WITH(NOEXPAND) hint forces SQL Server to use the indexed view. As I understand it, this is not necessary on Enterprise Edition. Since I’m on Standard Edition I cannot verify this, but I can verify that SQL Server would not use the indexed view until I supplied the hint.

What was the outcome of all of this? Well, instead of several thousand reads, this ended up taking 2 reads. Considering that this stored procedure gets called thousands and thousands of times a day, that’s a HUGE savings.

The downside to using an indexed view is that if the underlying data changes, the persisted view data has to be updated, so it’s best to avoid using indexed views on queries that change constantly.

This site is protected with Urban Giraffe's plugin 'HTML Purified' and Edward Z. Yang's Powered by HTML Purifier. 531 items have been purified.