在 SQL Server Management Studio 中将持久计算列标记为 NOT NULL
在 SQL Server 2005 中可以创建一个既持久又定义为 NOT NULL（不能包含空值）的计算列。当使用像 Linq2Sql 这样的库时，如果我们想避免大量的手动工作来确保我们的代码列“始终”具有值，那么第二个属性非常重要。
使用直接 SQL，这非常简单：ALTER TABLE Sales ADD Total AS (Price + Taxes) PERSISTED NOT NULL
在 SQL Server Management Studio 的设计窗口中查看时，此列正确显示为计算列，没有“允许空值”复选标记。但是，我在设计器中创建新列以匹配此模式时遇到了问题：公式在计算列规范 -> 中输入（公式）属性，并且持久化属性是通过将“持久化”设置为“是”来指定的，但尝试取消选中新计算列上的“允许空值”会导致显示“无法修改属性”的对话框。
我需要涵盖广泛的技能水平，为此，我需要提供添加列的过程，即使是新手也可以遵循（这意味着 Management Studio 设计器窗口）。 SQL Server Management Studio 中是否有一些秘密可以在设计器中创建一个新的计算列作为 NOT NULL，类似于如何使用 CTRL+0 将空值插入到单元格中？
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.
With straight SQL this is very simple:ALTER TABLE Sales ADD Total AS (Price + Taxes) PERSISTED NOT NULL
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".
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您可以使用 ISNULL(Price + Taxes, 0) 来欺骗它，它使用默认值 0 进行 NULL 计算。
You may cheat this with
ISNULL(Price + Taxes, 0)which uses the default value 0 for NULL computations.正如 Scoregraphic 所指出的，您可以使用
ISNULL来完成此操作。我经常将其用于计算标志，例如，在
User表中，我有一个DeletedDate来了解帐户何时被删除。然后，我创建一个名为IsDeleted（类型位）的计算的不可空布尔列，如下所示：需要注意的重要一点是，ISNULL 必须位于表达式的最外层，以便设计者实现它是不可为 null 的计算列。
As Scoregraphic notes, you can do this with
ISNULL.I often use this for computed flags, e.g., in the
Usertable, I have aDeletedDateto know when the account was deleted. I then create a computed non-nullable boolean column calledIsDeleted(type bit) like this:The important thing to note is that the ISNULL must be on the outermost part of the expression for the designer to realize it is a non-nullable computed column.
我尝试了这个并查看左侧的树视图，它确实将该列设置为非空，即使在右窗格设计器中未选中该复选框...
I tried this and looking at the treeview on the left it had indeed set the column up as not null, even though in the right pane designer the checkbox was not checked...
根据这篇文章，可空性由sqlserver根据可能的值确定计算表达式。由于
Price或Taxes可能可为空，因此无法确定它们的总和永远不会为空。然而，正如 @Richard 所建议的，使用 ISNULL 方法可以避免这种情况。然而，据我所知，声明列
NOT NULL应该不是必要的。According to this article nullability is determined by sqlserver based on the possible value of the computed expression. Since
PriceorTaxesis probably nullable, it cannot be sure that their sum is never null.However, as @Richard suggests, using the
ISNULLmethod avoids this. Declaring the columnNOT NULLshould however not be necessary as far as I have understood.