Difference between NULL and NULL("") in DML

Question

What is the difference between writing a NULL value to the DML and the NULL with default value. Does writing NULL value is a bad practice? When to decide which method to be adapted.

Answer 1

NULL will store one byte flag along with data where as Null('') doesn't store one byte flag in addition any blank value in field will be treated as Null ie it has "" as default and treat this equal to null. So its good practise to use null with default values.

Answer 2

Sarveshmishra is correct. To answer the other part of your question - whether it is good practice or not depends on how you're using the record format.

---

NULL as byte flags is primarily an unambiguous (ie no risk of collision with real data) representation of NULL for use within Ab Initio which comes at the cost of extra record width.

Good for: When strictly correct null-handling is required within and between graphs (and when the record-width and translation (reformat) cost is acceptable)

Bad for: File-based data interchange with source and destination systems. (Note that database output via bulk loader (eg SQL Server bcp) is file based.)

---

NULL(default) encodes NULL as a specific character sequence (or zero-width string). The encoding chosen must match the interface contract with upstream/downstream, otherwise there is a risk of data collision (example: in a database, "" <> NULL, but NULL("") treats "" as NULL).

Good for: Data interchange upstream/downstream, or where the distinction eg between "" and NULL is not important.

Bad for: Guaranteed strict null-handling.

---

For data-interchange with strict null-handling and zero risk of collision, an alternative strategy is to define explicit null flags in the interface contract.