Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A round number


A round number

Author
Message
Oleg Netchaev
Oleg Netchaev
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1693 Visits: 1808
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
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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 :-D
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
SQLblog.com
@SQL_Kiwi
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6235 Visits: 10403
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, 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

Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
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
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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 Sad

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
SQLblog.com
@SQL_Kiwi
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6235 Visits: 10403
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, 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

Chad Crawford
 Chad Crawford
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2438 Visits: 18649
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
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLblog.com
@SQL_Kiwi
Christian Buettner-167247
Christian Buettner-167247
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2949 Visits: 3889
Paul White NZ (5/6/2010)
Please vote and mark as reproducible if you can.

Done Smile

Best Regards,

Chris Büttner
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search