Início / dba / Perguntas / 114260
Accepted
Tomas Kubes
Asked: 2015-09-06 00:56:22 +0800 CST

Por que uma coluna computada NOT NULL é considerada anulável em uma exibição?

  • 772

tenho uma tabela:

CREATE TABLE [dbo].[Realty](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RankingBonus] [int] NOT NULL,
    [Ranking]  AS ([Id]+[RankingBonus]) PERSISTED NOT NULL
    ....
)

E uma vista:

CREATE View  [dbo].[FilteredRealty] AS
 SELECT 
realty.Id as realtyId,
...
COALESCE(realty.Wgs84X, ruian_cobce.Wgs84X, ruian_obec.Wgs84X) as Wgs84X,
COALESCE(realty.Wgs84Y, ruian_cobce.Wgs84Y, ruian_obec.Wgs84Y) as Wgs84Y,
realty.Ranking,
...
FROM realty
JOIN Category ON realty.CategoryId = Category.Id
LEFT JOIN ruian_cobce ON realty.cobceId = ruian_cobce.cobce_kod
LEFT JOIN ruian_obec ON realty.obecId = ruian_obec.obec_kod
LEFT JOIN okres ON realty.okresId = okres.okres_kod
LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1
                     AND ExternFile.ForeignTable = 5
INNER JOIN Person ON realty.OwnerId = Person.Id
WHERE Person.ConfirmStatus = 1

Eu tenho um modelo dbml em C# (LinqToSQL) com a exibição FilteredRealty nele. O campo [Ranking] é reconhecido como um int anulável e, portanto, tenho que corrigir o tipo no código gerado toda vez que altero algo no banco de dados. Isso é muito frustrante para mim e muito trabalho manual.

Não há agregações usadas no FilteredRealty (em relação a esta questão relacionada ).

Por que a coluna Classificação da exibição é considerada anulável se Realty.Ranking não for anulável?

sql-server view

2 respostas

  1. Best Answer

    O [Ranking]campo está sendo exibido como "Nullable" por ser uma coluna computada. Sim, é declarado como NOT NULL, mas como afirma a página MSDN para Computed Columns , o mecanismo de banco de dados pode alterar essa determinação no momento da consulta:

    O Mecanismo de Banco de Dados determina automaticamente a nulidade das colunas computadas com base nas expressões usadas. O resultado da maioria das expressões é considerado anulável mesmo se apenas colunas não anuláveis ​​estiverem presentes, pois possíveis estouros ou estouros também produzirão resultados nulos. Use a função COLUMNPROPERTY com a propriedade AllowsNull para investigar a nulidade de qualquer coluna computada em uma tabela. Uma expressão anulável pode ser transformada em não anulável especificando ISNULL( check_expression , constant ), em que a constante é um valor não nulo substituído por qualquer resultado nulo.

    Então, vamos ver se isso é verdade:

    CREATE TABLE [dbo].[Realty](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [RankingBonus] [int] NOT NULL,
    [Ranking]  AS ([Id]+[RankingBonus]) PERSISTED NOT NULL
);
GO

EXEC sp_help 'dbo.Realty';
-- Ranking: Nullable = "no"

SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.Realty'), N'Ranking', 'AllowsNull') AS [AllowsNull?];
-- 0

SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.Realty', '', NULL);
-- Ranking: is_nullable = 1  ==  :-(

    Agora vamos ver se o conselho deles ISNULLfunciona:

    SELECT * FROM sys.dm_exec_describe_first_result_set(
   N'SELECT Id, RankingBonus, ISNULL(Ranking, -99) AS [RealRanking] FROM dbo.Realty;',
   '',
   NULL);
-- RealRanking: is_nullable = 0

    O conselho deles parece preciso, então vamos tentar aplicá-lo à definição da coluna computada:

    ALTER TABLE dbo.Realty
  ADD [RankingFixed] AS (ISNULL(([Id]+[RankingBonus]), -99))
  PERSISTED NOT NULL;
GO

    E agora verificamos as propriedades novamente, mas para o novo campo:

    EXEC sp_help 'dbo.Realty';
-- RankingFixed: Nullable = "no"

SELECT COLUMNPROPERTY(OBJECT_ID(N'dbo.Realty'),
                      N'RankingFixed',
                      'AllowsNull') AS [AllowsNullsNow?];
-- 0

    Isso parece positivo até agora, mas mesmo a definição original relatou "NÃO NULO" nessas duas verificações. Então, vamos tentar o teste real -- como o mecanismo de banco de dados determina a nulidade em tempo de execução:

    SELECT * FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.Realty', '', NULL);
-- RankingFixed: is_nullable = 0  ==  :-) WOO HOO!
    • 24

  2. Para garantir que a expressão da coluna computada de classificação não retorne NULL em nenhuma circunstância, você deve envolvê-la ISNULLcom um valor padrão adequado. Por exemplo:

    Ranking AS ISNULL(Id + RankingBonus, 0) PERSISTED NOT NULL

    A NOT NULLrestrição garante que o valor persistente não seja nulo, no contexto das configurações de tabela e nível de sessão em vigor quando a tabela é modificada.

    No entanto, quando uma consulta faz referência a essa expressão, o SQL Server pode escolher entre usar o valor persistente (se as configurações corresponderem) ou computar a expressão novamente.

    Algumas configurações de sessão podem fazer com que um estouro retorne NULL, por exemplo, portanto, o SQL Server deve considerar essa possibilidade. Quando acessado por meio da exibição, o SQL Server marca corretamente a coluna como potencialmente retornando um NULL.

    Usar um extremo ISNULLna expressão é a única maneira com suporte de obter o que você deseja. Usar COALESCEnão vai funcionar, por exemplo.

    Demonstração:

    CREATE TABLE dbo.T1
(
    c1 integer NOT NULL,
    c2 integer NOT NULL,
    c3 AS c1 + c2 PERSISTED NOT NULL
);
GO
CREATE VIEW dbo.V1
AS
SELECT T.c1,
       T.c2,
       T.c3
FROM dbo.T1 AS T;
GO
SELECT AllowsNull = COLUMNPROPERTY(OBJECT_ID(N'dbo.V1', N'V'), N'c3', 'AllowsNull');
GO
ALTER TABLE dbo.T1
DROP COLUMN c3;
GO
ALTER TABLE dbo.T1
ADD c3 AS ISNULL(c1 + c2, 0) PERSISTED NOT NULL;
GO
EXECUTE sys.sp_refreshsqlmodule
    @name = N'dbo.V1';
GO
SELECT AllowsNull = COLUMNPROPERTY(OBJECT_ID(N'dbo.V1', N'V'), N'c3', 'AllowsNull');
GO
DROP VIEW dbo.V1;
DROP TABLE dbo.T1;
GO

    Observe o uso de sys.sp_refreshsqlmoduleporque sua exibição não é delimitada pelo esquema.

    • 13

relate perguntas