CAST(Null As varchar (25)) As CFDCode

Interesting question. []
Never thought about it, but from my understanding NULL itself is type-less.
The only reason I can imagine right now where it may make sense to explicitly CAST (NULL to some other type) is when you create a table via SELECT INTO. Try this:
SELECT
NULL AS Column1
INTO
dbo.Frank1

EXEC sp_help 'dbo.Frank1';

DROP TABLE dbo.Frank1;
You'll find that by engine default, the datatype is assumend to be an int. Nowe try this:

SELECT
CAST (NULL AS varchar(25)) AS Column1
INTO
dbo.Frank1

EXEC sp_help 'dbo.Frank1';

DROP TABLE dbo.Frank1;
Now you see that you forced the table to be created with a varchar(25) column.

[quote user="Adriaan"]
Hm, isn't that why we have the ISNULL() function?
[/quote]
Not sure I understand you here. [*-)]

But then you're still kind of depending on the interpretation of the datatype by the engine.
The first case would give a (var)char(1) datatype while the second gives an int, which may or may not what you want, since you could have wanted a tinyint or even a bit instead.
Another interesting thing to consider is here that the datatype precedence rules apparently apply. Say, for example, you want for some reason build up a "truth choices" table with the values 0, 1, NULL. If I were to build such a table, I would choose a bit datatype.
When you use the SELECT INTO syntax from my first reply, the batch may look like this
SELECT
*
INTO
dbo.Frank1
FROM
(SELECT
CAST (NULL AS bit) AS Column1
UNION ALL
SELECT
0
UNION ALL
SELECT 1) x
Now guess what datatype Column1 in the table now has?
EXEC sp_help 'dbo.Frank1';
It's an int. The engine regards the values 0 and 1 as of type int even though one might expect the CAST(NULL AS bit) would be enough to indicate the type of the column. However, int has a higher precedence than bit and so implicit conversion take places and leaves you with a different datatype.
Does that prove anything? Good question. At least it proves that it's better to be always explicit in what you want SQL Server to do. []

Yes the precedence of the datatype plays a major role in this case
See what happens when you run these
1 SELECT
ISNULL ( NULL, 1 )union all
SELECT ISNULL ( NULL, getdate() )
2SELECT
ISNULL ( NULL, 1 )union all
SELECT
ISNULL ( NULL, 19879.56)3
SELECT
ISNULL ( NULL, 1 ) union all
SELECT
ISNULL ( NULL, 'this is test to see the data type of null' )