title: How to make a view column NOT NULL
body: <p>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.</p>
  
  <p>I have a table called "Product" with the column "Status" which is <code>INT, NULL</code>. 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.</p>
  
  <p>Example SQL  </p>
  
  <pre><code>SELECT CAST( CASE ISNULL(Status, 0)  
                 WHEN 3 THEN 1  
                 ELSE 0  
               END AS bit) AS HasStatus  
  FROM dbo.Product  
  </code></pre>
  
  <p>If I save this query as a view and look at the columns in Object Explorer, the column HasStatus is set to <code>BIT, NULL</code>. But it should never be NULL. Is there some magic SQL trick I can use to force this column to be <code>NOT NULL</code>.</p>
  
  <p>Notice that, if I remove the <code>CAST()</code> around the <code>CASE</code>, the column is correctly set as <code>NOT NULL</code>, but then the column's type is set to <code>INT</code>, which is not what I want. I want it to be <code>BIT</code>. :-)</p>