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