This post is a response to this month's T-SQL Tuesday #101 prompt by Jens Vestergaard. T-SQL Tuesday is a way for SQL Server bloggers to share ideas about different database and professional topics every month.

This month's topic is about what essential SQL Server related tools you use on a regular basis.

SQL Server Management Studio is an excellent tool for my day to day SQL Server developing needs.

However, sometimes I need to do things besides writing queries and managing server objects.  Below is a list of my three most used tools I use on a regular basis when working with SQL Server.

Watch this week's video on YouTube

1. WinMerge

Often I need to compare the bodies of two stored procedures, table definitions, etc... to find differences.

While there are some built-in tools for doing difference comparisons in Visual Studio and SSMS source control plugins, I prefer using the third-party open-source tool WinMerge:

The tool is a pretty straightforward difference checking tool, highlighting lines where the data between two files is different.

It has some other merge functions available in it, but honestly I keep it simple and use it to just look for differences between two pieces of text.

2. OnTopReplica

When on a single display, screen real estate is at a premium.  This is especially true if you are forced to use a projector that's limited to 1024x768 resolution...

OnTopReplica to the rescue!  This nifty open-source tool allows you to select a window and keep it open on top of all other windows.

This is great for when I want to reference some piece of code or text on screen while working in another window:

In addition to forcing a window open to stay on top, it allows you to crop and resize that window so only the relevant parts are visible.

The OnTopReplica view is live too - that means it's great to use as a magnifier on your SSMS result sets when presenting (instead of constantly having to zoom in and out with ZoomIt):

Look at those beautifully zoomed in results!

3. ScreenToGif

Sometimes explaining concepts with pictures is hard.  For example, wouldn't that last screenshot be way better if it was animated?

ScreenToGif is an open-source screen capture tool that does an excellent job compressing your recorded videos into gif animations.  It also allows editing individual frames, allowing the addition of text, graphics, and keyboard shortcuts.

This post is a response to this month's T-SQL Tuesday prompt created by Aaron Bertrand.  Adam Machanic created T-SQL Tuesday as a way for SQL users to share ideas about interesting topics every month.  This month's topic is "Your passions outside of SQL Server".

Getting away

I spend most of my days in front of a computer.

While this is great because I love technology, a few times a year I like separating myself from all forms of semiconductors. I do this by going camping.

Watch this week's video on YouTube

I highly recommend watching this week's video on YouTube - I was able to include way more photos and video clips there!

Camping is a mental reset where I can focus on activities that are completely opposite of what I do every day. It involves different challenges and skill sets, and provides a literal breath of fresh air when compared to working in a typical office environment.

How did I come to enjoy sleeping on the ground and learning to survive with only 20lbs of gear? Let me describe three distinct eras in my camping evolution.

I didn't grow up camping

High school Bert's first camping trip in Vermont

My family never went on camping trips. I always enjoyed the outdoors, but I never learned any basic outdoors skills like you might learn in scouts.

My friends during high school came from similar upbringings. We didn't know anything about building campfires or sleeping in tents, but we wanted to learn.

So, during the summer before college we decided we'd drive up to Vermont, rent a campsite at a state park, and learn to live outdoors.

While an immensely fun trip, our skills were lacking since we basically ended up eating ramen and sleeping in our cars (because our incorrectly set up tent gushed with rainwater during the middle of the night).

We kept at it though, going back every summer, until we managed to figure out how things should work. After a few trips we were no longer eating just ramen and not needing to sleep in our cars anymore.

Have tent, will travel

My outdoor skills improved as I continued to camp through my college years; I learned to cook food over coals, stay dry during the harshest rain storms, and light fires with a single match instead of half a can of camping stove fuel.

During this time I also realized that could travel almost anywhere and have my accommodations cost less than \$20 per night PER CAMPSITE.

I must have been thinking "the water is too clear and warm for this New Englander"

This meant my friends (and future wife Renee) spent college spring break camping through the Everglades and the Florida Keys. We spent our days kayaking, zip lining, and eating key lime pies before returning to our ocean front campsite for the evening. I think in all that trip cost us less than \$400 per person for 9 days of fun, including gas for the minivan.

Ultra cheap vacation to Yellowstone National Park

Ultra cheap vacations continued and I now graduated to cooking single pot meals and foil packet dinners. My car was still parked nearby but I wasn't having to sleep in it at night.

I even ventured back to Vermont and proposed to Renee there while camping (she said yes even after three days of no showers. That's when I knew she was a keeper).

Backpacking and gourmet food

Everything we need, carried in and carried out.

Camping eventually evolved into backpacking - instead of having a car a few feet away with a cooking stove and cooler of cold food, now I was learning about how to go into the wilderness for days with everything I would need carried in on my back.

Backpacking is truly exciting. You get to go and see places only accessible by a long hike on foot. The trips require more planning, both in terms of hiking routes, food preparation, water scouting, wildlife management, etc.... all things that invigorate my researching, detail oriented personality.

Living out of a backpack also means that you can easily fly to locations and still have a cheap vacation. I got to see beautiful National Parks like Yellowstone, the Grand Tetons, and Shenandoah all because I knew how to fit everything I need to survive into a carry-on and personal item (except for stove fuel, bear spray, and pocket knife ...some things you just have to buy on location).

Not only is visiting these places cool to begin with, but camping overnight in them means you get to see the park early before any car driving tourists arrive and you get to stay out way longer after they all leave the park to go grab dinner.

By this point my car is parked miles away from where I am sleeping and my food game has stepped up. Although I am limited to carrying everything I need in a pack, we frequently dine on pad thai, pizza, and cinnamon rolls.

After a long day spent hiking...

... these are the only foods I need to eat.

Who says you need to be roughing it while camping?

With only a few days left in 2017, I thought it would be fun to do a year in review post.  Below you'll find some of my top 5 favorites in a variety of SQL and non-SQL related categories.  Hope you enjoy and I'll see you with new content in 2018!

Watch this week's video on YouTube

Top 5 Blog Posts

"Top 5" is totally subjective here.  These aren't necessarily ordered by view counts, shares, or anything like that.  These just happen to be my personal favorites from this year.

• How NOLOCK Will Block Your Queries
• One SQL Cheat Code For Amazingly Fast JSON Queries
• XML vs JSON Shootout: Which Is Superior In SQL Server 2016?
• ʼ;ŚℇℒℇℂƮ *: How Unicode Homoglyphs Can Thwart Your Database Security
• Are Your Indexes Being Thwarted By Mismatched Datatypes?

Top 5 Vlogs

You guys watched over 500 hours of my  videos this year, thank you!

Watch this week's video on YouTube

Watch this week's video on YouTube

Watch this week's video on YouTube

Watch this week's video on YouTube

Watch this week's video on YouTube

I think the content of this last video is good, but I don't actually like anything else about it.  Why did I bother including it then?

It's the first SQL Server vlog I made this year.  I'm a little embarrassed by how bad it is, but I keep it up as motivation for myself to see how much I've improved since I started filming videos.

If you ever think you want to start doing something - just start doing it.  Keeping track of progress and watching how you evolve is extremely rewarding.

Top 5 Posts That Never Got Written

I keep a list of post ideas.  Here are 5 ideas I didn't get to this year.

I'm not ruling out ever writing them, but don't hold your breath.  If you are ambitious, feel free to steal them for yourself - just let me know when you do because I'd love to read them!

• "ZORK! in SQL" - I actually think it would be really fun to program one of my favorite text based games in SQL Server.  Don't get eaten by a grue!
• "How To Fly Under Your DBA's Radar" - this could really go either way: how to do sneaky things without your DBA knowing OR how to be a good SQL developer and not get in trouble with your DBA.
• "Geohashing in SQL Server" - Geohashes are really cool.  It'd be fun to write about how to create them in SQL Server (probably a CLR, but it might be able to be done with some crazy t-sql).
• "A SQL Magic Trick"- from the age of 12-18 I worked as a magician.  Sometimes I dream of teaching a SQL concept via a card trick.  Don't rule this one out.
• "Alexa DROP DATABASE"- write an Alexa skill to manage your Azure SQL instance.  I know this is technically feasible, I don't know how useful this would be.

Top 5 Tweets/Instagrams

I'm not a huge social media guy to begin with, but I do like sharing photos.

https://www.instagram.com/p/BTynN1gjTyj/

https://twitter.com/bertwagner/status/903712788538949633

https://twitter.com/bertwagner/status/894373268857212929

https://www.instagram.com/p/BP08U99j3Wh/

https://twitter.com/bertwagner/status/892717494166802432

Top 5 Catch-All

These are some of the random things that helped me get through the year.

• Red Bird Coffee.  This is premium coffee at affordable prices.  The Ethiopian Aricha is the best coffee I've ever had - tastes like red wine and chocolate.
• Vulfpeck's The Beautiful Game - I listened to this album more than any other to get into a working groove.  So funky.
• Pinpoint: How GPS Is Changing Technology, Culture, and Our Minds - This was probably my favorite book of the year.  If you ever wonder how GPS works, or the implications of a more connected world, this book was absolutely fascinating.
• LED Lighting Strips - I put these behind my computer monitors to create some nice lighting for filming, but I've found myself leaving them on all the time because they add a nice contrasting back light to my screens.
• BONUS! Mechanical Keyboard - This thing is inexpensive, but amazing.  I didn't realize what I was missing out until I started typing on it.  The sound of the clacking keys brought on an immediate flashback to the 1990s when I last had a mechanical keyboard.  I don't know if it allows me to type faster like many users claim, but I am definitely happier typing on it. CLACK! CLACK! CLACK!

This post is a response to this month's T-SQL Tuesday prompt created by Mala Mahadevan. T-SQL Tuesday was created by Adam Machanic and is a way for SQL users to share ideas about interesting topics. This month's topic is Setting Learning Goals for 2018.

Watch this week's video on YouTube

What do I want to learn in 2018?

Next year I'm going to strive to learn more about tasks usually reserved for a DBA - backups, replication, user and role permissions, etc...

As a developer, I am generally isolated from the day-to-day work of a DBA.  Not that I necessarily want to be responsible for a system's backups (and restorations!), but I do feel that I am missing out on a broader understanding of SQL Server by never having to perform these tasks myself.

I like being able to do-all-of-the-things, or at least know how to do them, because I think it makes me a better developer and a better architect of solutions.  By not having an expert knowledge of things like availability groups, I feel like it hurts me when it comes time to architect solutions.

The learning process

I like to learn by doing.  Since I'm not planning on becoming a DBA anytime soon, I'll need to create my own scenarios to learn how these different DBA specific features of SQL Server work.

This won't give me real-world experience, but for the most part I am ok with that - I'm trying to learn more details on the broader concepts rather than the nitty gritty of various real-world scenarios.

The best way I've found to learn by doing in this academic sense is through blogging.  I like blogging because it forces me to learn a concept well enough to be able to articulate it back easily to my readers (hi mom!).

Blogging forces you to create demos that point out features that are critical to understanding a demo.  If I can create a demo that recreates a real-world scenario, then my understanding of that problem in the real-world will be pretty good; at least way better than if I just read a book or MSDN article.

Implementation

Blogging is all good and fun, but the best way I've found to really understand a topic is to present it.  If you can teach someone else then you have a pretty good handle on the content.

Now, I'm not aspiring to give a 500-level talk about disaster-recovery planning, but I do think I can learn enough to present some 100-level topics to SQL Server beginners who are just a few books online articles behind me.

The best part about presenting is that you will have people smarter than you in the room with you.  Or at least people who have a different perspective about the topic than you.  These people will generally ask really good questions that...you don't know the answer to!

But there's no shame in saying you don't know the answer.  It's actually a wonderful opportunity - after the presentation, you can take your time and learn the solution.  Then blog about it so others can know the answer too.  Then incorporate into your future presentations.  It's a beautiful cycle.

SQLskills is giving away free training for their performance tuning and optimization classes. My entry for the competition is below. If you decide to enter for yourself, entries are due by 11:59 PM Pacific Time on Sunday, August 13th, 2017.

Watch this week's video on YouTube

I would love to attend the Immersion Event on Performance Tuning and Optimization — Part 1 training with Paul and Kimberly of SQLskills.

Why do I want to attend?

I want my brain to be filled past the brim with SQL Server internals and performance tuning knowledge. And I know this class will provide that opportunity.

I saw Kimberly present at PASS Summit in 2013. In 75 minutes, I had filled 4 pages of notes about skewed table data and how it affects statistics. I received great information that was immediately applicable to the queries I was working on back at the office.

I've never seen Paul present live, but I've been responsible for more than a few dozen hits to his blog posts on DBCC IND and DBCC PAGE. Whenever I have a question about SQL Server internals, I inevitably think "does Paul have a blog post on this topic?"

I've heard from others that IEPTO1 is amazing (and exhausting…in a good way!). I've learned so much from reading SQLskills team's blog posts, watching Pluralsight, and sitting in at SQL Saturday sessions that I am certain that I would thoroughly enjoy a week of intense training with Paul and Kim.

How would I use the knowledge?

By giving back to the community.

Every week I write blog posts and create videos teaching analysts, developers, and DBAs how to improve their SQL querying skills. I speak at local user groups, SQL Saturdays, and at conferences. I help coworkers and those on Twitter with solving their SQL problems.

By taking this training, I will advance my own technical understanding, which in turn helps me be a better SQL mentor.

My favorite type of performance tuning challenge

I love it when I can decode some of the "magic" that SQL Server is doing behind the scenes.

For example, recently when learning to work with JSON in SQL Server 2016, I was mystified by how SQL Server could quickly filter JSON data using a non-persisted computed column index. Was it truly parsing JSON on the fly or was it doing something else?

That curiosity led me to investigate further with DBCC PAGE. To my surprise, SQL Server really wasn't persisting my parsed JSON values on the data pages; it was however persisting the parsed JSON property on the index pages.

One more SQL Server mystery revealed.

Thank you.

Thank you for running this competition and giving people the opportunity to receive world-class training.

Additionally, thank you for all of the blog posts, newsletters (book reviews!), Pluralsight courses, and everything else you do to help the SQL community; I have benefited tremendously from all of these resources over the years.