Forum Discussion

Element115's avatar
Element115
Memorable Member
1 year ago
Solved

TIP::COPY DATA::SOURCE::TSQL QUERY::LIMITATION

SUMMARY: The T-SQL window function ROW_NUMBER() OVER (ORDER BY T0.[DATE] ASC, T0.[TIME] ASC) AS ID does NOT work as expected when used in a T-SQL script in the pipeline activity Copy data->Source t...
Data Pipeline
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hello Element115 ,

    Thank you for your update and for clarifying your progress.


    You’re absolutely right in this case, PARTITION BY NULL isn’t strictly necessary. I included it to intentionally ensure ROW_NUMBER() treats the whole dataset as one partition, keeping the ID sequence continuous. It’s something we often do as a best practice to make the partitioning behavior clear.

    That said, leaving it out gives you the same result since the query defaults to one partition anyway. So, feel free to skip it, it won’t affect how the query runs.

     

    If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

     

    Thank you

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hello Element115 ,

     

    The ROW_NUMBER() function generates unique row numbers, and PARTITION BY groups the data into sections where numbering restarts. To partition by year and month, you should write PARTITION BY YEAR(vis.[DATE]), MONTH(vis.[DATE]) . This creates a new row number sequence for each year-month combination. The ORDER BY vis.[DATE] DESC ensures the latest dates get the first numbers

     

    If this post helps, then please give us Kudos and consider Accept it as a solution to help the other members find it more quickly.

     

    Thank you.

     

ObungiNiels's avatar
ObungiNiels
Resolver III
1 year ago

Hi Element115 ,

did you try the following already? 

Create a new column concatenating the date and time information, creating unique timestamp value to order over: 

 

CONCAT(T0.[DATE] , ' ', T0.[TIME]) AS DATETIME_VAL,
ROW_NUMBER() OVER (ORDER BY DATETIME_VAL ASC) AS ID

Let me know if that helped. 

Kind regards,

Niels 

 

  • Element115's avatar
    Element115
    Memorable Member
    1 year ago

    Anonymous  Thanks but I don't think we need or should lose datetime info and replace it by a string or VARCHAR.  Further, there is another issue related to VARCHAR (I already opened a support ticket for this as well).  For some reason, depending on some Copy data settings config, the system automatically converts VARCHAR type to NVARCHAR(MAX) and this causes Fabric to complain that NVARCHAR(MAX) is not a type compatible with the underlying version of SQL used causing the pipeline to terminate with an error.

     

    Here is the statement I used:

     

     

    ROW_NUMBER() OVER (ORDER BY vis.[DATE] ASC, vis.[M15] ASC) AS ID

     

     

    Like I said, it works as it should on a SQL Server on-prem.  So this is an issue once the T-SQL is executed from a Copy data activity against a lakehouse table and therefore we should not have to convert types DATE, TIME, or DATETIME2 to type VARCHAR, which later on when sorting or performing datetime intelligence ops causes other issues, at which point you to use M and reconvert back to the original type.  A non-starter in my book.