Based on Row level security I have created a table-valued function:
CREATE FUNCTION Security.userAccessPredicate(@ValueId int)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT 1 AS accessResult
WHERE @ValueId =
(
SELECT Value
FROM dbo.Values
WHERE UserId = CAST(SESSION_CONTEXT(N'UserId') AS NVARCHAR(50))
) OR NULLIF(CAST(SESSION_CONTEXT(N'UserId') AS nvarchar(50)),'') IS NULL
);
CREATE SECURITY POLICY Security.userSecurityPolicy
ADD FILTER PREDICATE Security.userAccessPredicate(ValueUd) ON dbo.MainTable
Let's say MainTable contains milions of rows. Is userAccessPredicate calculating SELECT Value FROM dbo.Values for every row independently? If so it is ineffective I guess. How to check what exact code is generating when executing table-valued function? SQL Server Profiler isn't way because I am using Azure DB.
I am using SQL Server 2016 Management Studio.
Best way is to look at an execution plan with the policy turned off then turned on. You'll see the extra work its doing as a consequence. You're adding another table to query so its similar to doing a join but probably more efficient.
To answer your question, if you see the addition of a nested loop in the plan when the policy is on, then yes its going row-by-row Nested Loops
Also do the same with DBCC SHOW_STATISTICS to get a look at the resource hits too. With smaller tables i never saw any noticeable performance hits, < 100,000 rows in a similar implementation.
I found this link useful when getting into this before.