在 SQL Server Management Studio 中标记持久计算列 NOT NULL [英] Marking persisted computed columns NOT NULL in SQL Server Management Studio

问题描述

可以在 SQL Server 2005 中创建一个计算列，该列既持久化又定义为 NOT NULL(不能包含空值).如果我们想避免大量手动工作以确保我们的代码始终"有一个值，那么在使用像 Linq2Sql 这样的库时，第二个属性很重要.

It is possible in SQL Server 2005 to create a computed column that is both persisted and is defined as NOT NULL (cannot contain a null value). The 2nd attribute is of importance when using libraries like Linq2Sql if we want to avoid a lot of manual work to assure our code that the column 'always' has a value.

直接使用 SQL 这很简单:
ALTER TABLE Sales ADD Total AS (Price + Taxes) PERSISTED NOT NULL

With straight SQL this is very simple:
ALTER TABLE Sales ADD Total AS (Price + Taxes) PERSISTED NOT NULL

在 SQL Server Management Studio 的设计窗口中查看时，此列正确显示为计算列，没有勾选允许空值".但是，我在设计器中创建新列以匹配此模式时遇到问题:在计算列规范 -> (Formula) 属性中输入公式，并通过将 Is Persisted 设置为 Yes 来指定持久属性，但尝试取消选中新计算列上的允许空值"会导致出现一个对话框，指出无法修改属性".

When viewed in the design window of SQL Server Management studio this column is correctly shown as a computed column with no checkmark for 'allows nulls'. However I run into a problem creating new columns in the designer to match this pattern: The formula is entered in the Computed Column Specification -> (Formula) property, and the persisted property is specified by setting Is Persisted to Yes, but attempting to uncheck the 'allows nulls' on a new computed column results in a dialog stating "Property cannot be modified".

我需要涵盖广泛的技能水平，为此我需要提供添加列的过程，即使是新手也可以遵循(这意味着 Management Studio 设计器窗口).SQL Server Management Studio 中是否有一些秘密可以在设计器中创建一个新的计算列作为 NOT NULL，类似于如何使用 CTRL+0 将空值插入单元格?

I need to cover a wide range of skill levels, and to do so I need to provide procedures for adding columns that even a novice can follow (which means the Management Studio designer window). Is there some secret in SQL Server Management Studio for creating a new computed column as NOT NULL in the designer, similar to say how CTRL+0 can be used to insert nulls into cells?

推荐答案

你可以用 ISNULL(Price + Taxes, 0) 作弊，它使用默认值 0 进行 NULL 计算.

You may cheat this with ISNULL(Price + Taxes, 0) which uses the default value 0 for NULL computations.

这篇关于在 SQL Server Management Studio 中标记持久计算列 NOT NULL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！