Pop Quiz - REPLACE in SQL Server

It's amazing the things I've run into with SQL Server this week that I never noticed. In today's pop quiz, let's look at REPLACE

DECLARE
    @Repro table
(
    SourceColumn varchar(30)
);

INSERT INTO 
    @Repro
(
    SourceColumn
)
SELECT
    D.SourceColumn
FROM
(
    VALUES 
        ('None')
    ,   ('ABC')
    ,   ('BCD')
    ,   ('DEF')
)D(SourceColumn);

SELECT
    R.SourceColumn
,   REPLACE(R.SourceColumn, 'None', NULL) AS wat
FROM
    @Repro AS R;

In the preceding example, I load 4 rows into a table and call the REPLACE function on it. Why? Because some numbskull front end developer entered None instead of a NULL for a non-existent value. No problem, I will simply replace all None with NULL. So, what's the value of the wat column?

Well, if you're one of those people who reads instruction manuals before attempting anything, you'd have seen Returns NULL if any one of the arguments is NULL. Otherwise, you're like me thinking "maybe I put the arguments in the wrong order". Nope, , REPLACE(R.SourceColumn, 'None', '') AS EmptyString that works. So what the heck? Guess I'll actually read the manual... No, this work, I can just use NULLIF to make the empty strings into a NULL , NULLIF(REPLACE(R.SourceColumn, 'None', ''), '') AS EmptyStringToNull

Much better, replace all my instances of None with an empty string and then convert anything that is empty string to null. Wait, what? You know what would be better? Skipping the replace call altogether.

SELECT
    R.SourceColumn
,   NULLIF(R.SourceColumn, 'None') AS MuchBetter
FROM
    @Repro AS R;

Moral of the story and/or quiz: once you have a working solution, rubber duck out your approach to see if there's an opportunity for improvement (only after having committed the working version to source control).

Pop quiz - altering column types

Pop quiz

Given the following DDL

CREATE TABLE dbo.IntToTime
(
    CREATE_TIME int
);

What will be the result of issuing the following command?

ALTER TABLE dbo.IntToTime ALTER COLUMN CREATE_TIME time NULL;

Clearly, if I'm asking, it's not what you might expect. How can an empty table not allow you to change data types? Well it seems Time and datetime2 are special cases as they'll raise errors of the form

Msg 206, Level 16, State 2, Line 47 Operand type clash: int is incompatible with time

If you're in this situation and need to get the type converted, you'll need to make two hops, one to varchar and then to time.

ALTER TABLE dbo.IntToTime ALTER COLUMN CREATE_TIME varchar(10) NULL;
ALTER TABLE dbo.IntToTime ALTER COLUMN CREATE_TIME time NULL;