Tag Archives: SQL Server

Windows Functions; Who Knew?

2 Replies

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!

 

Entering the SQL Matrix

1 Reply

MatrixI know why you are here and I know what you’ve been doing. You hardly sleep and why night after night you sit by the computer; you are looking for what they call ~ The SQL Knowledge. I know because I too have looked for the knowledge, and when that knowledge found me it told me I was not really looking for it; I was simply looking for an answer. It’s the question that drives SQL Data Professionals and is the question that brought you here……

The SQL Matrix is comprised of many components and with that comes SQL Data Professionals from all walks of life from all over the world. It has been amazing to see components of peoples skill set come together to provide different view points on various topics.

Well, I’m about to welcome everyone to the Desert of the Real where real SQL Data Professionals collaborate with one another on various topics related to SQL. What you will come to know as MCJJ (Mickey Stuewe, Chris Yates, Jeffrey Verheul, and Julie Koesmarno); you will find four distinct Data Professionals who seek to gain more knowledge inside the SQL Matrix and provide thoughts and real life scenarios into series of blog posts.

With that said I now must tell you; you have two choices:

  1. You can take the blue pill and go back to sitting in front of your computer all day long stuck in the mundane rut that happens to all of us
  2. You can take the red pill and join MCJJ on this collaboration effort and perhaps hopefully pick up some new and interesting SQL Tips along the way.

The option is yours what will you decide.

Tomorrow will mark our first collaboration; I’m happy to announce that it will be done on Red Gate’s Utility – SQL Search.

Check out my peers reviews tomorrow and also check out the others collaboration release posts.

Stay Tuned

Please visit the following links to see the unique views of my collaborators.

To follow our quest for SQL knowledge through this collaborative project, follow the #SQLCoOp tag on Twitter.

Renovations On The Way

Leave a reply

ProfessorWhat Is The SQL Corner

I attended my first PASS Summit back in 2011 in Seattle and quickly realized that I could be doing more for the community that what I was currently doing. When I came back I thought I could tackle the SQL world, leap tall buildings in a single bound, round up all the unnecessary bad practices and so on. The SQL Corner came about as a meeting place where people could come and gather to find solutions, present new techniques, and share some knowledge.

Growth

It has been an enjoyable three years in continuing to write for this blog and other avenues. I have realized that over time I enjoy being able to discuss what I may be going through and solutions found to issues that occur in an DBA’s life.

As time has progressed the community has seemed to welcome what The SQL Corner represented “Leadership Through Service”.

Time For A Change

With time, things change. In the latter part of last year I’ve developed a passion for writing more, sharing more in the community, and speaking to various groups. I started to see morphing The SQL Corner into something more; transitioning it over to what will become the SQL Professor.

Why The Change

The name change represents where I am at and what I’d like for the SQL Corner to become; a place for “continued learning”. In my 12+ SQL career one thing has always remained the same – there is always something to learn and always a skill set to work on. For me, I believe this is why I enjoy working with SQL so much. I enjoy coming into work everyday and the job of being a DBA. With being a DBA comes great responsibility and striving to continual learn new things is a passion that I hope will never burn out.

Upcoming

With all that said you will start to see some renovations being introduced over the next few months both from this blog standpoint, social media, etc. I’m thankful for the ride I’ve been on over the last few years and looking forward to what the new journey will hold.

Feedback is welcome

SQL Prompt What Is It Good For Absolutely…

Leave a reply

SOSEverything. If you’ve never had the opportunity to use Red Gates SQL prompt utility then you are missing out. Recently, I went without it for a day by some circumstances that I decided to create for myself. Of course these actions were not intentional but then it got me thinking somewhat on if I was stranded on a desert SQL island what would be the one utility I would want most in my survivor tool belt.

I posed this question over at SQL Brit’s Forum (if you haven’t been over there yet check it out; there is some good stuff going on over there) and got some really good responses back.

Back to the subject at hand, SQL Prompt, and why do I like it so much. I am a heavy Red Gate user; have their bundle and enjoy using all their utilities. Makes my job a lot easier; one of these is SQL Prompt.

What Is SQL Prompt?

SQL Prompt is an add-in for SSMS and Visual Studio, and according to Red Gate, has been designed to strip away the repetition of coding.

Features

There are many features about SQL Prompt but I won’t cover all of them. I want to focus on just a few that have helped me tremendously fight the good DBA fight.

Summarize Script Option – as you can see below the script that is in the query window for my test database called release I have 3 insert statements. If I had a very huge query I was working with, or perhaps even going through highlights of an SP I can get a quick summary of what I’m dealing with:

RedGate1

If you click on one of the Insert Scripts you will see that it is highlighted in your query window for quick review:

RedGate2

Just how do you get to this Summarize Script Option?  If you look at your toolbar inside SSMS you will see the SQLPrompt option available. From there it is as simple as selecting the summarize script feature:

RedGate3

Snippets – these are nice hot keys to allow you to get to code quicker. You can save your own snippets for frequently used code. For example some of the snippets that are quick to learn are

  • SSF which is select * from
  • DF which is delete from
  • CT which is create table
  • II which is insert into

RedGate4

The list and possibilities could go on but the mere fact of having this at my finger tips has cut down on a lot of coding and “repetition” that occurs on a daily basis.

Conclusion

What I’ve shared are just two features that SQL Prompt has to offer and we haven’t even begun to scratch the service. The more I think on it the more I believe I will be doing an in depth over view of my bundle. I believe I have features that I am not taking full advantage of that are at my disposal.

Just this utility alone has formatting short cuts, navigation short cuts (Summarize Script is Ctrl+B, Ctrl+S), Code-completion Shortcuts, Refactoring, and much more.

Check out SQLPrompt for more information at RedGates website.

What’s your go to utility? If you were stranded on a SQL Desert island what would you want to have with you that you could not survive without?

Change Sometimes Is A Good Thing

Leave a reply

Change2The past several weeks I have started to take inventory. Every Data Professional should do this whether it is a bi-monthly, quarterly, or yearly. Technically what comes to my mind is backups, security checks, what can be automated, what worked for you this year and what didn’t, etc.

With that said I came full circle back to this blog The SQL Corner. This adventure started in 2011 and I have shared this story with you many times over regarding the fire being lit after returning from PASS Summit (by the way if you have never been to one check into it; can be career changing hint hint). I started to look at this blog in a different light lately, something has been missing and I couldn’t quite put my finger on it.

One of my favorite posts I completed this past 2013 was The Fab Five; people who I looked up to in the industry; when going back and revisiting that post it dawned on me. Sure each one has their own style of writing; no one person is the same but they are all tied via one common thread – content.

2014

This year, 2014, has much promise. That statement in and of itself can be said each and every year. The hitch or catch is what will I make of it, or what will you make of it. I’ve become more focused in the SQL Community, being a SQL DBA day to day, and being simply a Data Professional than ever before. While 2013 was a great year I want 2014 to be an exceptional year.

With that said The SQL Corner will be getting a face lift here and there. The focus or theme for it this year is content. I want to focus on the message at hand and relay it to readers to the best of my ability all the while making it easy to perhaps get an answer to an underlying question that one might not have known the answer to, or better yet provoke thought into an issue that one can derive a solution.

Learning

I look back at 2013 and realize that perfection is just a word in our industry. Not one person is perfect. We all make mistakes, daily, however continuous learning is key. While content will be at the forefront I will also be sharing some learning aspects that I am going through as well.

A few things that have already come to mind are noted below:

Steve Jones came up with his Power Shell series which has challenged me to dive a bit more into Power Shell; I’ll be sharing some of my thoughts on that.

Grant Fritchey has spoken multiple times on Azure; to which I am going to dive into this year.

My own tool belt; I get asked often by people from all over what I have in my arsenal of attack to conquer issues within SQL.

Many more an exciting topics forthcoming.

Structuring

This year will provide much more structure to the blog. John Sansom brought to light to me the calendar idea for blogging which I plan to follow. A lot of times I pull from my One Note pool of ideas but structuring my posts I think will help me stream line a bit more along with providing that thought out content I am desiring to put forth for everyone.

**you can find this calendar idea and much more over at SQL Brit’s Forum; great content there. Why not register today and get your learn on

Conclusion

2014 is game time. I’d like to take The SQL Corner to a new level and for that matter my SQL Presence in the community. I challenge not just myself but you as well; what kind of year will you make it. Will you stay stagnant going with the status quo; or will the fire be lit and you start challenging yourself to step outside the comfort zone and box.

Let’s grow this community that is already a great one; heck lets blow the doors off and make it exceptional.

Thank you all for a great year; look forward to this year.

Making A Difference

3 Replies

MakeADifferenceA father was awoken by his wife to check on their son who was asleep but making noises that they could hear through his room monitor. The father, half a sleep and thinking his son was dreaming, wondered into his room and called out to his son only to find that his son wouldn’t answer him. The dad stumbled over to his son’s bed to find that his eyes were rolled back in his head and he was stiff as a board. Panic set in, the worst scenario for the parents was coming true. The son, who had type 1 diabetes, had gone into a diabetic shock – his blood sugar had dipped to low while sleeping. The father feeling for a pulse; scooped the son up and rushed him down stairs to apply an emergency Glucagon shot while the wife called for an ambulance.

Why am I sharing this you may ask? Simple, the father of that boy is me and from that night on he has been my hero which leads me into this article…….

I was fortunate and blessed to attend my first PASS SUMMIT in 2011. After getting passed the complete awesomeness and the many renowned speakers I found myself wanting to ask questions and kicking myself in the tail for not asking them. Come on, let’s be honest, you’ve been there with various things in your life. You don’t want to ask questions because you don’t want to feel not as smart, or you see someone who has years’ experience and think why would they want to waste time on what I think. I know I felt that same thing and then it all kind of just clicked in my head.

I got to thinking about my boy while at that PASS Summit and the inspiration he had given me that night, then sitting in a session and looking over and seeing Brent Ozar in the class actually learning (floored me; in my eyes this guy knows everything SQL related), or Paul Randal sitting in on a session a few seats away, or asking advice from Chris Shaw on a presentation he did on Utility Databases. I found out a few things at the Summit about myself that otherwise I would have stayed in my shell.

  • The people that you look up to in the industry are 9 times out of 10 the most down to earth people you would ever meet and are willing to offer advice if you ask.
  • Step out of that comfort zone; this blog was started based on attending the PASS Summit and thinking if I can help one person along my SQL journey as a DBA then it is all worth-while.
  • Speaking – never thought I’d do it but found out I truly enjoy it and helps me interact with a lot of faces and people.
  • Learn from the SQL Community as a whole; have you been to any forums, blogs on a regular schedule?
  • Not one question is a dumb question; everyone learns so go ahead and ask that question  you are hesitant about.
  • Hard work – it does pay off. Don’t short cut anything – dig in, dive in, and give it all you got.

If you have thought about stepping out and starting your own technical blog – DO IT

If you have thought about going up to someone and asking them for advice – DO IT

If you are at a conference and you have a question but the speaker is what you call a Master Jedi in our industry – DO IT

It takes one to make a difference in someone’s career, it takes one to make a difference to someone who might not yet be comfortable in asking the question to solve the problem, it takes one to encourage the person just starting out to get the most out of their potential.

For those of us who have been in the SQL industry for years; when is the last time we put away our ego’s and  helped the one starting out? Or maybe we have a wealth of  knowledge and need to step out and be a voice in the SQL Community.

It takes one ~ will you be that one?

Success Is Journey Not A……

1 Reply

I’ve ofSuccess or Failureten heard the quote “Success is not a journey, but a destination”. Over the course of the last few weeks that quote has taken on a life like form in many fashions. To name a few things that have come to light to make this quote a very real thing are the MCM retiring, having a VMAX overloaded by maintenance plans across the board, upcoming DR Testing, planned Power outages (better than non planned ones eh!).

All these things as I look back on them screams to me that Success is a journey, not a destination. For SQL Data Professionals our jobs are ever changing and growing as we grow with it. Each day is an opportunity to learn something within SQL that you or I didn’t know yesterday, each day presents it’s own opportunities to reach resolutions that you or I didn’t know yesterday, and each day represents that during our failures we can discover the true desire for success.

To the beginner who is starting out who doesn’t know about the sp_configure, the proper use of ORDER BY, or what Table Scan vs. Indexing; the answer is not to just ignore or give up. Dig in and discover what that solution is; it’s how we learn.

To the seasoned SQL Data Professional vet who has been putting off getting on the speaking circuit, taking a newbie under your wing and mentoring them, or simply are just comfortable; challenge yourself. Step outside that normal box and tackle new things head on.

When you have that replication issue that goes awry and you don’t have that big enough wrench to fix it (I use this analogy all the time thanks to Robert Davis a.k.a. SQLSoldier (B|T), stay the course and work your way through it.

I get asked a lot of questions when speaking, email traffic etc., where individuals feel defeated in the problems they are facing within SQL. Every problem has a solution; every solution is waiting to be found it’s up to us as SQL Data Professionals to buckle down and find the answer.

If you are not active in the community I’ve listed some of what I call top tier SQL Data Professionals on the right of this blog site that I follow who have helped me in my DBA career. Forums are also a great place to increase ones learning…to name a few you can check out these:

One last note; when is the last time you just said a simple thanks to someone who has helped you in your career? This past week that has weighed on my mind greatly. A former boss, a mentor, a person who you get newsfeeds from…..I fall in the boat where I get wrapped up in the work I do; I often overlook the simple thank you that in reality goes a long way.

Tackle obstacles, Tackle your fears, and Tackle the solution head one.

Back to Basics

Leave a reply

BasicsI can sum up this blog post in two words: The Basics

What do you mean?

I cannot be the only one in this same boat. There have been times where I, as a DBA, have overcomplicated resolutions when there was simply a very non complex answer. Come on, you know what I’m referring to – the basics. In looking up the exact meaning of what basic says; the dictionary tells me it is the “fundamental or basic principal”. I took that meaning this week and looked deeper into how I attack some DBA related items and I keep coming back to – “keep it simple” and “get back to the basics”.

The Basics

When I say basics I’m coming at it from a DBA standpoint. Do you have your own checklist that you go by for daily, monthly, quarterly, yearly checks? If not, then this is as good as time as any to start.

What are some of the things to include in your basic checklist?

Some items to include, but not limited to are:

  • Backup processes and alerting upon failure
  • Review of jobs (if any have failed)
  • Anything in the SQL Error Logs?
  • If you use Policy Based Management (PBM) and receive reports – any action to take?
  • Security logs – have you checked?
  • If issues are found how do you handle them? Just don’t sweep them under the rug
  • Hopefully you have something in place that tells you when you are running low on space (storage)….if not might want to start thinking about getting something implemented; by the way how is the space looking?
  • You’re fragmentation process for indexes working properly?
  • Remember those backups you checked? You spot checking any restores to validate them at all?

Summary

The list above is just a simple list to get started with….get back to the basics of DBA work. It’s a fun and enjoyable ride, but keep one thing in mind as you traverse through the SQL terrain not to overcomplicate things. You will find many checklists on the web by some renowned DBA’s that one could model a standard off of if you don’t already have one. I’m a big believer in automation and automate what you can to help you become more efficient and streamlined. Get the reports emailed to you when you start your day, at best make sure notifications are set up on the jobs running in case of failure.

Keep it simple, get some standards in place, above all remember the basic principals. Don’t overcomplicate issues and when you run across them don’t sweep them under the rug and wait for the next DBA to come along to fix them; take the time to fix them correctly.

So You Want To Go To Pass Summit?!?!

Leave a reply

http://www.sqlpass.org/summit/2013/ Back in 2011 I was afforded the opportunity to attend PASS in Seattle, Washington. It was the first time I had made such a journey to this conference; I really didn’t know what to expect. I’ve heard the stories, the     many positive things regarding the sessions, learning, and networking – but to be quite honest the stories do not give PASS justice until you experience it first hand. I get asked this question all the time by various  people throughout the country – Should I attend? Because of the interest I want to take a moment and lay out how it has benefited me in the span of 2 years.

The Beginning

When asked to go to PASS I figured it would be like any other business conference I had been to (boy was I wrong). Leading up to my departure I had a lot of work to get done and wasn’t really in a place I felt like I should go. I started to review the sessions online and I quickly saw how vast the information really is. Looking at the sessions I began to look at the speakers, some I knew and some I did not but what was obvious to me is that a lot of the leading leaders in my industry (DBA) were going to be there; hearing them speak on topics of interest to me I thought to be an extreme plus…..moving on

The Expectations

My expectations were that of learning some new techniques from the Jason Strate’s (B|T), Brent Ozar’s (B|T), Chris Shaw’s (B|T), etc. but what I walked away with were new connections with a SQL family that was over 2k strong and memories that I will take with me for the rest of my life. PASS has so much to offer to each individual, from the sessions, to the vendors interacting, the kick-off speakers, and much more.

Perspective

From my own personal experience attending is something that kick started my career into overdrive. From it I became more active in the community, started to speak locally, created the SQL Corner, and made many new friends along the journey. No, PASS is just not another business conference where you go and sit in on meetings; of course you do that but it is much more. Being part of the SQL community means you are part of a family and all the family comes together to gain more knowledge within the perspective fields.

Summary

Why should you go; glad you asked. I think PASS sums it up better than I ever could here. This year in 2013 PASS is going to be in Charlotte, N.C.; if you haven’t reserved your spot yet I hope you do so. It is one of the best conferences I’ve ever attended from both a business and personal standpoint. Do you want to kick start your career? Are you eager to learn more and connect with other people that are trying to do the same? If you are then PASS Summit is for you. If you have questions you can contact them here or drop me a line and I will do my best to get you an answer.

Being Hectic With The Thrill

Leave a reply

thrillIt happens every time anyone goes to an amusement park. The one park that sticks out to me is Disneyworld just because I was there on a recent family vacation. You know what I’m talking about – the crazy rides, the masses of people, the running to and fro. Vacation is supposed to be a relaxing time yet when you get into a park of any sort, at times, one can get caught up with the hustling and bustling – I know I’ve been there done that. While being on vacation a couple of things stuck out to me so much so that I think it does apply to the DBA life or any data professional for that matter. Below is a representation of what thoughts flowed through my head.

The Hectic

Have you ever noticed when you are in an amusement park people are rushing around as fast as they can to get to the next ride before the other person? While on vacation I was enamored to watch the bustling and running of people almost to the point of it being comical. I would watch the dad’s pushing the double wide strollers around basically at a run with the two kids in the stroller and one on his shoulders as they rushed to the next ride. The families that would run as soon as the park opens to get the famous “fast pass” to secure a time frame that they would not have to wait in line for hours on end.

How does that pertain to the DBA life? Easily enough the DBA life can at times be very hectic with several things going on all around them. It is just not one thing we focus on but a multitude of things that more times than not seem to go awry all at once. How do you react when those situations arise?

The Thrill

A long with the hectic aspect comes the thrill. You get to the that ride you’ve been waiting to ride and you’re next. You get on the ride and off you go down an 80 ft. drop or into that dark tunnel you never think  you’ll come out of. The unknown aspects and the thrill of the ride keeps you coming back for more.

As I am a SQL DBA, I think that same thought process can be applied. The thrill of solving that problem that has been unknown, working with other teams to find common ground to push forward on a project, getting that new index in place that takes a process running 45 minutes down to 10 seconds. It’s the thrill of the game that keeps you coming back for more.

The End Game

Have you taken inventory lately? When you get to work are you excited about your work? Do you always have that continuous desire to improve yourself as a data professional? Is that thrill still there like when you solved your first issue and you said, “I think I can do this!” If not recapture that fire you once had – enjoy what you do. There are a lot of people that would love to have the opportunities that we as data professionals have. Enjoy the time you have with it!