It is not a SQL Server Issue or SSMS issue. It is how things work. There is a simple trick to resolve this issue.

It is very common when users are coping the resultset to Excel, the floating point or decimals are missed.

The solution is very much simple and it requires a small adjustment in the Excel. By default Excel is very smart and when it detects the value which is getting pasted is numeric it changes the column format to accommodate that. Now as Zero which are training any digit after decimal points have no value, Excel automatically hides it. To prevent this to happen user has to convert columns to text format so it can preserve the formatting.

Here is how you can do it.

Select the corner between A and 1 and Right Click on it.

It will select complete spreadsheet. If you want to change the format of any column you can select an individual column the same way. In the menu Click on Format Cells…

It will bring up the following menu. Here by default the selected column will be General, change that to Text. It will change the format of all the cells to Text.

Now once again paste the values from SSMS to the Excel. This time it will preserve the decimal values from SSMS.

Solved!

Any other trick you do you know to preserve the decimal values? Leave a comment please.

I’m not sure why you’d want to do that? Decimal places are missing intentionally because Excel is simply representing the number, not the textual layout. In the solution you’ve given, they’re now text and you can’t sum them up or perform operations on them. If you genuinely only want the exact same representation then fine, otherwise you’re better off correctly formatting them as a custom format using something like 0.00000? I find the most common time this causes a problem is with things like order numbers which have leading zeros in which cause using the text formatting before pasting is acceptable because I don’t generally ever have to perform numeric operations on order numbers.

Quite often people want to do this for display purpose, if they want to add the value or do any mathematical operation then you have to either do this in SQL Server or convert them to INT format in Excel.

Hi Dave, in my SSMSBoost add-in I have implemented “Results Grid scripter”, that exports Results Grid as Open XML Spreadsheet. Benefits: -datatypes remain preserved. Numbers remain numbers, text remains text (etc). Excel does not try to convert anything on it’s own -you can export selection, one or even ALL ResultGrids, if you have several. In this case each Results Grid is exported as own Worksheet in same Excel document…

This solution among many others is not working for me, instead I upload data as text after I multiply to get rid of commas into decimal table column and then I update table and divide to cancel previous number

Pinal Dave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and have written over 3800 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14+ years of hands on experience he holds a Masters of Science degree and a number of database certifications. For any SQL Server Performance Tuning Issue send email at pinal @ sqlauthority.com .

Nupur Dave is a social media enthusiast and and an independent consultant.