Watch this week's video on YouTube
Pop quiz: What will be the output of the below three statements?
DECLARE @val_varchar varchar(3) = '100';
DECLARE @val_bigint bigint = 100;
DECLARE @val_tinyint tinyint = 100;
PRINT('varchar to varchar:');
PRINT(CAST(@val_varchar AS VARCHAR(2)));
PRINT('bigint to varchar:');
PRINT(CAST(@val_bigint AS VARCHAR(2)));
PRINT('tinyint to varchar:');
PRINT(CAST(@val_tinyint AS VARCHAR(2)));
As you might have guessed, the first conversion truncates the value, leaving the answer as "10":
The second response is also somewhat intuitive - a number can't be converted into string that has fewer places than original digits:
However, the final answer returns:
NOTE: if we change all of the conversions to VARCHAR(3) above, all three results return the string value '100'
What the ****?
Why does SQL Server sometimes error when converting a number into a string, but other times succeeds and returns an asterisk?
I don't know.
The best (and logical) answer I could find online is from Robert Sheldon, who attributes it to poor error handling practices, "...before error handling got a more reputable foothold."
My understanding is that the asterisk was originally the default truncation value. Later, when the SQL Server development team was adding the bigint datatype, they decided throwing an exception is a better way to handle truncation in errors. However, probably for backwards compatibility, they never went back to change the behavior of the asterisks for pre-existing datatypes.
The documentation is clear about this behavior as well: any int, smallint, or tiny int when converted to char or varchar with fewer characters will result in an asterisk.
This becomes a problem if you create strings out of tinyints, smallints, and ints and suddenly start receiving values larger than you originally expected:
DECLARE @Age int = 75;
PRINT(CAST(@Age AS VARCHAR(2)) + ' years old.');
SET @Age = 100;
PRINT(CAST(@Age AS VARCHAR(2)) + ' years old.');
Conversions are hard
So is SQL Server to blame for this issue? Sure. But I can empathize: breaking changes are not something people typically want to add to a product.
I've written before how implicit conversions can lead to funny results in SQL Server. This asterisk scenario is another case of SQL Server having to make a judgement call about handling an impossible situation you created for it.
While it's easy to complain about whether SQL Server handles the conversion the correct way or not, at the end of the day the responsibility falls on you to prevent these types of impossible calculations from being requested of SQL Server in the first place.