Post reply

Setting IS_NULLABLE on columns in a View

  • August 26, 2011 at 8:08 am

    #256261

    This might sound like a strange question, but I need to see if there is some way to manually set the IS_NULLABLE flag on columns in a View. The reason is because our developers are using Entity Framework within the application to pull data from the database using Views, and Entity Framework uses the View settings of when the View was added to the application to determine how to interact with the data.

    What's happening as we develop the application is we have a few fields I've had to remove from the database, but to not break the application and give the dev's time to remove the field from the app I've replaced the database column in the View with a static field. So for example this:

    Customer.Date as CustomerDate, -- Customer.Date being a Datetime field set to Not Null

    ... would be replaced by this ...

    cast(0 as Datetime) as CustomerDate,

    The problem though is I generally keep all columns in the database setup as Not Null, but when I change the column in the View to a static column it sets IS_NULLABLE = True on that column instead of keeping it as False when it was tied to an actual column which was set to Not null. I've tried using IsNull and similar functions in hopes that SQL would recognize that a Null would be impossible and set IS_NULLABLE = False on these static columns, but no dice thus far.

    So are there any options to set the IS_NULLABLE flag for columns in Views? I'm not a C# Developer nor do I know much about Entity Framework so I can't explain why the IS_NULLABLE flag breaks the application, but given I can make changes to the Views like this without affecting the datatype or IS_NULLABLE flag I should be able to feed any data, whether static or from a table, into the application using an existing View while keeping our developers from screaming.

    Thanks for any advice...

    Sam

  • August 26, 2011 at 9:40 am

    #1373887

    try '1 jan 1990' as [CustomerDate]

    in my view this

    create view vw_testing

    as

    select 1 as NumKey,'fred' as [JimBob],'1 jan 2011' as [TheDate],userid

    from dbo.;

    the columns which are static show as not null.

    [font="Comic Sans MS"]The GrumpyOldDBA[/font]
    www.grumpyolddba.co.uk
    http://sqlblogcasts.com/blogs/grumpyolddba/

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply