Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Ziden
Starting Member
2 Posts |
 Posted - 2014-06-03 : 14:51:10
|
Hello there. Lets supose, im migrating a whole database schema, with new tables, for a huge system. For standard, my CONCAT_NULL_YIELDS_NULL is OFF. However, we are creating indexed views for the old tables so the system can still work, however CONCAT_NULL_YIELDS_NULL must be ON in order for that to work. So, if i turn CONCAT_NULL_YIELDS_NULL to ON, some codes that can possibly use NULL+'String' can return NULL and some systems can malfunction. If i use CONCAT_NULL_YIELDS_NULL OFF, i cannot access my indexed view. So, for now, we are setting CONCAT_NULL_YIELDS_NULL manually on each connection that use those views. Im thinking, if , is there a a way i can automate this, for instance, setting CONCAT_NULL_YIELDS_NULL ON everytime the VIEW is read, then OFF again, not dependant on the connection, so old systems can still work fine, and i still can read my indexed view. Thanks alot for the attention ! Best Regards, Ziden  |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-06-03 : 15:42:16
|
| See if you can use a standard view instead of an indexed view.Also, I don't think the setting has to be ON when you read from the indexed view, only when you modify (CREATE table|view / DELETE / INSERT / UPDATE) the view. |
 |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2014-06-03 : 15:42:24
|
Why would you pick a standard that is going to go away and cause errors in future versions of SQL Server? I'd change your standard to ON.quote:
In a future version of SQL Server CONCAT_NULL_YIELDS_NULL will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
|
|
|
|
Ziden
Starting Member
2 Posts |
Posted - 2014-06-03 : 15:53:31
|
| Because my boss wants to lol, hes afraid some silent errors may appear. And because it says, turning it on, may cause problems inserting null values silently. Is there a way to throw errors while tryng to concat null fields with strings ?Thanks for the attention ! |
|
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2014-06-04 : 10:56:30
|
| No, I don't think you can force an error when concat'ing NULL.Another option would be to replace all NULLs in non-numeric columns with empty string (''). But that opens another problem of code that checks for NULL but not for ''. There's no way out of some issues coming up here.I think I'd go thru the code and try to re-code any concatenations to be "null aware". Do this 2-3 times. Then maybe turn on the setting and see what errors come up?! |
|
|
|
|
|
|
|
|
Views Indexes needing NULL CONCAT