CODEWITHSUNDEEP
sql-servert-sql
ulysses1904
ulysses1904

Reputation: 77

Are "any, all and some" used much in T-SQL?

I have been teaching myself T-SQL for about a year and only now have come across the ANY and ALL operators. I have never seen them used in any sample code until now, are these rarely used and are there preferred operators for getting the same results?

Upvotes: 4

Views: 112

Answers (2)

Ben Thul
Ben Thul

Reputation: 32707

I've used ANY once in production code. I blogged about it a while ago here. But here's the code for posterity:

with cte as (
    select * from (values
        ('Name'        ),
        ('StandardCost'),
        ('ListPrice'   )
    ) as x(col)
)
, column_ordinals AS (
    select sys.fn_cdc_get_column_ordinal('Production_Product', col) as [ordinal]
    from cte
)
SELECT *
FROM cdc.[fn_cdc_get_net_changes_Production_Product](
    sys.fn_cdc_get_min_lsn('Production_Product'), 
    sys.fn_cdc_get_max_lsn()
    , 'all with mask'
) AS fcgacpp
WHERE [__$operation] = 4 --after update
    AND 1 = ANY (
        select [sys].[fn_cdc_is_bit_set]([ordinal], [__$update_mask])
        from [column_ordinals]
    );

Essentially, if any of Name, StandardCost, or ListPrice is represented in the __$update_mask bitmask, return that row from the CDC data. I would agree that ANY and ALL are not often used, but when you need one of them, they're handy!

Upvotes: 2

Jim Horn
Jim Horn

Reputation: 889

Yes. They are used in Microsoft certification exams for Trivial Pursuit-style questioning of topics that aren't actually used much other than to demonstrate all functionality and to make the exams that much harder. Also perhaps as a profit motive to get more money out of exam takers by increasing the failure rate.

Aside from that, no they're not used much.

Upvotes: 0

Related Questions