How to indicate a columns as NOT NULL in a view?

Question

I'm trying to use a view to create an ADO.NET entity using a view. However, that view does not have a single column that is NOT NULL.

One thing that occurred to me was to create a NOT NULL column on the view to use as a 'primary key' for the view. This worked, but the field is still reported as NULL.

Is there a way to force or trick SQL Server to report that view column as NOT NULL?

Imagine the view as something like:

CREATE VIEW vwSample WITH SCHEMABINDING
AS
SELECT ID = convert(uniqueidentifier, /* some computed value */)
      ,Field1
      ,Field2
      ,Field3
  FROM tbSample

Note: Before saying that I can edit the entity XML to do such thing, I'm asking this because I have a VERY large number of entities to be created this way.

Answer 1

Use newid()

CREATE VIEW vwSample WITH SCHEMABINDING
AS
SELECT ID = NEWID()
      ,Field1
      ,Field2
      ,Field3
  FROM tbSample

Answer 2

Hi,

I don't know if it's worth the effort, but I think creating a function that returns Temporary Table with NOT NULL fields should work for the computed value. ex.

Create function fnSTestample()
returns @Test TABLE 
(
    tableId varchar(100) not null
)
WITH SCHEMABINDING
as
begin
    insert @Test(tableID)
    select 'some computed value' from dbo.someTable
return
end

and then you select that function in the view.

Cheers.

Answer 3

Firstly, to answer your question:

You don't want to use newid() to determine your id field, since this will be recalculated every time you use the view. Really, data integrity is the biggest issue.

select
    row_number() over (order by someStaticAndUniqueFieldLikeCreateDate) as ID,
    Field1,
    Field2,
    Field3
from
    tblA
order by
    someStaticAndUniqueFieldLikeCreateDate

This only works if you're ordering on a field that will have a consistent ordering that will append new rows, such as a CreateDate field. If you don't have this, that ID is subject to change. Now, if you only need these ID's at runtime, and there's nothing that permanently links to them, row_number will be just peachy keen. If you have unreliable data, there's no way to have a reliable ID field unless you create an additional table and use triggers to populate it.

Secondly, be careful with that with schemabinding. It's dangerous if used as a kludge. Remember that as soon as you create a view with schemabinding, you cannot alter the schema of any underlying table at all. This means you can't make a varchar(50) column a varchar(100) without dropping and re-adding all views that have with schemabinding enabled. Yes, you can index a view that's schemabound, but there are definitely tradeoffs that need to get taken into account before you go lock, stock, and barrel.