Tag Archive for #TSQL2sdays

Catching Up With Mickey

IMG_0555I can’t believe the year is almost half way through. I keep trying to slow the days down, but it just isn’t working. This year I’ve already accomplished so much, and I still have a long list before the year ends. Here is a recap and some events to look forward too!


I started the year off with a bang by starting a brand new Business Intelligence chapter in Irvine called Business Intelligence Group, A PASS Community (AKA BIG PASS Community). We consistently have 15 people every month and I’m really happy to announce that I have speakers lined up for the rest of the year! (Yippee!)

I also had the opportunity to participate in Pragmatic Work’s Training on the T’s. This is a free webinar series they have every Tuesday and Thursday.  I was able to present my Scalable SSRS Reports Achieved Through the Powerful Tablix presentation. You can still go to their website and view it.

I also had the honor of presenting remotely to the LA SQL UG for their 10th anniversary!


This month was spent writing abstracts for the year…and still understanding my new user group. I was also being courted for what became my new job. You can read about it here.


March was extra special. I had the opportunity to present at the Silicon Valley SQL Saturday. It was extra special, because it marked my 1 year anniversary for speaking in the SQL community. I also had my largest class to date! 97 people! Here was my favorite tweet of the day too. (Thanks Glenn!)


This month was full of meetings for our local Huntington Beach SQL Saturday that I helped host at the end of April. It was great having SQL Family come out to my neck of the woods beach.


I didn’t speak anywhere this month, but I did spend time every weekend writing. (Actually, I write every month.) I really enjoy participating in the T-SQL Tuesday Blog Parties, writing for myself, and participating in #SQLCoOp with my friends Julie, Chris, and Jeffrey.


And here we are in June, where I decided I would do EVERYTHING. I’m writing, speaking, leading, writing, and participating in #SQLHangout. Oh, and I’m getting my first dog. (More on her in a moment.)

My friend Boris Hristov (b|t|f), from Bulgaria, invited me to participate in an “episode” of SQL Hangout. We hung out in our two countries with 10 time zones between us and chatted about data types. You might not think this is an exciting topic, but it is a cornerstone to all databases. We came up with some great reasons why all database professionals should care about the data types of every field in their tables. So grab some popcorn or a glass of whiskey and hang out with us for half an hour.


You can find out about up and coming SQL Hangouts by following #SQLHangout on twitter, and you can find the full list of recorded SQL Hangouts here.

This month, I’ve also been blessed with a co-leader for my (now our) BI user group. His name is Rob Hatton, and I’m really happy he asked to lead the group with me.

I also had the opportunity this month to drive out to Riverside to speak with the Inland Empire User Group. This is the third time they’ve had me present, but the first time I’ve actually presented in person. Riverside is not a quick drive from where I live, but my boss, Steven was happy to be a carpool buddy for me. It ended up being a perfect presentation for him to hear, since it was on source controlling your SQL scripts with Red Gates’ SQL Source Control.

Now we get to look into the future…

2014-06-15 22.26.14Well, not to far into the future. Tomorrow (Wednesday) I’m heading out to Kentucky for a week. One of the events on my vacation will be speaking at SQL Saturday #286, Louisville. I’m really looking forward to the event since I enjoyed it so much last year. My husband and I are also going whiskey tasting with friends, we’ll hopefully be visiting the Corvette factory, and we’ll be picking up this adorable Labradoodle puppy who we’ve named Lucy. She will be 10 weeks old, and I can’t wait to hold her.

Here is a list of other events that I’ll be speaking at this year. You can also go to my 2014 Speaking Engagements page for an updated list through out the rest of the year.

I’ve applied to a few other events, but the accepted speaker lists have not been sent out for those events yet.

I’ll also be attending PASS Summit 2014 in Seattle in Nov this year. I hope to see all of you there.

T-SQL Tuesday #54 – How LinkedIn and Red Gate Landed Me My Job

My friend Boris Hristov (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009. This month’s invitation is on the topic of interviewing and hiring. This happens to be an apropos topic for me, since I have a new job.

It was a dark and stormy night…

OK, it was 8:30 am on a Monday morning and it was a beautiful January morning. It is Southern California after all…

I was checking my email like I always do after I get my morning coffee. There was yet another LinkedIn request. I’ve gotten to the point in my career where I’m picky about who I connect with. Basically, I always keep my LinkedIn account up to date, I’ve stopped connecting with people whom I couldn’t possibly help because our professions are so different, and I don’t connect with recruiters unless they are on my good side. This particular LinkedIn request was from a CEO named Richard at a company called DeskSite. Little did I know it would change my professional life.

A String of Events

Let’s step back a few days. I was already at a relatively new job. I had been there for six months, but I had also been frustrated. It was not the position I was expecting. I decided to pray about it the whole weekend and figure out if I wanted to start interviewing again or if I wanted to stick it out for another six months. By Monday I had decided to start looking.

So, we’re back to the LinkedIn request sitting in my email box on a Monday morning asking if I want to connect. Normally I would have said no. “He’s a CEO. He’s not a SQL person. We have no common connection.”, I would tell myself. But I was in a good mood. I thought, “Sure. Why not.”

Within an hour, I had an email from Richard, and it said:
Hey, we’re looking for a Database Architect. I was wondering if you could spend 10 minutes on the phone with me to see if you could help us find one.

I happen to be a chapter leader of a local BI user group called BIG PASS Community. While our group is BI focused, I know we have database professionals that cover the board, so I agreed to speak with him to see if I could find a match.

Side Story: I went down to my car to speak with Richard so that my colleagues wouldn’t know. I didn’t want them to think I was looking for a new job. Unfortunately, I have a blue tooth speaker at my desk and it was close enough to my car that my phone started transmitting through it. (Face Palm) So, I had to move my car. Problem solved.

Richard painted a picture for me of his startup company, DeskSite. He then told me where it currently stood technically and where he wanted to take it over the next three years. He then asked if I knew anyone who might be interested in joining his team or if he could possibly lure me away from my current company.

I kind of stumbled over my words, “I…I’m available. I just decided…literally, this weekend to start looking for a new position.” I cannot tell you how happy I made him. By time we got off the phone, DSC_3187I had an in-person interview scheduled for the next night at their office.

Side Story: When Richard called to verify that I could still make it, he told me that he had seen my SQL earrings that I wore in one of my Avatars. He loved them! So I wore them to the interview.

When I arrived at their office, I was greeted with a lot of enthusiasm. They were so happy to meet me. You see, they had Googled me. They knew that I’m heavily involved in the PASS Community. They had even seen the YouTube video that Red Gate published of me speaking at one of their SQL in the City events in 2013. They absolutely loved my enthusiasm and my obsession with SQL. They had already decided they wanted me on their team. They just had to convince me that I wanted to be on their team.  (You see, I was at a startup when the dot com bubble burst. It makes me leery of startups.)

My half hour interview ended up being three hours. At some point I was offered a job. Richard then wanted to know what it would take to have me on his team. Normally I don’t bring up the fact I like to speak and attend conferences in the first interview, but I’m also not normally sought after. So I asked if they would send me to conferences. Richard didn’t even blink. He gave me an allotment of days AND a budget. Wow. I did tell him I needed it in writing. I learned the hard way by taking something like that on good faith.

I was not prepared to have left with a verbal offer in hand. I was definitely thinking this offer was too good to be true. So like a good data professional, I started researching.

Over the next week or so, I asked many of my SQL friends what they thought. I sent Richard quite a few questions about the position, the company, the stock options, the offer, and even the culture of the company. (If you have never worked at a startup, they are VERY different than a mid-size or larger company.) I also made two more visits to their office.

The first trip was at lunch. You see, Richard’s ideal company is more like a family and families eat together. For those of you who don’t know me, I’m a people person. I despise eating by myself. It depresses me. Now that I have a Kindle, I have gotten more used to it, but I still prefer to eat with people. The bottom line is, I loved the culture of the company and Richard was one step closer to getting me on his team.

The second trip was a technical trip. They wanted me to meet with one of the consultants they use, to make sure I knew what I said I knew, and to talk deeper about the technical environment I would be working in. This one hour interview ended up being three hours. I think the technical part was only an hour, the other two hours was about the company… And my acceptance of their offer. (Aaacckkkk!)

Side note. I don’t do anything without talking to my awesome husband. I did take a bathroom break and talked to him on the phone for a bit and he was completely supportive of me taking the position.

The money dance

This is the part that has always been hard for me. Making sure I get paid fairly. I was asked what I wanted for a salary. I spent several hours researching what my salary should be. I have a wonderful friend who I got to talk real numbers with. I knew that I had been undervalued 2 jobs ago, but that had to do with the growth I experienced at that company. I grew professionally so fast when I first got involved with PASS that my salary soon became disproportionate to my knowledge, but because of red tape, my salary could not be fixed.

Anyhow, I finally came up with a number and sent it in. They made me an offer based on that number. It was made up of cash and options in the company. Unfortunately the cash portion was much too low. I was crest fallen. I know I could make a lot of money when the company goes public, and I really do think it will, but I have a daughter who will be heading to college in two years. I can’t risk her education or her younger sister’s education.

So what did I do? I talked to my friend who was also crest fallen for me. He offered some great advice and helped me devise a counter offer. I thought for sure it wouldn’t happen.  A week went by with no word. I prepared to start interviewing with other companies.

Then the clouds broke

I then received an email apologizing for the delay. They were in the processes of acquiring a larger office space do to the growth of the company and it had taken up much of their time. They really wanted me on their team. They understood my financial needs, but they had to discuss my counter offer.

In the end we came to an agreement and I became a member of an amazing team. I have been at DeskSite for a month and a half as of this writing. I’m very excited about the challenges ahead of me and I’m happy about being part of an amazing team.

So you see, I owe my awesome job to LinkedIn and Red Gate. I suppose Google should be added to the list since it was used to find my SQL in The City video on YouTube. So, YouTube should be added to the list as well……………

Thanks for all the fish

Thanks go out to Boris Hristov for hosting this month’s T-SQL Tuesday blog party. I always love and appreciate Boris’ enthusiasm about participating in T-SQL Tuesday, so please visit his website at http://borishristov.com/.

T-SQL Tuesday #51- Don’t Crap Out While Betting On Table Functions

My good friend Jason Brimhall (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009. As a compliment to the upcoming debut of the Las Vegas SQL Saturday, Jason has taken up a betting theme. He wants to know our stories of when we bet it all on a risky solution and won or lost.

Instead of telling you about the past, I want to help you win big at the table today. I really don’t want you to crap out while betting on the wrong table functions.

Snake Eyes

There are two types of table functions Multi-line Table Functions and In-Line Table Functions. There is a huge difference between the two of them.

Multi-line table functions sound great. You write as much code as you need in them and they will return all the data in a table variable. This is where the weighted dice rolls snake eyes every single time. You see, the statistics for a table variable always, always says there is only one row in the table being returned. It doesn’t matter if there are a hundred, a thousand, or a million rows. The statistics will say one. Which means the optimizer has a good chance of loosing when it picks the execution plan for that query.

Let’s Take a Look at the Bets

For my example, I have a simple query that returns 43 rows out of a Tally table. Notice that the index estimates 43 rows will be returned, which is great, because that is exactly on the money!



If we put that same query inside of a multi-line table function, we get an estimated number of rows of 1 (snake eyes!).






Double Down

An in-line table function will return the same result set, but there are some limitations on its construction. The entire query within the in-line table function needs to be done in only one statement.

Note: You can get very creative with Common Table Expressions (CTE) if need be.

There are two benefits to using an in-line Table Function. One, is that the Estimated Number of Rows will be accurate (or as accurate as the statistics on the table), and two, the “inside” of the in-line table function is not masked in the Execution Plan. It is plopped right into the middle of the calling query. (Yes, “plopped” is a technical term. )





Last Call

SoRemember to double down on in-line table functions and don’t crap out on the snake eyes of the multi-line table function.

Thanks for all the fish

Thanks go out to Jason Brimhall for hosting this month’s T-SQL Tuesday blog party. Please visit his website at http://jasonbrimhall.info/, or better yet come to Las Vegas for their SQL Saturday and thank him in person.

T-SQL Tuesday #50 – Automation Equates To Saved Time

SqlTuesday50Hemanth D (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009 and we hit the 50th “episode” this month. Could this be coincidentaly occurring during the 50th anniversary of Doctor Who?? I think not…

Anyhow, the topic this month is automation.

I can play that in 3 notes

There used to be a game show called Name That Tune. A contestant had to underbid the other contestant on what the minimum number of (musical) notes they needed in order to recognize a piece of music. Automating tasks remind me of this game show. First, you automate step one, then step two. Each time you tweak it and tune your automated process, you’re seeing just how far you can go… and how little manual work you are left with.

The first step

I’m going to talk about automating the first step of creating an SSRS report. I’ll be working with SSRS 2012, but the steps are the same for 2008 and 2008R2. The only difference is WHERE you store the templates so that they can be easily leveraged. Since that is the last thing you do, you’ll have to wait for the end of this post. So, hold on to your Tardis, and let’s go!

The first thing you need to know, is what are all the common elements to all of your reports. Here is a list of the elements I found to be the same on my reports:TSQL Tuesday 50 - Image 1

  • The location of the title: While my title was different each time, the font, font color, font size, and location remained static. So I created a placeholder for my title.
  • The company logo: The company logo was static as well as its location.
  • The color scheme: Since I used the same five colors, I created a temporary “pallet” for my colors. This was a mini Tablix control, with each of the five cell’s background color set to a different color in my pallet. I left it on the report until I was done setting all the properties of the report, then I deleted the Tablix. (No more looking up the colors in my documentation. Win!)
  • The location of the parameters: I personally think that the parameters should always be displayed on the report. This helps when troubleshooting a paper/pdf copy of a report. It also lets the users know the boundaries of the data they are looking at. (Note: While not depicted in my image, I put my parameters under the logo.)
  • The company address: Static location.
  • The confidentiality notice: Static location.
  • The page numbers: Static location.
  • The report identifier: This is a special number that helps you identify your report. Mine always has three parts.
    • TX or DW to mark the report as having transactional or data warehouse based data. This allows me to speak intelligently about a report that someone shows me in a meeting, especially since our data warehouse data was always older than our transactional data.
    • A number that corresponded to the documentation for the report. In our case, it was the TFS (Team Foundation Server) number.
    • An iteration number. This iteration number was specific to how many times the report was re-introduced into production. This allowed me to verify that the user was looking at the latest copy of the report, and it allowed me to document how many times the owner had requested changes to the report.
Creating the templates

A template is an RDL file saved in the templates folder. I created three templates for my team. Each template was identical to the others, except for two things. The paper size and orientation. I needed to make different templates to accommodate these attributes so that the controls that were centered or right aligned ended up in the correct location for viewing and printing.

  • SSRS 2008(R2) Location: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
  • SSRS 2012 Location: C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject
But wait! There’s more

If you want to go a bit further with your templates, you can add a watermark that can be leveraged during development all the way through user acceptance testing. The watermark will then be suppressed in production. The watermark says DRAFT. I found it helped with certain end users, who were getting caught up in the data (even though it was fake) and they weren’t focusing on the design and algorithms present in the report.

Here are the steps to add the dynamic watermark:

  1. Create a table in the database that contains the name and ID of the environment you are in, plus a parameter that dictates whether to show or hide the watermark. Note: You can also use this table to point to development file locations for documents and development URLs referenced in your reports.
  2. Add an image to the background of the report body that says DRAFT.
  3. Set the Background Repeat property to Repeat
  4. Create a data source in the report that points to the environment table you created in step 1 (preferably through a stored procedure).
  5. Use the data source to hide or show the background image of the report.

If you use shared data sources, then add the data source to the template directory so that it can be added to new projects the same way report templates are.

There you have it! You just automated quite a few (initial) steps for creating SSRS reports.

Thanks for all the fish

Thanks go out to Hemanth for hosting this month’s T-SQL Tuesday blog party. Please visit his website at http://sqlchow.wordpress.com/.

Reflections in the 2013 Mirror

2013Reflections-lake2013 was a wild ride. I had set up my goals in December of 2012 and surpassed some of them half way through the year. I hope that I continue reaching my goals in 2014. If I do, it will be another amazing year.

Here are the goals I had and how I measured up.

Being Mentored

I knew I wanted to be mentored, I even wrote a post about it here. I had no idea how valuable it would turn out to be. After I wrote my post, one of my friends convinced me to ask Grant Fritchey (b|t) if he would mentor me. I really like his speaking style and the topics he speaks on. So, I held my breathe, sent him an email, and he said yes. I couldn’t believe it.

We Skyped all through 2013. Our meetings were very valuable for me. You see, like many people, my confidence isn’t that high, but Grant believes in me. He encouraged me to go after my goals. He critiqued my abstracts and even my first speaking event. This was definitely an achieved goal.


This goal I blew out of the water. My goal was to speak at 5 events. I picked this number because there are 5 SQL Saturdays in California where I live. I ended up speaking at 15 different events. I exceeded my goal by 300%! Here is the full list of 2013 events and here is a summary of the events:

  • 6 User Group Meetings
  • 5 SQL Saturdays
  • 3 Women in Technology panels (I moderated 2 at SQL Saturday’s I was already attending, so I didn’t count them in the total count)
  • 2 Red Gate events (You can view one of my sessions here.)
  • 1 Conference (Dev Connections)

I’ve already listed the SQL Saturday’s I plan to apply for. The conferences will be added as I’m accepted as well as the user group meetings. Here is the list of 2014 events so far.


My blogging goals were only half met. The first part of my goal was to write on my personal blog (this one) at least once a month. Since I like to participate in the T-SQL Tuesday Blog Parties, I knew this was most likely achievable. The other half of my goal was to write once a month for mssqltips.com. I did not reach this goal at all because there just wasn’t enough time. I was only able to submit one tip this year.

For my person blog, I exceeded my expectations. Since this was my first full year having a blog, I compared the first half of the year with the second half of he year. In that time, I doubled my page views. I had 40 posts for the year, 18 of which were in December, 8 of which were for the T-SQl Blog Party, and 1 was an incredibly fun (and funny) story based on the pictures that Pat Wright (b|t) took at PASS Summit.

Here are the 3 posts that had the most hits this year:

Obtaining a New Job

It took six months of carefully identifying exactly what I wanted to do and going through interviews and talking to recruiters, but this past July I finally took a leap into a new job and I’m glad I did. I miss my former colleagues, but I’ve acquired some pretty cool new ones too.

Friends of Red Gate (ForG)

Red Gate has my absolute favorite tools. They also have some amazing people working for them. This year had the privledge of being part of their Friends of Red Gate program. This program conects various proffesionals who are power users of their products with developers and project managers at Red Gate. We get to disucss new features and how to improve existing features. It’s a wonderful program and I think their product is better for having the program.

Unexpected Surprises

Those were my goals for 2013, but I had some surprises along the way that have shaped me this year and are worth mentioning.

Jan 2013 SQL CruiseSQL Cruise

In December of 2012 I won a trip (the registration) on Tim Ford’s (b|t) SQL Cruise from SQL Sentry. The cruise was for a week in the Caribbean. Three wonderful days at sea listening to amazing speakers like Kevin Kline (b|t) and Allen White(b|t). And three wonderful days in port. I thoroughly enjoyed my experience and recommend it to ALL of you.


Half way through the year, Marlon Ribunal (b|t) approached me and asked if I wanted to help finish the book he was writing on reporting services. I was very honored that he asked me and I said yes. Marlon and I had our book published this past October. It’s called SQL Server 2012 Reporting Services Blueprints.

I also had the honor of being included in an ebook by Red Gate called 45 Database Performance tips for Developers. I’m very honored to be included in this endeavor with Grant Fritchey (b|t), Jonathan Allen (aka Father Jack) (b|t), Phil Factor (b|t), K. Brian Kelly (b|t), Ike Ellis (b|t), and Louis Davidson (b|t).


I was so impressed with the first SQL Saturday that I ever attended, that I agreed to help out with two local SQL Saturdays (Huntington Beach and San Diego). Since I enjoyed the planning and running around with my head cut off, I’ve agreed to help out again this next year.

I also help the Women In Technology virtual chapter (WIT). I had the honor of moderating three panels this past year, one of which was at PASS Summit in front of 600+ people. You can view the event here.

One of my most proud moments occurred this month, but it really started in September. (You can read the back story here.) I created an event for Jason Strate (b|t) and Bradley Ball (b|t) so that they could speak in Orange County. I had no idea if one person would show up or 100. QuickStart Intelligence hosted the event and they helped find attendees by calling all of their former students. Between my contacts, QuickStart’s former students, and Jason and Bradley’s reputations I was able to get 48 people sign up and around 30 people attend the event. I also found a sponsor for the pizza so it didn’t cost me or any of the attendee’s a dime. I call that a win!


I thought that my 2013 SQL year was at a close once December hit, but I was wrong. Red Gate put out a call for nominations for their new Tribal Awards and I was nominated for Best New Community Voice. I wrote about it here. All that I can say about this one, is that I’m pleasantly surprised and that I’m secretly hoping that Koen from Belgium wins.

2013CheersThank You

I want to thank all of you for reading my posts this year, for the comments that you leave, and for coming back time and time again. Cheers to you!

Where am I going now?

So what does 2014 hold for me? You’ll have to wait for my next post. (Evil grin inserted here.)

T-SQL Tuesday #47: SWAG And A Hit

Kendal Van Dyke (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.  The topic this month is a fun one, just like last month. Kendal wants us to brag about our favorite SWAG.

Wow, my favorite SWAG. Only one? Can’t do that. So I will give you my top 3.

Number 3 on the list

Tim Ford (b|t) gives the best All Inclusive Package of SWAG when you partake in his SQL Cruise conference (b). When I went on SQL Cruise this past January curtsey of SQL Sentry (b) (Thanks guys for paying for my classes!), we received the coolest fabric cooler. Inside this cool bag, was more awesome SWAG. There was a beach towel, several gifts from various vendors, and the coolest little multi-tool. (I unfortunately learned the hard lesson that multi-tools can’t be part of carry on luggage. Sad Panda.)

I use this bag when ever I need a simple cooler, which is ever SQL Saturday. I have so many food allergies, that I usually need to bring something for me to munch on. This bag does the trick.

Number 2 on the list

The PASS Summit 2012 backpack. I love this backpack. It’s sitting next to me right now while I type this post at the Las Vegas airport. This backpack is comfortable to wear and full of great pockets. My absolute favorite pocket, is the little pocket right under the handle. It’s great for putting my phone and wallet.

This bag is my laptop’s home. Timmy my Think Geek monkey gets to ride on the side of the bag, and my laptop gets to ride in the center. I can quickly store all my odds and ends for my speaking needs.


And Number 1 on the list

When I spoke at my first SQL Saturday in Silicon Valley (b), the speakers weren’t given speaker shirts, but were given jackets. The jackets were made by Port Authority and are absolutely wonderful.

Being that Southern California doesn’t “really” ever get cold, this jacket is now my new “winter” jacket…which means I wear it a couple of times a year.


Thanks for all the fish

Thanks go out to Kendal Van Dyke for hosting this month’s T-SQL Tuesday blog party. Please visit his website at http://www.kendalvandyke.com.

T-SQL Tuesday #46 – And I shudder

Rick Krueger (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.  The topic this month is a fun one. He has asked us to write about a Rube Goldberg type of solution (aka a hack job) we created at some point in our careers.

Like many of you I worked at a Start-up back in 2000, but I wasn’t a database developer back then. Back then I was a web developer who specialized in the business layer which I wrote in VB and later VB.net. Back then I never thought about consulting with the DBA to see if my idea was good idea from a database point of view. I can tell you that I shudder at the mere thought of the “brilliant” solution I came up with back then…It was actually a great idea…until you put in a database and queried it.

The Plan

The company I worked for was building a shopping cart from the ground up. We needed a way to keep track of the state of each item in an Order and display that information at the Order level. My idea was to keep track of each Order Item state with an alphanumeric letter and we would string them all together into one field at the Order level so that we could query for Orders with Items at a particular state. (This is where we all shudder.)

When I thought of the idea, I thought there would only be a couple of letters. R for return, O for ordered, C for Canceled, etc. As we started going through all the permutations we found a ton of exceptions. I think by the time I handed the responsibilities over for this one field, we had almost 10 different designations.

The Flaw

Since (back then) I could read execution plans as well as I could read Japanese (which means not at all), I had no idea the impact of my design. I didn’t know that indexes on that field would become pretty much useless since a Like operator would have to be used to find a particular part of  the concatenated field.

A better approach for this same solution would be to leave the designations at the item level and for reporting concatenate the values at the Order level.

Second Verse, Same as the First

Since I never learned at the first company that my idea was flawed from a database perspective, I pitched the same plan at the next company…where we implemented it…again. I have learned my lesson since then and advocate to all .net developers that working with the DBA or DBD from the beginning can help achieve a better application.

Thanks for all the fish

Thanks go out to Rick Krueger for hosting this month’s T-SQL Tuesday blog party. Please visit his website at http://www.dataogre.com.

T-SQL Tuesday #45 Summary–Follow the Yellow Brick Road

This is the summary of blog posts for T-SQL Tuesday #45 – Follow the yellow Brick Road. I was the lovely host this month with the topic being Auditing. As always, I enjoyed all the posts. It reminds of what Forest Gump once said:

 “It’s like a box of chocolates. You never know what you’re going to get.”

So, let’s meet up with Dorothy to find out what kind of auditing related advice and stories she found on her journey.

TSQLTuesday45 - YellowBricks


Jason Brimhall had a wonderful post about using the information that SQL Server automatically gathers for us. He used the  sys.FN_TRACE_GETINFO function to find out when and who executed the DBCC SHRINKFILE command  in his environment.

Rob Farley wrote a thought-provoking post about how you should think about your auditing data you do have and how you shouldn’t make excuses when you don’t have it.

Boris Hristov wrote a piece about the impact of external auditing entities on our activities. In this case, it was a story about unintentionally scaring the client about database security levels and how that could have impacted a SOX audit. Side note: I always enjoy Boris’ enthusiasm about T-SQL Tuesday. (keep it up Boris. Smile )

Chris Yates wrote a great summary piece about what to audit and a list of various tools to use to accomplish the auditing.

Steve jones wrote a wonderful piece on why auditing is important and how he caught a “cowboy coder”.

Steven Ormrod wrote an excellent piece on how to use a DDL Trigger to not only find out who modified the database, but from what IP address and application they did the deed. This is definitely one to bookmark.

Glenda Gable shares with us how she learned about writing triggers that audit. She then goes on to share with us how she had an opportunity (and seized it) to learn and implement Change Data Capture (CDC).

Jeffrey Verheul wrote about taking the initiative at a new company and creating a baseline on the system health of the 70 databases he was responsible for… and it paid off in a relatively short amount of time.

Dave Green wrote a thought-provoking post about what to do with the audit data during a data migration. I particularly enjoyed this post, since I get to ask my clients those very same questions next week. (Can we say perfect timing?)

Robert L Davis wrote about a type of auditing that I hadn’t even considered, and I’m happy he did. He wrote about a new feature in SQL Server 2012. SQL Server 2012 will automatically audit the execution of your SSIS package. He goes on to share how he was able to use the feature.

Mickey Stuewe (that would be me), wrote about a simple way to audit at the data level. She also made a plea at the end of her post to all the women bloggers out there. She asked them to contact Adam Machanic (b|t)  about being a T-SQL Tuesday blog party host.

…And That’s A Wrap

Thank you to the 11 bloggers who participated this month. We couldn’t have T-SQL Tuesday without you. Thanks also to Adam Machanic who put this brilliant idea in action and makes sure it continues each month. … Only 14  more days until the next invitation goes out.

T-SQL Tuesday #45–Follow the Yellow Brick Road

This month I’m the lovely host of the T-SQL Tuesday blog party which Adam Machanic (b|t) in December of 2009.  The topic this month is Auditing. I chose this topic because auditing, in some form, is needed for all databases (IMO).

Was it the lion, tiger or bear? Oh my!

One of the databases I use to manage had a schema where each table had four fields to keep track of who originally created the record and who last modified the record. The date was also stored for these two events. These four fields were populated by the .Net application that used the table.

TSQLTuesday45 - Table

We found, over time that this solution had a couple of holes.

1. Values were missing when someone inserted or updated data directly in the database.
2. Values were missing when the .Net programmer forgot to add code to populate the fields.

Luckily, the solution was a quick and easy one.

First, defaults were set on the InsertedBy and InsertedDts fields, SUSER_NAME() and GETDATE() respectively. This provided the user’s account that was used to access the table and the current date.

Second, an update trigger was created to populate ModifiedBy and ModifiedDts. This allowed the application to insert values if they were provided, but default values were available when they weren’t. More importantly, when someone modified the data directly, their login was captured.

CREATE TRIGGER dbo.trg_ClientOrder_iu ON dbo.ClientOrder

	UPDATE dbo.ClientOrder 
		ModifiedDTS = ISNULL(i.ModifiedDTS, GETDATE())
		,ModifiedBy = ISNULL(i.ModifiedBy, SUSER_NAME())
		dbo.ClientOrder AS tb
		INNER Join inserted AS i ON tb.ClientOrderID = i.ClientOrderID

Here you can see that when I inserted the first two records, my login was captured. On the third record I provided values for InsertedBy and InsertedDts.

TSQLTuesday45 - Data1

Next I updated the first record without providing a ModifiedBy or ModifiedDts. On the second row, I did provide a ModifiedBy and ModifiedDts with my update.

TSQLTuesday45 - Data2

This simple solution allows you visibility into who and when data was changed. While this is not a comprehensive solution, it did solve questions that we had over the years.

Thanks for all the fish

Usually this is where I thank the host, but as Spock would say, “That would be self-serving.” So instead I am making a plea to all the amazing female bloggers out there. You know who you are. I want you to ask Adam Machanic (b|t) if you can host the T-SQL Tuesday blog party. Why am I making this plea? I’m glad you asked.

Being a data geek, I went through all the past T-SQL Tuesday invitations, which Steve Jones (b|t) so graciously keeps up to date. (Thanks Steve!) I found that out of the 45 invitations, only FIVE hosts were women.  The other amazing fact was that three of us didn’t host until this year!

  • Jes Schultz Borland (b|t)
  • Erin Stellato (b|t)
  • Jen McCown (b|t)
  • Wendy Pastrick (b|t)
  • And yours truly

Ladies, that is only an 11% representation. Please, please, please email Adam Machanic (b|t) and ask him if you can host. The rules are here.

Ok, I’m getting off my soap box. 🙂

T-SQL Tuesday #45 Invitation–Follow the Yellow Brick Road

This month marks the 45th month in Adam Machanic’s (b|t) T-SQL Tuesday blog party which started in December of 2009. Each month an invitation is sent out on the first Tuesday of the month inviting bloggers to participate in a common topic. On the second Tuesday of the month all the bloggers post their contribution to the event for everyone to read. The host sums up all the participant’s entries at the end of the week. This month I’m the host and the topic is …


An audit trail is needed for various reasons. Some companies need it for compliance, others need it to find out who “accidently” did something stupid last week, and some specialized audit trails can tell you how the data has changed over time.

So, it is time to follow Dorothy and Toto down the yellow brick road and to share your experience with auditing data. If you are new to the T-SQL Tuesday blog party and need some ideas, here are a few:

  • How to implement SQL Server Audit which was introduced in SQL 2008.
  • Your favorite audit pattern.
  • Your worst experience with an implementation of a bad auditing pattern.

Pay no attention to that man behind the curtain!

The Great and Powerful Oz has spoken! … and these are the rules.

Rule 1: Make sure that you include the T-SQL Tuesday image at the top of the post which will help identify your post as a T-SQL Tuesday blog post.  Please include a link back to this invitation too.

Rule 2: Sometime next Tuesday using GMT, here’s a link to a GMT time convertor, publish your blog post.  For example in California, that would cover 5 pm Monday to 5 pm (PDT) Tuesday.

Rule 3: Come back here and post a link in the comments so that I can find all the posts for the round up.

Rule 4: Don’t get yourself fired. Make sure that you either generalize your post or get permission to blog about anything from work.

Rule 5: If you roam the Twitterverse, then don’t forget to Tweet about your blog post with the hashtag #tsql2sday.

Rule 6: Go read someone else’s blog on the subject!

Final Rule: Have fun!


%d bloggers like this: