Difference between numeric float and decimal in SQL Server

0 votes
What are the distinctions between the decimal, float, and numeric datatypes, and when should each one be used?

Which one is favoured and why for any form of financial transaction (like a salary field)?
Sep 10, 2022 in Database by Kithuzzz
• 38,010 points
1,945 views

1 answer to this question.

0 votes

use the float or real data types only if the precision provided by decimal (up to 38 digits) is insufficient

  • Approximate numeric data types(see table 3.3) do not store the exact values specified for many numbers; they store an extremely close approximation of the value.

  • Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators. It is best to limit float and real columns to > or < comparisons.

so generally choosing Decimal as your data type is the best bet if

  • your number can fit in it. Decimal precision is 10E38[~ 38 digits]
  • smaller storage space (and maybe calculation speed) of Float is not important for you
  • exact numeric behavior is required, such as in financial applications, in operations involving rounding, or in equality checks.

  1. Exact Numeric Data Types decimal and numeric - MSDN
  • numeric = decimal (5 to 17 bytes)
    • will map to Decimal in .NET
    • both have (18, 0) as default (precision,scale) parameters in SQL server
    • scale = maximum number of decimal digits that can be stored to the right of the decimal point.
    • money(8 byte) and smallmoney(4 byte) are also Exact Data Type and will map to Decimal in .NET and have 4 decimal points
  1. Approximate Numeric Data Types float and real - MSDN
  • real (4 bytes)
    • will map to Single in .NET
    • The ISO synonym for real is float(24)
  • float (8 bytes)
    • will map to Double in .NET

Exact Numeric Data Types Approximate Numeric Data Types

  • All exact numeric types always produce the same result, regardless of which kind of processor architecture is being used or the magnitude of the numbers
  • The parameter supplied to the float data type defines the number of bits that are used to store the mantissa of the floating point number.
  • Approximate Numeric Data Type usually uses less storage and have better speed (up to 20x) and you should also consider when they got converted in .NET
  • What is the difference between Decimal, Float and Double in C#
  • Decimal vs Double Speed
  • SQL Server - .NET Data Type Mappings (From MSDN)

main source : MCTS Self-Paced Training Kit (Exam 70-433): Microsoft® SQL Server® 2008 Database Development - Chapter 3 - Tables, Data Types, and Declarative Data Integrity Lesson 1 - Choosing Data Types (Guidelines) - Page 93

answered Sep 11, 2022 by narikkadan
• 63,700 points

Related Questions In Database

0 votes
0 answers

What's the difference between a temp table and table variable in SQL Server?

We can build temp tables in SQL ...READ MORE

Feb 25, 2022 in Database by Vaani
• 7,060 points
481 views
0 votes
0 answers

What is the difference between drop table and delete table in SQL Server?

What is the distinction between the following ...READ MORE

Aug 9, 2022 in Database by Kithuzzz
• 38,010 points
594 views
0 votes
0 answers

Difference between Implicit Conversion and Explicit Conversion in SQL Server

Could you kindly explain the differences between ...READ MORE

Sep 4, 2022 in Database by Kithuzzz
• 38,010 points
376 views
0 votes
1 answer

Difference between single and double quotes in SQL

Single quotes are used to indicate the ...READ MORE

answered Sep 11, 2018 in Database by CodingByHeart77
• 3,740 points
28,823 views
0 votes
1 answer

Calculate Time Intersection to Correlate Sequences of Independent Events

I think this solution requires a CROSS JOIN implementation. ...READ MORE

answered Oct 26, 2018 in Power BI by Upasana
• 8,620 points
626 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,060 points
654 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4, 2022 in Database by Vaani
• 7,060 points
359 views
0 votes
1 answer

What is a stored procedure?

A stored procedure is a set of ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,060 points
865 views
0 votes
1 answer

Difference between EXISTS and IN in SQL?

Although the exists keyword can be used ...READ MORE

answered Sep 11, 2022 in Database by narikkadan
• 63,700 points
641 views
0 votes
1 answer

Use of contains() in sql server

The straightforward method is shown here. You ...READ MORE

answered Sep 10, 2022 in Database by narikkadan
• 63,700 points
689 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP