Archive for Speaking

Year in Review for 2015 and Future Goals for 2016

AdobeStock_96432559This has been another spectacular year in the SQL world. Unfortunately, I can’t find my list of goals, so I’ll have to wing it. I can say, that I have had some unexpected surprises this year.

 

Level up!
  1. I have two favorite activities in the SQL world. One of which is speaking. This year I presented 14 times to over 1300 people. I spoke at two conferences, one of which was my second year at PASS Summit in Seattle. I spoke at 6 user groups, one of which was presented remotely in Australia. I also spoke at 5 SQL Saturday’s and once for Pragmatic Works.
  2. My second favorite activity is writing. This year, I started writing for SQL Server Central. I wrote two articles for them, which had more than 20K views. I have my third post scheduled for Jan 4th/5th, so keep your eyes open for it. I also had 17 blog posts on my website. My metrics for the year were off the wall. An 82 % increase in sessions, a 403 % increase in session duration, a 97 % increase in page views (that’s 23K page views!), and my favorite, 52% increase in Users, which yields 9.5K users. Thanks viewers!
  3. I created my meet and greet list for PASS Summit and I was able to meet most of the people on my list. Some of them I knew through conversing on Twitter, like Pinal Dave. I (finally) had an opportunity to give Buck Woody a big hug too. I also met some people that I didn’t know: Wawrick Rudd, Mellisa Lord, Michael Upton, Denis Horner, and many more.
  4. This is my second year co-leading our local BI user group with Rob Hatton.
  5. I had the honor of being part of the Friends of Red Gate program again. This is my third year.
  6. My husband granted me my wish for our 20th wedding anniversary of going on my second SQL Cruise in the Caribbean AND he went with me on it. When I mentioned I had been on a SQL Cruise in my interview for my current job, they thought I was kidding. If you have never heard of SQL Cruise, I highly recommend checking it out. There is no where else you can get 6 amazing instructors for 30 students. Those speakers are trapped on a ship with you, so you can actually spend time with them over drinks or dinner asking them any question you want. I had that opportunity with Jes Borland, Grant Fritchey, Kevin Kline, David Klee, Tim Ford, and Amy Ford.
I have some new goals for 2016 as well
  1. I was hoping to speak at least once a month again, but after looking at 2014 and 2015 it will be easy to speak an average of twice a month. I have a goal of of 9 SQL Saturdays. Hopefully, I will get another opportunity to speak at PASS Summit. And I plan on speaking over the inter-webs as many times as I can. Here are some of the speaking engagements, I already have planned.
    1. SQL Saturday 461 in Austin, TX, Janurary 30th, 2016.
    2. Pragmatic Works on Feburary 9th.
    3. Profession Development Virtual Chapter in March.
    4. SQL Saturday 497 in Huntington Beach, CA April 02, 2016.
    5. DBA Fundamentals Downunder Virtual Chapter in May.
    6. All the others will slowly appear on my Speaking Engagements page.
  2. I plan to continue writing for my own blog and for SQL Server Central with a goal of one post each month for each site. That is a lofty goal for me since almost all my writing is done on Sunday’s, in a little coffee shop with my daughter. (I also spend that time writing abstracts.) Wish me luck!
  3. I’ve enjoyed speaking on SSRS, but I’m going to change it up. I’ll continue speaking on writing better SQL, but I’m also going to take up another SQL subject. Stay tuned!
  4. I’ll continue co-leading our local BI user group.
  5. I plan on mentoring one of my colleagues, Ly Nguyen. He has a goal of becoming a DBA or a Database Developer. I’m super excited about this, since he is eager to learn.
  6. This next year, I want to spend more time on forums, helping others.
  7. Hopefully, I’ll be part of Friends of Red Gate for another year.
Stretch goals

I think it’s a good idea to have some stretch goals to help push yourself past your comfort zone. Here is mine.

  1. Create a full day session to present. This seems so overwhelming, but I was a Microsoft Trainer for two years at the beginning of my career, so I know it’s possible.
  2. Speak (physically) in another country. My Australian friends, have been pushing encouraging me to speak in Melbourn, AU. I’m not sure if it will be possible, since my oldest daughter will be attending college next year, and most of my speaking money will be redirected to her tuition. If I accomplish the first of my stretch goals, then this might be possible.
  3. Writing another book. This is a big commitment of time. The good news is, my family is willing to support me in this endeavor. This is great, since they would hardly see me, except at dinner, until the project was completed.
I want to thank…

There is no way I could accomplish what I do without the support of friends and family. Here is this year’s shout out.

  1. My husband is definitely number one on this list. Whenever I have a really bad week, or I get bummed out about something, his first question is, “When is your next SQL Saturday”? Also, he is encouraging me to speak and write until my heart’s content.
  2. I always say, my first language is SQL and my second language is English. Luckily I have my daughter Victoria to help with my grammar and spelling. She is now one of my official editors and will continue to be my editor through college.
  3. Ben McNamara is my second editor. While Victoria can catch my English mistakes, Ben can catch the technical ones. He is also one of my touchstones when I get nervous about speaking or am taking criticism personally.
  4. Jes Borland, Chris Yates, Julie Koesmarno, and Nghi Nguyen are my other touchstones in my life. They are great at keeping me grounded.
  5. Steve Jones asked me to write for SQL Server Central and I was very honored. He also has the most relaxed manner that I wish I had. I can learn how to be more laid back from him, since I see him at SQL Saturdays and at Summit.
  6. I want to thank you, my readers and those that attend my presentations. Without you, I wouldn’t be having any fun.
Now it’s your turn.

My question to you is, what are your technical goals for 2016? Do you have some achievable goals and some stretch goals? Here are some ideas.

  • Start a blog. Most people start writing a blog for themselves, to remember how to do something in the future.
  • Start speaking. This can be very scary, but there is always someone out there who needs to know what you know. It can be as simple as rewriting a cursor or as complex as setting up replications.
  • Mentoring. Speaking might be too scary, so instead take someone under wing. Not only can you mentor them in a technical capacity, but you can also mentor them in how to deal with different parts of a team or how to gather requirements.
  • Volunteering: Every organization that is run by volunteers, needs more volunteers. You can help out at your local SQL Saturday or at PASS Summit to name a couple of places. (I volunteered at Summit this year. I directed people to the WIT lunch or to the normal lunch. I had a blast!) You can also volunteer at local community centers that have programming classes for kids. We need to help encourage the next generation.

My Eleven Day PASS Summit 15 Experience

“STUEWE!” This is how I knew PASS Summit was about to begin. I was walking to the Friday night SQL Saturday Portland speaker dinner, prior to PASS Summit, when I heard my name being called, well, yelled. Since it was already dark, it took a minute to spot the black SUV with Mike Fal hanging out the window waving at me.

But wait, that is not when my Eleven Day Summit Experience started.

Day One – Thursday

It actually started on Thursday before Summit at LAX. I was there bright and early to pick up two of my Australian friends, Martin Cairney and Ben McNamara, who would be traveling with me to Portland and then on to Seattle. Despite the 14 hour flight, they wanted to go see the Endeavor space shuttle at the California Science Center in LA.

IMG_4924

Day Two – Friday

Friday was spent flying to Portland where we would be attending SQL Saturday Portland. We stayed in an Air B & B house. This is a great way to save some money for lodging and to feel like you’re at home. There is always a coffee machine with decent coffee and the best…no rambunctious kids on the floor above you. WIN!

Friday night, we attended a wonderful speaker dinner (This is when Mike Fal yelled my name out a window of an SUV). Speaker dinners are always fun to attend. You get to catch up with friends and make new ones. Portland is always a bit special, because it’s full of speakers from around the world who made a little extra time to speak at a community event before PASS Summit.

After dinner, we found an amazing restaurant. It had the most scrumptious gluten free bread. I think it was made of clouds.

IMG_4938

Day Three – Saturday

Saturday was spent at one of my favorite SQL Saturdays. Ok, I’ll be honest. ALL SQL Saturdays are my favorite. It’s a great time to do a little extra networking, get to see SQL Family, and meet new people in the community. I also love speaking and that is what I did in Portland. I spoke on SSRS.

2015 PASS Summit12

I was able to attend several wonderful sessions. My favorite was given by Julie Koesmarno and Cindy Gross: Moving beyond Unconscious Bias. I really liked their approach to this topic. Throughout the presentation, they strongly emphasized that we are all good people.

They spoke about how everyone categorizes everything in our lives, but sometimes we have a bias in our categorization, an unconscious bias that we are unaware of. They told us about a study by Harvard called the Implicit Association test, which tests how we categorize various topics. Julie and Cindy then showed us a video of Allen Alda taking the Harvard test. I strongly recommend learning about unconscious bias.

No Portland trip is complete without going to my two favorite places in the evening. We put our names in for our two hour wait at the Multnomah Whiskey Library, and then we headed over to Cassidy’s. They have great food and AMAZING bacon. I ordered enough bacon for all sixteen of us. (And there was much rejoicing). Afterwards we went to the Whiskey Library, where I was finally able to try some Scapa Scotch. I really liked it (Thanks for the recommendation Grant!).2015 PASS Summit13

Day Four – Sunday

One of the cheapest ways to get from Portland to Seattle is the train. When we bought our tickets, they were around $25. Little did we know it would be an unexpectedly amazing day. My friend, Ted Stathakis, was really looking forward to this day too. He loves trains; neither of us realized how amazing it would be.

Last year, there were only five of us on the train. Martin Cairney and I spent the majority of the trip troubleshooting a problem with my VM. Not this time. This time it was completely non-technical. There were sixteen of us in our car, and two who (foolishly) purchased tickets in a nicer car. Why do I say foolishly? Well, we were having so much fun, that they spent the majority of the trip in our car…eating our Voodoo Donuts. Next time, I think they will buy the general tickets. Next time, I’m also going to try getting the WHOLE car too. So if you want to ride with us, keep an eye out for a post from me in the summer.

2015 PASS Summit4

For more details on the fun we had, go look up the twitter handle #SQLTrain.

Day Five – Monday

Monday was a red day. Red Gate day that is. Red Gate puts on a free event called SQL in the City. They have put it on each year on the Monday before the PASS Summit for several years now. They bring quite a few people from the UK to talk about their tools, and they have amazing presentation. This year, they went with a common theme: Continuous Integration. I participated in the Lightening Talks. I spoke on how to use their DLM Dashboard. This is a great tool that keeps an eye on the databases you connect to the DLM dashboard. You can monitor who modified the database and which scripts they ran on the database. Did I mention this is a free tool?

2015 PASS Summit5

Monday night is the annual Networking dinner put on by Steve Jones and Andy Warren. This is a “must attend” event. Why? It’s all about networking and integrating 1st timers into the community. This year, a bunch of us crammed into a booth with four 1st timers. At least two were from Europe. I enjoyed getting to know them and they enjoyed being introduced to all the people that came by our table to say hi.

Note: Remember, networking isn’t just about talking tech. It’s about building relationships so that you can ask technical questions in the future. If you need ideas on how to meet people, take a look at my blog post, Top Ten Ways To Create Your Meet and Greet List For Summit.

Day Six – Tuesday

Tuesday was a relaxing day before the busyness of Summit. I started the day off with breakfast with Chris Yates. This is a rare pleasure. Chris and I met through the SQL Community on Twitter. We now blog together occasionally and I speak at the SQL Saturday in his hometown.

Before I took time to prep for my Wednesday morning presentation, my apartment mates and I went to the Starbucks where they roast all of their coffee beans. I had my first french press, and, I must say, I’m hooked. 2015 PASS Summit2-001

After I was done with my presentation preparation, it was time for the opening event for PASS Summit. This is a whirlwind event. I feel like a butterfly, fluttering from conversation to conversation. This year, I felt like a match maker too. I had three people from my company attending with me. I haven’t had that happen since the mid 90s! I enjoyed introducing them to a number of people.

I also did something different. I went looking for the lone 1st timers. The wallflowers who don’t know who to talk to. I found one. He was eating by himself near a wall, watching everyone by himself. I went up to him, introduced myself, and got to know him. I then asked if there was someone he wanted to meet. He wanted to meet Pinal Dave from India. Pinal and I know of each other, but we’ve never had the pleasure of shaking each other’s hands. So that’s what we did. Luckily, Pinal was standing nearby. Pinal and I finally shook hands, and I introduced him to the 1st timer. That made me very happy. Win!

2015 PASS Summit14

Day Seven – Wednesday

This was my day <deep breaths inserted here>. I was first up to bat <deep breaths inserted here>. I’m nervous as I walk to my room. I see my room and I get calmer. I climb up on the stage and I get calmer. I get my laptop set up and I’m ready to go. Boom.

As you’ve probably noticed, I really believe in networking. My class filled up with plenty of time to spare before the presentation, so I did the same thing I did last year. I had everyone stand up and introduce themselves to their neighbors. I stood on stage with a huge grin on my face, then I stepped off stage and introduced myself to people in the front row.

200 hundred people came to hear my presentation on Sophisticated Techniques in SSRS. I enjoyed every minute of the session. We did have a couple of exciting moments in class though. There was a laptop on the table (not mine) that started beeping. I thoroughly enjoyed slamming nicely shutting the laptop lid. There was also a crash of dishes behind me in the hallway. I said “Opa!” in my head and kept on presenting.

2015 PASS Summit15

With my presentation done, I was able to enjoy classes the rest of the conference.

One class I took was not found in a classroom, but on the edge of a planter on the ground floor. I started talking with this guy (sorry I can’t recall his name, but he was awesome). He was telling me how he had submitted his session, but wasn’t selected. After he told me his topic, I told him I would have taken his class. His presentation was right up my alley. So he asked if I would like to see it. I said yes and we had a wonderful one-on-one discussion, complete with demos on his laptop. Right there. On the edge of a planter. It was my favorite class. THAT is one the many special things about PASS Summit.

One of the things I was really happy about this year was the change in how special diets were handled. In the past, they have always messed up my meals. I have a lot of food sensitivities, so I understand it’s difficult to accommodate my diet, but I still need to eat. This year, they had our names on our meals, and they had the more common special requests handled in the regular food line. (Thank you)

Day Eight – Thursday

I was invited again to live-blog the keynote on Thursday. You can see my comments here. I was very sad to hear that this would be the last keynote for PASS Summit given by David DeWitt and Rimma Nehme. I’m happy that I’ve been able to see two of their keynotes.

2015 PASS Summit Notes1

Before lunch, I thoroughly enjoyed directing people to lunch (I’ll tell you a secret. I think I enjoyed twirling my sign a little too much. Open-mouthed smile).

Thursday is the Women In Technology Day (WIT). WIT puts on a great lunch, and I was in the perfect place to direct people to the regular lunch and the WIT lunch. One of the traditions of WIT day is for men and women to wear kilts. This tradition was started by Grant Fritchey many years ago. This was my fourth year participating.

2015 PASS Summit16

Day Nine – Friday

Friday is full of sessions and bitter sweet goodbyes. Now, I don’t want you to think I only had fun this week. I did attend sessions. I have proof. I also took notes.

2015 PASS Summit17

 

… And I had fun.

 

2015 PASS Summit9

Summit is not complete without at least one night hanging out with Jason Strate at Bush Garden participating in SQL Karaoke. I was super happy to make it there multiple times this year. Last year, I wasn’t able to go at all due to all the commitments I had at Summit.

2015 PASS Summit8

Day Ten – Saturday

Saturday was a vacation day. I spent the day with my Australian friends. These are friends that I’ve made through Summit. I even worked with a some of them for a few months. In the evening, Julie Koesmarno and I threw a dinner party for a few of our friends who were still in town. We were even able to invite some friends we met this year.

2015 PASS Summit19-001

Day Eleven – Sunday

Sunday I had to come back to reality on my flight home. While I had an amazing time in Seattle speaking, learning, networking, and reconnecting, I was happy to see my family and they were happy to see me.

LucybyVictoria

Top 10 Ways To Create Your Meet and Greet List For Summit?

There was a time not to long ago when I didn’t know how to meet others in my profession. I didn’t know about SQL Saturdays or PASS Summit. The conferences that I was starting to attend were full of people who really didn’t want to network. But I’m persistent. I was determined to find a community of professionals who wanted to network. Then I found my first SQL Saturday and I fell in love with the community that PASS helps create the world over. I now have friends on almost every major continent? (Are there any SQL professionals in Antarctica? If so, I want to meet you.)

Who you gonna meet?

As each PASS Summit approaches, I make a list. I check it twice. And I decide who I’m going to meet. My question to you is, who are you going to meet at PASS Summit (or at your next SQL Event)? How do you decide who you want to meet? If you are looking for ideas on how to make your list, and you should have a list, keep reading.

My Top Ten List of How I Pick People to Meet

 

1. Set up a Twitter account to get to know the #SQLFamily community

The first thing I did before my very first Summit was creating a Twitter account. The SQL community has a huge presence there and it is a great place to get to know people from all over the world. You’ll not only connect with other individuals, but you’ll also see tweets of links to great articles that people share. You’ll also have a place to ask others how they solved the problems you are now facing through the hashtag, #SQLHelp.

I had several people on my first “Meet and Greet” list whom I had met this way. One of them was Ed Watson, whom I’m still friends with.

IMG_3221We chatted on twitter often. It was great meeting Anil in person.

Note: I recommend reading this before acquiring your first Twitter account. http://www.brentozar.com/twitter/book/

2. Consider the bloggers you follow

I checked out the list of blogs that I read and compared the authors to the list of attendees to see if any of the bloggers I knew were attending.

1651Ola Hallengran is known for his maintenance scripts.
We connected at a karaoke bar.

 

3. Ask the people in your local community if they are attending

They will be able to introduce you to other people during the event. I met several people at the SQL Saturday in San Diego who were also going to Summit. They were happy to introduce me to people at the various events we attended.

723I know Phil from the local user groups.
He helped introduce me to other people.

4. Consider the speakers of the sessions you are attending

As you determine which of the sessions you want to attend, read up on the instructors. They all have small bios on the PASS Summit site. You can also check out their blogs. If they have something in common with you, or if they really helped shape your career, then add them to your list. Just don’t make your entire list out of the speakers. You need variety.

IMG_3245

Jes Borland is an amazing speaker.
I’m so happy I’ve gotten to know her.

Note: I would recommend introducing yourself to speakers you want to meet at various after parties, during lunch, or as you see them in the halls. They are usually super busy right before their sessions setting up and right after their sessions answering questions.
But wait! There’s more!

Those are the easy ways to create a list before the event. But don’t stop building your list after the event starts. The list you bring with you is just the beginning. Keep reading to find out how to add to your list during the event.

5. Go to the networking parties in the evenings

At PASS Summit, there is a Networking Party put on by Andy Warren and Steve Jones. GO TO IT. Sit with people you don’t know. I know I will be. This event is not a sponsored event. In other words, you need to pay for your food and drink, BUT the networking is free and encouraged. Register for it here.

When I went to my first one, I met the lovely Viki Harp. She introduced me to Wendy Pastrick who whisked me away to meet Pam Shaw. It was actually amazing that I ate any food at all. It was so much fun meeting new people.

MickeyAndJasonJason and I are connecting at the
Summit Networking event.

6. Sit with new people during breakfast and lunch

At my first Summit, I only knew the people I had met at my first SQL Saturday, and I was very determined not to eat a single meal by myself. So I didn’t. Every morning, I got on Twitter and asked if I could join anyone for breakfast at the Daily Grill. I used the hashtag #Summit2012 (this year it will be #Summit2015, obviously). And guess what. I never ate alone. This wonderful woman, Monica Rathburn asked me to join her almost every morning.

1143This was my last breakfast at my first Summit.
We started with four people.

7. Consider people in your sessions

Introduce yourself to people sitting around you before the session starts. Or strike up a conversation about the session with someone after the session has ended.

722Ritu and I connected when we realized
we kept attending the same sessions.

8. Hang out at the Community Zone at PASS Summit

This is a great place to meet people. Why? That is the purpose of the Community Zone. Usually there is a schedule for various groups of people to be in the community. So, if you really want to meet the Australians, then show up during the hour to hang out there. If you want to meet people from your own region, then come when they are scheduled to meet up in the Community Zone. (The best part is there are awesome bean bag chairs to sit in.)

1648Tjay and I ran into each other in
the Community Zone.

9. Attend the after parties

Attend as many after parties and other non SQL events as you can. Yes, quite a few of them have drinking, and that might be an issue for you, but not all of them do.  Here are some other events that have little to no drinking that are usually found at PASS Summit:

  • Running. That’s right, there is a large group of runners who get up when I’m still dreaming and go for a run. They usually have cool SQL shirts and Jes Borland is usually found leading the pack of SQL runners.
  • Board game night. Last year there were a couple of nights where people gathered around board games to talk and have fun.
  • PASS Prayers. This is a Christian group who meets for prayers and fellowship in one of the hotel lobbies in the morning, again when I’m still dreaming.
  • Photo walk: This is a great way to learn about Seattle, get a good walk outside, and get to know other SQL Photography lovers.

2014-11-07 21.39.38We were hanging out at an event in the evening.

Note: All the events that PASS knows about will be put on this page a few weeks before PASS Summit starts.

10. Attend other events that occur around PASS Summit

Last but not least, attend Redgate’s SQL in the City event on the Monday before PASS Summit. This is an amazing free event put on by Redgate. They have several speakers speaking on various topics. There’s also a free lunch and networking at the end of the event. When you are done, you can head over to the Networking dinner I mentioned in No. 5 above.

downloadSebastian Mein and I had a photo op with
the lovely Carly from Redgate visiting from the UK.

Hi. My name is…

One of the things you can do when you are talking to people is give them your card. Wait…You don’t have one? That is easily fixed. Vista Print is where I make mine and they always seem to have discounts. Since the card is about YOU and not your company, just put the contact info you are interested in sharing. I put my name, title, email, and a picture of myself.  My first year, I came home with 50 cards from other people. I wrote on the back where I met them. The following year I went through them and I was amazed at how many I still remembered and even interacted with through other SQL events and through social media (mostly Twitter).

Back to talking to people

So, you’ve got your list and you are standing in front of someone you wanted to meet. Now what?

If they’re not considered “famous”, then ask them if you could buy them a drink (coffee, soda, or bottled water works, too) or ask if they have time to meet in the Community Zone to chat. The Community Zone is usually full of awesome bean bag chairs to sit and talk in.  Tell them you wanted to meet them to talk about xyz, and xyz doesn’t have to be about SQL. Maybe you both enjoy art, or learning about Whiskey distilleries. (Oh wait, that’s me.)

2014KY4ChrisYatesChris and I will be reconnecting over breakfast this year.

If you consider them “famous”, thank them for writing/speaking/inspiring. If they have time to talk, tell them about yourself and maybe ask them a question about SQL.

What if you are shy or an introverted?

You can still make connections. You only really need to make one strong connection. It’s ok if it takes more than one Summit to develop. I have SQL Family friends that are shy/introverted. I make sure they go out to some of the events and are having a good time. I help with making introductions for them to make connections with people on their “Meet and Greet” list.

Anecdote: My first Summit I met someone who was shy. We saw each other again our second Summit, but it wasn’t until our third Summit when we developed a stronger connection. I know it was hard for them, but they called me and asked if they could go with me to an event. They weren’t comfortable going by themselves. I was happy to go to the event together. I was also happy to introduce them to other people in the community. I’m really looking forward to spending more time with them this Summit.
Follow the White Rabbit

In the end, it’s all about making connections. If Neo hadn’t followed the white rabbit, he wouldn’t have met Trinity who took him to Morpheus. These connections are not just for the yearly PASS Summit. They are there for as long as you nurture them. Some of the people I’ve met, I only see at Summit, some I see four or five times a year, and some I talk to every day through social media and Skype. These connections help remind me that I’m not crazy when the App Devs tell me that Foreign Keys are bad, and they help me when the poo hits the fan and I need to restore data in a way I’ve never done before. Finally, they are there for me because we are all part of this huge community, affectionately dubbed “SQL Family”.

Questions Answered From Presentation: SSRS 101 Creating Reports for Diagnostic Data

On Tuesday, July 7th, 2015, I had the privilege of speaking for the DBA Fundamentals Virtual Chapter. Thanks to the 375 who attended and to all who will be watching the recording. While I can’t read the comments during my presentation (way too distracting), I did enjoy reading the questions and comments that were sent to me. Below you will find answers to the questions I was sent.

Questions Answered

1. Are you talking about SSRS 2016 or 2014?

I was presenting using SSRS 2012, but the demos and discussions applied to versions 2008 through 2014.

2. When should you use SSRS RDL vs. RDLC (Remote Definition Language Client-side)?  Should you be able to invoke RDL from a web app (say web forms, asp.net) just as easily as RDLC?

The difference is RDLC reports will run on the client side. They don’t need to access the report server. RDL reports can be invoked on the client side, but they are rendered on the report server then delivered to the client. The RDLC reports would be part of an application and will take up client resources to generate.

3a. What is the difference between SSRS and Reports Application in Visual Studio 2010 (full version)?

3b.Why use SSDT over Visual Studio?

I liked both of these questions, and they have the same answer, so I wanted to group them together.

SSRS (SQL Server Reporting Services) files are generated in two applications, Report Builder and Visual Studio. Report Builder exists on the Reporting Server called Report Manager. Visual Studio is the Integrated development environment (IDE) that you write code in. Microsoft created (at least) two distributables for Visual Studio. The one that Application Developers, is only known as Visual Studio and the one that comes with SQL Server called SQL Server Data Tools (SSDT). The IDE is the same for both of them. When they co-exist on the same computer, they will be integrated. If you only have the SSDT version, then you can only create SSRS, SSIS, and SSAS projects. You can also download over project types for Visual Studio, such as PowerShell projects.

Note: For SQL Server 2008 it was called Business Intelligence Development Studio (BIDS). It was renamed to SSDT in version 2012.

3a. Is it better to use SSDT or Report Builder? I have been using Report Builder for my reports so far…

The answer to this depends on your comfort level for creating reports. Report Builder is meant for the power users in the business. SSDT is meant for developers. I’ve personally never used report builder, but that’s because I’ve been writing reports since before Report Builder was introduced. If you continue to create reports in Report Builder, then take a look into generating Report Parts. Those were introduced in SQL 2012 and are supposed to be “building blocks” to help create more complicated reports within Report Builder.

4. If you don’t have the source of the report can you get it back from the web

I am happy to tell you, yes! (and there was much rejoicing!)

In the Report Manager, pull down the menu to the right of the report and select download. You can also go into the properties of the report and see the download option in the toolbar for that report.

Answers201507_01

5. How did you link the two datasets?

The two datasets weren’t linked like you would see writing a JOIN statement. They were filtered using the same parameter. Datasets can’t be joined together within the report. There are a couple of functions introduced in SQL 2008 R2 that allow you to reference a single value from another data set, but that is as close as they have gotten.

6. How is the security configured within the data sources ? How can the double-hop authentication issue be avoided when accessing data from multiple servers ?

You have 4 different security options that you can use. Books online can tell you the details on each of them.

Answers201507_02

I personally use a SQL Server login, only used for reports to access the data. I then use windows authentication to access the reports themselves. Each data source can connect to a different server and even use a different SQL Server account.

I’m not sure I’m answering your question adequately since it can be read a couple of different ways. If you are looking for a solution to a double-hop authentication problem, then take a look at this article. I’ve faced this once, but it was about 5 years ago. The Double-Hop Authentication Problem

Send me an email if you still have questions on this topic.

7. I saw that you saved the password for the data source (during development). How can we ensure that it is encrypted when deployed to the server?

I would have to do some research to see how the password is sent during the publishing event, but I do know it is stored encrypted.  In fact, the Connection String, UserName, and Password are all encrypted. Go into the ReportServer database and run this query: SELECT * FROM ReportServer.dbo.DataSource.

If you are still concerned about the Data Source during the publishing event, then you can create the data source manually in Report Manager. As long as the SSRS project has the property “OverwriteDataSources” set to false, the data source will remain the one you manually created.

8. What if you want to show multiple databases (in your report)?

You can create as many data sources as you need and they can each point to a different database on a different SQL Server.

If you are gathering diagnostic data, like we were doing in the demo, then you might want to consider a two step approach. The first step would be to have an SSIS package or a PowerShell script retrieve the diagnostic data from each server on a schedule and save the data to a central database. Then you can have the report pull the diagnostic data from that central database.

9. Can hidden parameters have values passed to them, for example: can a hidden parameter list containing state names have “CA” passed to it when the report is opened?

Yes. That would be done in the parameter properties on the report in the Report Manager, from code, or from another report. You can also create Linked Reports in the Report Manager and change the values of the hidden parameters.

10. Is there any difference between previewing the report than just running it by r-clicking on the report?

Not really.

11. Can you set border style in your template? So it’s not there at all?

Yes. If you were to save a new tablix in the template with the borders set to None for each row. Then you would copy that tablix for each tablix you need. Unfortunately you can’t modify the actual tools in the toolbox.

12. Any strategy to fine tune multiple drop down option/filter in SSRS report.

Yes.

1. Make sure that the Queries that are populating the drop down lists are FAST.

2. If the drop down lists are filtering each other, known as cascading parameters, then have them filter within the report, instead of making a round trip back to SQL Server for the filtering. This would be done by applying the filter to the 2nd parameter in the filter property instead of attaching it to the parameter property.

3. Make sure that the predicates of each of the queries and the final data set have proper indexes.

4. If the queries that the drop down lists are using are really slow due to too many JOINs, then consider using an SSIS package to create a static table that is updated every X minutes/hours with the latest data. Then use that table for the parameter lists.

13. This is not the question you are looking for. Move along. Move along.

14. How to display a message in the report if the dataset doesn’t return any records ?

This is an excellent question and the answer is not used enough.

Set the NoRowsMessage property on the tablix (Table/Matrix/List) control. You can get to it through the property window. The value of this property is displayed when there is no data to show. The value can even be an expression.

15. How to get a big report to limit to 4000 records but well distributed in terms of days in a month?

I would love to have more information on why you have this requirement. It is an interesting one. Here is what you can do. Note: It will slow the query down though, so make sure you have good indexes on the predicates.

1. I have 1 million rows of random dates and numbers. This happens to be a very narrow table.

Answers201507_03

2. I use the ROW_NUMBER() function. It will give a sequential number to each row based on the partition. I partitioned the data based on Year, Month, Day. If your date field does not have time, then you can partition based on the date field. Within each partition, the data is sorted by Date then TestDataID (the PK). This will help guarantee the same order each time.

3. I determined how many days were in the range of dates I’m selecting from, divided 4000 by that number, and select only those row number values per date. This will provide an even distribution across days.

4. Since you can’t guarantee that the number of days will divide evenly into 4000, you need to either have less than 4000 rows returned, or more than 4000 returned by adding 1 to the number created in step 3.

Answers201507_04


–1 million rows
DECLARE @StartDate AS DATE = ‘1/1/1972’;
DECLARE @EndDate AS DATE = ‘1/1/1973’;

SELECT DATEDIFF(DAY,@StartDate, @EndDate) AS NumberOfDays;
WITH cte_ranking
AS
(
SELECT TOP 100 PERCENT
TestDataID
,SomeDate
,SomeTextNumber AS SomeNumber
,ROW_NUMBER() OVER (PARTITION BY dateyear, datemonth, dateday ORDER BY datevalue, TestDataID) AS RN
FROM
DemoProgramming.dbo.TestData AS td
JOIN dbo.DimDate AS dd ON td.SomeDate = dd.DateValue
WHERE
td.SomeDate >= @StartDate
AND td.SomeDate <= @EndDate
ORDER BY datevalue
)
SELECT
cte.TestDataID
,cte.SomeDate
,cte.SomeNumber
FROM
cte_ranking AS cte
WHERE
cte.RN <= (4000/DATEDIFF(DAY,@StartDate, @EndDate)) + 1;

–Returned 4038 rows

[\SQL]

16. With subscriptions is it possible to make one subscription to use the current fiscal month to generate report vs. creating 12 subscriptions for each fiscal month?

Unfortunately no. You can do it based on calendar months, but not based on fiscal months where the first day of the fiscal month may not be the 1st day of the calendar month. I would love to see this feature.  If this is a must have requirement, then email me and we can talk about some “creative” solutions.

17. What are the different security roles on report server side?

The SSRS development team were kind enough to add the definitions on the security screen.

Answers201507_05

 

18. Can you append the date/time to the file name that gets created with a subscription that saves to a fileshare?

Yes. Add the @ExecutionTime parameter to the filename.

19. How do you set-up the email option for the subscription?

First your Report Manager needs to be setup to send email. I usually have my Sys Admin help me with that.

After that, it’s a matter of populating fields (and there was much rejoicing.)

If you use the standard subscription, then the setup screen will look like figure A below. If you are creating a data driven subscription, then the setup screen will look like figure B below. Either way, you need to set all the properties. Then select a schedule for the emails to go out on.

I would highly suggest using Active Directory groups for the To list. This make is easier to manage when people change jobs. You may also want the email to go to yourself for a few days or weeks to make sure it is going out as predicted.

figure A

Answers201507_06

figure B

Answers201507_07

 

Thanks for all the fish

I wanted to give a shout out to Glenn Berry for letting me use his diagnostic queries for my demos. You can find the full diagnostic script, per SQL Version here on Glenn’s website.

The downloads for this presentation are available on the DBA Fundamentals Meeting Archives page and will be available on my website under Resources shortly.

Questions Answered From Presentation (Part 2): Creating SSRS Reports Efficiently Through Best Practices

On Tuesday, March 3rd, 2015, I had the privilege of speaking for Pragmatic Works. For the month of March, they are highlighting Women in Tech and have the whole month lined up with female speakers on each Tuesday and Thursday. I was the first in the line up for the month and it ended up being my largest audience to date. There were 419 people listening in. I even saw some comments about there being others who couldn’t make it. Wow

Thanks to everyone who came to listen to my session. While I couldn’t hear anyone, I enjoyed reading the questions and comments that were sent to me afterwards, and let me tell you there were quite a few. Below you can find the answers to SECOND HALF of the questions that were asked. The first half of the questions can be found here.

Questions Answered

1. How do you make the report to see test database or production db or development db?

This is done by changing the data source properties. Say I have a database server called Monkey. I would create a shared data source called Monkey that each of the reports would share. On the Development SSRS Server, I would have the Monkey data source point to the development database. On the QA server, I would have the Monkey data source point to the QA database. And on the Production server, I would have the Monkey data source point to the production database. Each time the report is loaded into the new environment, it would automatically point to the correct database server.

If you are in a situation, where environments have to share a SSRS report manager (and I’m sorry if you are in that situation), then I would create a separate parent folder for each environment. For instance a parent folder called Development and a parent folder called QA. In each of these parent folders, I would create a folder for all your shared data sources for that environment. Each parent folder would have its own copy of the report and its own shared data source called Monkey pointing to the correct database.

2. When copyrighting, does your company need to be registered with the copyright office?

The answer to this question should come from your legal department. Through my experience, the answer is no. Copyright laws are different than patent laws. When I was an artist, the fact I made the artwork first, meant I owned the copyright. Even in another country.

3. Query for getting reports that have not been run.


SELECT

c.ItemID

,c.Path

,c.Name

FROM

ReportServer.dbo.Catalog AS c

LEFT JOIN [ReportServer].[dbo].[ExecutionLog3] AS e ON c.[Path] = e.ItemPath

WHERE

e.ItemPath IS NULL

AND Path NOT LIKE '/Decommisioned Reports%' --Ignore folders I don't care about.

AND c.Type NOT IN (1, 5, 8) -- Ignore Folders, Data Sources, and Data Sets

ORDER BY

c.Path

,c.Name

4. How did you add the template file in Visual studio?

For SSRS 2012, I put the RDL, RSD, and RDS files in the following directory: C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies\ ProjectItems\ReportProject. The directory will be different for 2008 and 2014.

5. What if a company doesn’t have any procedure for gathering requirements and creating/keeping documentation?

This puts you in the driver position. I would suggest meeting with your manager to discuss the benefits of having requirements documentation and a standard way of maintaining the documentation. I would then outline a plan for starting on this new path. I would start with new reports, and slowly add in existing reports as they get modified.

Awesome quote from one of the attendees:
I ask “what the decision is going to be taken with the data from the report”

6. How do you know the width to fit on landscape and portrait nicely?

I like half inch margins. So I subtract the two half inch margins from the width of the landscape or portrait paper. That becomes the width of the base report. I also set the half inch margins in the Margin property of the Report.

7. Could you show an example of a Tracking Number?

Sure! If I were using an Excel spreadsheet to keep track of my reports, I would number all my reports starting with 1000. (Why 1000? I like starting with 4 digits. No other reason.) If I were using a product like Team Foundation Server (TFS), then I would have each report represented by a work item. In either case, say the report named National Sales Revenue was represented by the number 1500. 1500 would always represent the report National Sales Revenue.

The first time this report is successfully published to production, it would be given a “release number” of 00. So the tracking number would look like 01500.00. After modifications were made to the report and republished to production, it would get a new release number of 01. The new tracking number would look like 01500.01.

In the location where I store all my documents for my reports, the report National Sales Revenue, will be stored under the number 01500. I usually add the name of the report to the folder name so that it looks something like 01500 – National Sales Revenue. This allows for two things. One, you can create the folder before you have a permanent title. (The temporary folder name would just be 01500.) Two, if the title changes later for some reason, you don’t lose the documentation location. It’s still under 01500.

8. What methodology do you use to create the Tracking Number or is it automatically generated from TFS?

When I’m using TFS, it is automatically generated for me. TFS uses a single sequential string of numbers across all the work item types (task, bug, custom work item, etc.), so you may have a report with number 1500, and then the next report will be number 1583. I do put a leading zero or two in front of the number so that when it goes two 5 digits, my folders are still in the correct order.

9. How do you put in the watermark?

a. Click on the base report.

b. Go to the properties window.

c. Click and open the BackgroundImage property.

d. Set the BackgroundRepeat property to repeat.

e. Set the MIMEType property to the correct format.

f. If you are using an Embedded image, set the following properties.

i. Source = Embedded

ii. Value = iif(First(Fields!EnvironmentID.Value, “GetReportStandards”) <3,”DRAFT”,””)

g. If you are using an image from the database, set the following properties.

i. Source = Database

ii. Value =iif(First(Fields!EnvironmentID.Value, “GetReportStandards”) <3,First(Fields!Draft.Value, “GetReportStandards”),””)

h. If you are using an image from a server location, set the following properties.

i. Source = External

ii. Value =iif(First(Fields!EnvironmentID.Value, “GetReportStandards”) <3,”location of your image”,””)

10. If a report is setup as a subscription, how can I tell if the subscribers are actually using/accessing the report?

This is an excellent question. The ReportServer Database will tell us which reports were executed through a subscription, but it cannot tell us if they were used. I have some ideas on ways to still capture the information, but they would require research and extra layers of complexity. If you want to hear about these ideas, send me an email.

Awesome idea from one of the attendees:
Maybe we should add a “pixel tag” to the report so that hits a web server which can track access.

11. This is not the question you are looking for. Move along. Move along.

12. Is there any way to handle the row hight dynamically in Excel output where include the merged cells? (Can grow does work with merged cells)

I don’t know a definitive answer to this question, but my inclination is to say no. Dynamically changing row height/width outside of the auto grow properties, is usually not possible. I have found ways around this by providing multiple layouts of columns/rows and hiding the ones I don’t want.

13. How can you use a business glossary to develop your label and be consistent across reports?

This question is a big one. The simple answer, is you need buy in from the business to make it happen and discipline from the entire team to make it successful. I would suggest you look into the topics for Data Governance, Business Semantics glossary, and Conceptual Data Modeling. These areas are what would help create and drive the business glossary from the very beginning, so that by the time you are ready to create a report, the terms already exist.

If you can’t get buy in yet, or ever, then I would suggest creating your own business glossary. Excel would work and is easily searchable. Make sure it is stored where your whole team can update it and use it.

14. Any major changes to SSRS 2014?

No. You can see the changes from version to version here. Just select the version you want to look at from the “other versions” drop down at the top of the article.

15. Can you tell what format they placed it in? PDf Excel

The Format Field in the ExecutionLog3 view will give you that information. Here is an example looking at the rendered format of a subscription.


SELECT

ItemPath

,UserName

,RequestType

,[Format]

,ItemAction

,TimeStart

,TimeEnd

FROM

ReportServer.dbo.ExecutionLog3

WHERE

RequestType = 'subscription'

AND ItemAction = 'Render'

16. How long did it take you to build this framework?

It took me a couple of years. I started by tracking down all the reports and entering them in an Excel spreadsheet. My manager wanted a more permanent solution that could be shared with others, so I was given time to create a custom Report Work Item in TFS. I then moved the whole catalog there and decommissioned the Excel based catalog.

For the requirements and design document, I slowly saw a pattern of questions that I kept asking, so I designed a requirements document and tweaked it over time.

For my report templates, I looked at existing reports. I took the consistent layouts I found, filled in some things I felt were missing like showing the parameters, and created the templates. They stayed pretty consistent over the years.

17. Is there a way to find out the limit of data export to Excel? We used to have reports which we can view, but time out when exporting to Excel. The limit is per size of other Excel or number of rows?

The limit will be how long it takes to do the export. There is a time limit and it if the export is not accomplished in that time frame, then it will time out. This can be changed with settings in the Report Manager. As far as the amount of data Excel can take, I would Google that, since different versions have different limits.

18. What’s better to use stored procedure or embedded SQL query and how to determine that?

This is an excellent question. This is also a highly debatable discussion, so this is my stance on the topic.

I prefer stored procedures for the following reasons:

  • Stored procedures are easier to modify when they need to be optimized. It is easier to track down a stored procedure in the cache plan than an inline SQL Statement in a report. You also only need to deploy the updated stored procedure back to production, not the report.
  • Dynamic queries are more likely to be classified as an “Adhoc query” by SQL Server. Depending how your SQL Server properties are set, the Execution Plan may never be kept in the cache, which means you will always have the overhead of creating the best Execution Plan.
  • On the reverse, if your SQL Server properties are set to handle Adhoc queries, then you can cause unnecessary bloating in your cache plan since every embedded query with different parameter values will be seen as a different query. That is not true of Stored Procedures….unless they have dynamic SQL inside them.

19. Would you recommend some sort of standards for Data Source Names?

I’m in favor of standards for everything. It satisfies my OCD needs. I keep all my data sources in one folder called either “Data Sources” or “DataSources”, depending if you like spaces in names. For the names, my data source names are the same name as the database they represent. If you have multiple databases with the same name in your production environment, then I would suggest adding the server name to the data source name. You want the Data Source names to represent what they are pointing to.

Name data sets the same way. I use the name of the stored procedure as the name of the data set. If you have the same stored procedure name in different Schemas, then I would include the Schema name in the data set name.

20. How to deploy the SSRS reports using TFS auto build?

I haven’t had the pleasure of using this feature. I unfortunately had to write a deployment document so that the report(s) could be deployed manually.

21. Can you mention the bug again with 2008 R2 with concatenated fields in excel prints?

Sure. In the footer of the report, I had a single textbox that had multiple fields in it. When the report was exported as an Excel document, the textbox was created in the footer of the Excel document, but the font size had been increased to 72 PT font. This bug did not occur on all of our servers.

22. All these best practices are good. I would like to see some more of practical on how to create hierarchies and links and other stuff, maybe in next webinar.

Please, please, please email me what you would like to see in sessions. I am always looking for ideas.

I have an example of a way to handle a hierarchy in my other Pragmatic Works session called: Scalable SSRS Reports Achieved Through the Powerful Tablix.

You can look at my recorded presentations page to see a list of presentations that have been recorded.

You can also look on my 2015 Speaking Engagements to see where I will be speaking or hope to be speaking this year.

23. No Russian Thank you? 🙁

Спасибо

Thanks for all the fish

Thanks go out to everyone who attended the presentation and for the patience of those who waited two weeks for me to answer the second half of the questions.

This is me sitting at a local café with my oldest daughter while I write, she studies, and we both drink flat white coffee. (This week she was working on an essay about Frankenstein and an exegetical essay for another class.  I’ll stick to SQL and SSRS.)
Mar 2015 Victoria and Me at Cafe

Questions Answered From Presentation (Part 1): Creating SSRS Reports Efficiently Through Best Practices

This past Tuesday, March 3rd, 2015, I had the privilege of speaking for Pragmatic Works. For the month of March, they are highlighting Women in Tech and have the whole month lined up with female speakers on each Tuesday and Thursday. I was the first in the line up for the month and it ended up being my largest audience to date. There were 419 people listening in. I even saw some comments about there being others who couldn’t make it. Wow

Thanks to everyone who came to listen to my session. While I couldn’t hear anyone, I enjoyed reading the questions and comments that were sent to me afterwards, and let me tell you there were quite a few. Below you can find the answers to HALF the questions that were asked. The other half of the questions will be answered next weekend.

Questions Answered

1. The number one question that was asked was: Will your documents, like your requirements document, be available?

Yes! You can find all the assets to the presentation under the Resources\Presentation menu on my website. I list all my presentation on this page, so just scroll to the correct presentation title.

I did get some comments about my SSRS templates missing. That has been fixed. Here are the three separate downloads:

If you missed the presentation, don’t worry. Pragmatic Works will be adding it to their catalog of past presentations here.

2. Hey, can you please share us that sample database?

I’m not sure which sample database this was pertaining to. Please email me.

3.  Would you give that mock up tool again?

It’s called Balsamiq. They are a wonderful company with a great user support forum. You can find tons of templates on their website and get your feet wet with a trial version.

4. Do you have any scripting that can be used to see what subscriptions are set up using all the various parameters?

I broke this question up into two questions because I wasn’t clear if you wanted existing subscriptions or executed subscriptions.

Executed

The ExecutionLog3 view in the report server database will have this information for you. You can filter the data on the RequestType field to show you only subscriptions. The Format field will tell you how it was rendered and the Parameters field will tell you what all the parameters were set too. The down side, is they use special escape characters, so you’ll have to decipher it.


DECLARE
@StartDate DATETIME = '03/1/2015'
,@EndDate DATETIME = '04/5/2015'

SELECT
InstanceName
,ItemPath
,UserName
,RequestType
,ItemAction
,TimeStart
,Status
,Format
,Parameters
FROM
ReportServer.dbo.ExecutionLog3
WHERE
ItemPath NOT LIKE '/DataSets/%'
AND TimeStart >= @StartDate
AND TimeStart <= @EndDate
AND RequestType = 'Subscription'
AND ItemPath <> 'Unknown'

Existing  

For existing subscriptions, you can look in the Subscriptions table in the ReportServer database. This lists the subscriptions that are set up, how they will be rendered, and the parameter values that will be used as well as other information.

5. Is there is way to bulk change the owner of a subscription? We have people leave and then when their account is deleted, the subscriptions stop working.

I’ve been asked this before. The information is stored in the ReportServer database, but I am always cautious about directly modifying this database. So , if you change it, then test it heavily  in development before applying the changes to production.

To help with this problem in the future, I would recommend a new standard. All subscriptions should be directed to Active Directory distribution lists. Even if the distribution list has only one person in it. I try to use existing distribution lists, but when I can’t, then I work with the Active Directory Admin (or whoever wears that hat). You could make a distribution list for a specific title, position, or category of people. I also made sure the description on the distribution list was filled out and included something like, “Do not delete this distribution list. It is used for subscriptions.”

6. What tool do you use for your report catalog?

There are several ways to create a report catalog. I started with an Excel document. After I outgrew that, I ended up creating a custom work item in TFS (Team Foundation Server). This allowed me to catalog the report, keep track of the workflow as the report went through its life cycle, and associate work items and bugs to the report.

Here is a blog post by Ted Gustaf on creating custom Workflows in TFS 2010.

7. Don’t you think that its better to get the Requirements and have them sign off on That ?? Prior to a Mock Up ?

Yes, it would save us some work too. Unfortunately, I found that the end users have a hard time verbalizing what they actually want. I find that the changes they request after seeing the mock-ups are not normally based on scope creep, but on the way they verbalized their needs when they initially gave them. This can be due to them not understanding what they really want, to them, assuming we know their data as well as they do. By creating the mockup, they have something tangible to look at. It’s analogous to telling a realtor that you want a three bedroom house, but the first one they show you is not what you were looking for. Now that you’ve seen the house, you can better articulate what you really want in a house.

8. Did you discuss the data quality requirements with your customers?

This is a very good question, and a delicate one. Some customers are very glad you bring this question up, and some customers assume all the data is accurate and will become concerned with past reports if they think the data is “dirty”.

Here are some scenarios and how I handled them. 

  • I was replacing an existing report that had bad data. This is an example of a delicate situation. My manager ended up coming with me to the meeting and backing me up. I showed samples of raw data under both reports and explained why the data was inaccurate before. Always save the explanation and data in an easy to locate place because they will be back asking again why a previous report is so different than a current report.
  • I had to aggregate data based on key phrases in a text field. This is an example where the customer is expecting to have a dialog about the quality of the data found. I spent some time looking at the raw data that did not match her phrases exactly and provided recommendations on additional phrases.
  • One of the requirements I was given for a report was based on ranges of numbers. For this report, I approached my client about the data I found that did not fall into the ranges he was expecting. In this case, it uncovered the need to validate the data when it was entered, so the client was grateful.

The bottom line is, be careful how and to whom you bring this question up to. It is a very important question and should always be addressed. It just may need to be addressed by your team instead of with the client, directly with the client, or in a very subtle manner with the client.

9. How do you place the Tablix color palette in the template form? And, could you provide, in your blog, info about Microsoft’s suggested methods of maintaining tracking info.

Below are the steps for creating a “color palette” on a new template. For your second question, I don’t know if there is an article from Microsoft for best practices on tracking info. In next weeks post, I have examples of how I did it.

Steps for adding your own color palette to a template

  • Add a table control (Tablix) to the body of a new report.
  • Remove the header row.
  • Make sure there is a column for each color in your color palate.
  • Make the width of each cell 0.2 in.
  • Change the background property of each cell to the name or hexadecimal value of your colors.

DeGraeve.com is a really cool website that can be used to create a color palette from an image.

Awesome quote from one of the attendees:
A formal report request process gives the requestor an opportunity to think closely about what is really wanted. Does not force it to happen, but increases the likelihood that a good and complete request will be done.

10. What do you use for Version Control?

I have used both Team Foundation Server (TFS) by Microsoft and Subversion, which is an open source product. There are a few versions of Subversion out there. I used the Tortoise version.

For maintaining my SQL source code easily, I also use Redgate’s SQL Source Control product. It’s like a bridge between your repository (TFS, Subversion, etc.) and SSMS. I am happy to talk to anyone on Redgate’s products. Just send me an email. (No, I don’t work for them. I’m just addicted to their products.)

11. Is it better to use Visual Studios of Report Builder?

Better is not the word I would use. More Powerful is the adjective I would use. Report Builder only has a subset of features that you have in Visual Studio. It was designed to empower “power users” and to provide “self service BI solutions”. If you are designing reports all the time, I would highly recommend using Visual Studio. To see the power of what you can do in Visual Studio, take a look at my Scalable SSRS Reports Achieved Through the Powerful Tablix. It’s recorded on Pragmatic Works website.

Here’s some additional terminology for you with regards to the different versions of Visual Studio. The version that comes with SQL Server is all that you need to design reports. For SQL Server 2012 and 2014 it’s called SQL Server Data Tools and commonly referred to as SSDT. For SQL Server 2008 and 2008R2 it was called Business Intelligence Development Studio and commonly referred to as BIDS.

12. What’s the major difference of standalone SSRS and the SSRS on SharePoint server?

I have never worked with the SharePoint version of SSRS. From what I know, the major difference is how the metadata for the management of the reports is stored in SQL Server. The SharePoint tables are used instead of the ReportServer database. I don’t think the creation of the reports is any different.

13. Can excel templates be imported or easily developed so that if I export to excel it looks like the data came from excel?

I’m not aware of the ability to bring in an Excel templates as an SSRS template. If the user exports the report using the Excel render type instead of the CSV render type, then the report should look like it came from Excel. You might need to email me an image of what you are trying to accomplish for me to answer this question better.

Next weekend I will answer the rest of the questions from this presentation.

Thanks for all the fish

Thanks go out to Liz Hamilton at Pragmatic Works for moderating my presentation. She is wonderful to work with and I loved the movie trivia that she did prior to the presentation.

This is me sitting at a local café with my oldest daughter sorting, compiling, and answering questions. (I think she’s doing Latin homework. I’ll stick to SQL andSSRS.)Cafe201503

Summit 2014 Experience Or My Week With the Aussies

Summit may officially only have three daysfive days if you count the two pre-con days, but my “Summit 2014 Experience ” this year was two weeks long. How can that be? Well, I hung out with my favorite Aussies.

(I will apologize right here. This is a looong post. I created a timeline for those that want the short version.)PASSSummit2014Experience

JulieAndMickeyAtFrys#SQLAussieFamily comes to visit

Some of you may know that I’m really close friends with Julie Koesmarano and she lives way too far away. So I asked her to come visit a week before Summit. That is when my “Summit 2014 Experience” officially started, when Julie arrived on my doorstop. After she settled in, we headed out to lunch, and then shopping at Fry’s for adapters. By Thursday (before Summit) two more Aussies came to visit, Martin Cairney and Ben McNamara.I hadn’t met Ben before, so he gets to be the official first networking opportunity of my “Summit 2014 Experience”. He’s also the one I spent the most time with during Summit. The next day we hopped on a plane heading to Portland and their SQL Saturday.

Portlandia

I’m born and raised in California. We drive everywhere. Aussies, on the other hand, do things differently, and I loved it. We didn’t stay in a hotel. We used Airbnb and stayed in an old Victorian parsonage. It was wonderful. We could have taken a taxi to the house we rented, but instead we took our suitcases on a tour of Portland via the light rail, the street cars, and our feet. We even picked our dinner location based on walking distance from our house. So much fun.

Since I don’t want to leave any of my “flat mates” out, we were joined late Friday night by the lovely Heidi Hastings, straight from Australia.

SQL Saturday #337 – Portland

I love SQL Saturdays. Attendees get to learn for free, I get to teach about the things I love, I get to meet new people, and to top it all off, I get to see and hug all my SQL Family. At this SQL Saturday, I gave my presentation called: Changing Your Habits to Improve the Performance of Your T-SQL.

SQLSatOregonSpeakerRoom

I found my favorite moments to be in the bathroom. I know, you’re scratching your head, but it’s true. While I was heading out of the restroom, I was stopped by one of the other speakers. She introduced herself to me and told me how she has been reading this very blog for the last two years and she has enjoyed watching me grow. Wow, that touched me. I’ve never been told that before. It made my day.

Believe it or not, my Aussie friends and I were the very LAST people to leave the event. Mostly because we needed to wait for a taxi and we waited to the last minute to call one. Once our taxi arrived, we headed for dinner. Someone had found a restaurant/bar that had whiskey called The Library. WOW! If you like whiskey, and you’re in Portland, you need to go there. They had ladders to get to the top shelves!

TheLibrary

IMG_3202Make sure you get reservations though. We had to wait 1.5 hours! Which we did at a wonderful restaurant called Cassidy’s. The food and company were wonderful. They even let me order a plate of bacon as an appetizer.

All aboard!

Sunday we needed to get to Seattle, so Neil Hambly joined our little Aussie Posse and we took a ride north on the train. This was a very relaxing train ride for Ben, Heidi, and Neil. Martin and I spent most of the time trouble shooting some domain controller issues on my laptop. In the end, Martin prevailed, and we all left the train happy. (And there was much rejoicing.)

TrainStation

 

IMG_3205Red Gate had a speaker dinner that night, just as I arrived into town at a wonderful restaurant called Tango’s. I have NINE different foods that I can’t eat. Annabel (from Red Gate), gave the chef my list of allergies, and they made me a custom meal. It was SO delightfully delicious. Here is a picture of what I considered my desert. A salad with fruit and BACON! Yum!

SQL in the City

IMG_3210Monday was spent with my favorite vendor, Red Gate. They put on an amazing free event called SQL in the City. I was honored to be a speaker for the 2nd year in a row. This year I had two presentations. I gave a lightning talk at the end of the day called: Finding the delta with SQL Compare and backups. I also gave a full session in the middle of the day called: Customize your faux test data with SQL Data Generator. I was very pleased with this presentation. I showcased a tool that isn’t talked about very often and demoed the new features that have been implemented over the last year or so, including the ability to use Python to better customize the faux test data. It was quite a bit of fun to present.

While I was wondering networking, I ran into John. The two of us participated in the First Timer’s dinner that was put on in 2012. He told me that he had been reading my blog ever since then and that he enjoyed watching me grow from a first timer at Summit to a First Timer Speaker at Summit.

Has Summit started yet?

No, Summit hasn’t officially started yet, but networking has. After networking throughout the day at SQL in the City, I had dinner with a couple of friends on our way to the beloved Tap House, whereI did more hugging and networking.

SSIS Pre-con and Mickey

Every day of this amazing “Summit 2014 Experience”, I was giving back to the SQL community. Either through speaking at an event or doing some other volunteer duty. But TuesdayTuesday was for me. I took a pre-con from Brian Knight and Devin Knight called: SSIS: Problem, Design, Solution. They are amazing presenters to watch, and I enjoyed the content I learned.

That evening I ran around with my head cut off trying to say hi (and hug) everyone I knew. I’m pretty sure I was still saying hi (and still hugging) people on Thursday that I hadn’t seen in a year. In fact, there are a couple of people I missed completely. That’s not too surprising, there were only 5000 people wandering around.

IMG_3221It was really fun getting to meet some of my Twitter friends whom I hadn’t met in person yet, like Andre Ranieri, Anil Mahadev, Annette Allen, and Adam Machanic. Now I have voices to go with the faces. (hhmmmMy data is skewed. All the people I listed have a first name that starts with “A”.)

Woot! First Day of the Summit

I started the day off at the Bloggers Table at the first Keynote. This was very exciting. You see, I was invited to live blog the keynote. I’m no Brent Ozar when it comes to live blogging, but I had a great time and I had at least one reader following the live blog.

After Julie and I had breakfast, I prepped for my very first Summit session, which was scheduled for Thursday. I’m so glad that I prepped mid Wednesday, because I was able to enjoy the rest of the afternoon and evening.

Which brings me to the last session of the day and the first session that I was able to attend. I took Mladen Prajdic’s  class called: SQL Server and Application Security for Developers. Anyone who has to write any inline SQL, should take his class. It was great.

I’ll just tell you right now. I networked EVERY single night. Why? Well, I got laid off right before I left for Summit. If you are going to get laid off, do it right before Summit, because it is the absolute BEST place to get the word out. I came home with several potential opportunities. I even gave up taking classes so that I could make deeper connections in the SQL community.

JessAndMickeyTHE BIG DAY

I was bummed to miss the Keynote on Thursday, but I don’t sit still well before a big presentation. I thought it was more important to have a good breakfast.

Since I was up first for the day, I went to my classroom forty minutes earlyand there were people already there! It gets better! I asked the room proctor if she could tell me how many people attended my session after it ended. She thought I was someone else, and started telling me about how many attendees they expected for my session. She told me that there were 232 people who added my session to their schedule and that I was on the “overflow watch list”. (Yeah, that made me a tad nervous.) That is a big class! Do you want to hear the best part??? I ended up with THREE HUNDRED AND SIXTY ONE attendees in my session. That’s 35% more than expected. There were only 12 empty seats. That’s more people than the number of students in my youngest daughter’s elementary school! Did I mention this was the FIRST time I was speaking at PASS Summit?

While those numbers still blow my mind, it wasn’t my favorite part of my session. Not even close. There were two other much more exciting events that happened in my session. The first, was the attendees themselves. They were engaged. They were so engaging that I ran a bit over. I felt bad about that, but my class was so interactive and I love that. The second happened during the six minutes prior to the session starting.

BenAndMickeyI was completely ready to start and didn’t know what to do with myself. There was a very low murmur in the room, but I could see many people just sitting there waiting for the session to start. So I turned my mic on and announced, “My favorite thing about Summit is the Networking. I want everyone of you to turn to the person on your left and the person on your right and introduce yourself. I will then know you have networked with at least two people today.”and the room exploded in conversation. I even introduced myself to two people in the front row. I then waited for my session to start with a huge grin on my face. I even had to quiet the room down when I started. Man that was awesome.

My Thursday is not done yet

I didn’t get to attend any sessions this day, because I was busy doing other things. I attended the Women in Technology lunch. I hung out in the community zone for an hour. And I was asked to live blog a Q & A session with the Executive Board. This was a way for the bloggers to ask questions of the Executive Board and blog about them before the Q & A session that was held on Friday. I was honored to be asked to participate. I really have a lot of respect for our Executive Board. It’s not easy pleasing the entire world of SQL professionals.

That evening I was invited to a special dinner put on by Red Gate for their Friends of Red Gate members. I’m mentioning it here, because of where we had dinner. We ate at a special restaurant called FareStart. This amazing restaurant helps the homeless get back on their feet by training them, housing them, and feeding them. All proceeds from the restaurant go back into the program. The chef and main waiting staff are all volunteers and there is even a waiting list to work there. I encourage you to follow the link and read about the restaurant. (This restaurant was also great about my food allergies. I had an AMAZING meal.)

ChrisJulieAndMickeyAnother great thing happened at the Friends of Red Gate dinner. I was able to introduce Chris Yates to Julie Koesmarano. The three of us, plus Jeffrey Verheul from The Netherlands all blog together under the hashtag, #SQLCoop. This was the first time that Chris and Julie had met in person. It’s the little things, like introductions, that make me happy.

And you know what I did after dinner. I networked… and celebrated this amazing day.

Friday already?

I actually was able to attend classes all day on Friday. I was quite happy about that. I was even able to attend Martin Cairney’s session called:Thinking Out of the Box: Manage SQL Server Using Built-in Tools . He had the very last session of summit and it was wonderful.

At lunch I lead one of the Birds of a Feather lunch tables in the discussion of Data Models. Our table was pretty full and we had a great discussion about someone’s data model challenges.

2014-11-07 21.39.38

Summit may officially end at 5:15 PM on Friday, but the “Summit 2014 Experience” doesn’t end until you’re buckled into the seat of an airplane, train, or car. (Hopefully, not a straight jacket.)

Two of my Aussie “flat mates” and I snuck off to a wonderful Lebanese restaurant for dinner. The food was great, but I could have done without the steep hills to get there. I guess it is a requirement to walk up and back down at least one steep hill while you are in Seattle. After dinner we went to a birthday party and finally ended up at… the Tap House where I continued to network.

Bittersweet

My “Summit 2014 Experience” started with the Aussies and ended with the Aussies. We all went out to breakfast one last time at the Daily Grill. It was a great breakfast, but still sad that I probably wouldn’t see these wonderful people for another year.

 

IMG_3270-003

Thanks for all the fish

I wish I could give a shout out to all the SQL Family members I spent time with, but there are far too many of you. I do want to thank everyone for being part of a fabulous community. It is a rarity in the computer programming world and we are all very fortunate to be part of it.

Demos Available For Techniques For Dyanice SSRS Reports Presentation from PASS Summit 2014

Thank you for all the attendees who choose my presentation at Summit 2014. I was really happy to see such a full and interactive class. You can find three downloads here.

  • The PowerPoint presentation is provided as a PDF
  • The two databases I used in conjunction with the AdventureWorks2008R2 database are provided in the SQL Server 2012 version.
  • The demos plus all the database and data scripts needed to create my demo databases.

Please send me an email if you have any questions.

Where in the SQL World is Mickey Stuewe

PASSFlag_thumb.jpg

PASSFlagI’m in countdown mode right now. Countdown to Halloween. Not because I get to dress up this Friday, but because I’m leaving for my favorite time of yearPASS Summit. This will be my first year speaking at Summit, but I’m getting ahead of myself. Let’s start at the beginning.

JulieAndMickeyMentorsOne of the most amazing things about Summit is the networking opportunities. These opportunities have the potential to start lifelong friendships. And that is where my post really needs to start, with my friend Julie Koesmarno. Julie and I were at most of the same events at my first Summit, but we didn’t get to know each other until after the Summit. Now she has flown in from Australia to spend a week at my place before we head out to Summit together. Thursday, two more Australian friends will be joining us here in Orange County before we fly up to Oregon together on Friday for SQL Saturday in Portland, where I’ll be presenting my Changing Your Habits to Improve the Performance of Your T-SQL session and taking as many classes as I can.

Sunday we’ll be taking a leisurely ride on a train up to Seattle. It will be the last day of relaxation before the madness of Summit related activities start.

SiTCMickeyMonday I will be spending the day hanging out with my favorite tool company, Red Gate. They are putting on a free one day conference called SQL in the City. This is an amazing day to network, eat great food, see amazing presentations, and end the day English style with a pint of beer. You heard me correctly. Red Gate is a British Company, and they always end SQL in the City with a pint of beer. The beer bottles have customized labels. Last year’s said “Query Hoptimizer”.

This year I have two presentations at SQL in the City. My first presentation is called Customize your faux test data with SQL Data Generator and focuses on how to generate test data. My second presentation is a lightning talk. I’ll be presenting on called Finding the delta with SQL Compare and backups.

Monday night I will be attending Andy Warren and Steve Jone’s networking party. (I hope to see you all there.) This is a great way to start off on the right foot at the Summit. You get to sit down to share a meal and network. I was introduced to many new people the first time I went, and yes, I remember quite a few of them. Tuesday is all about me. Ok, almost all about me. I signed up for an all-day precon on the topic of SSIS. I do have to step out in the afternoon to attend a chapter leader’s meeting though.

Then the even hits with Summit’s opening night event followed closely with several other events that I will be attending. (Sleeping has been rescheduled for later in November.)

On Wednesday, I’ve been invited to be at the bloggers table for the Day 1 Keynote. I’m very excited about this. So, if you are unable to be at the Summit this year and you want to know what is going on during the first Keynote presentation Wednesday morning at 8:15 am, then come hang out on my blog. It will automatically update until 9:45 am. Stay tuned to my Twitter feed as well, I might “practice” by live blogging one of the sessions on Saturday or Monday too.

PASS_Summit_2013_WIT-2Thursday is my big day. I will be presenting for the fourth time in a week, but it will be my very first time speaking at the PASS Summit. My presentation is entitled Techniques for Dynamic SSRS Reports. The presentation will be held in room 2AB at 10:45 am. Since it is Thursday, I’ll most likely be presenting in my Kilt. Many attendees wear kilts to celebrate Women In Technology (WIT). After I’m done with my presentation, I’ll be running over to the WIT lunch. The event has sold out, so I hope I can find a seat. Kimberly Bryant, founder of Black Girls CODE will be giving a keynote at the event. Thursday afternoon at 3:30 to 4:30 you’ll find me hanging out at the community center. (I hope you come by and say hi.) I’ll be there to network, answer questions about community, SQL, SSRS, and whatever else comes up. (All questions I can’t answer will be forwarded to Grant Fritchey since he has nothing to do until January when his first term on the PASS board starts.)

WendyMartinMickeyJuliePaulThursday night I hope to celebrate by singing SQLKaroake style at Busch Gardens, but you never know where I’ll end up. The evenings at the Summit are like being caught in a strong current. You never know where the evening will take you. The point is to enjoy the ride and meet new people.

Friday I’ll be getting my final learning on and attending the Birds of a Feather lunch. This is an event where you can sit with others to talk about specific SQL related topics. So, bring your questions and share a meal with like minded individuals.

Saturday I’ll be heading back to Orange County to give hugs to my family and my puppy before I crash and catch up on all my sleep.

Questions Answered From Presentation: Changing Your Habits to Improve the Performance of Your T-SQL

QuestionMark_127880048This past Tuesday, Oct 8th, 2014, I had the privilege of speaking for the DBA Fundamentals Virtual PASS Chapter. It was my biggest audience to date and the fourth biggest audience for the user group to date. There were 374 people listening in, and they weren’t all from the US. I was thrilled to see that I had at least one person listening in from the UK.

Since there were so many people on line, I couldn’t answer all the question…thus my post today. You can download the slide deck, demos, and my sample database. (All my presentations eventually find there way to my resources page, here.)

I will post the link to the presentation on this page when it becomes available.

Questions Answered

1.    What is the name of the Red Gate tool for source control you mentioned?

Red Gate’s tool is called SQL Source Control. It’s like a bridge between your source control repository and SSMS or Visual Studio. They have a trial version that you can download here

2.    What to do when you come into a team and there was no SQL coding standards before and we do not want to spend time re-writing everything?

I would first get buy in on why standards are important to have and outline the standards you want to use. Then I would slowly change the code. What I mean by this is, update the code as you make changes to it. For instance, you need to modify stored procedure XYZ, I would add the standards to only that stored procedure or even only the part of the stored procedure that you change.

3.    What version of SSMS are you using?

In the presentation I was using SQL 2012, but all the concepts I covered applied to SQL 2005, 2008 and 2008 R2. Even the Template Browser and Template Parameters I covered are available in those versions (Talk about a well kept secret!). Note: If you need the sample database in SQL 2008 or SQL 2008R2, please let me know and I’ll see what I can do.

4.    Can you give an example of commenting within a stored procedure?

Sure. You can comment using two different ways. You can comment at the end of a line using two dashes. You can also comment any where in the code using /* and */ to encompass the comment. This allows for comments to spend multiple lines.

When I’m adding comments about my changes, I will include the date, my name and brief comment. If I’m lucky to have a work item or bug tracker application to keep track of my work, then I’ll reference that number as well. Here is an example.


USE AWMonkey
GO
/* ***********************************************************************************
Purpose:    Returns list of Territory Groups.
Notes:
Sample:
EXEC List.GetTerritory
Author:     THEZOO\mstuewe
Date:       9/16/2014

Revision History
(Change Date)    (Author)        (Description of Change)
-----------------------------------------------------------------
10/10/2014        Mickey Stuewe    #2345 Added All to the list of values as requested
By end user.
************************************************************************************ */
ALTER PROCEDURE List.GetTerritory
AS
BEGIN TRY

SET NOCOUNT ON;

SELECT
st.TerritoryID
,[Group] AS CountryRegion
,st.Name AS Territory
FROM
AdventureWorks2008R2.Sales.SalesTerritory AS st
UNION ALL
SELECT
-1
,' All'
,' All'
ORDER BY
CountryRegion; --Added the ALL to the list.
END TRY
BEGIN CATCH

DECLARE
@ErrorMessage AS nvarchar(3000)
,@ErrorSeverity AS int;

SET @ErrorMessage = ISNULL(DB_NAME(DB_ID()) + N'.' + SCHEMA_NAME(SCHEMA_ID()) + N'.'

+ OBJECT_NAME(@@PROCID, DB_ID()), N'SQL Object Name Not Available')

+ N': Error: ' + CONVERT(nvarchar(10), ERROR_NUMBER()) + N' Line: ' + CONVERT(nvarchar(5), ERROR_LINE()) + N' - ' + ERROR_MESSAGE();

SET @ErrorSeverity = ERROR_SEVERITY();
RAISERROR(@ErrorMessage, @ErrorSeverity, 1);
END CATCH;

SET NOCOUNT OFF;

5.    Is there any configuration for the Template Browser? Can it integrate with any source control?

No configuration for the Template Browser is needed. Here are the generic steps to integrate your Template Browser folder with your source control repository. Each Source Control repository will be different, but these are the general steps.

1. Open the location of the Template Browser. Since I’m using SQL 2012, I found mine here:

C:\Users\<Your User Name>\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\Templates\Sql\<My Templates>

2. Import your templates into your source control repository.

3. Set your templates folder as the “working folder” location.

4. Test by getting the latest from your repository.

5. Connect the template browsers on your team’s computers the same way.

6. Let them know when you make changes to the templates so that they can do a “get latest” to download the changes to there computers.

6.    Is there any kind of Freeware repository tool?

Yes, Subversion is an open source product and widely used repository tool. It has two parts. The first part is the repository which resides in a common location, usually on the network so that everyone can use it. Then each user needs to have a subversion client tool to connect to the repository. There are many different clients to pick from. I use Tortoise. Why? It was suggested to me and recommended by Red Gate. Since I was going to use SQL Source Control by Red Gate to connect to subversion, I decided to stick with there recommendation.

7.    So, Row_Number() is non-deterministic?

Yes, that is correct.

8.    Is there any difference or improvements in using a CAST vs CONVERT or vice versa?

There isn’t a different in using the two functions, in terms of performance. As Jeff Joy kindly pointed out, the difference between the two comes down to compliance.

Jeff Joy – CAST is ANSI – compliant while CONVERT is Microsoft-specific casting function that builds in additional formatting functionality. If you may ever need to convert to another platform you need to write code that is cross-platform compliant, use CAST.

The downside of writing all of your code to ANSI compliant standards, is you miss out on quite a few rich features that Microsoft has added to the SQL language. These rich features will help SQL statements perform faster or make your life easier. 

9.    Isn’t CTEs much better than subqueries? This is in regards to readability?

Yes CTEs (Common Table Expressions) are much easier to read. There are even patterns that can be accomplished with CTEs that can’t be accomplished with sub queries, but CTEs aren’t always faster. I have had a few queries I’ve had to re-write with sub queries because I needed every millisecond of performance.

10.    Can I see the trace options selected?

Yes, here are the events I captured. I also isolated the database, to the demo database.

 

QuestionsAnswered20141010_Image1

11.    Is there a standards document about T-SQL Formatting?

I don’t have one I can give you. I can create a template for you if you would like. Just send me an email.

12.    Can you e-mail that URL to us?

I’m not sure which URLs this is referring to, so I’ve listed the URLs that were mentioned in my presentation.

13.    Is there better performance between CTE and Apply?

Comparing CTE and APPLY is like comparing apples and oranges. They are two different things. A CTE provides you the ability to create a query to be used within another query. APPLY is an operator like INNER JOIN, but it is specifically used to join table functions (in line and multi line) as well as sub queries and CTEs.

14.    On the comparison of temporary, table variables, and materialized tables…Why was the second execution plan eligible for auto-parameterization and the others were not?

This will take a little bit of investigation since I don’t know off the top of my head. I will post this one in the next week or so.

15.    What is the proper way to use DISTINCT with lots of columns in the SELECT without duplicate records? (Per our SDLC, avoiding using DISTINCT with lots of columns.)

The DISTINCT operator causes a SORT in the execution plan which is usually a very costly operator. Unfortunately sometimes you can’t avoid it. One thing that you can do, is see if there are other ways to filter the data so that there aren’t duplicate records. For example maybe there is a column that marks the latest record as active. You could filter on that field to provide uniqueness across the rest of the fields. Another example would be joining to another table that will help reduce the records, but not alter the data you need to return.

IMHO, your SDLC is trying to avoid unnecessary DISTINCT operators in the code. I had added that same requirements to my SDLC document when DISTINCT was being abused. I later added the comment, “If you can prove the need for the DISTINCT operator, then the use of DISINCT will be approved.”

16.    About predicate and especially LIKE, we know that to put predicate more efficient, we shouldn’t start with a wildcard, but if we need to return data that end by defining string, it exists a way to improve that?

See # 17 below.

17.    REVERSE string?

I wanted to answer 16 and 17 together. #18 is related, but I answered it separately. While all the string functions are deterministic, the particular pattern that needs to be implemented (looking at the end of string), does not allow it’s self to an efficient execution plan. This has to do with how indexes are built. The keys in the index are stored sorted. This means that finding records can only be efficient when looking from left to right, not right to left when comparing strings. You can see this by running the following sample on my demo database I provided.

I created an index on the field we are looking at, then I tried various techniques to get the last three characters. All three execution plans show an Index Scan.


USE DemoProgramming
GO

IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'dbo.TestData') AND name = N'IX_TestData_SomeDateText')
DROP INDEX IX_TestData_SomeDateText ON dbo.TestData WITH ( ONLINE = OFF )
GO
CREATE NONCLUSTERED INDEX IX_TestData_SomeDateText ON dbo.TestData
(
SomeDateText ASC
)

SELECT
SomeDateText
FROM
dbo.TestData AS td
WHERE
td.SomeDateText LIKE '%-09'

SELECT
SomeDateText
FROM
dbo.TestData AS td
WHERE
REVERSE(SomeDateText) LIKE '90-%'

SELECT
SomeDateText
FROM
dbo.TestData AS td
WHERE
LEFT(REVERSE(SomeDateText),3) = '90-'

When you run this sample, you do get an Index seek.


SELECT
SomeDateText
FROM
dbo.TestData AS td
WHERE
SomeDateText LIKE '2004%'

With all that said, if a particular column is constantly being split into separate parts to be searched, then I would consider storing those separate parts in fields in the database. That way they can be indexed and improve performance.

18.    Full Text index vs Like?

I have not had the opportunity to use or even investigate Full Text indexes, so I can’t comment on this at this time. (sorry)

19.    Is it ok to manually create tables in the temp DB? Would that be the same as creating materialized tables, but will be wiped out after a DB server restart?

Temp tables and global temp tables are tables with in the TempDB. As far as creating materialized or permanent tables within in TempDB, that is not a good idea. TempDB can already be burdened by all the other queries that use temp tables, table variables, and worktables. It would be better to create materialized tables outside of the TempDB.

20.    For the Template explorer…how do you share your templates to other users in SQL because when we create templates they seem to only be available to the user specifically that created them?

See Question #1.

21.    What’s the best hosting solution for MSSQL server standard/enterprise?

This is outside the scope of this session and an area I don’t have a lot of experience with.

22.    Does including Actual Execution plan also puts an overhead on the server?

No. The Actual Execution plan is what is created when a query is ran. The query itself can cause overhead, but not the plan. In fact, the engine allows a specific amount of time to find the best plan. If it can’t go through all the different possibilities, then it will do the best it can with the possibilities it ran through . You can see this in the execution plan. Click on the SELECT icon and look at the properties. For this query, it told me it found a “good enough plan”.

 

QuestionsAnswered20141010_Image2

 

If you want to get the last execution plan that was generated for a query, then you can get it using sys.dm_exec_query_plan. See how to use this DMV here.

Last but not least…

23.    What’s your favorite whisky?

Well in the immortal words of Grant Fritchey…that depends. It depends on where I am, what I’m eating, and how many people I’m with. (I know, that is a weird one. But I find I can handle the smokier Scotchs when I’m around a group a friends, but not when I’m home relaxing.) Also, I drink all my whiskey neat.

So, since I don’t have one favorite, I’ll answer the question by country in the order of my preference.

  • Ireland – I don’t think I’ve met an Irish whisky I didn’t like, but my favorite (right now) is Redbreast.
  • USA – I love 100% Rye whiskey! Mitcher’s Rye and Highwest Rendevous Rye are at the top of my list.
  • Scotland – I like the Fruity and Spicy Scotch’s from Speyside, with Oban being at the top of list.
But wait there is more

Mark Finch mentioned a free formatting tool. I wanted to include it in my summary since I had never heard of it. You can find it at poorsql.com.

Thanks for all the fish

I want to thank everyone for attending my presentation, as well as for all the encouraging feed back I received.

%d bloggers like this: