Mickey's T-SQL Ponderings

Sharing my tidbits of knowledge on T-SQL, SSRS, and whatever catches my fancy.


7 Comments

T-SQL Tuesday #36 – What Community Means to a Newbie


The 4th quarter of 2012 has marked many firsts for me. I attended my first SQL Saturday and my first PASS Summit. I joined my first SQL User’s Group. I started a blog, and now I’m participating in T-SQL Tuesday. This month’s topic is about what community means to me. Since I have only recently found this amazing community, I’m blogging about the community from a Newbie’s perspective.

In the beginning…..

I knew no one in the SQL community outside of my IT department. The database side of my IT department has less than 5 people. I desperately searched for a local SQL Users group, but I couldn’t find an active one, so I widened my search criteria. I found I was half way between the LA SQL PASS chapter and San Diego SQL PASS Chapter, both of which are 50 miles away and a 1.5 to 2 hour drive on a Thursday night. What to do, what to do.

I lucked out. On Sept 15th, 2012 the San Diego SQL PASS Chapter (SDSQL) was hosting a SQL Saturday (#157).

It changed my life.

I drove down to San Diego (by myself) not expecting much. I took Jason Strate’s (B|T) session on Discovering the Plan Cache. His session was so good, that I changed my schedule and attended his other 2 sessions. By the end of the day, I was happy with the day, but I had hardly said two words to anyone. I had really wanted to network, but my shy side had stopped me (yes I do actually have a shy side. It’s very small, but it’s there). Phil Robinson, the president of the chapter, invited everyone to a local sports bar for dinner and I went.

This is where the magic happened.

Before I even went into the building I was greeted by someone from Quest. She happens to work in the building next to me. She introduced me to others and I finally began to network. As the night moved on, I met Phil Helmer (B|T) and everything really changed. He introduced me to other frequent members of the local chapter, as well as some of the speakers. We talked about the PASS Summit 2012, which I wasn’t going to be able to attend until 2013. After talking to Phil for a couple of hours, I decided I wanted to go this year, so I convinced my manager to let me go.

Phil was also the one who convinced me to set up a Twitter account, which I’m now an active member of. Twitter has provided me a way to stay in touch with the SQL community. It has also provided a way to help others with SQL questions.

That night I also struck up a conversation with Benjamin Nevarez (B|T). I told him how I use to be a Microsoft Certified Trainer and I wanted to start speaking on SQL topics in a year or two. He thought that goal was too far away and that I should speak at the next Huntington Beach SQL Saturday coming up in early 2013. He was very encouraging and has convinced me to submit a proposal.

But it gets better.

Fast forward to a couple of weeks prior to SQL PASS Summit 2012. I received an email from Joe Fleming (T). He was to be my mentor for Summit 2012. He was so helpful, answering all my questions and making sure I met people at the Summit. He’s not the only one either. Before the Summit even started, I had met new people on Twitter who would be attending the conference. Once I arrived at the Summit, I didn’t sit still for a week. I met so many fabulous people, and they accepted me, just the way I am. I never felt like the ugly stepsister. I never felt like the eccentric artist. I was never shut out because I’m Christian. I was accepted.

I was part of the SQL community.

Update: After reading the other T-SQL Tuesday #36 blog posts. I realized that I hadn’t thanked the host, Chris Yates (B|T) for hosting this month’s SQL blog party. Thank you Chris.

Advertisements


Leave a comment

The ROW_NUMBER Function As An Alternate To The MAX Function

It has taken a month to fuss over my new blog, but I finally made my first SQL entry. Since I’m excited about the upcoming SQL Pass conference, I thought I would show a fictitious problem about employees and their interests in SQL Conferences.

Problem: You are given two tables. The first table contains employees. The second table contains all the SQL Conferences each employee has been interested in along with the date they showed interest in the conference and whether or not they are still interested. You are asked to find the last SQL Conference that was added for each employee. Only conferences the employees are still interested in should be included, and only one conference per employee should be listed. The returned data should be ordered by the employee’s last name and first name.

Employee and Interest Data Model

The first solution that came to mind, was to use the MAX function on the InterestAddDate field to find the last added interest. There are two issues with this approach though.

1. In order to get the activity field returned, the Interest table has to be joined a second time on the MAX(InerestAddDate).
2. Multiple rows will be returned if the employee had an interest in two SQL conferences on the same date. While this could be a valid result set, in this case only one activity should be returned.


WITH CTE_InterestsByMax
AS
(
      SELECT
         EmployeeID
         ,MAX(InterestAddDate) AS LastInterestAdDate
      FROM
            dbo.Interest AS i
      WHERE
            isActive = 1
      GROUP BY
            EmployeeID
)
SELECT
      e.FirstName + ‘ ‘ + e.LastName AS EmployeeName
      ,i.InterestAddDate
      ,i.Activity
FROM
      CTE_InterestsByMax AS im
      JOIN dbo.Interest AS i ON im.LastInterestAdDate = i.InterestAddDate
                                                AND im.EmployeeID = i.EmployeeID
      JOIN dbo.Employee AS e ON im.EmployeeID = e.EmployeeID
ORDER BY
      e.LastName
      ,e.FirstName

Solution: To address these two issues, I used a Common Table Express (CTE)  and the ROW_NUMBER function. This function will number each row with a unique sequential number based on the OVER clause. Inside the OVER clause, I will order the data by the InterestAddDate field in descending order. Since I want to find the last SQL Conference of interest for each employee, I’m going to add the PARTITION statement on the EmployeeID field to the OVER clause. This will cause the ROW_NUMBER function to start over for each EmployeeID. Since I’m not using an aggregate function, I can return all the data from the Interest table that I need.

In the next part of the query , I join the CTE to the Employee table and add a WHERE clause. Since I ordered each partition in descending order, I know that the first row of each partition will have a rowindex of 1. I can now filter my data by rowindex = 1.

WITH CTE_InterestsByRow_Number
AS
(
      SELECT
         i.EmployeeID
         ,i.InterestAddDate
         ,i.Activity
         ,ROW_NUMBER() OVER (PARTITION BY i.EmployeeID ORDER BY i.InterestAddDate DESC) AS RowIndex
      FROM
         dbo.Interest AS i
      WHERE
         i.IsActive = 1
 )
SELECT
      e.FirstName + ‘ ‘ + e.LastName AS EmployeeName
      ,i.InterestAddDate
      ,i.Activity
FROM
      CTE_InterestsByRow_Number AS i
      JOIN dbo.Employee AS e ON i.EmployeeID = e.EmployeeID
WHERE
      rowindex = 1
ORDER BY
      e.LastName
      ,e.FirstName

When I looked at the logical reads for these two separate queries, the query using the MAX function had twice as many logical reads as the query with the ROW_NUMBER function. When I looked at the Execution Plan for both queries, I found the query using the MAX function had a higher Query Cost relative to the batch. My first run with the data, I used 20 Employees and 40 Interests. For the second run, I used 1000 employees and 4000 interests. I found that the Query Cost for the query using the MAX function increased with the larger datasets.

Execution Plan


Leave a comment

Pimping My SQL Saturday T-Shirt

I’m very excited about attending my first SQL Pass Summit in Seattle in a few weeks. I haven’t even attended yet and I’m so impressed with the schedule, extracurricular events, and the mentors they assign first-timers like myself.

One of the events they have schedule is on Wednesday. They want everyone to wear their SQL Saturday t-shirts. There is only one problem…..mine makes me look like a house. I absolutely refuse to where a men’s t-shirt that makes me look like that. So I tapped into the right side of my brain and pimped my t-shirt out. Now not only do I look like a girl, but a stylish one.

20121020-154000.jpg
Not looking too great.
20121020-154459.jpg 20121020-154014.jpg
Look out how happy I look. Now I look like a SQL girl.

Growing up my grandmothers taught me crocheting and sewing. (My maternal grandmother taught me in Spanish. ) Now a days, I just go to Google or You-Tube and search for the technique I want to learn. I found out how to alter a neckline and how to add a hoodie. Here are the steps I went through.

Step 1. I researched all the techniques I needed to learn in order to feel comfortable making the changes to my precious SQL Saturday t-shirt. I answered questions like…

  • What kind of needle should I use?
  • Do I need special thread?
  • Do I need any special notions like binding? (Notions are all the little extras you use for sewing, like grommets for the hoodie, clasps, binding,… The stores have whole walls for notions.)
  • How do you sew a hoodie?

Here are the sites I found most helpful based on the sewing skills I already posses. If you have never sewn, I would suggest looking for some YouTube videos on the subject as well.

Step 2. I figured out what aspects of my SQL Saturday t-shirt I wanted to alter

  • Neckline – I prefer a scoop neck or v neck. (Next time I will make a v neck.)
  • Shoulders – The shoulder seams needed to be moved up to my shoulders. I think this, out of all the issues with my t-shirt, made me look the boxiest. By moving the seams up to the edge of my shoulder where they belonged, I improved the look of my t-shirt immensely.
  • Sleeves – I’m only 5’6, so my sleeves needed to be shorter. Since I had an extra t-shirt that was in a contrasting color, I used it to change the color of my sleeves.
  • Length – I shortened the t-shirt so that it sat on my hips properly
  • Hoodie – Because I just needed it.


Step 3.
I marked up my t-shirt so that I knew where I needed to cut it. While the video tutorials talked about using scissors to cut the t-shirt up, I found using my rotary cutter much easier. I folded my t-shirt in half, making sure there were no wrinkles, then I cut the sides, arms, and neckline out. Since I folded the t-shirt in half, I knew my t-shirt would remain symmetric.

20121020-161202.jpg 20121020-161210.jpg
Clocks and plates make
great templates for cutting circles.

Step 4. I created a pattern for the hoodie based on my favorite hoodie in my closet. I did buy some red t-shirt material for the hoodie, but I could have used another t-shirt. Since Red Gate is my absolute favorite toolset, I cut their logo out of a second t-shirt I had and put it in my hood. I used that same t-shirt for my sleeves and the rest of the hoodie.

Step 5. I sewed the hoodie together.

Step 6. I sewed the sides and the sleeves.

Step 7. I attached the hoodie to the neckline. I did this by finding the center line of the back of the t-shirt, and the center line of the hoodie. I started sewing from there so that I knew my t-shirt would be symmetric. since the hood will be down more than up, I have the seam facing out.

Step 8. I used my twin needle and a contrasting color thread to sew the neckline. I used the same twin needle to sew the hem as well.

20121020-161235.jpg 20121020-161228.jpg
20121020-161243.jpg


Leave a comment

Hello World!

When I taught beginning programming with Microsoft Visual Basic, I always used the traditional “Hello World!” to teach how to create a msgbox. Since this is my first Blog, I thought I would do the same.

You may ask, why am I starting a Blog, and why should you read it? Here are my goals:

  • Share with the T-SQL community what I have learned over the years.
  • Share with the BI community my knowledge with Microsoft SQL Reporting Services.
  • Better myself as a writer.

I hope you enjoy my posts.