May 3, 2010 at 9:49 pm
Comments posted to this topic are about the item A Round Number[/url]
Some extra detail on this question to anyone interested:
SQL Server uses internal rules to assign a type to a literal value. When SQL Server sees the 0.5 value it assigns a type to the expression according to its internal rules. A lot of the documentation speaks of SQL Server using the smallest type it needs to contain the value - so in this case a NUMERIC(1,1) would be used. That may or may not be the case on every version of 2005 and 2008 ever - but I think it covers the currently supported versions. The problem highlighted by this question is that the result of the ROUND does not fit in a NUMERIC(1,1) so an overflow error results.
According to all but the most recent Books Online entry (which seems apply only to 2008 R2) the ROUND function returns an expression with the same type as its first parameter: http://msdn.microsoft.com/en-us/library/ms175003(SQL.90).aspx. My version of 2008 BOL shows the same thing (dated October 2009).
However, the current online entry in Books Online says that ROUND returns DECIMAL(38, s) when presented with a numeric input with precision p and scale s: http://msdn.microsoft.com/en-us/library/ms175003.aspx.
That would be fine if it were so, and would avoid the problem highlighted by this question, but even the latest versions of 2008 (10.02766) and 2005 (9.04285) do not behave this way. I believe the documentation is referring to 2008 R2, which is not very helpful for the vast majority of us. This seems like a welcome change - though there may be unexpected side-effects in some code.
I'm hoping for a good discussion about this question because the documentation is so thin/poor in this area.
Finally, a snippet of code to explore the auto-type-assignment behaviour of your version of SQL Server:
-- Just declare a table variable with the constant value of interest as a computed column
-- The computed column has a GUID name to make it easy to find
DECLARE @a TABLE (dummy INT NULL, [AB85729E-F946-4165-9F71-9162A5CFCCEB] AS 0.5);
-- Show the type assigned to the column
SELECT table_name = (SELECT T.name FROM tempdb.sys.tables T WHERE T.[object_id] = C.[object_id]),
[type_name] = (SELECT Y.name FROM tempdb.sys.types Y WHERE Y.system_type_id = C.system_type_id),
C.[precision],
C.scale,
C.is_nullable,
max_byte_length = C.max_length
FROM tempdb.sys.columns C
WHERE C.name = N'AB85729E-F946-4165-9F71-9162A5CFCCEB';
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 3, 2010 at 10:13 pm
Paul..Can u plz explain me the concept behind this "Stack overflow error" occurring?? !! Really challenging question..Everything is new for me in this query! Thanks in Advance! 🙂
------------------------
~Niths~
Hard Work never Fails 🙂
May 3, 2010 at 11:44 pm
I am not getting the error. It returns result as 1.0. I am using SQL Server 2005. Is there any setting, due to which I am not getting any error?
May 3, 2010 at 11:51 pm
ra.shinde (5/3/2010)
I am not getting the error. It returns result as 1.0. I am using SQL Server 2005. Is there any setting, due to which I am not getting any error?
U would have executed all the queries one by one separately.. try execute all the queries together by selecting all the 3 lines
------------------------
~Niths~
Hard Work never Fails 🙂
May 3, 2010 at 11:53 pm
ra.shinde (5/3/2010)
I am not getting the error. It returns result as 1.0. I am using SQL Server 2005. Is there any setting, due to which I am not getting any error?
But even if u execute one by one, the same error is thrown. I'm also using SQL server 2005 only. I did not change any setting before executing this query set.
------------------------
~Niths~
Hard Work never Fails 🙂
May 3, 2010 at 11:55 pm
I executed all three statement together. Still no error. Getting 1.0
May 4, 2010 at 12:42 am
Paul White NZ (5/3/2010)
Comments posted to this topic are about the item <A HREF="/questions/T-SQL/69825/">A round number</A>
Me too. I executed the code and there will be no error.
May 4, 2010 at 1:18 am
One important observation: This is an error caused within the client tool, not within SQL Server.
Query Analyzer has no issues; SQL Server management Studio does have the issue.
It does not matter against which database version you are executing this query.
Try this as well to see the error happen only when you actually return results to the client:
DECLARE @a sql_variant
SET @a = ROUND(0.5,0);
SELECT CAST(SQL_VARIANT_PROPERTY ( @a , 'BaseType') AS varchar(10)) [BaseType]
,CAST(SQL_VARIANT_PROPERTY ( @a , 'Precision') AS varchar(10)) AS [Precision]
,CAST(SQL_VARIANT_PROPERTY ( @a , 'Scale') AS varchar(10)) AS [Scale]
SELECT @a
SQL Server Query Analyzer will execute the code without error, and SQL Server Management Studio will error only for "SELECT @a".
To me it looks like this is an issue with incorrect metadata being returned. The data itself obviously seems to be correct.
Edit: sqlcmd does not throw an error either - so this seems to be an issue with SSMS only.
Best Regards,
Chris Büttner
May 4, 2010 at 1:36 am
Christian Buettner-167247 (5/4/2010)
One important observation: This is an error caused within the client tool, not within SQL Server. Query Analyzer has no issues; SQL Server management Studio does have the issue....
SQL Server Query Analyzer will execute the code without error, and SQL Server Management Studio will error only for "SELECT @a".
To me it looks like this is an issue with incorrect metadata being returned. The data itself obviously seems to be correct.
Edit: sqlcmd does not throw an error either - so this seems to be an issue with SSMS only.
Results may depend on session SETtings which may be different for those tools.
What does the following return for you in each client tool?
SELECT S.quoted_identifier,
S.arithabort,
S.ansi_null_dflt_on,
S.ansi_defaults,
S.ansi_warnings,
S.ansi_padding,
S.ansi_nulls,
S.concat_null_yields_null
FROM sys.dm_exec_sessions S
WHERE session_id = @@SPID;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
May 4, 2010 at 1:51 am
Does not look like it is settings related.
Here is the code executed via each client tool:
SET QUOTED_IDENTIFIER OFF
SET ARITHABORT OFF
SET ANSI_NULL_DFLT_ON OFF
SET ANSI_DEFAULTS OFF
SET ANSI_WARNINGS OFF
SET ANSI_PADDING OFF
SET ANSI_NULLS OFF
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT S.quoted_identifier,
S.arithabort,
S.ansi_null_dflt_on,
S.ansi_defaults,
S.ansi_warnings,
S.ansi_padding,
S.ansi_nulls,
S.concat_null_yields_null
FROM sys.dm_exec_sessions S
WHERE session_id = @@SPID;
DECLARE @a sql_variant
SET @a = ROUND(0.5,0);
SELECT CAST(SQL_VARIANT_PROPERTY ( @a , 'BaseType') AS varchar(10)) [BaseType]
,CAST(SQL_VARIANT_PROPERTY ( @a , 'Precision') AS varchar(10)) AS [Precision]
,CAST(SQL_VARIANT_PROPERTY ( @a , 'Scale') AS varchar(10)) AS [Scale]
,CAST(@a as varbinary) Binary
SELECT @a
SSMS:
quoted_identifier arithabort ansi_null_dflt_on ansi_defaults ansi_warnings ansi_padding ansi_nulls concat_null_yields_null
----------------- ---------- ----------------- ------------- ------------- ------------ ---------- -----------------------
0 0 0 0 0 0 0 0
BaseType Precision Scale Binary
---------- ---------- ---------- --------------------------------------------------------------
numeric 1 1 0x010100010A000000
-------------------------------------------------------------------------------------
An error occurred while executing batch. Error message is: Arithmetic Overflow.
SQLCMD:
quoted_identifier arithabort ansi_null_dflt_on ansi_defaults ansi_warnings ansi_padding ansi_nulls concat_null_yields_null
----------------- ---------- ----------------- ------------- ------------- ------------ ---------- -----------------------
0 0 0 0 0 0 0 0
BaseType Precision Scale Binary
---------- ---------- ---------- --------------------------------
numeric 1 1 0x010100010A000000
------------------------------------------------------------------------------------------
.0
The error message as such also indicates a client error as SQL Server errors usually look a little different, for example like this:
Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type numeric.
Best Regards,
Chris Büttner
May 4, 2010 at 2:00 am
Nice intuitive question and an equally impressive explanation, Mr.Black Cap. Learnt quite a few behind-the-scenes of datatype conversions and roundings!
Thanks!
May 4, 2010 at 2:34 am
Christian Buettner-167247 (5/4/2010)
One important observation: This is an error caused within the client tool, not within SQL Server.
Good observation, Christian. The format of the error message, which looks quite different from errors that are thrown by SQL Server, is also an indication that there is a different source of the error.
Here is another repro that runs fine on all clients, to demonstrate that SQL Server itself actually does not have any issues with this code:
DECLARE @a sql_variant;
SET @a = ROUND(0.5,0);
-- no error
SELECT CAST(@a AS decimal(12,2));
-- error when running on SSMS
SELECT @a;
go
-- Or, the more direct approach
SELECT CAST(ROUND(0.5,0) AS decimal(12,2));
go
May 4, 2010 at 2:48 am
ra.shinde (5/3/2010)
I executed all three statement together. Still no error. Getting 1.0
Same here. Looks like another bad question. The explanation was good but the answer is still wrong.
I executed all three commands at one time and am using SQL 2005 with SQLDbx as the client. It produces Numeric(1,1) with an answer of 1.
I selected 1 as the correct answer and was told it was wrong.
May 4, 2010 at 3:05 am
cengland0 (5/4/2010)
ra.shinde (5/3/2010)
Looks like another bad question.
I would not consider this a bad question - I assume that almost noone actually knew the behaviour differences and actual cause of the error in SSMS before reading the comments.
I definitively learned something again today, and therefore I consider this a good question.
In the end, this is to some extent unpredictable behaviour. Paul definitively did enough research & due diligence before posting this question - sometimes items only get caught in the field test when tested with a wide variety of tools & settings etc. This is the real life.
So definitively thumbs up from me for this QODT!
Best Regards,
Chris Büttner
May 4, 2010 at 3:08 am
Thank you Hugo - feels good to get positive feedback from an expert 🙂
Best Regards,
Chris Büttner
Viewing 15 posts - 1 through 15 (of 43 total)
You must be logged in to reply to this topic. Login to reply