Let's I have a large data set with a "CustomerId" column. This data comes from a some external source and is used in some transformations and exposed to other systems.

CustomerId is in practice always an integer but is received as a string type so in theory it can contain non-integer characters. I don't want to explicitly cast it to integer to guard against a potential case where it's a string as that would break the flow, but I also want to cast it to integer since that would considerably speed up its usage.

So what I was considering is creating a CustomerId column which is an integer cast from the original and a CustomerIdVarchar which is the same value but with a varchar type. This CustomerIdVarchar will be null unless the original can't be cast to integer. That way for all intents and purposes the former can be used in an efficient manner, but it allows for guarding against the potential outlier and avoid breaking the system.

Is this a good idea?

CodePudding user response：

You are saying that CustomerId is in practice always an integer, so if it comes as something that cannot be convert to INT, then there must be something wrong.

Why you want to risk to insert and have wrong data, that can potentially cause errors?

The better will be to explicitly cast the value to INT on INSERT and if there is error to check what's the issue and resolve it.

CodePudding user response：

Can't you write a conditional statement?

You can use ISNUMERIC() function to determine whether CustomerID is valid. It would return '1' if the given value is numeric, and 0 if not.

EX:

DECLARE @ISNUMERIC Integer 
SET @ISNUMERIC = (SELECT ISNUMERIC(CUSTOMERID))

Then you can use the variable to determine whether if you want to convert it to an int or not.