Hi David ,
Pretty poorly. Your method also performs poorly in relation to a traditional sub-select.
This may or may not be platform agnostic, although an important point its not a concern for me personally. BTW your code does need mods to run in a SQLServer environment 😉
See the attached image for a screen shot from profiler
drop table #testtab
go
create table #testtab
(
userid integer,
tableid integer
)
go
create unique clustered index idx1 on #testtab(userid,tableid)
go
with cter
as
(
select top(1000) row_number() over (order by (select null)) as r
from syscolumns a cross join syscolumns b cross join syscolumns c
)
insert into #testtab
Select r1.r,r2.r
from cter r1 cross join cter r2
select @@rowcount -- 1,000,000 rows
go
-- David Soussan Method
SELECT t1.userid, COUNT(t1.tableid) AS sequence, t1.tableid, case when t1.tableid >= t2.tableid then 1 else 0 end AS flg
FROM #testtab t1 INNER JOIN #testtab t2 ON t1.userid = t2.userid
GROUP BY t1.userid, t1.tableid, case when t1.tableid >= t2.tableid then 1 else 0 end
HAVING case when t1.tableid >= t2.tableid then 1 else 0 end = 1
go
-- count(*) sub select
select *,(Select count(*)
from #testtab t2
where t1.userid = t2.userid
and t2.TableId <= t1.tableId)
from #testtab t1
go
-- 2005 + , row_number()
Select * ,row_number() over (partition by userid order by tableid)
from #testtab