Monthly Archives: April 2014

RedGate SQL Search Has What????

CollaborateImageThis week I am back at it with my SQLCoOp friends in sharing the goodness of a utility that has helped us all ~ RedGate’s SQL Search plug in for SSMS.  This utility has always been helpful in perusing through the schema of any SQL architecture in the search for the gems that we as data professional look for.

The Quest

The outlook was grim and bleak recently one day at the shop. I could recall a portion of a job name and step name for that matter but couldn’t find it for the life of me. The server I was on at the time had a vast amount of jobs on it which I will save for another day.

I will admit frustration started to set in and I started to just open up a query analyzer and start writing some code then it dawned on me…..I had just downloaded a new version of RedGate’s SQL Search Utility. I recalled from the version that they extended the search into SQL jobs; at that moment it was like a light bulb went off in the old noggin.

Now, if we are honest, we all like to work smarter not harder and save time along the way; with that said I decided just to crack this utility open and give it a shot.

Let’s Go

First thing I needed to do was fire up SQL Search inside SSMS. To do this and for the sake of explaining I will be showing this use case on my local, because well, we shouldn’t be showing the world our prod servers now should we! As you can see below firing up SQL Search is simple an easy. While on my local instance if I click on the SQL Search highlighted icon it will bring me to my SQL Search screen where I can then expound on my search criteria.

FiringUp

I had to define my search; I knew a phrase in my job but didn’t know the whole name….so with that said typing in what I knew and not selecting the exact match I knew would pull back the full job name; or so I thought.

 

Phrase

Now this is where it gets really good and makes the SQL Professor very happy. RedGate decided to go on and throw job search functionality into their updated utility. If you click on the All Object Type drop down you will see a Server Object called jobs. Simply go ahead and check that. Now for my use case I went on ahead and checked them all.

Jobs

So, what have we done up to this point is type in our phrase, ensure the jobs server object is check then it’s giddy up and go time. After letting the search occur in all databases I find the results I’m looking for:

JobResults

My job name pulled back along with the actual step detail. Whoa wait, I had no clue I’d get the detail with it as well. Hats off RedGate this is something that every Data Professional can utilize and is a tool that saves time. It took me less than a minute to do a quick search and pull the information that I required.

Like What You See?

Listen, this just is one avenue in which RedGate has enhanced this product. I’m an everyday Data Professional that goes into work everyday and wants to work efficiently. Check this product out for yourself here

Some of the other enhancements made are:

  • Search in jobs
  • Select multiple object types and databases to search in
  • Improved indexing
  • Search with % wildcards
  • Search with Boolean operators

I tell you what; check out what my other colleagues have to say on the matter and how they have benefited from this utility:

If you want to read more about the newly released SQL Search, don’t forget to check out these blog posts:

 

On a SQL Collaboration Quest

Four SQL professionals gathered from the four corners of the world to share their SQL knowledge with each other and with their readers: Mickey Stuewe from California, USA, Chris Yates from Kentucky, USA, Julie Koesmarno from Canberra, Australia, and Jeffrey Verheul from Rotterdam, The Netherlands. They invite you to join them on their quest as they ask each other questions and seek out the answers in this collaborative blog series. Along the way, they will also include other SQL professionals to join in the collaboration.

Advertisements

Communication – is it key?

Communication2Communication is easier now than it has ever been. Whenever we need to get in touch with somebody, we can call, e-mail, or text, regardless of where we are and where they are. Geography isn’t an issue; we can instantly send a message to somebody halfway around the world. Some people even text each other while they are in the same house – just because they don’t want to get up and go to another room. Without much effort, we are almost always “connected” if we want to be.

But have you thought about what we may be losing when we depend more on electronic contact and less on face-to-face communication? When we’re with others, we understand them much better. We can read facial expressions, pick up on emotional responses, and communicate our own feelings very clearly. The results is more interaction, more depth, and more substance.

The communication tools available to us today are wonderful. It’s great to be able to get in touch with people anywhere, anytime, and I wouldn’t want to go back to being unable to contact someone easily when I’m out of town or need a quick response on an urgent matter. I like being able to keep in touch with my family regardless of where they are. But when we start to depend on technology as a substitute for one-one-one time with each other, we are missing an important key to relationships. In forming virtual bonds, we may be forsaking the benefits of true human interaction. We need to make sure we are not so connected with everything out there that we miss the chance to connect with the people directly in front of us. Being connected means thinking of others and reaching out. Make that happen as often as you can.

Each person, and or leader, is different and has different methodologies and view points. One of the key components or attributes I have found in leadership is communication and having an open communication with other team members and business units. When was the last time you had effective communication in a meeting? When was the last time you had effective communication about an idea with a team member? Let’s take it a step further, when was the last time you had an open dialogue with your team? Building confidence and building a team takes a strong and discipline element composed of communication.

Don’t settle for the status quo; become an exceptional leader and/or an exceptional Data Professional. Be the game changer and motivator your team needs today.

Windows Functions; Who Knew?

CollaborateImage

SQL windows functions, it would seem, get lost in the shuffle most of the time when dealing with SQL. Why do you think that is? I honestly think that it is more or less developers, DBA’s, and data professionals are oblivious to some of the cool features that can be accomplished with its utilization. To put it simply windows functions could be viewed as looking in “windows” of your data. That is about as simple as an explanation as I received when I first started using these types of functions.

Example

Take the below table structure that I created on my local environment in SQL. My table name I created is called CustomerLog within that table I have two columns CustomerFirstName and CustomerLastName (represented below):

CustomerLog

For example(s) sake lets say I have a vast amount of data in this customer table and I want to partition it out and view just how many (categories) or windows I am looking at. This is where the windows function comes in nicely. With the below query I can find the results I am looking for pretty quick and easy:

Code

As you can garner with the above SQL windows function code you can derive how many customers share the same first letter in their name (result set below)

CustomerLogResult

Bottom Line

As you go through the SQL journey note that with any aggregate function being used you can turn that into a windows function by utilizing the over clause. You can only use windows functions though within the select or order by clause. This example only showed you how to partition using windows functions and only scratched the surface. It would behoove you to do some more research on what all you can do with SQL and window functions. Also, while you are at it; check out some of my colleagues as well

Mickey Stuewe: A Date At The End of The Month

Jeffrey Verheul: Write readable and high-performance queries with Window Functions

Julie Koesmarno: ABC Classification With SQL Server Window Function

Don’t sell yourself short. I guarantee once you start utilizing windows functions  you will want to use them everywhere. Just make sure you know the positives and negatives and as always just don’t blindly copy code from the net. Explore it, set something up on your Dev or VM sandbox. Failure to learn is not an option!

 

Growth In The Face Of Adversity

 

StumbleAdversity as described in Webster’s Dictionary is a state, condition, or instance of serious or continued difficulty or adverse fortune.

Not really a topic most want to talk about, but nonetheless is something we experience on any given day. There are all types of adversity if you think about it. The Data Professional might face adversity on the job, one may face adversity with their health, or one may have ongoing issues with family. Whatever the case may be adversity at some point in ones life will come.

How does the Data Professional handle adversity when it rears it’s ugly head?

You look at your board and you see over 20 initiatives waiting to get completed, a process on one of your SQL Servers is executing extremely slow and you need to figure out why, log shipping decided to croak and you need to ensure that you get this back up and operational, and to top it off your core business server decided to fail over to another node all of a sudden.

All that ever hit at once? If you’ve worked in this business long enough than you realize that adversity will present itself in some form or fashion ~ it’s inevitable. If you have not had to be faced with adversity before than you will at some point.

With all that said adversity does not have to be viewed as a negative occurrence. Looking back, in my own circumstances of being a Data Professional, I see that in some of these instances it is where I have grown and built the experience that I longed to obtain.

Get your learn on

Let me ask this question; when is the last time you ventured out and really dug in and started to learn something? If you had down to learn CMS or Policy Based Management would you depend on others to facilitate that or would you install it on a local machine or spin up a VM and start tinkering around with it and how it works?

In today’s work environment we have so many tools at our disposal; so many community members who are willing to help and offer advice. One of the best phrases I ever read stated, “Failure to learn is not an option”.

The Peaks and the Valleys

Careers can be a set of peaks and a set of valleys. Have you ever heard someone say, “I’m stuck in the same old mundane rut”. How do you break that? One great thing about being a data professional is that we have so much that goes on in a given day. If you really step back and look at it you can start your morning on SSIS, follow it up with some log shipping or disaster recovery planning, review capacity planning, heck why not even throw in some learning of Always On and column store indexes.

I’ve seen the gamut from professionals who are on fire, the flame starts to dwindle, they get burned out, and then you never see them anymore as they move on to other things. Will that be you?

The Opportunities

Now here is the great part that is just lying around the bend. Opportunities abound ~ daily. When adversity strikes don’t get faint hearted or weary. I have first hand knowledge that inspiration is rampant through the SQL Community. When we fall we pick each other up, when you least expect it someone that you works with SQL from overseas will say one encouraging word that will ignite that flame that was about to go out.

Yes, I know it won’t always be a bed of roses for this is the game called life, but how do you handle adversity when it comes your way. Tackle adversity head on, it is in times when you are faced with something great that character is built and learning abounds.

Game Time

It’s fourth quarter and game is on the line. It’s now time as a Data Professional to “D” up. Tackle every day as if it were your last and when you seem to faced with adversity whether it revolves around SQL, learning in general, Cubes, Azure or whatever the case may be – stop, assess, learn, and grow from it.

Be Challenged

Don’t let that flame burn out

Keep Pressing Onward