This post is a response to this month's T-SQL Tuesday #108 prompt by Malathi Mahadevan.  T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month.

This month's topic asks to share how we learn skills other than SQL Server.

Watch this week's video on YouTube

I enjoy learning to do new things; there's a major sense of accomplishment I feel when I can tell myself, "Wow, I went from knowing almost nothing to being able to have this new skill."

Over the years I've realized I'm pretty consistent in how I go about learning something new, so what follows is my process for learning a new skill.

What I Am Learning

Recently, my non-SQL Server related learning goals have been to learn to use plain old vanilla JavaScript.

In this case I'm not necessarily starting from nothing (I have been writing JavaScript for close to 20 years now...) but previously where it was necessary to use a library like jQuery to get any kind of compatibility and consistency across browsers, the time has finally come where the JavaScript (ECMAScript) standard is mostly implemented correctly in most modern browsers.  No more need for large helper libraries!

And so the appeal here is that if I can ditch the overhead of a large library, my code will be simpler, easier to maintain, and faster to load and execute.

Steps to Learning a New Skill:

1. Commitment

For
me, the hardest part to learning a new skill is time management: if I don't
make time for it, it won't happen on its own.

I think the easiest way to make time to learn a new skill is to incorporate it into a project at work.  By aligning it with your day job, you're guaranteeing some time to work on it most days.  Yes, critical projects and deadlines do come up where learning has to be set aside temporarily, but if you can find a project that doesn't have urgent deadlines AND aligns with learning goals, then you'll be good to go.

For me, learning vanilla JavaScript is a great "at-work" project since
I'm already developing a lot of web apps with JavaScript anyway – the main
difference is I'll be using the standard JavaScript functionality instead of
working through a library like jQuery.

Now obviously this won't work in all scenarios: if you want to learn to build drones and you do development work for a chain of grocery stores, you probably can't figure out a way to align your interest with work (unless of course your company is trying to build out a drone delivery service).

In that case, you need to set aside time at home. This essentially comes down to your own discipline and timemanagement.  The key here is that youneed to set up time regularly and set yourself deadlines.  Instead of having the deadline of a workproject to help motivate you to learn, you need to tell yourself "I'mgoing to get this chunk of plastic and copper wiring up in the air by the endof the month" and try to deliver on that goal.

2. Go Cold Turkey

This is the hardest part of kicking any old habit. 
Ideally when learning something new, I like to use it exclusively in all
scenarios where I can.

This may not always be possible: sometimes there is a deadline you have to meet and trying a new skill that slows you down is not always the best idea.

But even if that's your scenario, pick at least one project to go completely cold turkey on for learning your new skill.  Going cold turkey on a project will force you to work through the hurdles and actually learn the necessary skills.

Thiscan be challenging.  I have the jQuerysyntax and methods ingrained in my brain from years of use; switching to usingstandard JavaScript is tough because I'm frequently having to look up how to dothings.  But if I picked the rightproject (ie. one without urgent deadlines), then this becomes a fun learningexperience instead of something stressful.

3. Build a Collection of Resources

The internet is awesome: it contains nearly all of the information you could ever want for learning a new skill.  The internet can also be a terrible place for learning a new skills if used incorrectly.

When learning something new, I try to find resources that guide me through a topic.  Whether it's a book, a website with a structured guide, a video course, or documentation with clear examples, it's important to find something that will teach you the why as well as the how.  I prefer using something with structure because it helps me learn the fundamentals correctly.

With my JavaScript learning, I have been enjoying the guides and daily newsletter at https://vanillajstoolkit.com/ .  That site also has clear documentation for the most common features.  The "official" documentation (https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference) is good to reference too, but can be overwhelming when first starting out.

What I don't like doing is searching for each question I have on StackOverflow.  Don't get me wrong, I love StackOverflow, but when learning some brand new skill I don't think it always provides the best answers.  Sometimes you get good answers, but sometimes you'll come across answers that, while technically correct, apply to some edge case, old version of a language, etc... that make them less-than-helpful when learning a new skill.

4. Document and Share

As I learn, I document what I learn.  This could be as simple as code snippets that I find myself using all the time, or it could be links to additional helpful resources.

Eventually I like writing up what I'm learning.  Even if no one reads it, summarizing your thoughts and ideas will help clarify and retain them better.  A summarized document or blog post also provides an additional reference for you to use if you need to in the future.

I haven't been blogging publicly about my JavaScript learning, but I have been
taking notes and sharing with individuals who are learning along with me.

5. Rinse and Repeat

That's it!  On my first pass at learning a new skill I try to finish a small project to get some immediate satisfaction.  I then pick a new project that's a little bit more ambitious, but still be manageable because I now have some knowledge that I didn't have before starting my first project.

Baby steps.  A little bit each day (or every other day).  Do it enough times and eventually you find yourself being fully capable of whatever skill you set out to learn.

Watch this week's video on YouTube

While families and friends are scaring each other this Halloween week with stories of ghosts and ghouls, I thought it'd be way scarier to talk about truncate tables and source control.

Accidental Data Deletion

You've spent all morning loading millions of records into your tables.  Some of that data was painstakingly loaded by hand from flat files, and some lookup values you even authored manually because "you'll only have to do this once."

You are jumping around between SSMS windows, running this query here and that query there, pressing F5 like there's no tomorrow. And then right before you press to F5 to finish all of your work...

...you realize that you ran the whole tab worth of code instead of the one statement you meant to highlight.  "Commands completed successfully" flashes in your Message window, and in horror you discover you accidentally ran a truncate table statement!

Ok so this is pretty bad.

You know backups are being taken but…can the DBA restore the most recent data you spent all morning working on?  You tell her about your misfortune ("...yeah one of my coworkers accidentally deleted the data…") and hold your breath.

After a few minutes she gets back to you saying everything is back up and working.  Saved by good backup practices...this time!

Problems With code You Inherited

You receive an instant message from your DBA.

DBA: "Hey , how are you doing?

"You: "Good how are you?"

DBA: "Look, the instance XYZ has been at 100% CPU utilization for the past hour.  I see you have that has been running that whole time….and it's using a scalar function.

You: "…"

You: "Sorry.  I am just running this job for Bill since he left a few weeks ago.  Obviously there are some problems with the way he was writing some of this queries and I haven't had a chance to look them over yet."

DBA: "That's ok.  I know you would never write a scalar function that totally destroys the server.  Anyway, I was able to easily rewrite the query into a table-function that should run a lot more efficiently.  Is it ok if I kill your original query and run this new one instead?"

You: "Yeah sure, that'd be great.  Thank you!"

Missing Index Details

You've been having a good day: no outages, no annoying customer requests, and the only thing keeping you from going home is to finish tuning the query you are working on.

You execute your query after making all necessary changes and…nothing.  The executing query icon just keeps spinning, and spinning, and spinning…

You go grab some water, and by that time the query has finished.  You switch over to the Execution plan tab and…you see SQL Server's Missing Index hint recommending that you create an index.

Now don't be confused, this text looks green and friendly.  You might be tempted to follow the recommendation and add the missing index it recommends - but don't! 

That siren song of easy query tuning will steer you straight into the rocks known as unnecessary index maintenance. 

Just before you execute Microsoft's missing index details script, you realize that if you add some include columns and reorder a couple of the key columns, you'll have an index that will be applicable to all of your table's queries.

You avoid getting ship wrecked and create an index that useful for more than just this one query - phew!

Email Subjects That Read: "Does anyone know why none of my queries are running?"

Last night's data load failed.  "No big deal," you assure yourself. "I'll just run it now - no one will know the difference."

You kick-off your ETL queries and decide to go make some oatmeal.  At your office's kitchen you run into a coworker you haven't seen in a while, and you get to talking about work, life, that weekend's football game that was lost in overtime…that's been happening a lot this season, huh?

On the way back to your desk, one of your customers stops you to ask some questions, your manager needs to discuss project planning with you, a new employee is introduced, etc…

By this point the office is busy with activity.  You finally make it back to your desk MUCH later than you originally intended.  What were you doing again?

You can't remember so you decide to check your email.  You notice a chain emails asking "Does anyone know what's going on with server ABC?" Things are running so SLOW!  I can't run even the simplest queries!"

OH CRAP you think.  You look at your load process queries and they are still running with no end in sight.  Do you kill the queries and wait for the hour-long rollback to complete or…

...just then your queries finish.  A minute later you receive another email saying "seems like everything is working fine now." 

Another bullet dodged.

Where Did My Code Go?

Working on this team project has been a fun experience for you - responsibilities have been shared and you and your coworkers have been delivering on deadlines ahead of schedule.  You've never been able to write queries and make progress to the end product as quickly as this.

You sit down to start working and remember that you had to debug a query from yesterday afternoon.  You go and open the stored procedure in dev and…… it's not the same query anymore.

Sure, parts of it are the same, but there are some extra parameters and some of the logic has changed.  What the heck…?

"Did anyone mess with USP_GetProducts since yesterday?" you ask your alley of teammates.

"Uhh…I updated it this morning to make it work for my business logic requirement," said intern Sam.  "Is something wrong?"

"Well, it's totally different from the way I had it when I left yesterday evening.  I bet we got out of sync and the merge conflict wasn't resolved correctly" you say.

"Oh yeah, I got some message about merging.  I just clicked the 'keep my version' button," says Sam.

"No big deal," you say with confidence.  "I can pull my version out of source control.  Come on over and I can show you how we can merge both of our queries together."

Source control saves the day.

Happy Halloween.

I do my best work in the mornings.  Evenings are pretty good too once I get a second wind.

Late afternoon are my nemesis for getting any serious technical or creative work done.  Usually I reserve that time for responding to emails, writing documentation, and brewing coffee.

Some afternoons I can't help myself though and end up getting myself into trouble.

What is THAT!?

It all started when I was troubleshooting an existing query that was having issues.  During the process of trying to understand what the query was doing, I happened to look at the execution plan:

CREATE TABLE ##DatetimeTest ( SomeField varchar(50) NULL, CreateDatetime datetime);
INSERT INTO ##DatetimeTest VALUES ('asdf',GETDATE());

DECLARE @Today datetime = GETDATE();
SELECT DISTINCT
    *
FROM 
    ##DatetimeTest
WHERE
    CreateDatetime >= @Today-30;

When I hovered over the Table Scan, the Predicate section caught my eye.  Specifically, I wondered "Why is 1900-01-31 showing up? I don't have that anywhere in my query!"

(fun side story: the real query I was working on was dealing with user-defined datatypes, something I don't have experience with, so I thought those had something to do with the problems I was experiencing.  I latched onto this 1900 date as the potential cause but it ended up being a red herring!)

Help!

Like I mentioned, late afternoons are not when I do my best work.  I couldn't figure out why SQL Server was converting my -30 to January 31, 1900.

Intrigued and having no clue what was going on, I decided to post the question with the #sqlhelp hashtag on Twitter.  Fortunately for me, Aaron Bertrand, Jason Leiser, and Thomas LaRock all came to the rescue with ideas and answers - thanks guys!

Implicit Conversion

In hindsight, the answer is obvious: the -30 implicitly converts to a datetime (the return type of my @Today variable), in this case 30 days after the start of the minimum datetime value, 1900-01-01.

This makes perfect sense: SQL Server needs to do some math and in order to do so it first needs to make sure both datatypes in the equation match.  Since int readily converts to datetime but not the other way around, SQL Server was just doing its job.

Future Problems

As I mentioned earlier, this int to datetime conversion wasn't the actual issue with my query - in my drowsy state I mistook it as being the source of my problem.

And while it wasn't a problem this time, it can become a problem in the future.

Aaron has an excellent article on the problems with shorthand date math, but the most relevant future issue with my query is: what if someone in the future decides to update all datetimes to datetime2s (datetime2 being Microsoft's recommended datatype for new work)?

If we update to a datetime2s and run the query again:

ALTER TABLE ##DatetimeTest
ALTER COLUMN CreateDatetime datetime2;

DECLARE @Today datetime2 = GETDATE();
SELECT DISTINCT
    *
FROM 
    ##DatetimeTest
WHERE
    CreateDatetime >= @Today-30;

AHHH!  While SQL Server had no problem converting our previous code between datetime and int, it's not so happy about converting datetime2.

Morals

In the end, the above scenario had nothing to do with the actual problem I had on hand (which had to do with some operator precedence confusion).

To avoid future confusion and problems it's still better to refactor the code to be explicit with what you want to do by using the DATEADD() function:

DECLARE @Today datetime = GETDATE();
SELECT DISTINCT
    *
FROM 
    ##DatetimeTest
WHERE
    CreateDatetime >= DATEADD(day, -30, @Today);

Watch this week's video on YouTube

While I normally prefer formatting my query results in a downstream app/reporting layer, sometimes I can't get around adding some business formatting logic to my SQL queries.

Today I want to show you four different ways to conditionally output rows of data based on a SQL query.

Setting Up The Base Query

You've probably worked with a query that results in either 0 or 1 rows being returned:

DECLARE @CurrentDatetime datetime = getdate();
SELECT
    1 AS AreSecondsDivisbleBy2,
    @CurrentDatetime AS CurrentDatetime
WHERE
    DATEPART(second,@CurrentDatetime) % 2 = 0

If you put that query into a derived table and add some IIF() logic on top of it, you now have a situation where you your result may contain a row with one of two distinct values or no rows at all:

If you put that query into a derived table and add some IIF() logic on top of it, you now have a situation where you your result may contain a row with one of two distinct values or no rows at all:

DECLARE @CurrentDatetime datetime = GETDATE();
SELECT
    IIF(DATEPART(second,d.CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisbleBy3And2,
    d.CurrentDatetime
FROM
    (
    SELECT
        1 AS AreSecondsDivisbleBy2,
        @CurrentDatetime AS CurrentDatetime
    WHERE
        DATEPART(second,@CurrentDatetime) % 2 = 0
    ) d

(Note: if you ever need to know whether the seconds part of the current time is divisible by 3 and 2, use SELECT IIF(DATEPART(second,GETDATE()) % 6 = 0,1,0) and not this monstrosity of a query  I'm creating to demonstrate a scenario when you dependent derived table logic).

Sometimes we may want to force certain scenarios based on the output of the query above. Here are few common patterns that I find myself doing when needing to meet certain business requirements of queries to return data conditionally.

Always Return 1 Row

Let's say we are happy with getting a result value of 0 or 1 for AreSecondDivisbleBy3And2, but want to additionally return some other value when our derived table returns no rows. We can accomplish this with a UNION ALL and some sorting:

DECLARE @CurrentDatetime datetime = GETDATE();
SELECT TOP 1
    AreSecondsDivisbleBy3And2,
    CurrentDatetime
FROM
    (
    SELECT
        IIF(DATEPART(second,CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisbleBy3And2,
        d.CurrentDatetime,
        0 AS OrderPrecedence
    FROM
        (
        SELECT
            1 AS AreSecondsDivisbleBy2,
            @CurrentDatetime AS CurrentDatetime
        WHERE
            DATEPART(second,@CurrentDatetime) % 2 = 0
    ) d
    UNION ALL
    SELECT
        -1 AS AreSecondsDivisbleBy3And2,
        @CurrentDatetime AS CurrentDatetime,
        1 AS OrderPrecedence
    ) p
ORDER BY
     OrderPrecedence

We can limit our query to return TOP 1 and then add an OrderPrecedence column to determine which query result row to return. If our original query has a row of data, it will be returned because of it's OrderPrecedence. If our original query returns 0 rows, our fall back UNION ALL default value of -1 will be returned.

Return 1 row when value is 1, 0 rows when value is 0

What about a situation where we want to return a row when AreSecondsDivisbleBy3And2 is equal to 1, but no row when it is equal to 0?

The IIF function and CASE statements work great for conditionally returning a value in a SELECT, but how can we conditionally return a row?  Here's one approach:

DECLARE @CurrentDatetime datetime = GETDATE();
SELECT
     AreSecondsDivisibleBy3And2,
    CurrentDatetime
FROM
    (
    SELECT
        IIF(DATEPART(second,d.CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisibleBy3And2,
        CurrentDatetime
    FROM
        (
        SELECT
            1 AS AreSecondsDivisbleBy2,
            @CurrentDatetime AS CurrentDatetime
        WHERE
            DATEPART(second,@CurrentDatetime) % 2 = 0
        ) d
    ) d1
    INNER JOIN
    (
    SELECT
        0 AS ValueToNotReturn
    ) d2
        ON d1.AreSecondsDivisibleBy3And2 != d2.ValueToNotReturn

In this scenario, we return a row when our value is 1, but do not return a row when the value is 0 or our derived table doesn't return any rows.

We use an INNER JOIN to filter out the row value that we want to return 0 rows.

Return 1 row when null, and 0 rows when the value is 1 or 0

In this scenario we want to return no rows when AreSecondsDivisibleBy3And2=1 and a row when its not.

I've never had a real-world use for this one, but it's essentially a combination of the first two solutions.

SELECT
    AreSecondsDivisibleBy3And2,
    CurrentDatetime
FROM
    (
    SELECT TOP 1
        AreSecondsDivisibleBy3And2,
        CurrentDatetime
    FROM
        (
        SELECT
            IIF(DATEPART(second,d.CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisibleBy3And2,
            d.CurrentDateTime,
            0 AS OrderPrecedence
        FROM
            (
            SELECT
                1 AS AreSecondsDivisbleBy2,
                @CurrentDatetime AS CurrentDatetime
            WHERE
                DATEPART(second,@CurrentDatetime) % 2 = 0
            ) d
        UNION ALL
        SELECT
            -1 AS AreSecondsDivisibleBy3And2,
            @CurrentDatetime AS CurrentDatetime,
            1 AS OrderPrecedence
        )u
        ORDER BY
             OrderPrecedence
    ) d1
    INNER JOIN
    (
    SELECT
        -1 AS ValueToNotReturn
    ) d2
        ON d1.AreSecondsDivisibleBy3And2 = d2.ValueToNotReturn

This one is by far the most difficult one to logic through, but it's a pure reversal of return no rows when rows are present and return a row when no rows are present.

Always return 0 rows

I'm not exactly sure what the business case for never returning any rows would be, but this one is pretty simple: just add a condition that will always evaluate to false:

DECLARE @CurrentDatetime datetime = GETDATE();
SELECT
    IIF(DATEPART(second,d.CurrentDatetime) % 3 = 0, 1,0) AS AreSecondsDivisibleBy3And2
FROM
    (
    SELECT
        1 AS AreSecondsDivisbleBy2,
        @CurrentDatetime AS CurrentDatetime
    WHERE
        DATEPART(second,@CurrentDatetime) % 2 = 0
    ) d
WHERE
    1=0

Since 1=0 will never be true, your query will never return any results regardless of what kind of logic is happening in your SELECT or derived table.

This post is a response to this month's T-SQL Tuesday #107 prompt by Jeff Mlakar.  T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month.

This month's Halloween themed topic asks to "... share a story about a project you worked on or were impacted by that went horribly wrong."

Watch this week's video on YouTube

I've been fortunate enough to never have been part of a large disastrous project at work.  My projects always have a "fail fast" mentality, so they never build up to a point where they come crashing down in a death spiral.

But that's not to say I haven't experienced my own project horror story in my personal work.

A while back I made a goal to produce a quality SQL Server focused blog post and video every week.  Essentially this means I am starting a new small-scale project each week where I play the part of project manager, developer, analyst, etc... with a delivery deadline of every Tuesday morning.  While I've gotten better at this process over time, I have also failed to meet my personal goals numerous for a variety of reasons.

Scope Creep-y

In order to meet my weekly deadline, I need to stay laser focused on the topic I choose for that particular week.  If I get additional ideas while writing and start trying to incorporate them into my post (ie. scope creep), I inevitably miss midweek milestones and have to try to make up time elsewhere to make my deadline.

One instance of scope-creep I experienced earlier this year was when I was trying to write a post on how to build a table-driven validation system.

I've built many table-driven processes in the past so this seemed like it would be an easy topic to write about.  I started that week's blogging process by building the demo templates that would include table structures, execution scripts, etc...

Instead of wrapping up my basic demos so I could move on to writing the actual post, I kept building out demos for more features: logging functions, parameterization, SQL injection protection, common performance problems, etc...

It was exciting to be building all of this out, but instead of creating one-week's content, I realized I had started working on enough demos for several weeks of posts.  This wouldn't have necessarily been a bad thing on its own; after all it's nice to be a few weeks ahead on content creation.

However, I didn't quite finish enough demos for any one post in particular, and due to some other life events I didn't get back to working on my demos until Sunday afternoon.  Normally at that point I'd already have my demos done, a blog post written, a video filmed, and either a finished video edit that I'm uploading or getting really close to uploading to YouTube.  What I had instead was a bunch of half-finished SQL demos saved in a very rough outlined blog post.

The Project Graveyard

This isn't the first time poor time management and scope creep has gotten me in trouble:

I have several posts that I've invested a good amount of time into but never released because they are incomplete.  In almost all of these cases my problems stemmed from poor planning and scope creep.

In the case of my table-driven post, by late-Sunday afternoon I realized I was going to miss my weekly deadline goal if I continued with that post, so I scrapped the idea for now and quickly wrote and shot a different post on an SSMS trick instead.  It was discouraging to have to do that, but at the end of the day I was able to meet my weekly deadline even if it was with a different result than I initially expected.

You might be thinking, "Why not ignore deadlines and release the post later in the week/month?"  For me, I like my weekly deadlines because I like the creative challenges that come from having time constraints.  It forces me to limit my scope and work on different projects on a regular basis.  My goal from blogging and video making is to learn how to present information in a succinct manner so that my communication skills, both written and verbal, improve.  So while I can (and probably will) complete these posts at some point in the future, I treat them as failures for that particular week's project.

And while failures aren't particularly fun, they can wind up being great learning opportunities: after all, I haven't gotten so off track due to scope creep ever since.