Sample Header Ad - 728x90

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 enter image description here 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