PASS Summit 2015: My Experience

Where to start?  I’ve asked myself this question a lot during the Summit, and now that it’s a wrap, it’s hard to narrow down where to begin!  Looking back at my goal for the Summit, “to engage in the experience and breadth of the conference, and of course to soak up as much knowledge as possible”, I can say with absolutely certainty that I have far exceeded this goal.

First and foremost, I could probably fill a book with all that I learned during the conference, and to that end, I would like to thank the following people who’s sessions I attended:

  • Jason Horner (B|T)
  • Konstantin Melamud (T)
  • Brian Knight (B|T)
  • Chris Webb (B|T)
  • Neil Hambly (B|T)
  • Benjamin Nevarez (B|T)
  • Sandy Rivas
  • Andy Leonard (B|T)
  • Davide Mauri (B|T)
  • Tim Mitchell (B|T)
  • Borko Novakovic (T)

In addition to this list, I would also like to convey a special THANK YOU to my employer, Slalom Consulting, for supporting me on this trip.

Secondly, experiencing a technical conference like the Summit is un-matched to any other conference I have been to in the past.  I met many people from all walks of life and from very dynamic backgrounds.  The best part is that these people enjoy working with SQL Server and the Microsoft Data Platforms just as much, if not more, than I do.  This is truly inspiring and has re-invigorated my drive and passion for what I do in the data world everyday.  Furthermore, this experience has pushed me to become more active in the SQL community and to branch out my network of peers.  I hope to attend many local and virtual chapter meetings in the coming months and possibly present at a few.

Lastly, it was great to meet and talk to all the vendors that made such an event possible – Microsoft, Dell, and HP to name a few.  It was really cool to learn about the new products and tools coming down the pike that scale and extend SQL Server functionality.  Also, it was a real eye opener to see how many freebies they give away.  I got more than enough books, software, and flyers to keep me busy for a while.

All in all, this was by far the best technical conference I have attended.  Counting down the day till the next Summit!

Heading to PASS Summit 2015

At long last, after 9 years of working with SQL Server, I am finally fulfilling my goal of attending the largest SQL Server conference on the planet – the PASS Summit.  This has been a goal of mine since the very beginning of my database career.  Having the largest pool of sessions, experts, and networking events covering SQL Server under one roof is truly an awesome opportunity.

My main goal is to engage in the experience and breadth of the conference, and of course to soak up as much knowledge as possible.  As this is my first time at the Summit, I’m heading in with an open mind ready to take on anything.

See you at the Summit!

Handy Query Execution Shortcut

Recently I was doing a demo for a client that involved executing some T-SQL in Management Studio.  As I seamlessly talked and executed code, the client took notice of how I was executing the code on my laptop.  He asked, “I see you don’t use F5 to execute, so how are you executing your code?”.  I replied, “Alt-X”.  He was completely shocked, “That is really awesome and way better than using F5!”.

My reasoning for using Alt-X is twofold.  On the one side, I learned T-SQL using SQL Server 2000 and the hot-key option I used to execute code (as dictated by my Senior DBA at the time) was Alt-X.  Secondly, and more importantly, I don’t like having to always lift my left wrist to hit F5 to execute code.  Alt-X is perfectly situated so I do not even have to move my left arm.  Coding in this style has become quite fluid and natural as I have coded throughout the years.

This got me thinking – is Alt-X displayed in the Query menu of SSMS?  Strangely, it is not:


I’ll leave the exercise of figuring out why Alt-X is left out to you.

Which handy short-cut method do you use (if any)?

A Security Nugget

Security is always a massive topic to discuss within the SQL Server realm.  There a literally hundreds of articles, books, and webinars on the subject and each medium gets you deeper and deeper into the SQL security underground.  Many of these outlets provide detailed information on SQL Server’s wide array of security mechanisms (i.e. certificates, keys, TDE, and so on).  However, over the last couple of weeks I found myself in need of something more development/programming based than what is traditionally delivered in SQL Server Enterprise edition, for example.  To that end, I had to do some digging.

For my task, I had to create an encrypted identifier.  Programmatically, this is something that you usually do in a Comp Sci 101 with a random number generator library.  However, outside of the classroom nothing is that straight forward.  In the past, I would just take a GUID, cast it as a string, and store it in an “encrypted” column.

Simple and effective, right?  It would seem so in databases which are not internet facing.  The NEWID() function, which uses a combination of the server’s network MAC address and the system date-time stamp, would ensure no duplicates exist and return a long, complicated-looking series of letters and numbers.  Problem solved, you say?   Not for me.

My main issue with the above solution is that although the result may “appear” encrypted, it most certainly is not.  It really is nothing more than a reasonable facsimile of what an encryption output could look like.  I wanted something that was truly encrypted and could tie back to the original data in some foreign way.

To meet this challenge, I began looking at the simple HASHBYTES() function which is a standard function within SQL Server.  Given an encryption algorithm (MD5, SHA1, etc.) and an input, it will encrypt your input using the requested algorithm and return the encrypted result.

This would seem to solve all my problems with one big exception: the output was in varbinary – I need it in varchar.  A simple CAST or CONVERT proves ineffective as there is no explicit conversion for binary objects.  Seems like I’m going back to the drawing board, right?  Not quite!

Remember the digging I was doing earlier?  Well I uncovered a little, relatively unknown system function that is delivered in the SQL Server master database that you can use to convert varbinary objects to string objects:


I thought I hit the lottery!  After doing a quick test:

EUREKA!! Not only did this solution use an expressly designated encryption algorithm, but it also converted the resultant binary object into a string object to allow me to insert it into a string field.  Finding these atypical, odd functions is something akin to finding a $20 bill in the pocket of a pair of pants you just washed – priceless.

P.S. – There is also a sys.fn_varbintohexsubstring function that does the exact same thing as the one above except it will substring the output.

Goals for 2015

I’m not too keen on setting “resolutions” because it never really made sense to me.  Since I work in an environment that is heavily goal-oriented, I thought it best to set goals for 2015 instead.  This list is not all encompassing, but putting a small list out here might keep me better accountable for sticking to them.

So for 2015, here are my goals:

  • Contribute more to my local SQL Server and Database communities (NY/NJ)
  • Put more into my website (If you are a longtime follower, you will notice I’ve started over)
  • Earn some IT credentials
  • Get married!

Happy New Years!  Here’s to a great 2015!