Need some t-sql clarification

Question

This is a follow up to my previous question, in t-sql

SELECT SCOPE_IDENTITY()

returns a BIGINT, I did the following to get it to return an INT:

DECLARE @X INT

INSERT ...

SELECT @X = SCOPE_IDENTITY()

-- if i don't include the line below, it will return a BIGINT

SELECT @X

Why does it return a BIGINT unless I do SELECT @X at the end?

p.s. turns out

SELECT @X = SCOPE_IDENTITY()

doesn't return anything, it just sets @x

Answer 1

SELECT SCOPE_IDENTITY()

returns a BIGINT as you have seen

SELECT @X = SCOPE_IDENTITY()

returns BIGINT and casts it into INT variable @X

So you are returning BIGINT SCOPE_IDENTITY and also concurrently casting it to INT and setting that result to @X.

Returning @X returns the INT result.

Just some interesting reading on the subject.

SQL Server 7.0 Books Online also stated: "It is recommended that SET @local_variable be used for variable assignment rather than SELECT @local_variable."

Answer 2

The statement

  SELECT @X = SCOPE_IDENTITY()

is an assignment statement. As in most programming languages, an assignment statement is executed by first evaluating the right hand side. In this case the right hand side evaluates to a bigint. When the value of @X gets the resulting bigint, there is an implicit type conversion, because @X is a different type (int) than the value it's receiving.

SQL is a typed language, and the type of an expression (such as SCOPE_IDENTITY() here) depends on the expression, not on what happens to the expression's value after evaluation.

Analogy:

DECLARE @i INT;
SET @i = 3.2 + 0.2;

You wouldn't suggest that 3.2 + 0.2 is an integer, would you? It's 3.4, a decimal. Only because of the assignment is there an implicit conversion to INT.

There's no magic in most programming languages.