I’m converting some Firebird database code to T-SQL and I’m seeing a lot of code that doesn’t work in the same way that SQL Server.

No surprise – I already expected that but for those that still say that “all engines/databases are equal”…”is SQL right?” here is another proof that is not true.

On Firebird it is possible to use the CAST function using a DOMAIN (the equivalent in SQLServer is UDDT – User-Defined Data Types) as target data-type, well turns out that on SQL Server…that is not possible.

Which means that, even if I have created a UDDT as:

CREATE TYPE dbo.VARCHAR_50 FROM VARCHAR(50);

The following code will not work

SELECT CAST('value' AS VARCHAR_50)

and it yields the following error message:

Msg 243, Level 16, State 2, Line 1

Type VARCHAR_50 is not a defined system type.

this means that we need to change it to the defined system type like:

SELECT CAST('value' as VARCHAR(50))

Maybe it works with CONVERT() function?!…not really, the behaviour is the same.

To finish the title…Using CAST() function with User-Defined Data Types…Did you know… it is not possible. You need to use the system type.

Thanks for reading.