How to make a non-unique row unique again

Question

How to make a non-unique row unique again

DinoRS

SSCrazy

Points: 2683
More actions
September 2, 2019 at 3:49 pm

#3676370

First of all I don't have code to provide but imagine the following scenario:
You are manufacturing and selling Product A. There is a DWH solution which throughout the manufacturing LifeCycle composes unique Product A from Component B, Component C and a datetime.
Component B, Component C and the datetime are right now together the unique identifier throughout the DWH and in the end you receive Product A. So in this reporting solution for Product A you have a few intermediate steps which make it into the DWH. In the end you have the same 8 rows for each Product A, just Details change.
As I said we have a true datetime value with - in theory - the most deterministic way for the data processing in the DWH so we should be able to sort all 8 rows accordingly and fill the manufacturing stage details in the right order per row.
But it's so not, the first few datasets I've taken a look at seemed to be cut off at the second but I found a lot of records where even the microseconds match exactly.
So dear SSC Readers, can I get the right columns order managed even if I lose the uniqueness from the actual datetime and how would I approach that? The datetime is not unique at times due to processing speed (we are much faster with pure MSSQL Server now than with the 3rd Party Product we had been using before for this) so if 2 subsequent rows are being processed at the same time from different CPU Cores I lose my virginity .. eh I mean uniqueness in the data. 🙁

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

You can use an IDENTITY or SEQUENCE column as a temporal tie breaker. But, having the columns you mentioned and having the tied data in them, doesn't bode well for all those processes that create the data. Something is really wrong in the data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

DinoRS SSCrazy Points: 2683 More actions · Answer 2

I definitely agree with you Jeff that there is something wrong but it's not the data as stands but instead of replacing the logic from the 3rd Party ISV with something that gets to the same result their .Net idea is to replicate part of that logic to make it work as good as possible. The problem is I made things around so fast that their approach of "data is coming in slow anyways" does not work anymore.

Now I'm not going to look at that .Net code (I did and right after the first 10 lines decided "nope, that one is going to happen without me") as I don't write code in any other language than TSQL and some PowerShell and even the customer is willing to accept "that it probably can't be solved" without redoing the whole thing.

But I don't like something like that in the space I'm actually trying to tidy up a bit so I'll have a look at sequences and see if I can make good use of that.