QUOTED_IDENTIFIER & ANSI_NULLS
Ted Theo
two options on and off along with blank lines at the beginning and end
of every object you edit? i have searched quite a bit on this but
haven't been able to come up with anything.
Uri Dimant
I'm affraid you cannot. What is your concern?
"Ted Theo" <ted...@gmail.com> wrote in message
news:4b68c64c-e5ab-4024...@d21g2000prf.googlegroups.com...
Erland Sommarskog
There does not seem to be an option for this.
The reason they are there, is that these to set options are saved with
the procedure. I can understand that it is a bit of a nuisance. But since
Enterprise Manager incorrectly has these two off by default, it's
probably a good thing that QA includes them with the right setting. (But
it's not good that there is a SET OFF for one of them at the end.)
Personally, I don't find this a hassle, since I keep my code under source
control, and rarely have reason to script it from the database.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Ted Theo
> Ted Theo (tedt...@gmail.com) writes:
> > does anyone know how to keep QA from adding the lines setting these
> > two options on and off along with blank lines at the beginning and end
> > of every object you edit? i have searched quite a bit on this but
> > haven't been able to come up with anything.
>
> There does not seem to be an option for this.
>
that don't use source control (single dev projects) than ones that do
so i encounter it frequently. i have a high level understanding of
what both options accomplish and i haven't found a case where setting
them at the individual object level has been advantageous. maybe i'm
just missing that part.
it seems sql server management studio just turns these settings on
when scripting an object and doesn't turn them off. is there a way to
turn this behavior off in mgmt studio? is there a reason i wouldn't
want to do this?
> The reason they are there, is that these to set options are saved with
> the procedure. I can understand that it is a bit of a nuisance. But since
> Enterprise Manager incorrectly has these two off by default, it's
> probably a good thing that QA includes them with the right setting. (But
> it's not good that there is a SET OFF for one of them at the end.)
>
> Personally, I don't find this a hassle, since I keep my code under source
> control, and rarely have reason to script it from the database.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
--CELKO--
Conformance to ANSI/ISO Standards should be a goal in any shop, so you
would not turn off options that bring you to that goal. Why would you
want to write your own database language?
Alex Kuznetsov
> >> is there a reason I wouldn't want to do this? <<
>
> Conformance to ANSI/ISO Standards should be a goal in any shop,
Unfortunately, currently the goal in most places is making money, and
unfortunately adherence to suboptimal standards may prevent businesses
from making money. How about improving the standard first?
Erland Sommarskog
> it's just a bit of a nuisance like you said. i have more projects
> that don't use source control (single dev projects) than ones that do
> so i encounter it frequently. i have a high level understanding of
> what both options accomplish and i haven't found a case where setting
> them at the individual object level has been advantageous. maybe i'm
> just missing that part.
Like it or not, the settings of the two *are* saved with each procedure.
This is in difference from, say, ANSI_WARNINGS, where the run-time setting
of the two apply.
As for which of the two settings to use, keep in mind that there are
features in SQL Server that are not available if any of ANSI_NULLS
or QUOTED_IDENTIFIER are off:
o Indexed views and index on computed columns.
o Xquery.
o Queries involving linked servers (ANSI_NULLS only).
Of course, if you use default settings etc, there should never be any
reason to include these in the script, because it should be a rare
exception that you deliberately would create a procedure with any of
them off. (The only half-good reason I can think of is that you work
with dynamic SQL in several layers and nesting quotes is driving you
crazy. Turning off QUOTED_IDENTIFIERS permits you to use " as a string
delimiter as well to save your sanity.)
> it seems sql server management studio just turns these settings on
> when scripting an object and doesn't turn them off. is there a way to
> turn this behavior off in mgmt studio? is there a reason i wouldn't
> want to do this?
The fact that SSMS do not set them OFF, is probably my fault. I bitched
about that during the beta of SQL 2005.
No, neither SSMS appears to have an option for this, just like QA there
is only an option for controlling whether ANSI_PADDING should be
scripted tables.
The best I can suggest is that you file an suggestion to add such an
option on https://connect.microsoft.com/SQLServer/feedback/. If you do,
please post the URL. I may vote for it. :-)
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Bill Yanaire
"--CELKO--" <jcel...@earthlink.net> wrote in message
news:29789bc2-91b0-4f7a...@b15g2000hsa.googlegroups.com...
The goal here is helping people with their queries, and NOT telling them
they should be a "BY THE BOOK" kind of STIFF like yourself.
Just FYI