May 29, 2004 at 11:54 pm
If I have SET CONCAT_NULL_YIELDS_NULL OFF in my SP, does it recompile each time it is accessed? Someplace I read that it would.
Thanks,
Phil
May 30, 2004 at 8:47 am
To test for sure use profiler as I don't have a way to test here.
May 31, 2004 at 5:06 am
Yes, it will. If you write "SET CONCAT_NULL_YIELDS_NULL OFF" or "SET CONCAT_NULL_YIELDS_NULL ON" (as well as several other SET options) in a stored procedure, then the SP will be recompiled everytime this statement is executed. For more informations, read:
http://support.microsoft.com/?kbid=294942
http://www.winnetmag.com/SQLServer/Article/ArticleID/16308/16308.html
The workarounds suggested in the KB article are:
a) use the ISNULL() function
b) set the CONCAT_NULL_YIELDS_NULL option before executing the procedure
Razvan
May 31, 2004 at 6:59 am
Razvan,
Thank you very much.
Phil
May 31, 2004 at 7:35 am
Yes, It do recompile everytime. Here are some more notes:
Recompilations due to SET options in procedures are a very common problem, but hard to find. In many
cases, consultants or DBAs may have trouble scanning the sources of large applications looking for
these variables. When developing new applications, unless you have a very good reason, leave the
following parameters at their default ON setting. Setting them OFF inside a procedure will result in
recompilation. The settings include:
– ANSI_NULLS
– ANSI_DEFAULTS
– ANSI_PADDING
– ANSI_WARNINGS
– CONCAT_NULL_YIELDS_NULL
If these parameters must be set, do so outside the procedure body. Also, remember that these settings
can also be modified as the result of generated code from another procedure or application.
May 31, 2004 at 7:35 am
Yes, It do recompile everytime. Here are some more notes:
Recompilations due to SET options in procedures are a very common problem, but hard to find. In many
cases, consultants or DBAs may have trouble scanning the sources of large applications looking for
these variables. When developing new applications, unless you have a very good reason, leave the
following parameters at their default ON setting. Setting them OFF inside a procedure will result in
recompilation. The settings include:
– ANSI_NULLS
– ANSI_DEFAULTS
– ANSI_PADDING
– ANSI_WARNINGS
– CONCAT_NULL_YIELDS_NULL
If these parameters must be set, do so outside the procedure body. Also, remember that these settings
can also be modified as the result of generated code from another procedure or application.
Regards,
Jithender
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply