How to specify columns as NOT NULL in a view?

Question

I am trying to use a view to create an ADO.NET object using a view. However, there are no columns in this view that are not NULL.

One thing that arose for me was to create a NOT NULL column in the view to use as the "primary key" for the view. This worked, but the field still displays as NULL.

Is there a way to force or trick SQL Server into reporting this view column as NOT NULL?

Imagine a view something like:

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

Note. . Before I say that I can edit an entity XML object to do this, I ask about it because I have a VERY large number of objects created this way.

Answer 1

I don't know if it's worth it, but I think that creating a function that returns a temporary table with NOT NULL fields should work for the computed value. e.g.

 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 this function in the view.

Greetings.

Answer 2

First, to answer your question:

You do not want to use newid() to define your id field, as this will be recounted every time you use the view. Indeed, data integrity is the biggest problem.

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

This only works if you order a field that will have a sequential order that will add new rows, for example the CreateDate field. If you do not have this, this identifier may be changed. Now, if you only need this identifier at runtime, and there is nothing that forever binds to them, row_number will be just strong. If you have unreliable data, you do not have a reliable identifier unless you create an additional table and do not use triggers to populate it.

Secondly, be careful with this with schemabinding . This is dangerous if used as shreds. Remember that once you create a view with schemabinding , you cannot completely change the schema of any base table. This means that you cannot make a varchar(50) a varchar(100) column without resetting and re-adding all views with with schemabinding . Yes, you can index a view that is related to schemabound, but there are certain trade-offs that you need to consider before you block, store, and barrel.

Answer 3

I used ISNULL for this. That is, something like the following worked for me:

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

Answer 4

Use newid ()

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