What are the rules for CAST or CONVERT, and INT vs BIGINT?

Question

What are the rules for CAST or CONVERT, and INT vs BIGINT?

jpSQLDude

SSCrazy

Points: 2571
More actions
May 5, 2011 at 12:47 pm

#389454

I get why the first one doesn't work, it is too big for an INT. But what about the next two? It seems like they should work. And why is it if you CONVERT just the 8 to a BIGINT it then works? Any simpler way to do a calculation like this?
--Does not work:
SELECT 8*1024*1024*1024
--Does not work:
SELECT CONVERT(BIGINT,8*1024*1024*1024)
--Does not work:
SELECT CAST(8*1024*1024*1024 AS BIGINT)
--Works
SELECT CONVERT(BIGINT,8)*1024*1024*1024
--Works
DECLARE @1024 BIGINT
SET @1024 = 1024
SELECT 8 * @1024 * @1024 * @1024

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

In each of the first three you have ints doing the math. The arithmetic overflow occurs prior to the attempt to convert those ints to a bigint.

Thus by converting one of the values to bigint, you are now performing the math first on bigint.

Make sense?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Lowell SSC Guru Points: 323518 More actions · Answer 2

SQL stays in a single datatype when you are doing the math;

so when you do this portion of your formula:

8*1024*1024*1024, which are all integers, SQL assumes/shortcuts a decision that INT math remains in an INt datatype...so it overflows before teh CONVERT/CAST occurs.

SQL assumes that teh results will fit in the largest datatype featured int eh formula.

when one or more of teh items are a datatype that takes more memory (like when you converted one of the items to BIGINT, ot works.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jpSQLDude SSCrazy Points: 2571 More actions · Answer 3

SQLRNNR (5/5/2011)
The arithmetic overflow occurs prior to the attempt to convert those ints to a bigint.

Got it! That makes sense.

Thanx!

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

jpSQLDude (5/5/2011)
SQLRNNR (5/5/2011)
The arithmetic overflow occurs prior to the attempt to convert those ints to a bigint.
Got it! That makes sense.
Thanx!

Good - you're welcome.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Paul White SSC Guru Points: 150468 More actions · Answer 5

It's all about type conversions. You can find the data type precedence rules here:

http://msdn.microsoft.com/en-us/library/ms190309.aspx

An extract:

When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type

Notice that BIGINT has a higher precedence than INT, so in an expression that mixes the two, the INT will be implicitly converted to BIGINT. Some of the more complex conversion rules are undocumented and a bit quirky (often for backward compatibility reasons). It is best to be explicit about types rather than relying on implicit conversions.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

jpSQLDude SSCrazy Points: 2571 More actions · Answer 6

SQLkiwi -- now THAT actually makes it clear... thank you very much!