Real Maths

Question

Real Maths

Viewing 2 posts - 16 through 16 (of 16 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

John Mitchell-245523 - Tuesday, September 19, 2017 8:31 AM
Good question. Let's investigate.
USE tempdb;
SELECT -1 AS Col1 , 0 AS Col2 , 1 AS Col3 , 65 AS Col4 , 32768 AS Col5 , 2147483648 AS Col6 , 9223372036854775808 AS Col7 , 2.2 AS Col8 , 2.0000002 AS Col9 , 2.0000000000000002 AS Col10 , 2.0000000000000000000000000000000000002 AS Col11 , 222222.02 AS Col12 , 4.0/3.0 AS Col13 INTO #MyTest;
SELECT * FROM #MyTest;
EXEC sp_help '#MyTest';
Looks as if it uses int if it can, otherwise numeric with the smallest scale and precision possible.
John

It annoys me that it picks numeric when bigint would do. If it would choose bigint for integers that don't fit into int but do fit into bigint it would be easy to choose numeric instead by adding ".0" at the end of the integer. Writing a cast or conversion call to get a bigint is a pain. However we have to suffer that pain if we want columns with varchar(x) type (for specific values of x) or nvarchar or smallint or tinyint which are often needed.
It is of course easy to get it to create float columns, just use scientific notation for the value, for example a line like
, 1E0 as Col14
would lead to a column called Col14 with type float(53) containing the value 1.
However, getting float(24) (aka "real") requires writing a cast or conversion call.

Tom

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

Nice question to get the grey matter moving. As is true so many times on this site, the discussion that follows is so very good.

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