Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Precision and Scale Impact for SQL Server Data Types


By:   |   Read Comments (3)   |   Related Tips: More > Data Types

Attend these FREE MSSQLTips webcasts >> click to register


Problem

The numeric data inserted in my database table after my SQL Server Integration Services (SSIS) package executes does not look like the data from its comma-separated source file. What can I do to fix the problem?

Solution

In this tip, we will examine the importance of using the proper data types for storing numbers in both the database table and in configuring SSIS packages. In the first image below, we see the comma-separated value source file in Excel. The second image shows the results of a query after this data was loaded to the database with extra digits in each column.

Precision and Scale Do Matter


Using the proper data types for storing numbers in both the database table and in configuring SSIS packages.

When encountering this problem, make sure to inspect the source file with an application other than Excel such as Notepad++. This will ensure that your data is correct and has not been formatted by Excel. The next image shows the raw data in a text editor.

This will ensure that your data is correct and has not been formatted by Excel

Next we need to examine the SSIS Flat File Connection Manager where we defined the columns in the file. As we can see below, the first column of the file was read by SSIS using the float data type as defined in the Flat File Connection Manager.

Examine the SSIS Flat File Connection Manager where we defined the columns in the file.

Also, the table has been defined with the columns having the float data type.

The table has been defined with the columns having the float data type.

The float and real data types are classified as approximate number data types. We need to be using an exact number data type such as numeric. When using numeric data types, we should define the precision and the scale. The precision is the maximum number of digits on both sides of the decimal point. The scale is the number of digits to the right of the decimal point. In this example, Columns 1, 2, and 3 will have a precision of 4 and a scale of 3, while Column 4 will have a precision of 7 and a scale of 6. Scale must always be less than or equal to the precision. Let's start to fix this problem by changing the data types of our database table columns from floats to numerics with the appropriate precision and scale.

he float and real data types are classified as approximate number data types.

Next, let's go into the SSIS Flat File Connection Manager and change the data types of our file columns from floats to numerics with the appropriate precision and scale.

Go into the SSIS Flat File Connection Manager

Because we changed the data type in the source, SSIS will ask us if we "want to replace the metadata of the output columns with the metadata of the external columns." Choose "Yes" and the warning triangle should disappear.

SSIS will ask us if we "want to replace the metadata of the output columns with the metadata of the external columns."

Now after we run our SSIS package the data in the table matches what is observed in the source file.

Run our SSIS package the data in the table matches what is observed in the source file.

Summary

In this tip, we have shown that precision and scale do matter when working with non-integer numeric data.

Next Steps

Check out these other tips relating to data types.



Last Update:


signup button

next tip button



About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, October 23, 2014 - 4:53:20 AM - David Scott Back To Top

I am trying to emulate wahat you are doing in the Connection Manager. I have opened the SSIS package by launching the Visual Studio 2012 editor supplied with SQL2012 (shell). I can't see any options which give me the options in your screen shot 'Flat File Connection Manager Editor'

Is it possible in the 'Shell' version of Visual Studio 2012 ?

Also, I have noticed that on the few occasions I have used this shell, it is very slow. is this normal ?

Thanks & Regards

David Scott


Thursday, October 23, 2014 - 4:13:54 AM - David Scott Back To Top

 I have been aware of differences between my SQL and Excel calculations for a while, but was blaming Excel. Now I know why.

Thanks for an excelent article


Wednesday, October 22, 2014 - 11:36:32 AM - Ray Bales Back To Top

I am new to SQL and especially SSIS. This is a very helpful article. Thanks!


Learn more about SQL Server tools