Some examples on how to generate the SQ field when inserting records.

-- examples on how to generate the sequesnce field (SQ)

-- wrong way #1
select pid, dt, sq, (select max(sq) from ads as subads where ads.pid = subads.pid) + 1 as NewSQ
from DST15000AeriesDemo..ads

-- better but not there yet.
select pid, dt, sq, ROW_NUMBER() over (partition by pid order by dt) + (select max(sq) from ads as subads where ads.pid = subads.pid) as BetterSQ
from DST15000AeriesDemo..ads    --                                        ^--- why not check for del-tagged records?

--Oops! still some issues.. Some SQs will be NULL if the origin table has 0 records to start with.
-- Correct
select pid, dt, sq, ROW_NUMBER() over (partition by pid order by dt) + isnull((select max(sq) from ads as subads where ads.pid = subads.pid),0) as BestSQ
from DST15000AeriesDemo..ads