Преди да получим RowNo от yukon нямам никаква по добра. Ето моят тест:

SET         NOCOUNT ON



CREATE TABLE #Numbers (

            SeqNo       INT IDENTITY (1, 1)

            , Dummy     INT NULL

            )



INSERT      #Numbers(Dummy)

SELECT      TOP 5000 NULL

FROM        syscolumns s1, syscolumns s2



CREATE TABLE #MyTable (

            DIN         INT NOT NULL

            , RSN       INT NOT NULL

            , AuditId   INT NOT NULL

            , UNIQUE (DIN, RSN, AuditId)

            )



INSERT      #MyTable(DIN, RSN, AuditId)

SELECT      n.SeqNo

            , s.RSN

            , s.AuditId

FROM        (

            SELECT      2,2 UNION ALL

            SELECT      1,1 UNION ALL

            SELECT      1,3 UNION ALL

            SELECT      3,4 UNION ALL

            SELECT      2,5 UNION ALL

            SELECT      1,5 UNION ALL

            SELECT      1,2 UNION ALL

            SELECT      3,3

            ) s(RSN, AuditId)

CROSS JOIN  #Numbers n



PRINT { fn CURRENT_TIME() }



SELECT      t.*

            , 1 + ( SELECT      COUNT(*)

                    FROM        #MyTable t1

                    WHERE       t1.DIN = t.DIN

                                AND t1.RSN < t.RSN

                                OR t1.DIN = t.DIN

                                AND t1.RSN = t.RSN

                                AND t1.AuditId < t.AuditId  )       AS ASN

FROM        #MyTable t

ORDER BY    Din, RSN, AuditId



PRINT { fn CURRENT_TIME() }



SELECT      t.*

            , 1 + ( SELECT      COUNT(*)

                    FROM        #MyTable t1

                    WHERE       t1.DIN = t.DIN

                                AND t1.RSN < t.RSN  )

                + ( SELECT      COUNT(*)

                    FROM        #MyTable t1

                    WHERE       t1.DIN = t.DIN

                                AND t1.RSN = t.RSN

                                AND t1.AuditId < t.AuditId  )       AS ASN

FROM        #MyTable t

ORDER BY    Din, RSN, AuditId



PRINT { fn CURRENT_TIME() }



DROP TABLE #Numbers

DROP TABLE #MyTable