Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

A round number Expand / Collapse
Author
Message
Posted Tuesday, May 04, 2010 10:21 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, April 21, 2014 10:01 AM
Points: 1,676, Visits: 1,745
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
Post #915456
Posted Tuesday, May 04, 2010 12:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #915557
Posted Tuesday, May 04, 2010 1:28 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 6,546, Visits: 8,770
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #915608
Posted Tuesday, May 04, 2010 9:40 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
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?




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #915787
Posted Wednesday, May 05, 2010 2:22 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:19 AM
Points: 2,814, Visits: 3,851
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
Post #915886
Posted Wednesday, May 05, 2010 4:20 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #915925
Posted Wednesday, May 05, 2010 8:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 3:26 PM
Points: 6,546, Visits: 8,770
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?

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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #916078
Posted Wednesday, May 05, 2010 11:21 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:17 PM
Points: 2,602, Visits: 17,846
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
Post #916303
Posted Thursday, May 06, 2010 7:27 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 11,168, Visits: 10,939
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #917549
Posted Friday, May 07, 2010 12:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 7:19 AM
Points: 2,814, Visits: 3,851
Paul White NZ (5/6/2010)
Please vote and mark as reproducible if you can.

Done :)


Best Regards,
Chris Büttner
Post #917661
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse