Type INT

Question

Type INT

lesole.sebego

SSC Veteran

Points: 290
More actions
November 17, 2016 at 10:09 pm

#319062

Comments posted to this topic are about the item Type INT
November 17, 2016 at 10:11 pm

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

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

andrew_dale Ten Centuries Points: 1280 More actions · Answer 1

sneaky question. I tried to use by brain and check the valid data ranges (https://msdn.microsoft.com/en-us/library/ms187745(v=sql.105).aspx) in my head so choose the option that both fail, but missed the implicit conversion to INT by SUM so got it wrong.

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Thanks for an easy one to end the week.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

david.gugg SSCertifiable Points: 6094 More actions · Answer 3

GO 2

I've actually never seen this before, never knew you could tell it how many times to execute.

Be still, and know that I am God - Psalm 46:10

Revenant SSC-Forever Points: 42467 More actions · Answer 4

Revenant

SSC-Forever

Points: 42467

November 18, 2016 at 10:31 am

#1913488

A nice one - thanks, Lesole!

MMartin1 One Orange Chip Points: 27795 More actions · Answer 5

Interesting that SMALLINT will implicitly convert to INT , but INT wont implicitly convert to BIGINT. Is this the case also with a .NET language like C#?

----------------------------------------------------

George Vobr SSCrazy Eights Points: 9993 More actions · Answer 6

Thanks Lesole for an interesting topic.

In MSDN https://msdn.microsoft.com/en-us/library/ms187745.aspx is stated that

"Integer constants greater than 2,147,483,647 are converted to the decimal data type, not the bigint data type."

But integer constant with the border value equal 2,147,483,647 is converted always to the int data type.

See the examples below:

-- An example in the paragraph "Converting the Integer Data"

-- in MSDN "int, bigint, smallint, and tinyint (Transact-SQL)"

-- with an implicit conversion from an int to a decimal for Result2:

SELECT 2147483647 / 2 AS Result1, 2147483649 / 2 AS Result2;

-- Arithmetic overflow error converting expression to data type int for Result3:

SELECT 2147483647+1 AS Result3, 2147483649 AS Result4;

-- Explicit conversion from an int to a decimal in the arithmetical

-- expression for the Result5 or Result7:

SELECT 2147483647.+1 AS Result5, 2147483649+1 AS Result6;

-- OR

SELECT 2147483647+1. AS Result7, 2147483649+1 AS Result8;

-- etc...

Results

--------

Result1 Result2

----------- ---------------------------------------

1073741823 1073741824.500000

(1 row(s) affected)

Result3 Result4

----------- ---------------------------------------

Msg 8115, Level 16, State 2, Line 10

Arithmetic overflow error converting expression to data type int.

Result5 Result6

--------------------------------------- ---------------------------------------

2147483648 2147483650

(1 row(s) affected)

Result7 Result8

--------------------------------------- ---------------------------------------

2147483648 2147483650

(1 row(s) affected)

Yev.d Ten Centuries Points: 1029 More actions · Answer 7

david.gugg (11/18/2016)
GO 2
I've actually never seen this before, never knew you could tell it how many times to execute.

Same here.

Thanks, Lesole!

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 8

akljfhnlaflkj

SSC Guru

Points: 76202

November 21, 2016 at 7:48 am

#1913713

Thanks for that one.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 9

MMartin1 (11/18/2016)
Interesting that SMALLINT will implicitly convert to INT , but INT wont implicitly convert to BIGINT. Is this the case also with a .NET language like C#?

I didn't know that. Thanks.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

Thanks for the question.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP