Difference between try_cast and cast in SQL server

CAST:

CAST is used to convert one data type into another. If the conversion is valid, SQL Server returns the converted value. And If CAST fails to convert a value, SQL Server throws an error and stops execution.

See the example

SELECT CAST('123' AS INT);

It returns the value.

SELECT CAST('ABC' AS INT);

It throws an error.

Try_Cast:

TRY_CAST attempts the conversion. If the conversion fails, it returns NULL instead of throwing an error.

See below

SELECT Try_CAST(123 AS INT);

Its return the value.

SELECT Try_CAST('ABC' AS INT);

It will return null.

Where we use CAST: CAST is better only when data is guaranteed to be valid.

Below is the case where we can use this.

Ø  Enforcing strict rules  

Ø  Clean master data       

Ø  Schema-level conversions       

Ø  Data quality validation

Where we use Try_Cast

Below is the case where we can use this.

Ø  ETL / Data warehouse to avoids job failure

Ø  CSV / API / file data to handles invalid values safely

Ø  Reports / dashboards for one bad row won’t break report

Ø  Production queries because    Safer, more stable

TRY_CAST is better for real-time systems because it handles invalid data safely without failing the query, whereas CAST is strict and fails on bad data.