Truncating and Rounding Results
Last month, in the SQL Server Users Group meeting, I presented a session called “Things You didn’t Know about SQL Server”. The first “thing” I presented was the result of casting (and truncating) a long integer value into a short VARCHAR type. The result is “*” (asterisk).
For example, the result of the following batch is “*”:
DECLARE @Text AS VARCHAR(5) = 123456; SELECT @Text; GO
@Text AS VARCHAR(5) = 123456;
In the session I said that I have no idea why this is the result and that I haven’t found any documentation about it. Yakov Markovitz sent me an email a few days later and pointed me to the section “Truncating and Rounding Results” under the topic “CAST and CONVERT” in Books Online. Here is a quote:
When you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.From data typeTo data typeResultint, smallint, or tinyintchar*varchar*ncharEnvarcharEmoney, smallmoney, numeric, decimal, float, or realcharEvarcharEncharEnvarcharE
* = Result length too short to display. E = Error returned because result length is too short to display.
So first of all, I would like to thank Yakov for referring me to the documentation. But I still have no idea why SQL Server behaves like that. I asked Yakov if he knows, and he replied that this is a very old behavior from the happy Sybase days.
In the next SQL Server Users Group meeting I will present the second part of the “Things You didn’t Know about SQL Server” session. I hope to see you there…