SQL Server: Make a column nullable in a view

I had a SQL view and I was reading HR info into the MIM database via SQL Connector. The manager column in the view was coming up as not-nullable. That is, it does not allow null values for that column. In MIM you have to declare the manager attribute in a feed as a reference attribute so MIM can identify the manager. The column was coming in from the feed as a string attribute, so I went to the MIM SQL connector schema to change it to a reference attribute, I got the message that you cannot do this for a non-nullable column. So I had to go to the view and make the column nullable.

Well, a View column by default inherits the property of the table column that feeds it. But, the data feeding my View column was a derivative column and not a direct table column. There was a logical Supervisor table and the view was doing this

supervisor.Id AS manager

So I had to make this change direct on the view. I used the CAST function. This function converts one datatype to another. Of course depending on the size and frequency of use of the table it can be performance drain. My table is a very small table (~20k) rows so its not an issue at all. I used

Cast(supervisor.ID as (varchar(50)) AS manager

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s