Why can't I persist binary(4) computed column?

Question

I'm playing with storing and indexing IP addresses. I'm starting with a simple, stupid table:

CREATE TABLE [dbo].[IP_addresses](
    [IP_as_text] [char](16) NOT NULL,
    [IP]  AS ([dbo].[fnBinaryIPv4]([IP_as_text]))
) ON [PRIMARY]

Where fnBinaryIPv4 is from https://stackoverflow.com/questions/1385552.

CREATE FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)
SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

RETURN @bin

END

However, when I try to add PERSISTED to the IP column or use it in an index, I get a message that it is not deterministic. I've Googled around a bit and that usually has to do with the style passed to CONVERT() for a date but that doesn't seem to apply here. http://www.sql-server-helper.com/functions/system-functions/index.aspx says CAST() and PARSENAME() are deterministic so I don't see why fnBinaryIPv4() is nondeterministic. But it turns out that PARSENAME() used to be but is no longer deterministic. So I rewrote that function:

CREATE FUNCTION [dbo].[fnBinaryIPv4](@ip AS VARCHAR(15)) RETURNS BINARY(4)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @int_addr AS bigint = 0;
    DECLARE @b CHAR(3);
DECLARE bCursor CURSOR FOR ( 
    SELECT value FROM STRING_SPLIT(@ip, '.')
)

OPEN bCursor

FETCH NEXT FROM bCursor INTO @b
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @int_addr = (@int_addr * 256) + CAST(@b AS INTEGER)
    FETCH NEXT FROM bCursor INTO @b
END

CLOSE bCursor
DEALLOCATE bCursor

RETURN CAST(@int_addr AS BINARY(4))

END

But this version is still nondeterministic.

Josh Darnell · Answer 1 · 2020-07-11T15:43:34.937

I'm not sure what "http://www.sql-server-helper.com/" is, but this is from the current, official documentation:

The following built-in functions from other categories are always nondeterministic.
...
PARSENAME

Side note: it looks like PARSENAME was deterministic, at least on SQL Server 2005 (thanks for that link, jpa). So perhaps that other site just has outdated information on it.

You would need to re-implement this function using only deterministic functions (for instance, SUBSTRING and CHARINDEX).

Just to show that it can be deterministic (note that this implementation doesn't deal with general IP addresses, it's just an example):

CREATE OR ALTER FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
AS
BEGIN
    DECLARE @bin AS BINARY(4)
SELECT @bin = CAST( CAST( PARSENAME( @ip, 4 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( PARSENAME( @ip, 3 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( PARSENAME( @ip, 2 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( PARSENAME( @ip, 1 ) AS INTEGER) AS BINARY(1))

RETURN @bin

END
GO
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.fnBinaryIPv4'), 'IsDeterministic') AS IsDeterministic;
GO
CREATE OR ALTER FUNCTION dbo.fnBinaryIPv4(@ip AS VARCHAR(15)) RETURNS BINARY(4)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @bin AS BINARY(4)
SELECT @bin = CAST( CAST( SUBSTRING( @ip, 0, 3 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( SUBSTRING( @ip, 4, 3 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( SUBSTRING( @ip, 8, 3 ) AS INTEGER) AS BINARY(1))
            + CAST( CAST( SUBSTRING( @ip, 12, 3 ) AS INTEGER) AS BINARY(1))

RETURN @bin

END
GO
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.fnBinaryIPv4'), 'IsDeterministic') AS IsDeterministic;
GO

score 0 · Accepted Answer · answered Jul 13 '20 at 19:50

Finally got it.

CREATE OR ALTER FUNCTION [dbo].[fnBinaryIPv4](@ip AS VARCHAR(15)) RETURNS BINARY(4)
WITH SCHEMABINDING
AS
BEGIN
    DECLARE @bin AS BINARY(4);
WITH bytes AS (
    SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 1)) AS id, 
    CAST(value AS INTEGER) AS value
    FROM STRING_SPLIT(@ip, '.')
)
SELECT @bin = CAST((SELECT value FROM bytes WHERE id = 1) AS BINARY(1))
            + CAST((SELECT value FROM bytes WHERE id = 2) AS BINARY(1))
            + CAST((SELECT value FROM bytes WHERE id = 3) AS BINARY(1))
            + CAST((SELECT value FROM bytes WHERE id = 4) AS BINARY(1))

RETURN @bin

END

The ORDER BY (SELECT 1) hacks is from https://stackoverflow.com/questions/44105691/row-number-without-order-by

Why can't I persist binary(4) computed column?

2 Answers2