3

I'm a SSIS and SQL beginner, and I'm trying to understand what data type I should choose. The SQL Server data type is [decimal](4, 3), and there are two conversion options, decimal and numeric. Which one is more appropriate in this case?

KizyMatzen
  • 31
  • 3

1 Answers1

4

Referring to the SSIS official documentation you should use DT_NUMERIC data types.

Note that there is a similar data type available in SSIS called DT_DECIMAL. DT_NUMERIC supports a wider range than DT_DECIMAL.

Based on the official documentation, DT_DECIMAL is defined as:

An exact numeric value with a fixed precision and a fixed scale. This data type is a 12-byte unsigned integer with a separate sign, a scale of 0 to 28, and a maximum precision of 29.

While DT_NUMERIC is defined as:

An exact numeric value with a fixed precision and scale. This data type is a 16-byte unsigned integer with a separate sign, a scale of 0 - 38, and a maximum precision of 38.

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 2
    It should be emphasized that SSIS types are OLEDB types, not SQL Server types. They are similar to programming language types, so `DT_DECIMAL` is similar to .NET's `decimal` type – Panagiotis Kanavos Mar 09 '21 at 07:37
  • 1
    @PanagiotisKanavos Regarding the small difference : https://stackoverflow.com/a/1842031/9517769 https://learnsql.com/blog/understanding-numerical-data-types-sql/#:~:text=There%20is%20a%20small%20difference,is%20specified%20by%20the%20coder. – Amira Bedhiafi Mar 09 '21 at 07:47
  • @SandraGuilepZouaouiZandeh for which database? In SQL Server there's only one type. Each database has a different type system and treats types differently. As for SQL 92 - not only was that 20 years ago, but NO DATABASE offers anything but the most basic compliance with ANSI SQL. None. The standard aspirational, created by negotiations between vendors, mainly trying to normalize the new features and different vendor implementations *after* the fact – Panagiotis Kanavos Mar 09 '21 at 07:54
  • @SandraGuilepZouaouiZandeh `at the most basic` isn't a figure of speech either. The standard has compliance levels so *vendors* only comply with the lowest level for commercial reasons - they want to be able to take customers from each other. MySQL though is notorious for breaking even the most basic compatibility level – Panagiotis Kanavos Mar 09 '21 at 08:03
  • @SandraGuilepZouaouiZandeh there's no difference between `decimal` and `numeric` in SQL Server. That's it. Even the blog post you linked says `be aware that many top database management systems have vendor-specific representations of numeric types`. Actually, *all* databases have product-specific implementations – Panagiotis Kanavos Mar 09 '21 at 08:13