Archive for SQLMickey

| January 13, 2015
Comments Off on T-SQL Tuesday #62 – Primary Key Constraints Are Good For Your Database

T-SQL Tuesday #62 – Primary Key Constraints Are Good For Your Database

Robert Pearl (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.

This month’s invitation is about making sure your database is healthy. For me, that is not just making sure that patches are updated and backups restore properly. It also means the database schema is healthy.

In the beginning

One of the simplest things that we can do when creating a new table, is to make sure there is a primary key constraint. A primary key constraint is simply a specialized index. It can be a clustered or non-clustered index.  The important part is, it is created as a primary key constraint.

Why?

Two strong reasons come to mind on why you want to have primary key constraints on your tables. The first, is the accuracy of your data. The purpose of a primary key is to uniquely identify a record (just like your fingerprints). By using primary key constraints, SQL Server will guarantee that the primary keys are unique.

I’ve had application developers argue with me that the application will guarantee that the primary key is unique, so a primary key constraint is not needed. I’ve also had other developers tell me that using GUIDS or identities will guarantee uniqueness. Even if you use these data types or have checks in your application to verify the value is unique, it is still possible to go around the application to insert data and it is possible for multiple applications to simultaneously provide what they think is a unique value. A primary key constraint can will prevent duplication.

This does bring me to the second reason. Say, you do a have a spectacular way to keep all of your primary keys unique. Without primary key constraints, you can’t implement Transactional Replication. That’s right. Transactional Replication requires primary key constraints.

How can I find my straying tables?

Now that I’ve (hopefully) peeked your curiosity, let’s look at a way to find a list of tables that are missing primary key constraints.

SELECT
     t.name AS TableName
FROM
     sys.tables AS t
     LEFT JOIN sys.indexes AS i ON i.object_id = t.object_id
          AND is_primary_key = 1
WHERE
     is_primary_key IS NULL
ORDER BY
     t.name

Remember it is not a good idea to “just add” new constraints without determining the impact on the current system. Once you have the list of tables that are missing primary key constraints, you can go through the process of determining if you can add them into the environment without side affects. Some side affects can include having to deal with duplicate data, increase database size by the addition of new indexes, and better performance.

Thanks for all the fish

Thanks go out to Robert Pearl for hosting this month’s T-SQL Tuesday blog party.

Category: Data Modeling, Indexes, T-SQL, T-SQL Tuesday | Tags: , , , ,
| December 31, 2014
Comments Off on Reflections in the 2014 Mirror

Reflections in the 2014 Mirror

DSC03551-BWLast year I read an article about setting goals that are measurable. So I did just that. Instead of having a goal of speaking at more SQL Saturdays than the previous year, my goal was to speak at 7 SQL Saturdays. While I only spoke at 6 SQL Saturdays this year, I did deliver 9 SQL Saturday presentations. I would consider that a completed goal.

Level up

Here are some other achievements for this year.

  1. I received the Best New Community Voice award from Red Gate.
  2. I delivered 25 presentations to roughly 1,740 people across 20 physical and virtual events.
  3. I founded the BIG PASS user group in January for Orange County, California and have been joined by Rob Hatton (l) over the summer as my co-leader.
  4. Chris Yates, Jeffrey Verheul, Julie Koesmarno and I started a collaborative blogging series called SQLCoOp.
  5. I had the honor of being part of the Friends of Red Gate program for a second year, speaking at SQL in The City for a second year and getting to work very closely with the SQL Prompt team. (Yes, they are my favorite team too.)
  6. I wrote 31 posts this year that were viewed in 116 different countries. This happens to be the same number of posts my friend Jeffrey Verheul (b|t) had this year. So I am publicly throwing down a challenge to him to see which of us will come out of 2015 with more posts.
  7. At PASS Summit I networked, live-blogged two events, hosted a Birds of a Feather table, networked, hung out at the Community Zone, networked and the feather in my cap this year…
  8. I gave my first presentation at PASS Summit to a full room of 361 people. My favorite part was not the number of people, but how engaged they were. That made my day.
IMG_3197-001I raise my glass to my friends

We never accomplish anything in a vacuum, so I want to thank my friends who had the biggest impact on my life this year. There are many that are not listed, because if I did, this would be a very long post.

  1. Ben McNamara (t)
  2. Jes Borland (b|t)
  3. Chris Yates (b|t)
  4. Jeffrey Verheul (b|t)
  5. Julie Koesmarno (b|t)
  6. Brian Moran (b|t)
  7. Ted Krueger (b|t)
  8. Grant Fritchey (b|t)
  9. Tim Ford (b|t)
  10. Jason Horner (t)
  11. Argenis Fernandez (b|t)
  12. Red Gate (b|t) staff – Specifically Carly Harding, Aaron Low, and David Priddle
My next adventure

What will 2015 hold? I have some ideas and I’m setting some new goals, but only time will tell. I’ll see everyone on the flip side. Happy New Year!

Category: Community, Reflections | Tags: , , ,
| November 19, 2014
3 comments

SELECT Thankfulness FROM Person.Person

ThankYou500Hi. I’m with Bob

I love how blog posts can cascade into each other. I follow Chris Yates’ (b|t) blog. He wrote a post that started with Bob Pusateri (b|t). And here I am joining the blog party. Hopefully you too will join the blog party and write about something you are thankful for.

I’m thankful for…

I’m thankful for a lot and not appreciative of enough. One of the non-SQL things that I’ve learned more about is the world. Some of my SQL Friends have gotten to where they are at the hard way, through the school of hard knox. Every time I hear another story, it makes me think of how lucky I have been. I am very thankful for the sobering stories.

I’m thankful for my Holistic Nutritionist, Kristi Acuna (b). Without her, I could not be an active member in the SQL Community. I started seeing her in 2009. She immediately found foods that I was eating that my body did not function well on. Things like gluten, tomatoes, potatoes, etc. As I slowly took those foods out of my diet, I started to have more energy. Enough energy to start things like blogging on the weekends, attending SQL Saturdays, speaking at conferences, and staying up late to network after the conferences. Without her, you wouldn’t know me.

I’m thankful for my husband, Dan. He supports me and encourages me to be me. For some of you that may seem obvious. Why would he not allow me to be me? But not all are so lucky. I know men and women a like who participate in local SQL Saturdays only, or in user groups occasionally because their spouse needs them at home. Dan encourages me to write. He encourages me to lead my user group. He encourages me to present at conferences. In fact, when I was really bummed about work this past summer. His first question was, “When do you see your SQL Family again?” How cool is that?! He knows I’m an extrovert and he makes sure I get that interaction.

I’m thankful for this AMAZING SQL Community we call SQL Family. The first half of my career I was a Visual Basic programmer. VB 3.0 through VB.Net. I left IT twice because of the way I was treated by co-workers. I came back kicking and screaming. I was blessed to land in the world of SQL and to find an amazing community who accepts, supports, and encourages each other.

Down to the details

I want to thank some specific people, but some of them are private people. So I’m going to send out some digital “thank you cards”. I encourage you to do the same. Say thank you to the people who have touched your lives and made you who you are today.

Thanks for all the fish

Thanks go out to Bob Pusateri (b|t) for this great idea.

Category: Community, Reflections, SQL Family, T-SQL | Tags: ,
| November 17, 2014
3 comments

Summit 2014 Experience Or My Week With the Aussies

Summit may officially only have three days…five 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, where…I 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 Tuesday…Tuesday 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. (hhmmm…My 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 early…and 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.

Category: Community, PASS Summit, Red Gate, SQL Family, SQL PASS, SQL Saturday | Tags: , , , , , , , , ,
| November 13, 2014
One comment

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.

Category: PASS Summit, Presentation Resources, Speaking, SSRS | Tags: , ,
| November 11, 2014
2 comments

T-SQL Tuesday #60 – Stretching Past My Comfort Zone

My good friend Chris Yates (b|t) is hosting this month’s T-SQL Tuesday blog party. The party was started by Adam Machanic (b|t) in December of 2009.

This month’s invitation is about something we’ve learned recently. Being that I just got back from Summit, I wasn’t quite sure what to pick. I ended up picking something prior to Summit. I learned how to recover from my Transaction Log filling up the disk via our SQL Community.

Silly rabbit, #SQLHelp is not just for kids

Hashtags have a WIDE variety of uses. They are used to express emotions, categorize tweets, to follow global discussions, and even to state the obvious. But my favorite use is to get help from our community with the use of #SQLHelp, #SSRSHelp, and others. And this is where my story begins… Freaking out and going immediately to #SQLHelp.

I am not a DBA. I’m a BI girl. I’m a Data Modeler. I’m a Database Developer. But please, oh please, don’t ask me to troubleshoot a serious problem on a failover cluster. That is not my cup of tea… and that is exactly what I found myself doing one Wednesday afternoon.

Before the internet, I would have been completely helpless. Today I’m not. Why? Because I have an entire community behind me. One that does not think twice before lending a hand. I posted a request on twitter requesting assistance for my problem and I included the #SQLHelp hashtag. Less than a minute passed before I had TWO community members offering to help. Wow! (I did take the help of only one of them, since it would have been confusing to have them both help.) He was kind enough to answer my questions and walk me through the solution over the course of a few hours. That is what I call Community. That is what I call #SQLFamily.

Earning my DBA card

I now know how to fix this problem. I even had the “opportunity” to try out my new knowledge a few weeks later due to some deadlocks and the lack of email notification setup on the our monitoring software. (Don’t ask.)

Lesson 1

I thought that a full backup also took care of the transactions stored in the Transaction Log. This is false. In order to have your Transaction Log truncated, you have to create Transaction Log backups. (Lesson Learned)

Lesson 2

Once the disk is full, you can’t make a backup of the transaction log. To get around this, you add a new, tiny, empty log file to the database. I don’t know the technical reasoning, but this will allow a backup to be created, thus truncating the very full log file. After which you can shrink the really big original log file to a reasonable size. Finally, you can remove the tiny, empty log file that you temporarily created.

When troubleshooting a log file that has filled up a disk on a Failover Cluster, you can only create a new log file on a shared resource. I had none. Luckily, I had a brand new database on that server. The database had been released into production, but was not used. Yet I was able to remove it from the server and create a new transaction log in its place. (And there was much rejoicing.)

Lesson 3

Fast forward to SQL Saturday Portland, right before Summit. I took a class from Randy Knight (b|t). His presentation was called Locks, Blocks, and Deadlocks Oh My! In his session he spoke about how the transaction log can get filled during a deadlock (been there, done that, got the T-shirt). He suggested creating some fake files on the drive to reserve space for when that awful day comes and your disk is full. I had been wondering if that would be a good solution. Not only did he confirm that it was, but he gave a great way to quickly create five 1G files that can be removed as needed to get the server back on its feet.

Conclusion

The moral of this story is not only to have Transaction Log Backups as well as Full Backups, but to also nurture your network. Your network is like gold. They are there to lift you up when you need it and to help you through technical challenges that are outside of your comfort zone.

Thanks for all the fish

Thanks go out to Chris Yates for hosting this month’s T-SQL Tuesday blog party. Thanks also go out to all the community members who quickly respond to people in need.

Category: Community, SQL Family, T-SQL, T-SQL Tuesday | Tags: , ,
| November 6, 2014
Comments Off on Blogger Q & A

Blogger Q & A

Category: T-SQL |
| November 5, 2014
One comment

PASS Summit 2014 Keynote – Evolving Microsoft’s Data Platform – The Journey to Cloud Continues

Category: T-SQL |
| November 3, 2014
Comments Off on SQL in the City Keynote – LIVE BLOG

SQL in the City Keynote – LIVE BLOG

Category: T-SQL |
| October 29, 2014
2 comments

Who You Gonna Meet?

SushiNightSummit is mere days away. One of the things I do each year is to figure out a short list of people I want to meet or get to know better. My list doesn’t include all the “SQL Celebrities” either. It includes people I’ve gotten to know through twitter, people whose questions I’ve answered when I’ve taught over the web, and people whose blogs I’ve come across. I make sure that I have some international people on my list as well. I think this is very important since the US is only one of 52 countries represented at Summit.

One of my favorite memories of my first summit was sitting at a table at midnight with people from around the world and around the country, talking about SQL.

So I challenge you. Who you gonna meet at Summit? You can go here to start compiling your personal list.

(The picture is of my good friends Maladen from Slovenia, Scott from Canada, Julie from Australia, Martin from Australia, and Father Jack from England. The pictures were taken at dinner at the 2013 PASS Summit.)

Category: T-SQL |
« Older Entries   Recent Entries »
%d bloggers like this: