Dynamic Data Masking Doesn't Seem To Work Correctly With ISNULL
3
votes
2
answers
610
views
Here is the code to reproduce the issue:
CREATE TABLE [dbo].[EmployeeDataMasking](
[RowId] [int] IDENTITY(1,1) NOT NULL,
[EmployeeId] [int] NULL,
[LastName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
[FirstName] [varchar](50) MASKED WITH (FUNCTION = 'partial(2, "XXXX", 2)') NOT NULL,
CONSTRAINT [PK_EmployeeDataMasking] PRIMARY KEY CLUSTERED
(
[RowId] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
) ON [PRIMARY]
GO
Insert Into dbo.EmployeeDataMasking (EmployeeId, LastName, FirstName)
VALUES( 1,'Smithsonian','Daniel'),( 2,'Templeton','Ronald')
-- Partial data masking works correctly here
Select
EmployeeId,
LastName,
FirstName
From dbo.EmployeeDataMasking
-- Partial data masking does not work correctly here. Full masking is used.
Select
EmployeeId,
ISNULL(LastName,'') as LastName,
ISNULL(FirstName,'') as FirstName
From dbo.EmployeeDataMasking
It appears, when using ISNULL, the partial masking is being converted to a full (default) mask. Is this the expected behavior?
Update: I discovered that COALESCE does not exhibit this problem. These queries return the expected results:
Select
EmployeeId,
COALESCE(LastName,'') as LastName,
COALESCE(FirstName,'') as FirstName
From dbo.EmployeeDataMasking
Using Azure SQL Database

Asked by Randy Minder
(2032 rep)
Oct 15, 2020, 07:29 PM
Last activity: May 18, 2025, 08:18 AM
Last activity: May 18, 2025, 08:18 AM