Native Query for SQL Server is different for 2008 vs 2017 - TRIM statement is a mess

[edhans]

We have SQL Server 2008 at work which is our production server. I have SQL 2017 dev edition on my laptop for testing. The production database has been copied to my machine, so that is exact. (Done via backup/restore.)

Here is the query:

let
    Source = Sql.Database("localhost", "DATA"),
    dbo_IMINVLOC_SQL = Source{[Schema="dbo",Item="IMINVLOC_SQL"]}[Data],
    #"Filtered Rows" = Table.SelectRows(dbo_IMINVLOC_SQL, each ([loc] = "MAN")),
    #"Renamed Columns" = Table.RenameColumns(#"Filtered Rows",{{"item_no", "Item Number"}, {"std_cost", "Standard Cost"}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns",{"Item Number", "Standard Cost"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns",{{"Item Number", Text.Trim, type text}})
in
    #"Trimmed Text"

Not complex. Just removes all but two columns, then does a TRIM on the item number column.

Here is the native query that is generated when this goes against the SQL Server 2008 edition.

select ltrim(rtrim([_].[item_no])) as [Item Number],
    [_].[std_cost] as [Standard Cost]
from 
(
    select [_].[item_no],
        [_].[std_cost]
    from [dbo].[IMINVLOC_SQL] as [_]
    where [_].[loc] = 'MAN'
) as [_]

Very simple.

Here is the same query against SQL 2017.

select trim(concat(nchar(32), nchar(160), nchar(5760), nchar(8192), nchar(8193), nchar(8194), nchar(8195), nchar(8196), nchar(8197), nchar(8198), nchar(8199), nchar(8200), nchar(8201), nchar(8202), nchar(8239), nchar(8287), nchar(12288), nchar(9), nchar(10), nchar(11), nchar(12), nchar(13), nchar(133), nchar(8232), nchar(8233)) from [_].[item_no]) as [Item Number],
    [_].[std_cost] as [Standard Cost]
from 
(
    select [_].[item_no],
        [_].[std_cost]
    from [dbo].[IMINVLOC_SQL] as [_]
    where [_].[loc] = 'MAN'
) as [_]

What is all of that mess? I understand TRIM() is new to SQL Server 2017, but what is it concatenating with all of those NCHAR() statements? The result it returns is correct, but that looks like a mess. It seems to take longer to process too, but that could be my laptop (2017) vs the server (2008).

Is that correct? 

I get the same thing when I test this against the ContosoRetailDW database

let
    Source = Sql.Databases("localhost"),
    ContosoRetailDW = Source{[Name="ContosoRetailDW"]}[Data],
    dbo_DimSalesTerritory = ContosoRetailDW{[Schema="dbo",Item="DimSalesTerritory"]}[Data],
    #"Removed Other Columns" = Table.SelectColumns(dbo_DimSalesTerritory,{"SalesTerritoryRegion"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Other Columns",{{"SalesTerritoryRegion", Text.Trim, type text}})
in
    #"Trimmed Text"

The native query is:

select trim(concat(nchar(32), nchar(160), nchar(5760), nchar(8192), nchar(8193), nchar(8194), nchar(8195), nchar(8196), nchar(8197), nchar(8198), nchar(8199), nchar(8200), nchar(8201), nchar(8202), nchar(8239), nchar(8287), nchar(12288), nchar(9), nchar(10), nchar(11), nchar(12), nchar(13), nchar(133), nchar(8232), nchar(8233)) from [_].[SalesTerritoryRegion]) as [SalesTerritoryRegion]
from 
(
    select [SalesTerritoryRegion]
    from [dbo].[DimSalesTerritory] as [$Table]
) as [_]

I can reproduce this in both the Feb 2019 version of PBI Desktop as well as Excel build 1902 and 1903 Insider build.

Any SQL gurus that can explain to me what is going on, and if that is how it should be working?

[v-piga-msft]

Hi @edhans ,

Based on your scenario, I have made a test for SQL Server 2008 and 2017 with different version of Power BI Desktop.

For SQL Server 2008, it will always show without NCHAR().

For SQL Server 2017, it will show with NCHAR() on the latest version of Power BI Desktop which is similar to your test result.

However, it won't show with NCHAR() for the version earlier like below by my test.

So I'm afraid that may be caused by the version of Power BI.

Best  Regards,

Cherry

[edhans]

Doesn't that seem like a bug? For example, the Person.Address table in the AdventureWorks2017 database. Power BI Desktop and Excel both produce this if I trim the AddressLine1 field.

select trim(concat(nchar(32), nchar(160), nchar(5760), nchar(8192), nchar(8193), nchar(8194), nchar(8195), nchar(8196), nchar(8197), nchar(8198), nchar(8199), nchar(8200), nchar(8201), nchar(8202), nchar(8239), nchar(8287), nchar(12288), nchar(9), nchar(10), nchar(11), nchar(12), nchar(13), nchar(133), nchar(8232), nchar(8233)) from [_].[AddressLine1]) as [AddressLine1]
from 
(
    select [$Table].[AddressLine1] as [AddressLine1]
    from [Person].[Address] as [$Table]
) as [_]

However, in SSMS, while the above works, I can just use this query:

select trim([AddressLine1]) as [AddressLine1]
from 
(
    select [$Table].[AddressLine1] as [AddressLine1]
    from [Person].[Address] as [$Table]
) as [_]

Why would Power Query generate all of that NCHAR() stuff?