主页 / dba / 问题 / 270746
Accepted
Chris Nelson
Asked: 2020-07-11 07:33:42 +0800 CST

为什么我不能保留 binary(4) 计算列？

  • 772

我正在玩存储和索引 IP 地址。我从一个简单、愚蠢的表开始：

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

fnBinaryIPv4来自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

但是，当我尝试添加PERSISTED到IP列或在索引中使用它时，我收到一条消息，表明它不是确定性的。我在 Google 上搜索了一下，这通常与传递给CONVERT()日期的样式有关，但这似乎不适用于这里。 http://www.sql-server-helper.com/functions/system-functions/index.aspx说CAST()并且PARSENAME()是确定性的，所以我不明白为什么fnBinaryIPv4()是不确定的。但事实证明，这PARSENAME()曾经是确定性的，但不再是确定性的。所以我重写了那个函数：

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

但是这个版本仍然是不确定的。

sql-server computed-column

2 个回答

  1. 我不确定“http://www.sql-server-helper.com/”是什么，但这是来自当前的官方文档：

    以下来自其他类别的内置函数始终是不确定的。
    ...
    解析名称

    旁注：它看起来PARSENAME是确定性的，至少在 SQL Server 2005 上（感谢该链接，jpa）。所以也许那个其他网站只是有过时的信息。

    SUBSTRING您将需要仅使用确定性函数（例如和）重新实现此函数CHARINDEX。

    只是为了表明它可以是确定性的（请注意，此实现不处理一般 IP 地址，这只是一个示例）：

    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

    在此处输入图像描述

    • 12
  2. Best Answer

    终于明白了。

    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

    ORDER BY (SELECT 1)黑客来自https://stackoverflow.com/questions/44105691/row-number-without-order-by

    • 0

相关问题