0

Assume the following table structure:

CREATE TABLE [dbo].[TEST_TABLE]
(
   [TEST_VALUE] [smallint] NOT NULL
)

The value in the column can be 0, 1 or 2. The rationale why this can happen is irrelevant for the sake of the question.

Now, assume we create a view to encapsulate a simple query to this table:

CREATE VIEW [dbo].[TEST_VIEW]
AS 
SELECT RETURN_VALUE =
       CASE
           WHEN TBL.[TEST_VALUE] = 1 THEN CAST(1 as bit)
           ELSE CAST(0 as bit)
       END
FROM [dbo].[TEST_TABLE] AS [TBL]

After this view is created if we check what columns the view returns then the RETURN_VALUE column is typed as (bit, null) in SQL Server Management Studio, and no constructs such as wrapping the entire CASE ... END clause into a CAST() seems to change this fact.

In which scenario can the return value truly be NULL? Or is SQL Server just being extra-cautious here? Is there a way how I can enforce the view column's datatype to be (bit, not null)?

Antti Keskinen
  • 13
  • 3
  • A hint - is `TEST_VALUE` nullable? If it takes a value of NULL, what should `RETURN_VALUE` be? – Ben Thul Nov 26 '20 at 17:41
  • @BenThul: As stated in the table creation DDL, it's not nullable. – Antti Keskinen Nov 27 '20 at 09:07
  • @Turo: Yes, the answer is sufficient to resolve my issue. I did find a link to DBA Stack Exchange from the discussion linked to the answer you gave: https://dba.stackexchange.com/questions/114260/why-is-a-not-null-computed-column-considered-nullable-in-a-view This explains the situation, although I'm not using a computed column. – Antti Keskinen Nov 27 '20 at 09:11

0 Answers0