Search code examples
sqlsql-serverformatdefault

SQL Server setting a default format when displaying columns

In SSMS or Visual studio writing:

  • query: select 1.234567 -> display: 1.234567
  • query: select format(1.234567, 'n') -> display: 1.23

For a long query it is heavy:

select format(1.234567, 'n'), format(1.234567, 'n'),
       format(1.234567, 'n'), format(1.234567, 'n')

Is there a way of setting a default display format for floats or at least at query level?

I did not find any solution. I am expecting a setting in SQL Server or SSMS or VS or at query level.

Solution

  • There is no such setting, at least natively. SSMS and VS just display the results as sent to them by SQL Server and/or as determined by how those apps are coded. There is no opportunity for you to dictate formatting of values other than things like whether carriage returns are maintained on copy.

    There have been various 3rd party plug-ins over the years that might have something like the functionality you're after (e.g. SSMSBoost, SSMS Tools Pack), but you'd have to try them out; whether they support this kind of thing (or even support the most recent versions of SSMS) isn't on topic here.

    Alternatives that are more programming-related:

    • Use a view or computed columns that handle the formatting for you
    • Use a more appropriate data type in the first place (e.g. a decimal with the right precision instead of float which... doesn't seem right if you only care about two decimal places)

    As a side note, you should use convert() instead of format() because the latter has significant CLR overhead.