PROCEDURE GetDuplicateEmailCount

Oct 4, 2015 at 7:43 AM
do not use vw_Users!!!!!


my version ===>

BEGIN
SELECT COUNT(1) TotalCount
 FROM (SELECT 1 N FROM dbo.[Users] U Inner Join dbo.[UserPortals] UP on UP.[UserId] = U.[UserId]
        WHERE Coalesce(UP.PortalId, @PortalID, -1) = IsNull(@PortalId, -1)
        GROUP BY U.[Email] HAVING COUNT(1) > 1) S
OPTION (OPTIMIZE FOR (@PortalID UNKNOWN));
END / Procedure /
Coordinator
Oct 6, 2015 at 7:22 AM
An inner join will between users and userportals will exclude most superusers, that's why vw_users is correct IMO.
I still plan to create userportal records for all superusers in all sites, which would allow an inner join (and indexing vw_users)