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 Monday, May 03, 2010 9:49 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:29 PM
Points: 11,168, Visits: 10,928
Comments posted to this topic are about the item A Round Number

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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #915058
Posted Monday, May 03, 2010 10:13 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 08, 2010 7:47 AM
Points: 26, Visits: 42
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
Post #915060
Posted Monday, May 03, 2010 11:44 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 23, 2012 10:23 PM
Points: 400, Visits: 16
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?
Post #915077
Posted Monday, May 03, 2010 11:51 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 08, 2010 7:47 AM
Points: 26, Visits: 42
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
Post #915081
Posted Monday, May 03, 2010 11:53 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 08, 2010 7:47 AM
Points: 26, Visits: 42
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
Post #915083
Posted Monday, May 03, 2010 11:55 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 23, 2012 10:23 PM
Points: 400, Visits: 16
I executed all three statement together. Still no error. Getting 1.0
Post #915084
Posted Tuesday, May 04, 2010 12:42 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Sunday, February 23, 2014 9:41 PM
Points: 646, Visits: 45
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.
Post #915098
Posted Tuesday, May 04, 2010 1:18 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:19 AM
Points: 2,814, Visits: 3,851
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
Post #915105
Posted Tuesday, May 04, 2010 1:36 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 11:29 PM
Points: 11,168, Visits: 10,928
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #915116
Posted Tuesday, May 04, 2010 1:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:19 AM
Points: 2,814, Visits: 3,851
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
Post #915123
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse