The 5 Scariest Moments for a SQL Server Developer

Published on: 2018-10-30

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 <your name here>, 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.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Conditionally Returning Rows Based On Query Results

Published on: 2018-10-16

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.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!

Displaying Long Values in SSMS

Published on: 2018-08-07

Click here to watch this week’s video on YouTube.

I write a lot of dynamic SQL and frequently encounter variables that contain many characters:

DECLARE @LongValue NVARCHAR(MAX) = CAST('' AS NVARCHAR(MAX)) + 
N'SELECT
    ''A'' AS AShortValue,
    '''+REPLICATE(N'A',4000)+''' as ALongValue
ORDER BY 1';

This variable is 4059 characters long, and when I execute it it runs great.

SELECT LEN(@LongValue); -- 4059 characters
EXEC(@LongValue);

A homage to one of my favorite Uncyclopedia entries.

If my programmatically built query had an error in it, the first thing I’d want to do when debugging it would be to see the the text of the entire @LongValue variable.

I could do this by just saying SELECT @LongValue, and while recent versions of SSMS will display the whole value for me, it completely loses my formatting which stinks (and is especially bad if there are any comments prefixed with   in the query):

Need a ultra HD wide display to fit this all on one screen.

I can say PRINT @LongValue, which will keep the formatting, but it will get trimmed at 4,000 characters (notice the missing ORDER BY):

Some Better Ways

Erik Darling posts one solution to this problem in his T-SQL Tuesday #104 entry (as well as some other problems/solutions for lengthy SQL variables). Specifically he links to a SQL string printing script that will loop through the lengthy variable and print everything while maintaining formatting:

Not perfectly formatted, but good enough.

And while I like using that stored procedure on my primary server, I’m too lazy to install it every where I need it.

Instead, I have a couple of go-to solutions that work on all SQL Server instances 2008 forward.

Solution 1: CAST to XML
SELECT CAST(@LongValue AS XML) AS LongValue

Casting the long variable to XML allows SSMS to generate a clickable, single-row result that preserves formatting:

IntelliSense complains but I’m OK with it

The only downside to this approach is that certain charaters, like “<” and “>”, can’t be converted to XML:

Solution 2: FOR XML PATH

A slight variation on solution 1, we can get similar results using FOR XML PATH:

SET @LongValue = '<' + @LongValue -- Let's add in an invalid character
SELECT @LongValue FOR XML PATH('')

FOR XML PATH is one of the most abused SQL Server functions.

In this solution, the “<” is escaped to “&lt;”, which isn’t perfect but at least my variable can be displayed with formatting intact.  A quick find and replace for any escaped characters and I’m good to go.

Good Enough

These techniques aren’t perfect, but for purposes of debugging dynamically generated code they are good enough.

Maybe one day SSMS will print longer strings or include a syntax formatter and I won’t care nearly as much.

And if not, I’ll happily continue to abuse FOR XML to do things other than generate XML documents.

Thanks for reading. You might also enjoy following me on Twitter.

Want to learn even more SQL?

Sign up for my newsletter to receive weekly SQL tips!