Skip to content
Fix Code Error

Difference between numeric, float and decimal in SQL Server

March 13, 2021 by Code Error
Posted By: Anonymous

What are the differences between numeric, float and decimal datatypes and which should be used in which situations?

For any kind of financial transaction (e.g. for salary field), which one is preferred and why?

Solution

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

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

  • Avoid using float or real columns in WHERE clause search conditions, especially the = and <> operators (Technet)

so generally because the precision provided by decimal is [10E38 ~ 38 digits] if your number can fit in it, and smaller storage space (and maybe speed) of Float is not important and dealing with abnormal behaviors and issues of approximate numeric types are not acceptable, use Decimal generally.

more useful information

  • numeric = decimal (5 to 17 bytes) (Exact Numeric Data Type)
    • 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.
    • kindly note that money(8 byte) and smallmoney(4 byte) are also exact and map to Decimal In .NET and have 4 decimal points(MSDN)
    • decimal and numeric (Transact-SQL) – MSDN
  • real (4 byte) (Approximate Numeric Data Type)
    • will map to Single in .NET
    • The ISO synonym for real is float(24)
    • float and real (Transact-SQL) – MSDN
  • float (8 byte) (Approximate Numeric Data Type)
    • will map to Double in .NET
  • 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)

Exact Numeric Data Types
Approximate Numeric Data Types

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 By: Anonymous

Related Articles

  • Reference — What does this symbol mean in PHP?
  • Form field border-radius is not working only on the…
  • How to get second-highest salary employees in a table
  • How can i display data from xml api in flutter?
  • What are the undocumented features and limitations…
  • Sequelize returning one result when counting AVG in…
  • How to verify strings and doubles in switch satements
  • Node JS and Sequelize Error: Cannot set headers…
  • What does this symbol mean in JavaScript?
  • How do I include certain conditions in SQL Count
  • Problems with Android Fragment back stack
  • What's the difference between a single precision and…
  • Unable to begin a distributed transaction
  • Python: Float to Decimal conversion and subsequent…
  • Printf width specifier to maintain precision of…
  • Ember Data: how do you render 'hasMany' data?
  • WordPress/WooCommerce: Save custom payment post meta
  • Convert JSON object to Ember data Model
  • Database development mistakes made by application developers
  • java.sql.SQLException: - ORA-01000: maximum open…
  • There's an InputMismatchException, how do i fix it?
  • Find max and second max salary for a employee table MySQL
  • How can I format a decimal to always show 2 decimal places?
  • Memcached vs. Redis?
  • Improve INSERT-per-second performance of SQLite
  • How to truncate float values?
  • Get the second highest value in a MySQL table
  • SQL query return data from multiple tables
  • How to declare a type as nullable in TypeScript?
  • The definitive guide to form-based website authentication
  • How to implement a in JavaScript?
  • Why do we always prefer using parameters in SQL statements?
  • How do SO_REUSEADDR and SO_REUSEPORT differ?
  • Ember-data transactions and resubmitting forms
  • How to change the color of vaadin-select-text-field…
  • How do I determine whether my calculation of pi is accurate?
  • Controlling number of decimal digits in print output in R
  • Most effective way to parse JSON Objects
  • How to find user first name letter
  • D3 chart integration into Vuejs
  • "Large data" workflows using pandas
  • Managing longs and short orders like LucF PineCoders…
  • Retain precision with double in Java
  • For-each over an array in JavaScript
  • Is there a more efficient way to divide and conquer…
  • Use SQL Server Management Studio to connect remotely…
  • delete item in array of struct that has char array…
  • 'WITH' clause does not work with in operator
  • How is printf printing 53 digits after decimal point?
  • What is the difference between float and double?
  • Could not commit JPA transaction: Transaction marked…
  • Change column type in pandas
  • How to edit `cell[i]` in data grid view C# win form…
  • Start redis-server with config file
  • data.table vs dplyr: can one do something well the…
  • SQL Transaction Error: The current transaction…
  • How to compute precision, recall, accuracy and…
  • load list from file in c
  • Ukkonen's suffix tree algorithm in plain English
  • How do I upload FIle in Vuejs and Expressjs
  • Sorting 1 million 8-decimal-digit numbers with 1 MB of RAM
  • Fastest way to iterate over all the chars in a String
  • Relational room database: The class must be either…
  • Why does C++ code for testing the Collatz conjecture…
  • Firebase cloud function onUpdate is triggered but…
  • Rails vanity URL preventing #edit from working
  • What are best practices that you use when writing…
  • C# Equivalent of SQL Server DataTypes
  • Polymer 1.0 'array-style' path accessors,…
  • Fetching to substring before operator from a dynamic string
  • Logging best practices
  • SQL syntax error when executing MySQL script using…
  • How do I obtain a Query Execution Plan in SQL Server?
  • Spring transaction REQUIRED vs REQUIRES_NEW :…
  • Does C# have extension properties?
  • Using NVL in PLSQL function
  • How do you clear the SQL Server transaction log?
  • What is a plain English explanation of "Big O" notation?
  • C: convert double to float, preserving decimal point…
  • Regular expression to match numbers with or without…
  • Search code inside a Github project
  • PL/SQL ORA-01422: exact fetch returns more than…
  • Finding all possible combinations of numbers to…
  • operator is treated as a variable in SQL
  • MySQL get ID from a table based on email then create…
  • What is your most productive shortcut with Vim?
  • Angular Karma - Component undefined
  • Is floating point math broken?
  • DOUBLE vs DECIMAL in MySQL
  • SQLException: No suitable Driver Found for…
  • How can I find the product GUID of an installed MSI setup?
  • using Vue.js & DataTable(jquery plugin)
  • What is a NullReferenceException, and how do I fix it?
  • What represents a double in sql server?
  • How to round an average to 2 decimal places in PostgreSQL?
  • What is an optional value in Swift?
  • What does %>% function mean in R?
  • dynamically set decimal separator in number/string…
  • C++ template,typename and operator
  • Confused about UPDLOCK, HOLDLOCK

Disclaimer: This content is shared under creative common license cc-by-sa 3.0. It is generated from StackExchange Website Network.

Post navigation

Previous Post:

TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes

Next Post:

How to iterate through two lists in parallel?

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error