Forum Discussion
TIP::COPY DATA::SOURCE::TSQL QUERY::LIMITATION
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
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.
Anonymous That's an interesting T-SQL trick! However, not sure why you use 'PARTITION BY NULL' since without it, the statement seems to work just the same. Not being a T-SQL god, I need to ask: Is there some esoteric reason why 'PARTITION BY NULL' would be necessary here?
Here is a revised version of your SQL because as I said in my previous post, having to work with strings, ie varchars, is less efficient than integers, therefore one needs to add a last step to extract the row number from the string and convert it to a BIGINT type since the purpose of the statement is to generate a unique ID.
However, I hope everyone will appreciate that this is a monstrous kludge. Just look at the length of this SELECT statement and how we repeat the CONCAT(). I guess one could then argue we should put all this code (the original script is a lot longer as the whole starting point is a CTE) in a stored procedure and declare a variable, then specify it in the Copy data->Source tab. However it is not clear whether the result set will be properly handled by the Copy data process and passed correctly to the data sink specified in the Destination tab.
SELECT
CONVERT(
BIGINT,
RIGHT(
CONCAT(
CAST(GETDATE() AS DATETIME2(0)),
':',
ROW_NUMBER() OVER (ORDER BY vis.[DATE] DESC)
),
CHARINDEX(
':',
REVERSE(
CONCAT(
CAST(GETDATE() AS DATETIME2(0)),
':',
ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY vis.[DATE] DESC)
)
)
) - 1
)
) AS ID
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 Thanks. I do have a follow-up question though. What happens if the lakehouse data sink is partitioned by YEAR, or by YEAR and MONTH?
Do we just change the window function call like so?
ROW_NUMBER() OVER (PARTITION BY YEAR(VIS.[DATE]) AND MONTH(vis.[date]) ORDER BY vis.[DATE] DESC)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.