0

Lets say I have

CREATE  FUNCTION [sec].[PartyGroupAccessPredicate](@PartyGroupId uniqueidentifier)
    RETURNS TABLE     
    WITH SCHEMABINDING
AS
    RETURN SELECT 1 AS accessResult
        FROM [sec].[PartyGroupPartyLink] partyGroupLink
        WHERE
        (
            partyGroupLink.PartyGroupId = @PartyGroupId AND partyGroupLink.PartyId = CAST(SESSION_CONTEXT(N'PartyId') AS VARCHAR(50))
            AND @PartyGroupId IS NOT NULL
        )
        
GO



CREATE SECURITY POLICY [sec].[PartyGroupAccessPolicy] 
ADD FILTER PREDICATE [sec].[PartyGroupAccessPredicate]([PartyGroupId]) ON [int].[Program]

... would the security policy execute for each row or does sql do some optimization where its executed only once?

Is there any better approaches that would be more performant?

lptr
  • 1
  • 2
  • 6
  • 16
Murdock
  • 4,352
  • 3
  • 34
  • 63
  • If I had to bet, I would think it executes once per row. If so, memory optimized tables might help, if that were available to you. – Jacques Bosch Sep 06 '20 at 19:25
  • 1
    https://stackoverflow.com/questions/59827994/sql-server-2016-with-row-level-security-addressing-the-bottleneck – lptr Sep 07 '20 at 09:55

1 Answers1

0

The security policy is applied at row level. This is how row level security is being implemented in SQL Server. Reference: Row level security Regarding, your query on performant, If you need to apply multiple filters, then it will have an impact.

SQLZealots
  • 42
  • 2