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.
Best Answer
I'm not sure what "http://www.sql-server-helper.com/" is, but this is from the current, official documentation:
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
andCHARINDEX
).Just to show that it can be deterministic (note that this implementation doesn't deal with general IP addresses, it's just an example):