Identity() is a piece of automation that solves a set of tricky problems for you.  Choosing not to use it is tantamount to saying you can do it better.  Well,...

How would I approach checking the OrderNumbers for validity within the steps so an item in "AU" and locked cannot appear at a different site as next record, same...

The S in SCD is "slowly" so I don't see much benefit either.  Also, afaik this feature is not available in SQL DW.

Unless you are required to do this by law, it's more trouble than it's worth.  If you absolutely have to do it, I would use a SEQUENCE and run...

Either by querying your own table or by storing the maximum sequence per day somewhere.  Fyi, this is really not a good way to handle keys and is not recommended. ...

I don't see where you used a GUID either in the original post nor here as a part of the formula.  To be sure, what you did with the...

• This reply was modified 6 years ago by Steve Collins.

With 550 columns it's not possible to test every combination of 2 or more columns to see if they are unique. It's a combinatorial problem with a search space...

Yea right, it's super precise so it's...

If the number of candidate columns is small you could exhaustively test the possible combinations using a cursor and dynamic sql.

It seems like you're talking about two things here. Not sure what temporal tables have to do with the tokens. Usually you are hashing somehow to create this token...

Suppose you have 3 people and each has...

What Jeff wrote is enough to scare me away from even trying this approach.  Using internal locking could enable end-users to do things which produce unpredictable performance.  You wrote "users...

When there no uncommitted transactions do the EMpbasetable and  EmpIncTable have the same number of rows?

Here's a similar way that's maybe simpler.

with x_cte as (
select
  *,
  row_number() over (partition by recid order by docdate desc) row_num 
from 
  #tmptbl
where
...

Here is another option:

   With groupedDates
     As (
 Select t.recid
      , t.docdate
   ...