sql server - Efficient way to delete values based on multiple columns in SQL -

i want delete rows based on combination of 2 columns. table looks (simplified):

[id], [sub_id], [value]  

values example be:

1234 - 1 - 100 1234 - 2 - 50 5678 - 1 - 90 4321 - 1 - 75 4321 - 2 - 75 

i want delete records except specific combinations of [id] , [sub_id]. example: delete combinations except combination 1234-2 , 4321-2.

*edit: 2 values example, in reality need maintain on 10,000 combinations of id-sub_id.

to combine 2 id columns cast , delete not match combination.

delete  table            cast(id varchar(4))+'-'+cast(sub_id varchar(1)) not in     ('1234-2', '4321-2') 

this works, slow , inefficient. takes several minutes execute query , expand selection each month, making worse each time, know how can make more efficient?

many thanks, steven

you use cte selects records should not deleted, can join original table:

with keep (     select id=1234, sub_id=2       union     select id=4321, sub_id=2 )     select t.*  table1 t inner join keep k   on t.id = k.id , t.sub_id = k.sub_id  

this shows you'll keep: demo

if want delete other can use not exists:

with keep (     select id=1234, sub_id=2       union     select id=4321, sub_id=2 )    delete t table1 t not exists (    select 1 keep k     k.id = t.id , k.sub_id = t.sub_id ) 

demo

this approach should efficient , readable.