December 7, 2011 at 5:51 pm
Microsoft documentation for the unary plus operator seems to me to be blindly cloned from the doc for the negative operator. After describing what it is (just a plus sign in front of a numeric expression), and confirming that it doesn't act like the ABS() function, but rather leaves negative expressions as negative and positive expressions as positive the doc for the Result Types says this:
Returns the data type of numeric_expression, except that an unsigned tinyint expression is promoted to a smallint result.
At first that made sense to me, since a single-byte tinyInt has no provision for a sign. But after running a test using the DataLength() function, I've come to the conclusion that BOL is telling a lie here. In the results of these three queries, one can see that the internal length of the original tinyInt datum and the result of the unary plus operation are both 1 byte, indicating the unary plus did NOT promote the result to a smallInt and the length of the result of the negative operation is the 2 bytes, showing that it did the documented promotion to smallInt.
My question is whether I've missed something here, or was my initial guess right, that MS has an error in the BOL?
Create Table UnaryPlusExperiment
(myNumber tinyInt not null)
Insert UnaryPlusExperiment (myNumber)
values (42)
Select myNumber as myNumber
,+myNumber as myNumberPlus
,-myNumber as myNumberMinus
from UnaryPlusExperiment
Select len(myNumber) as LengthMyNumber
,len(+myNumber) as LengthMyNumberPlus
,len(-myNumber) as LengthMyNumberMinus
from UnaryPlusExperiment
Select datalength(myNumber) as DataLengthMyNumber
,datalength(+myNumber) as DataLengthMyNumberPlus
,datalength(-myNumber) as DataLengthMyNumberMinus
from UnaryPlusExperiment
Results:myNumber myNumberPlus myNumberMinus
-------- ------------ -------------
42 42 -42
(1 row(s) affected)
LengthMyNumber LengthMyNumberPlus LengthMyNumberMinus
-------------- ------------------ -------------------
2 2 3
(1 row(s) affected)
DataLengthMyNumber DataLengthMyNumberPlus DataLengthMyNumberMinus
------------------ ---------------------- -----------------------
1 1 2
(1 row(s) affected)
December 8, 2011 at 12:35 am
It assumes the number is positive unless specifically marked as negative. Thus 42 and +42 are same.
December 8, 2011 at 2:50 am
john.arnott (12/7/2011)
or was my initial guess right, that MS has an error in the BOL?
Wouldn't be at all surprising. You can post it as a connect item and ask for it to be corrected in the next refresh.
You are completely correct, the +42 is still a tinyint and the -42 a smallint. You can see that clearly with this slight modification to your code
Create Table UnaryPlusExperiment
(myNumber tinyInt not null)
Insert UnaryPlusExperiment (myNumber)
values (42)
Select myNumber as myNumber
,+myNumber as myNumberPlus
,-myNumber as myNumberMinus
INTO CheckingDataTypes
from UnaryPlusExperiment
EXEC sp_help 'CheckingDataTypes'
The data types are tinyint, tinyint, smallint.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 8, 2011 at 9:30 am
Thank you Gail.
Using sp_Help removes any doubt. The datalength seemed like strong evidence, but this is more clear.
February 16, 2012 at 2:36 pm
GilaMonster (12/8/2011)
john.arnott (12/7/2011)
or was my initial guess right, that MS has an error in the BOL?Wouldn't be at all surprising. You can post it as a connect item and ask for it to be corrected in the next refresh.
.......
Thanks again, Gail. I just got notice from MS that they're correcting the entry in SS 2008 R2 and 2012, but that it won't appear for a while. I'm proud to have done my tiny <snicker> part for the SQL community.
February 16, 2012 at 2:57 pm
Good to hear.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply