A round number

Question

A round number

Viewing 14 posts - 31 through 43 (of 43 total)

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

Oleg Netchaev SSCertifiable Points: 5276 More actions · Answer 1

Very good question, thank you Paul.

It is interesting to see how perfectly sql_variant_property handles the literals. For example, 3 billion can be represented as bigint or as decimal(10, 0), and the latter is definitely cheaper:

declare @bi bigint;

declare @dc decimal(10, 0);

select @bi = 3000000000, @dc = @bi;

select

datalength(@bi) bigint_length, datalength(@dc) decimal_length;

The above returns

bigint_length decimal_length

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

8 5

So, sql_variant_property correctly opts for decimal base type, 10 precision and 0 scale rather than bigint base type which would yield 19 precision and 0 scale:

select

sql_variant_property(cast(3000000000 as sql_variant), 'BaseType') BaseType,

sql_variant_property(cast(3000000000 as sql_variant), 'Precision') ThePrecision,

sql_variant_property(cast(3000000000 as sql_variant), 'Scale') Scale;

This returns

BaseType ThePrecision Scale

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

numeric 10 0

Oleg

Paul White SSC Guru Points: 150467 More actions · Answer 2

Oleg Netchaev (5/4/2010)
So, sql_variant_property correctly opts for decimal base type, 10 precision and 0 scale rather than bigint base type which would yield 19 precision and 0 scale:

Hey Oleg, and thanks - it's been a fun night here 😀

About the data type - I don't think you can credit sql_variant_property - it's those mysterious rules SQL Server uses for assigning a type to a literal. The literal gets a type before being cast to sql_variant. The sql_variant just contains the assigned type. That's my take on it anyway!

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95460 More actions · Answer 3

Paul White NZ (5/4/2010)
Results so far for SELECT ROUND(0.5, 0) for me:

Paul, this question has turned into a very interesting topic.

I also have isqlw / isql for SQL 2000 available, and a few SQL 2000 servers; here are my tests for running:

SET ARITHABORT OFF;SET NUMERIC_ROUNDABORT OFF;PRINT ROUND(0.5,0);SELECT ROUND(0.5,0)for all clients on 2000/2005/2008 servers:

isql:

--> SQL 2008: PRINT: Arithmetic overflow occurred msg (no error #); SELECT: NULL, + Arithmetic overflow occurred msg (no error #)

--> SQL 2005: PRINT: 1.0; SELECT: 1.0

--> SQL 2000: PRINT: 1.0; SELECT: 1.0

osql:

--> SQL 2008: PRINT: Error 8115; SELECT: Error 8115

--> SQL 2005: PRINT: 1.0; SELECT 1.0

--> SQL 2000: PRINT: 1.0; SELECT 1.0

sqlcmd:

--> SQL 2008: PRINT: Error 8115; SELECT: Error 8115

--> SQL 2005: PRINT: 1.0; SELECT .0

--> SQL 2000: PRINT: 1.0; SELECT .0

isqlw:

--> SQL 2008: PRINT: Error 8115; SELECT: Error 8115

--> SQL 2005: PRINT: 1.0; SELECT 1.0

--> SQL 2000: PRINT: 1.0; SELECT 1.0

SSMS (2005 & 2008):

--> SQL 2008: PRINT: Error 8115; SELECT: Error 8115 & returns an empty result set

--> SQL 2005: PRINT: 1.0; SELECT Arithmetic overflow error msg (no error #)

--> SQL 2000: PRINT: 1.0; SELECT Arithmetic overflow error msg (no error #)

Edit: removed excess quotes, bolded text.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Paul White SSC Guru Points: 150467 More actions · Answer 4

Thanks Wayne, that's very useful! My only remaining question is what versions of SQL Server (engine and SSMS) you are running. I guess they are similar to mine - and you have patched your SSMS as well as the server - since our results agree so well.

isql eh? :blink:

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 5

Some more tests showing the bug "in action":

SELECT '0.5' [Expression], CAST(0.5 as varbinary) AS [Binary Representation]

UNION ALL SELECT 'ROUND(0.5,0)', CAST(ROUND(0.5,0) as varbinary)

UNION ALL SELECT '1.0', CAST(1.0 as varbinary)

UNION ALL SELECT '0.0', CAST(0.0 as varbinary)

Expression Binary Representation

0.5 0x0101000105000000

ROUND(0.5,0) 0x010100010A000000

1.0 0x020100010A000000

0.0 0x0101000100000000

The binary representation of the rounded value shows the problem: A (10) does not fit into decimal(1,1). This is definitively a bug (as already shown by Paul with DBCC CHECKDB)

Just some further explanation on the binary representation:

The first 2 digits seem to be the precision, the second 2 digits the scale.

I have no idea what the following 4 digits ('0001') mean

The rest seems to be the number without decimal point (in reverse byte order)

Example:

1.0 0x020100010A000000

Precision = 2, Scale = 1, Value = A (10)

Since we have a scale of 1, the decimal point is inserted between the 1 and the 0:

1.0

So obviously the following is not a valid binary representation of the decimal datatype:

0x010100010A000000

You cannot have a precision of 1 for the value A (10)

You either need a precision of 2 for that, or you need to have the value 1 instead.

Paul, did you already (re-)open a connect item, or should I do that?

Best Regards,

Chris Büttner

Paul White SSC Guru Points: 150467 More actions · Answer 6

Christian Buettner-167247 (5/5/2010)
I have no idea what the following 4 digits ('0001') mean.

The second byte '01' is the sign. Can't think what the first byte is for 🙁

Paul, did you already (re-)open a connect item, or should I do that?

I think the issue is different enough to warrant a new item - I haven't started on it yet, but I intend to.

I'm not expecting too much from Microsoft though: 2005 is close to the end of its life, and the problem is already fixed in 2008.

Maybe they'll surprise me - they do seem to have been somewhat charitable recently.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

WayneS SSC Guru Points: 95460 More actions · Answer 7

Paul White NZ (5/4/2010)
Thanks Wayne, that's very useful! My only remaining question is what versions of SQL Server (engine and SSMS) you are running. I guess they are similar to mine - and you have patched your SSMS as well as the server - since our results agree so well.
isql eh? :blink:

Servers:

SQL 2008: 10.0.1600.22

SQL 2005: 9.00.4053.00

SQL 2000: 8.00.2055

SSMS 2008: 10.0.1600.22

SSMS 2005: 9.00.4035.00

SQL Query Analyzer 8.00.2039

I'll edit this to include SSMS 2005 later... have to get that from a co-worker.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Chad Crawford SSChampion Points: 11610 More actions · Answer 8

Wow. I didn't believe that the error message was coming from the client - I still figured it was one of the settings being missed somewhere, but I ran a profiler trace watching user errors and sure enough, the server didn't send an error back to the client. I never would have guessed that the client would provide an error when the server didn't. My apologizes for ever doubting!

Thanks,

Chad

Paul White SSC Guru Points: 150467 More actions · Answer 9

Connect Item added:

https://connect.microsoft.com/SQLServer/feedback/details/557523/automatic-type-assignment-causes-data-corruption

Please vote and mark as reproducible if you can.

Thanks to everyone that contributed to the discussion - especially Christian, Wayne, and Oleg.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 10

Paul White NZ (5/6/2010)
Please vote and mark as reproducible if you can.

Done 🙂

Best Regards,

Chris Büttner

Michael Poppers SSCrazy Points: 2151 More actions · Answer 11

Christian Buettner-167247 (5/7/2010)
Paul White NZ (5/6/2010)
Please vote and mark as reproducible if you can.
Done 🙂

Ditto.

Paul, isn't it worth noting the different "ThePrecision" results from the sql_variant_property() output noted earlier in this discussion? Would you like me to add the following code snippet as a comment to your Connect submission? Thanks.

select

sql_variant_property(cast(1.0 as sql_variant), 'BaseType') BaseType,

sql_variant_property(cast(1.0 as sql_variant), 'Precision') ThePrecision,

sql_variant_property(cast(1.0 as sql_variant), 'Scale') Scale;

select

sql_variant_property(cast(ROUND(0.5,0) as sql_variant), 'BaseType') BaseType,

sql_variant_property(cast(ROUND(0.5,0) as sql_variant), 'Precision') ThePrecision,

sql_variant_property(cast(ROUND(0.5,0) as sql_variant), 'Scale') Scale;

Paul White SSC Guru Points: 150467 More actions · Answer 12

Michael Poppers (5/7/2010)
Ditto.
Paul, isn't it worth noting the different "ThePrecision" results from the sql_variant_property() output noted earlier in this discussion? Would you like me to add the following code snippet as a comment to your Connect submission?

Thank you for your vote Michael - and yes, please feel free to add the comment 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Michael Poppers SSCrazy Points: 2151 More actions · Answer 13

Paul White NZ (5/7/2010)
Michael Poppers (5/7/2010)
Ditto.
Paul, isn't it worth noting the different "ThePrecision" results from the sql_variant_property() output noted earlier in this discussion? Would you like me to add the following code snippet as a comment to your Connect submission?
Thank you for your vote Michael - and yes, please feel free to add the comment 🙂

Done. Thanks.

Dhruvesh Shah SSCrazy Points: 2303 More actions · Answer 14

Dhruvesh Shah

SSCrazy

Points: 2303

November 7, 2010 at 4:59 pm

#1247499

I didn't get any error. i got 0.00 i wonder why