Resources For IT Professionals
Post an article
Wikis - Page Details
  • First published by (eMicrosoft Partne)
  • When: 16 Jun 2013 4:17 PM
  • Last revision by (eMicrosoft Partne)
  • When: 4 Sep 2013 1:10 PM
  • Revisions: 19
  • Comments: 14
Options
RSSSubscribe to Article (RSS)
Can You Improve This Article?
Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.
Wiki  >  TechNet Articles  >  How SQL Server Determines Type of the Constant

How SQL Server Determines Type of the Constant

How SQL Server Determines Type of the Constant

Table of Contents


Problem Definition


 There was an interesting question asked recently in Transact-SQL forum "Basic doubt in Round function". 

The problem was stated as following:

SELECT ROUND(744, -3)

produced 1000 while

SELECT ROUND(744.0, -3)

gave an error "Arithmetic overflow error converting expression to data type numeric."

Explanation


So, what is happening here? Why we're getting this error? The explanation lies in the way SQL Server determines the type of the constant. In this particular case it figures that it can use precision 4 and scale 1 (1 figure after decimal point). So, that precision will not be enough to hold the value 1000 and thus we're getting the error.

We can verify the type, precision and scale using the following query:

SELECT
    SQL_VARIANT_PROPERTY(744.0, 'BaseType') as BaseType,
    SQL_VARIANT_PROPERTY(744.0, 'Precision') as Precision,
    SQL_VARIANT_PROPERTY(744.0, 'Scale') as Scale,
    SQL_VARIANT_PROPERTY(744.0, 'MaxLength') as MaxLength

which returns:

 BaseType  Precision   Scale   MaxLength 
 numeric  4   1  5


This page in BOL shows what types the constants can be. It does not explain the rules how SQL Server figures it out.

All constants have datatypes. Integer constants are given datatype int, decimal values are given datatype numeric(p,q) where p is the number of digits (not counting leading zeros) in the number, and q is the number of digits to the right of the decimal point (including trailing zeroes).

Conclusion


As shown in this article it is better to explicitly CAST to the desired type rather than rely on SQL Server making the decision.

See Also


This entry participated in the TechNet Guru contributions for June contest and won the Gold prize.
, , , , , , , , , , , , , , [Edit tags]
Leave a Comment
  • Please add 3 and 8 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Sort by: Published Date | Most Recent | Most Useful
Comments
  • 3 Sep 2013 10:32 AM

    Naomi  N edited Revision 14. Comment: Added See Also

  • 7 Aug 2013 11:57 AM

    Naomi  N edited Revision 13. Comment: Formatting

  • 11 Jul 2013 10:35 AM

    Naomi  N edited Revision 11. Comment: Added link to the blog

  • 10 Jul 2013 3:13 PM

    Naomi  N edited Revision 10. Comment: Minor edit

  • 7 Jul 2013 12:31 PM

    Naomi  N edited Revision 7. Comment: Added toc

  • 3 Jul 2013 5:42 AM

    Naomi  N edited Revision 6. Comment: Minor edit

  • 3 Jul 2013 3:34 AM

    Samuel Lester - MSFT edited Revision 5. Comment: Removed image since it was too bulky and replaced with a table for readability.

  • 3 Jul 2013 2:58 AM

    Samuel Lester - MSFT edited Revision 4. Comment: Formatting of the result set was a little confusing, so I just added a picture of the results from SSMS for readability.

  • 30 Jun 2013 8:26 PM

    Naomi  N edited Revision 3. Comment: Minor edit

  • 30 Jun 2013 8:16 PM

    Naomi  N edited Revision 1. Comment: Added content

Page 1 of 1 (10 items)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
Page 1 of 1 (14 items)