create table #Card 
(
  CardID         numeric( 15 , 0 )
 ,ResourceID     numeric( 15 , 0 ) null
 ,StateOrder     int           null  
 ,IsActive       bit   
 ,IsNew          bit
 ,CrdGroup       smallint      null 
 ,CardRole       smallint           
 ,isMain         smallint
 ,DateExpiration smalldatetime
 ,MidRest        numeric( 18 , 2 ) 
)

create clustered index i1 on #Card(CardID)
create index i2 on #Card(ResourceID, CardRole)

update #Card
set isMain =  1 
where isActive= 0 
  and not exists (select  1  from #Card c1  (index i2)       --если индекс не указать, будет Table Scan  
                 where #Card.ResourceID = c1.ResourceID
                   and isActive =  1 )

QUERY PLAN FOR STATEMENT 11 (at line 77).
    STEP 1
        The type of query is UPDATE.
        The update mode is deferred.
        FROM TABLE
            #Card
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Run subquery 1 (at nesting level 1).
        Using I/O Size 8 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            #Card
        Using I/O Size 8 Kbytes for data pages.
    STEP 1
NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 11.
  QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 80).
    Correlated Subquery.
    Subquery under an EXISTS predicate.
    STEP 1
        The type of query is SELECT.
        Evaluate Ungrouped ANY AGGREGATE.
        FROM TABLE
            #Card
            c1
        EXISTS TABLE : nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Using I/O Size 8 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
  END OF QUERY PLAN FOR SUBQUERY 1.
Total estimated I/O cost for statement 11 (at line 77): 378.
Parse and Compile Time 0.

QUERY PLAN FOR STATEMENT 1 (at line 1).
    STEP 1
        The type of query is SELECT.
Total estimated I/O cost for statement 1 (at line 1): 0.
Parse and Compile Time 0.
SQL Server cpu time: 0 ms.
QUERY PLAN FOR STATEMENT 1 (at line 1).
    STEP 1
        The type of query is UPDATE.
        The update mode is deferred.
        FROM TABLE
            #Card
        Nested iteration.
        Table Scan.
        Forward scan.
        Positioning at start of table.
        Run subquery 1 (at nesting level 1).
        Using I/O Size 64 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
        TO TABLE
            #Card
        Using I/O Size 8 Kbytes for data pages.
    STEP 1
NESTING LEVEL 1 SUBQUERIES FOR STATEMENT 1.
  QUERY PLAN FOR SUBQUERY 1 (at nesting level 1 and at line 4).
    Correlated Subquery.
    Subquery under an EXISTS predicate.
    STEP 1
        The type of query is SELECT.
        Evaluate Ungrouped ANY AGGREGATE.
        FROM TABLE
            #Card
            c1
        EXISTS TABLE : nested iteration.
        Index : i2
        Forward scan.
        Positioning by key.
        Keys are:
            ResourceID  ASC
        Using I/O Size 64 Kbytes for index leaf pages.
        With LRU Buffer Replacement Strategy for index leaf pages.
        Using I/O Size 64 Kbytes for data pages.
        With LRU Buffer Replacement Strategy for data pages.
  END OF QUERY PLAN FOR SUBQUERY 1.

create table #Card 
(  CardID         numeric( 15 , 0 ) primary key
...

create index i1 on #Card(CardID)  
create index i2 on #Card(ResourceID, CardRole)  

create table #t (t int)
insert #t select  1 
insert #t select  2 
select * from #t
drop table #t

Это, извини, ты попутал. Это тебе не MSSQLServer, это ASE.
В листах некластерных индексов ROWID лежит, а не значения ключей
кластерного индекса.