SQL 如何使视图列 NOT NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2326813/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to make a view column NOT NULL
提问by René
I'm trying to create a view where I want a column to be only true or false. However, it seems that no matter what I do, SQL Server (2008) believes my bit column can somehow be null.
我正在尝试创建一个视图，其中我希望一列只有 true 或 false。但是，似乎无论我做什么，SQL Server (2008) 都认为我的位列可能以某种方式为空。
I have a table called "Product" with the column "Status" which is INT, NULL. In a view, I want to return a row for each row in Product, with a BIT column set to true if the Product.Status column is equal to 3, otherwise the bit field should be false.
我有一个名为“产品”的表，其中的“状态”列是INT, NULL. 在视图中，我想为 Product 中的每一行返回一行，如果 Product.Status 列等于 3，则 BIT 列设置为 true，否则位字段应为 false。
Example SQL
示例 SQL
SELECT CAST( CASE ISNULL(Status, 0)
WHEN 3 THEN 1
ELSE 0
END AS bit) AS HasStatus
FROM dbo.Product
If I save this query as a view and look at the columns in Object Explorer, the column HasStatus is set to BIT, NULL. But it should never be NULL. Is there some magic SQL trick I can use to force this column to be NOT NULL.
如果我将此查询保存为视图并查看对象资源管理器中的列，则 HasStatus 列将设置为BIT, NULL。但它永远不应该是 NULL。是否有一些神奇的 SQL 技巧可以用来强制此列为NOT NULL.
Notice that, if I remove the CAST()around the CASE, the column is correctly set as NOT NULL, but then the column's type is set to INT, which is not what I want. I want it to be BIT. :-)
请注意，如果我删除CAST()周围的CASE，则该列将正确设置为NOT NULL，但是该列的类型设置为INT，这不是我想要的。我想要它BIT。:-)
回答by RedFilter
You can achieve what you want by re-arranging your query a bit. The trick is that the ISNULLhas to be on the outside before SQL Server will understand that the resulting value can never be NULL.
您可以通过稍微重新安排查询来实现您想要的。诀窍是ISNULL必须在外部，然后 SQL Server 才能理解结果值永远不会是NULL。
SELECT ISNULL(CAST(
CASE Status
WHEN 3 THEN 1
ELSE 0
END AS bit), 0) AS HasStatus
FROM dbo.Product
One reason I actually find this useful is when using an ORMand you do not want the resulting value mapped to a nullable type. It can make things easier all around if your application sees the value as never possibly being null. Then you don't have to write code to handle null exceptions, etc.
我实际上发现这很有用的一个原因是在使用ORM并且您不希望结果值映射到可为空类型时。如果您的应用程序认为该值永远不可能为空，则可以使事情变得更容易。那么您就不必编写代码来处理空异常等。
回答by user1664043
FYI, for people running into this message, adding the ISNULL() around the outside of the cast/convert can mess up the optimizer on your view.
仅供参考，对于遇到此消息的人，在强制转换/转换的外部添加 ISNULL() 可能会弄乱您视图中的优化器。
We had 2 tables using the same value as an index key but with types of different numerical precision (bad, I know) and our view was joining on them to produce the final result. But our middleware code was looking for a specific data type, and the view had a CONVERT() around the column returned
我们有 2 个表使用相同的值作为索引键，但具有不同的数值精度类型（不好，我知道），我们的观点是加入它们以产生最终结果。但是我们的中间件代码正在寻找特定的数据类型，并且视图在返回的列周围有一个 CONVERT()
I noticed, as the OP did, that the column descriptors of the view result defined it as nullable and I was thinking It's a primary/foreign key on 2 tables; why would we want the result defined as nullable?
我注意到，正如 OP 所做的那样，视图结果的列描述符将其定义为可为空的，我在想它是 2 个表上的主键/外键；为什么我们希望将结果定义为可为空？
I found this post, threw ISNULL() around the column and voila - not nullable anymore.
我找到了这篇文章，在列周围抛出了 ISNULL() ，瞧——不再可以为空了。
Problem was the performance of the view went straight down the toilet when a query filtered on that column.
问题是当在该列上过滤查询时，视图的性能直接下降。
For some reason, an explicit CONVERT() on the view's result column didn't screw up the optimizer (it was going to have to do that anyway because of the different precisions) but adding a redundant ISNULL() wrapper did, in a big way.
出于某种原因，视图结果列上的显式 CONVERT() 并没有搞砸优化器（由于精度不同，无论如何它都必须这样做），但是添加一个冗余的 ISNULL() 包装器确实如此，在一个很大的道路。
回答by Charles Bretana
All you can do in a Select statement is control the data that the database engine sends to you as a client. The select statement has no effect on the structure of the underlying table. To modify the table structure you need to execute an Alter Table statement.
您在 Select 语句中所能做的就是控制数据库引擎作为客户端发送给您的数据。select 语句对基础表的结构没有影响。要修改表结构，您需要执行 Alter Table 语句。
- First make sure that there are currently no nulls in that bit field in the table
- Then execute the following ddl statement:
Alter Table dbo.Product Alter column status bit not null
- 首先确保表中该位字段中当前没有空值
- 然后执行以下ddl语句：
Alter Table dbo.Product Alter column status bit not null
If, otoh, all you are trying to do is control the output of the view, then what you are doing is sufficient. Your syntax will guarantee that the output of the HasStatus column in the views resultset will in fact neverbe null. It will alwaysbe either bit value = 1 or bit value = 0. Don't worry what the object explorer says...
如果，哦，您所做的只是控制视图的输出，那么您所做的就足够了。您的语法将保证视图结果集中的 HasStatus 列的输出实际上永远不会为空。它始终是位值 = 1 或位值 = 0。不要担心对象浏览器说的是什么......
