Casting Tiny Numbers

Question

Casting Tiny Numbers

Steve Jones - SSC Editor

SSC Guru

Points: 736063
More actions
March 2, 2020 at 12:00 am

#3710035

Comments posted to this topic are about the item Casting Tiny Numbers

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 1

Good basic question, Steve. My only objection would be the title of the question itself because this also occurs for much larger numbers and larger VARCHARs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 2

While I agree with Jeff, it doesn't apply to all larger numbers. TINYTINT, SMALLINT, and INT's have the same behavior but bigints throw an error instead.

As for the VARCHAR value, as long as it is 1 or more under the number of digits in the variable you will get the answer (or a derivative of it) from the QOTD.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 3

This question isn't designed to cover all possibilities. Writing questions like that is hard and fraught with perils. You are welcome to submit your own questions about other types of numerical values.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 4

I wasn't trying to rag on you Steve. I was just commenting on the different data types (at least in SQL 2008 R2 which I tested it in) that give that result.

Testing it against all SQL versions and all datatypes would be a nightmare. And what happens when a SP or CU changes the behavior? I like the question and the link is good too. Tells you what to expect with most data types!

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jeff Moden SSC Guru Points: 1004284 More actions · Answer 5

Steve Jones - SSC Editor wrote:

This question isn't designed to cover all possibilities. Writing questions like that is hard and fraught with perils. You are welcome to submit your own questions about other types of numerical values.

Oh no... I get that. It was a good question.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stewart "Arturius" Campbell SSC Guru Points: 72617 More actions · Answer 6

This has bitten me in the past (front-end developers not making provision for very large integers).

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

SQLWinther SSCertifiable Points: 6294 More actions · Answer 7

Thanks for the Question! That was unexpected.

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform