如何使视图列 NOTNULL

我试图创建一个视图，其中列只能是 true 或 false。然而，似乎无论我做什么，SQLServer (2008)都认为我的位列可以是空的。

我有一个名为“ Product”的表，其中“ Status”列为 INT, NULL。在视图中，我希望为 Product 中的每一行返回一行，如果 Product。状态列等于3，否则位字段应为 false。

示例 SQL

SELECT CAST( CASE ISNULL(Status, 0)
WHEN 3 THEN 1
ELSE 0
END AS bit) AS HasStatus
FROM dbo.Product

如果将此查询保存为视图并查看对象资源管理器中的列，则 HasStatus 列设置为 BIT, NULL。但它不应该是 NULL。是否有一些神奇的 SQL 技巧，我可以用来强制这个列为 NOT NULL。

注意，如果我移除 CASE周围的 CAST()，该列被正确地设置为 NOT NULL，但是该列的类型被设置为 INT，这不是我想要的。我希望是 BIT。:-)

63802 次浏览
小开

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.

  1. First make sure that there are currently no nulls in that bit field in the table
  2. Then execute the following ddl statement: 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 never be null. It will always be either bit value = 1 or bit value = 0. Don't worry what the object explorer says...

小开
最佳答案

You can achieve what you want by re-arranging your query a bit. The trick is that the ISNULL has to be on the outside before SQL Server will understand that the resulting value can never be 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 ORM and 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.

小开

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.

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

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?

I found this post, threw ISNULL() around the column and voila - not nullable anymore.

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.


提交答案