Sql-server – Why does SELECT 1/2 return 0

1. @@identity returns a numeric(38,0). You'll need to cast it to get it to an int.

   SELECT CAST(@@identity AS INT)

2. Also, try using scope_identity instead. If you have any triggers on the Customers table, you may end up getting the last identity from another table.

3. Finally, since you're using dapper, you'll want to wrap all of that inside a stored procedure so that you are guaranteed to execute the insert and then the select on the identity in the same batch.

   Theoretically, it should work most of the time to execute both of those on their own. But issues could arise if you have to go to the database twice. (E.g. how does this work with connection pooling? What about dropped connections? etc.) If you just throw it all in a stored procedure, you won't have to worry about that extra effort down the road.

From the POWER documentation:

Syntax

POWER ( float_expression , y )

Arguments

float_expression
Is an expression of type float or of a type that can be implicitly converted to float.

y
Is the power to which to raise float_expression. y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

Return Types

Returns the same type as submitted in float_expression. For example, if a decimal(2,0) is submitted as float_expression, the result returned is decimal(2,0).

The first input is implicitly cast to float if necessary.

The internal calculation is performed using float arithmetic by the standard C Runtime Library (CRT) function pow.

The float output from pow is then cast back to the type of the left hand operand (implied to be numeric(3,1) when you use the literal value 10.0).

Using an explicit float works fine in your case:

SELECT POWER(1e1, 38);
SELECT POWER(CAST(10 as float), 38.0);

An exact result for 10³⁸ cannot be stored in a SQL Server decimal/numeric because it would require 39 digits of precision (1 followed by 38 zeros). The maximum precision is 38.