Remove trailing zeros from decimal in SQL Server

You can use the FORMAT() function (SqlAzure and Sql Server 2012+):

SELECT FORMAT(CAST(15.12     AS DECIMAL(9,6)), 'g18')  -- '15.12'
SELECT FORMAT(CAST(0.0001575 AS DECIMAL(9,6)), 'g10')  -- '0.000158'
SELECT FORMAT(CAST(2.0       AS DECIMAL(9,6)), 'g15')  -- '2'

Be careful when using with FLOAT (or REAL): don't use g17 or larger (or g8 or larger with REAL), because the limited precision of the machine representation causes unwanted effects:

SELECT FORMAT(CAST(15.12 AS FLOAT), 'g17')         -- '15.119999999999999'
SELECT FORMAT(CAST(0.9 AS REAL), 'g8')             -- '0.89999998'
SELECT FORMAT(CAST(0.9 AS REAL), 'g7')             -- '0.9'

Furthermore, note that, according to the documentation:

FORMAT relies on the presence of the .NET Framework Common Language Runtime (CLR). This function will not be remoted since it depends on the presence of the CLR. Remoting a function that requires the CLR would cause an error on the remote server.

Works in SqlAzure, too.

A decimal(9,6) stores 6 digits on the right side of the comma. Whether to display trailing zeroes or not is a formatting decision, usually implemented on the client side.

But since SSMS formats float without trailing zeros, you can remove trailing zeroes by casting the decimal to a float:

select 
    cast(123.4567 as DECIMAL(9,6))
,   cast(cast(123.4567 as DECIMAL(9,6)) as float)

prints:

123.456700  123,4567

(My decimal separator is a comma, yet SSMS formats decimal with a dot. Apparently a known issue.)