Anyone know how to or where the instructions are for setting up SQL 2000 for
SMS 2.0 usage is? I know you have to create the accounts in both the
computer / domain and in SQL, but what rights do you give them, etc ? There
used to be a "How To" article in the knowledge base, but that has all but
disappeared when SMS 2003 rolled out.
TIA"_M_" <here@.gone.com> wrote in message
news:#b77YR8nDHA.2676@.TK2MSFTNGP11.phx.gbl...
> Anyone know how to or where the instructions are for setting up SQL 2000
for
> SMS 2.0 usage is? I know you have to create the accounts in both the
> computer / domain and in SQL, but what rights do you give them, etc ?
There
> used to be a "How To" article in the knowledge base, but that has all but
> disappeared when SMS 2003 rolled out.
>
This would normally be a post for the SMS newsgroups :)
The easiest way to run your installation, here are the basic steps:
1) allow your SMS Service account to be sysadmin of the SQL Server
2) log in as the SMS Service account and create the database that SMS will
use, I always use SMS<SiteCode>
3) Install SMS under the context of the SMS Service account, when you get to
the database install dialogs, choose SQL Server 2000, do not create a
database, which will then prompt for the name of the existing database.
There are several other decision points, authentication mode and a prompt to
enter the service account...
Steve
2012年3月25日星期日
Anyone know anything about Sql Server HIPAA compliance?
Does anyone know of a good tutorial for setting up a website for HIPAA compliance? In particular, what's involved in setting up Sql Server to be compliant?
I'm developing a simple c# website for a friend who owns a small company based in the medical field. Now that his company is growing, he wants to allow his doctors to log into his website to view their patients' test results rather than having to snail mail out the test results every day.
This brings us to a rather delicate issue because even though it's just a few simple pages on the website to view this test result data, I think we're now into HIPAA compliance territory. Meaning we'll have to look at more secure (and expensive) measures than what is currently being performed. For example, I was looking at a web hosting service that provides .net hosting & shared sql server for $15/month. Will I now need to look into a $199/month dedicated server (which probably would cancel the project since, as I said, it's just a simple website with a few small simple features).
Thanks for any help on this.
-Goalie35
You could start by looking athttp://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1193101,00.html?topic=301323
By shared, is it multiple instances of SQL Server or just multiple databases attached to the one server instance?
2012年3月11日星期日
Any Timeout setting in ConnectionString of web.config
Hi:
I have some query that takes quite a long time to process in the sql server and every time the page seems to time out.
I wondor is there any Timeout setting that I can defined in the database ConnectionString in web.config file so that I can extend the "wait" time?
Many thanks!
I believe there is:
"integrated security=SSPI;SERVER=YOUR_SERVER;DATABASE=YOUR_DB_NAME;Connect Timeout=45;";
|||Jason,
I recently had a time out issue with an app after some servers where moved to a centralized location. In my case, the use of the sql server name caused my app to time out as apparently the newly located web server wasn't able to resolve the sql server name in the web.config file fast enough against the WINS server. The solution was to use our sql server's IP address in web.congif file instead of it's name. Then the app ran just fine.
Time outs can definitely be network related. Need to look at that as well as any app configuring.
|||Unfortunately not Jason. The timeout in the connection string only controls the timeout of the connection (How long the connection will wait before giving up), normally it's irrelevant as you'll get a (fairly) quick negative response.
That said, in your pages where the default 30 second command timeout is too short, in your SqlDataSource_Selecting/Inserting events, the e parameter will have a reference to the actualy SqlCommand object that is about to be used. Set it's timeout. Off the top of my head, I believe it is like:
e.SelectCommand.Timeout=90
That is assuming that you are having the issue with a SqlDataSource. SqlCommand objects have their own timeout parameter that you can set if that is what you are using and having an issue with.
|||Actually, e.Command.CommandTimeout=90
2012年3月8日星期四
Any setting that would reduce locking during a SQL script?
I am running an upgrade script to update a database from one release to
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
the
occurs.
way
locking?
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
the
occurs.
way
locking?
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
quote:
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
quote:
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
quote:
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
quote:
> that I could make it so that I get table locks instead of row-level
locking?
quote:|||One to look at will be setting the database into single user mode.
> Thanks in advance.
>
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
quote:
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
quote:
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
quote:
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
quote:
> that I could make it so that I get table locks instead of row-level
locking?
quote:
> Thanks in advance.
>
Any setting that would reduce locking during a SQL script?
I am running an upgrade script to update a database from one release to
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>|||One to look at will be setting the database into single user mode.
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>
another. There are a lot of SQL scripts that run during this process.
While this process is running - there should be no other users accessing the
database.
With this in mind - I am hoping to reduce the amount of locking that occurs.
Since no one else will be accessing the system - I don't need all of the
locks at the granular level (I am fine with table locks). Is there some way
that I could make it so that I get table locks instead of row-level locking?
Thanks in advance.You can specify the TABLOCKX hint to acquire an exclusive table lock. For
example:
INSERT INTO MyTable WITH (TABLOCKX)
SELECT * FROM MyOtherTable WITH (TABLOCKX)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>|||One to look at will be setting the database into single user mode.
I'm not sure whether it causes locks to escalate, but admin work such as
you're performing is one of it's indended usage scenarios.
You can set a db into single_user by using the alter database command, eg:
alter database [dbname] set single_user
Regards,
Greg Linwood
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23yBrPIH4DHA.2380@.TK2MSFTNGP10.phx.gbl...
> I am running an upgrade script to update a database from one release to
> another. There are a lot of SQL scripts that run during this process.
> While this process is running - there should be no other users accessing
the
> database.
> With this in mind - I am hoping to reduce the amount of locking that
occurs.
> Since no one else will be accessing the system - I don't need all of the
> locks at the granular level (I am fine with table locks). Is there some
way
> that I could make it so that I get table locks instead of row-level
locking?
> Thanks in advance.
>
2012年2月13日星期一
AntiVirus Software for SQL Cluster Servers
Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
software I should use. Any information will be greatly appreciated.
I like TrendMicro, whatever you get - exclude the MSCS and data directories,
if not the entire shared disks at a minimum.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||I have never used TrendMicro. Should I order a specific version? will it be
aware of the cluster?
Thanks for your help!
Yuhong
"Rodney R. Fournier [MVP]" wrote:
> I like TrendMicro, whatever you get - exclude the MSCS and data directories,
> if not the entire shared disks at a minimum.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://www.msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
>
>
|||The latest OfficeScan is cluster aware. Don't let the silly name fool you,
it work for both workstations and servers.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:E2E02DA9-F7B3-48C4-9846-7B0835B4A09D@.microsoft.com...[vbcol=seagreen]
>I have never used TrendMicro. Should I order a specific version? will it be
> aware of the cluster?
> Thanks for your help!
> Yuhong
> "Rodney R. Fournier [MVP]" wrote:
|||Great. Thanks so much for your information!
Yuhong
"Rodney R. Fournier [MVP]" wrote:
> The latest OfficeScan is cluster aware. Don't let the silly name fool you,
> it work for both workstations and servers.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:E2E02DA9-F7B3-48C4-9846-7B0835B4A09D@.microsoft.com...
>
>
|||We use McAfee VirusScan on our clusters. It seems to be working fine. As
Rodney mentioned, do exclude the SQL Server files from scan.
Linchi
"Yuhong" wrote:
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||Thanks, Linchi! I will look into these products.
"Linchi Shea" wrote:
[vbcol=seagreen]
> We use McAfee VirusScan on our clusters. It seems to be working fine. As
> Rodney mentioned, do exclude the SQL Server files from scan.
> Linchi
> "Yuhong" wrote:
|||In addition, exclude any backup file locations from virus scan, either on
the local box or on a network share. You don't want to see what happens to
a SQL server if an AV program detects a virus in the backup data stream. It
isn't pretty.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||OK. Thanks!
Do I need to exclude the MSDTC cluster resource drive from the scan also?
"Geoff N. Hiten" wrote:
> In addition, exclude any backup file locations from virus scan, either on
> the local box or on a network share. You don't want to see what happens to
> a SQL server if an AV program detects a virus in the backup data stream. It
> isn't pretty.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
>
|||I would!
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:2CA26428-B8A6-47FB-8DEC-F031CB34F449@.microsoft.com...[vbcol=seagreen]
> OK. Thanks!
> Do I need to exclude the MSDTC cluster resource drive from the scan also?
>
> "Geoff N. Hiten" wrote:
software I should use. Any information will be greatly appreciated.
I like TrendMicro, whatever you get - exclude the MSCS and data directories,
if not the entire shared disks at a minimum.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://www.msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||I have never used TrendMicro. Should I order a specific version? will it be
aware of the cluster?
Thanks for your help!
Yuhong
"Rodney R. Fournier [MVP]" wrote:
> I like TrendMicro, whatever you get - exclude the MSCS and data directories,
> if not the entire shared disks at a minimum.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://www.msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
>
>
|||The latest OfficeScan is cluster aware. Don't let the silly name fool you,
it work for both workstations and servers.
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:E2E02DA9-F7B3-48C4-9846-7B0835B4A09D@.microsoft.com...[vbcol=seagreen]
>I have never used TrendMicro. Should I order a specific version? will it be
> aware of the cluster?
> Thanks for your help!
> Yuhong
> "Rodney R. Fournier [MVP]" wrote:
|||Great. Thanks so much for your information!
Yuhong
"Rodney R. Fournier [MVP]" wrote:
> The latest OfficeScan is cluster aware. Don't let the silly name fool you,
> it work for both workstations and servers.
> Cheers,
> Rodney R. Fournier
> MVP - Windows Server - Clustering
> http://www.nw-america.com - Clustering Website
> http://msmvps.com/clustering - Blog
> http://www.clusterhelp.com - Cluster Training
> ClusterHelp.com is a Microsoft Certified Gold Partner
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:E2E02DA9-F7B3-48C4-9846-7B0835B4A09D@.microsoft.com...
>
>
|||We use McAfee VirusScan on our clusters. It seems to be working fine. As
Rodney mentioned, do exclude the SQL Server files from scan.
Linchi
"Yuhong" wrote:
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||Thanks, Linchi! I will look into these products.
"Linchi Shea" wrote:
[vbcol=seagreen]
> We use McAfee VirusScan on our clusters. It seems to be working fine. As
> Rodney mentioned, do exclude the SQL Server files from scan.
> Linchi
> "Yuhong" wrote:
|||In addition, exclude any backup file locations from virus scan, either on
the local box or on a network share. You don't want to see what happens to
a SQL server if an AV program detects a virus in the backup data stream. It
isn't pretty.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
> Hi, I am setting up a 2 nodes sql cluster and not sure what AntiVirus
> software I should use. Any information will be greatly appreciated.
>
|||OK. Thanks!
Do I need to exclude the MSDTC cluster resource drive from the scan also?
"Geoff N. Hiten" wrote:
> In addition, exclude any backup file locations from virus scan, either on
> the local box or on a network share. You don't want to see what happens to
> a SQL server if an AV program detects a virus in the backup data stream. It
> isn't pretty.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
> news:3201F19C-E8CF-4BF1-AEEF-A5BAEB8D80F1@.microsoft.com...
>
|||I would!
Cheers,
Rodney R. Fournier
MVP - Windows Server - Clustering
http://www.nw-america.com - Clustering Website
http://msmvps.com/clustering - Blog
http://www.clusterhelp.com - Cluster Training
ClusterHelp.com is a Microsoft Certified Gold Partner
"Yuhong" <Yuhong@.discussions.microsoft.com> wrote in message
news:2CA26428-B8A6-47FB-8DEC-F031CB34F449@.microsoft.com...[vbcol=seagreen]
> OK. Thanks!
> Do I need to exclude the MSDTC cluster resource drive from the scan also?
>
> "Geoff N. Hiten" wrote:
2012年2月11日星期六
ANSI_NULLS OFF SQL2K5
Hi folks,
I'm using SQL Server 2005 SP1 and wants to know how to set to OFF the
setting ANSI_NULLS. When I create or modify a SP, SQL Server 2005 proposes
automatically the settings (global)
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
When I modify the SP cliccking on "Script store procedure as" and then
"ALTER TO" to change the setting ANSI_NULLS to OFF, it remains in OFF. The
problem is when I modify the SP cliccking on "Modify"; if the SP was modified
first changing the ANSI_NULLS setting to OFF, SSMS continues to visualize
such setting in ON. Very weird!
Any insight about how to preserve such setting in OFF is really welcome.
Thanks. Roberto.BOBNET wrote:
> Hi folks,
> I'm using SQL Server 2005 SP1 and wants to know how to set to OFF the
> setting ANSI_NULLS. When I create or modify a SP, SQL Server 2005 proposes
> automatically the settings (global)
> SET ANSI_NULLS ON
> SET QUOTED_IDENTIFIER ON
> When I modify the SP cliccking on "Script store procedure as" and then
> "ALTER TO" to change the setting ANSI_NULLS to OFF, it remains in OFF. The
> problem is when I modify the SP cliccking on "Modify"; if the SP was modified
> first changing the ANSI_NULLS setting to OFF, SSMS continues to visualize
> such setting in ON. Very weird!
> Any insight about how to preserve such setting in OFF is really welcome.
> Thanks. Roberto.
If at all possible you should avoid setting ANSI_NULLS OFF. Use the ON
setting always. The only good reason I can think of to use the OFF
setting is in order to support legacy code that you can't change. If
you do use the OFF setting then some features are not supported and
worst of all your code will be harder for others to comprehend.
Set ANSI_NULLS in the script just before the CREATE PROC statement:
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_proc]
...
You can also set Management Studio's default behaviour under Options /
Query Execution\SQL Server\ANSI. Changing this to OFF is definitely not
recommended because it will affect ALL new procs unless you remember to
specify otherwise.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
I'm using SQL Server 2005 SP1 and wants to know how to set to OFF the
setting ANSI_NULLS. When I create or modify a SP, SQL Server 2005 proposes
automatically the settings (global)
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
When I modify the SP cliccking on "Script store procedure as" and then
"ALTER TO" to change the setting ANSI_NULLS to OFF, it remains in OFF. The
problem is when I modify the SP cliccking on "Modify"; if the SP was modified
first changing the ANSI_NULLS setting to OFF, SSMS continues to visualize
such setting in ON. Very weird!
Any insight about how to preserve such setting in OFF is really welcome.
Thanks. Roberto.BOBNET wrote:
> Hi folks,
> I'm using SQL Server 2005 SP1 and wants to know how to set to OFF the
> setting ANSI_NULLS. When I create or modify a SP, SQL Server 2005 proposes
> automatically the settings (global)
> SET ANSI_NULLS ON
> SET QUOTED_IDENTIFIER ON
> When I modify the SP cliccking on "Script store procedure as" and then
> "ALTER TO" to change the setting ANSI_NULLS to OFF, it remains in OFF. The
> problem is when I modify the SP cliccking on "Modify"; if the SP was modified
> first changing the ANSI_NULLS setting to OFF, SSMS continues to visualize
> such setting in ON. Very weird!
> Any insight about how to preserve such setting in OFF is really welcome.
> Thanks. Roberto.
If at all possible you should avoid setting ANSI_NULLS OFF. Use the ON
setting always. The only good reason I can think of to use the OFF
setting is in order to support legacy code that you can't change. If
you do use the OFF setting then some features are not supported and
worst of all your code will be harder for others to comprehend.
Set ANSI_NULLS in the script just before the CREATE PROC statement:
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[usp_proc]
...
You can also set Management Studio's default behaviour under Options /
Query Execution\SQL Server\ANSI. Changing this to OFF is definitely not
recommended because it will affect ALL new procs unless you remember to
specify otherwise.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
2012年2月9日星期四
ANSI_NULL setting and null comparisons
I tried this experiment:
The database setting for ANSI_NULL is OFF. I set up two columns in a table
with null values. The following code returns my table:
SET ANSI_NULLS OFF
SELECT * FROM Exam WHERE TestNull1 = NULL
And this code does not:
SET ANSI_NULLS ON
SELECT * FROM Exam WHERE TestNull1 = NULL
But this codes won't return my table no matter what the ANSI_NULLS setting:
SELECT * FROM Exam WHERE TestNull1 = TestNull2
First question: Why doesn't the database setting determine the behavior in
the first two samples, instead of the setting I'm making in the query window?
Second question: Since both the fields are null, why aren't they considered
equal in the third sample? The documentation says:
"When OFF is specified, comparisons of non-UNICODE values to a null value
evaluate to TRUE if both values are NULL."
I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
letters, numbers, and symbols that SQL Server recognizes in the nchar,
nvarchar, and ntext data types." And I thought I got around that my defining
the two columns as char(10).Bev Kaufman,
I would suggest to move on and forget about setting it to OFF, and keep it
always set to ON. You should use IS [NOT] NULL instead, if you want you are
script to work as intended.
AMB
"Bev Kaufman" wrote:
> I tried this experiment:
> The database setting for ANSI_NULL is OFF. I set up two columns in a table
> with null values. The following code returns my table:
> SET ANSI_NULLS OFF
> SELECT * FROM Exam WHERE TestNull1 = NULL
> And this code does not:
> SET ANSI_NULLS ON
> SELECT * FROM Exam WHERE TestNull1 = NULL
> But this codes won't return my table no matter what the ANSI_NULLS setting:
> SELECT * FROM Exam WHERE TestNull1 = TestNull2
> First question: Why doesn't the database setting determine the behavior in
> the first two samples, instead of the setting I'm making in the query window?
> Second question: Since both the fields are null, why aren't they considered
> equal in the third sample? The documentation says:
> "When OFF is specified, comparisons of non-UNICODE values to a null value
> evaluate to TRUE if both values are NULL."
> I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> letters, numbers, and symbols that SQL Server recognizes in the nchar,
> nvarchar, and ntext data types." And I thought I got around that my defining
> the two columns as char(10).
>|||In real life situations, I would always use IS [NOT] NULL. I'm just trying
to understand the database option settings, and I am puzzled when the setting
in the database properties has no effect on the behavior.
"Alejandro Mesa" wrote:
> Bev Kaufman,
> I would suggest to move on and forget about setting it to OFF, and keep it
> always set to ON. You should use IS [NOT] NULL instead, if you want you are
> script to work as intended.
>
> AMB
> "Bev Kaufman" wrote:
> > I tried this experiment:
> > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > with null values. The following code returns my table:
> > SET ANSI_NULLS OFF
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > And this code does not:
> > SET ANSI_NULLS ON
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> >
> > First question: Why doesn't the database setting determine the behavior in
> > the first two samples, instead of the setting I'm making in the query window?
> >
> > Second question: Since both the fields are null, why aren't they considered
> > equal in the third sample? The documentation says:
> > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > evaluate to TRUE if both values are NULL."
> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > nvarchar, and ntext data types." And I thought I got around that my defining
> > the two columns as char(10).
> >|||According to BOL, SET ANSI_NULLS OFF is deprecated and should be avoided.
The SET ANSI_NULLS OFF option is more or less a backwards-compatibility
feature going way back, and was probably originally designed for programmers
coming from other languages who couldn't understand the concept that NULL is
not equal to NULL.
NULLs are not equal to any other value, including other NULLs. Don't try to
use them in equality or comparison expressions; instead use IS NULL and IS
NOT NULL as Alejandro suggested. This will make your code that much easier
to upgrade to later versions of SQL Server, and easier for other developers
to maintain since they'll be able to reference the standard behavior of
NULLs in your code.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:C1B0E1B0-5D1E-4D5F-BA61-5A57BFCA0528@.microsoft.com...
>I tried this experiment:
> The database setting for ANSI_NULL is OFF. I set up two columns in a
> table
> with null values. The following code returns my table:
> SET ANSI_NULLS OFF
> SELECT * FROM Exam WHERE TestNull1 = NULL
> And this code does not:
> SET ANSI_NULLS ON
> SELECT * FROM Exam WHERE TestNull1 = NULL
> But this codes won't return my table no matter what the ANSI_NULLS
> setting:
> SELECT * FROM Exam WHERE TestNull1 = TestNull2
> First question: Why doesn't the database setting determine the behavior in
> the first two samples, instead of the setting I'm making in the query
> window?
> Second question: Since both the fields are null, why aren't they
> considered
> equal in the third sample? The documentation says:
> "When OFF is specified, comparisons of non-UNICODE values to a null value
> evaluate to TRUE if both values are NULL."
> I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> letters, numbers, and symbols that SQL Server recognizes in the nchar,
> nvarchar, and ntext data types." And I thought I got around that my
> defining
> the two columns as char(10).
>|||Many of the SET options are set on a connection-wide basis, so if your
connection specifies a different value for the setting or you set the value
differently during your connection, you will not override the default
database setting for that connection. BOL tells you which settings are
server-wide, database-wide or connection-wide.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:590C3266-05DD-47BF-9CBA-607915549931@.microsoft.com...
> In real life situations, I would always use IS [NOT] NULL. I'm just
> trying
> to understand the database option settings, and I am puzzled when the
> setting
> in the database properties has no effect on the behavior.
> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the behavior
>> > in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >|||Ooops, should read "you will override the default database setting for that
connection."
"Mike C#" <xyz@.xyz.com> wrote in message
news:eTHgtnmHIHA.280@.TK2MSFTNGP03.phx.gbl...
> Many of the SET options are set on a connection-wide basis, so if your
> connection specifies a different value for the setting or you set the
> value differently during your connection, you will not override the
> default database setting for that connection. BOL tells you which
> settings are server-wide, database-wide or connection-wide.
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:590C3266-05DD-47BF-9CBA-607915549931@.microsoft.com...
>> In real life situations, I would always use IS [NOT] NULL. I'm just
>> trying
>> to understand the database option settings, and I am puzzled when the
>> setting
>> in the database properties has no effect on the behavior.
>> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the
>> > behavior in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >
>|||Bev,
For the most part you should ignore the database settings for query options.
SET options for a connection override the database options, and almost all
client interfaces set values for most of the query options including
ANSI_NULLS. So it doesn't matter what you set it to at the db level, as soon
as you open a connection with Query Analyzer or Management Studio, your
connection will set its own setting. I wrote an article about this several
years ago for SQL Server Magazine. It's very confusing to someone just
reading about database options.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:590C3266-05DD-47BF-9CBA-607915549931@.microsoft.com...
> In real life situations, I would always use IS [NOT] NULL. I'm just
> trying
> to understand the database option settings, and I am puzzled when the
> setting
> in the database properties has no effect on the behavior.
> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the behavior
>> > in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >|||Well, I think there is some inconsistency in the T-SQL behavior as Bev
described regardless what the best practices may be. Note that the ANSI_NULLS
seeting was not set by Query Analyzer or its driver, but set in the script
immediately before each SELECT.
I don't know whether this inconsistency is a feature by design or not. But
it appears to be a bug to me.
Linchi
"Alejandro Mesa" wrote:
> Bev Kaufman,
> I would suggest to move on and forget about setting it to OFF, and keep it
> always set to ON. You should use IS [NOT] NULL instead, if you want you are
> script to work as intended.
>
> AMB
> "Bev Kaufman" wrote:
> > I tried this experiment:
> > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > with null values. The following code returns my table:
> > SET ANSI_NULLS OFF
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > And this code does not:
> > SET ANSI_NULLS ON
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> >
> > First question: Why doesn't the database setting determine the behavior in
> > the first two samples, instead of the setting I'm making in the query window?
> >
> > Second question: Since both the fields are null, why aren't they considered
> > equal in the third sample? The documentation says:
> > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > evaluate to TRUE if both values are NULL."
> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > nvarchar, and ntext data types." And I thought I got around that my defining
> > the two columns as char(10).
> >|||From BOL:
"SET ANSI_NULLS ON affects a comparison only if one of the operands of the
comparison is either a variable that is NULL or a literal NULL. If both
sides of the comparison are columns or compound expressions, the setting
does not affect the comparison."
http://msdn2.microsoft.com/en-us/library/ms188048.aspx
Whether or not SET ANSI_NULLS OFF is inconsistent in its behavior is
probably a moot point, since BOL also says this:
"This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature."
Don't use SET ANSI_NULLS OFF, and inconsistent (though documented) behavior
won't be an issue.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:08DB5BC1-4051-4B12-84B4-0A5D3F6A9634@.microsoft.com...
> Well, I think there is some inconsistency in the T-SQL behavior as Bev
> described regardless what the best practices may be. Note that the
> ANSI_NULLS
> seeting was not set by Query Analyzer or its driver, but set in the script
> immediately before each SELECT.
> I don't know whether this inconsistency is a feature by design or not. But
> it appears to be a bug to me.
> Linchi
> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the behavior
>> > in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >|||Linchi Shea,
As Mike stated, the comparison should be against the literal NULL. Setting
ANSI_NULLS to OFF, can yield many weird results, that I would prefer forget
about it, instead trying to understand it. Microsoft is thinking in
deprecating most of those settings, and I guess the future behavior will be
like having them ON.
NULL puzzle by Steve Kass
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/4693f68a7140bb80/daca91a7912bdc76?q=steve+kass+and+null+puzzle&rnum=2&hl=en
AMB
"Linchi Shea" wrote:
> Well, I think there is some inconsistency in the T-SQL behavior as Bev
> described regardless what the best practices may be. Note that the ANSI_NULLS
> seeting was not set by Query Analyzer or its driver, but set in the script
> immediately before each SELECT.
> I don't know whether this inconsistency is a feature by design or not. But
> it appears to be a bug to me.
> Linchi
> "Alejandro Mesa" wrote:
> > Bev Kaufman,
> >
> > I would suggest to move on and forget about setting it to OFF, and keep it
> > always set to ON. You should use IS [NOT] NULL instead, if you want you are
> > script to work as intended.
> >
> >
> > AMB
> >
> > "Bev Kaufman" wrote:
> >
> > > I tried this experiment:
> > > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > > with null values. The following code returns my table:
> > > SET ANSI_NULLS OFF
> > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > >
> > > And this code does not:
> > > SET ANSI_NULLS ON
> > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > >
> > > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> > >
> > > First question: Why doesn't the database setting determine the behavior in
> > > the first two samples, instead of the setting I'm making in the query window?
> > >
> > > Second question: Since both the fields are null, why aren't they considered
> > > equal in the third sample? The documentation says:
> > > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > > evaluate to TRUE if both values are NULL."
> > > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > > nvarchar, and ntext data types." And I thought I got around that my defining
> > > the two columns as char(10).
> > >|||It's good that at least it's documented in BOL. I missed it. Any clue why
comparing two columns is treated differently?
Linchi
"Alejandro Mesa" wrote:
> Linchi Shea,
> As Mike stated, the comparison should be against the literal NULL. Setting
> ANSI_NULLS to OFF, can yield many weird results, that I would prefer forget
> about it, instead trying to understand it. Microsoft is thinking in
> deprecating most of those settings, and I guess the future behavior will be
> like having them ON.
> NULL puzzle by Steve Kass
> http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/4693f68a7140bb80/daca91a7912bdc76?q=steve+kass+and+null+puzzle&rnum=2&hl=en
> AMB
> "Linchi Shea" wrote:
> > Well, I think there is some inconsistency in the T-SQL behavior as Bev
> > described regardless what the best practices may be. Note that the ANSI_NULLS
> > seeting was not set by Query Analyzer or its driver, but set in the script
> > immediately before each SELECT.
> >
> > I don't know whether this inconsistency is a feature by design or not. But
> > it appears to be a bug to me.
> >
> > Linchi
> >
> > "Alejandro Mesa" wrote:
> >
> > > Bev Kaufman,
> > >
> > > I would suggest to move on and forget about setting it to OFF, and keep it
> > > always set to ON. You should use IS [NOT] NULL instead, if you want you are
> > > script to work as intended.
> > >
> > >
> > > AMB
> > >
> > > "Bev Kaufman" wrote:
> > >
> > > > I tried this experiment:
> > > > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > > > with null values. The following code returns my table:
> > > > SET ANSI_NULLS OFF
> > > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > > >
> > > > And this code does not:
> > > > SET ANSI_NULLS ON
> > > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > > >
> > > > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > > > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> > > >
> > > > First question: Why doesn't the database setting determine the behavior in
> > > > the first two samples, instead of the setting I'm making in the query window?
> > > >
> > > > Second question: Since both the fields are null, why aren't they considered
> > > > equal in the third sample? The documentation says:
> > > > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > > > evaluate to TRUE if both values are NULL."
> > > > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > > > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > > > nvarchar, and ntext data types." And I thought I got around that my defining
> > > > the two columns as char(10).
> > > >
The database setting for ANSI_NULL is OFF. I set up two columns in a table
with null values. The following code returns my table:
SET ANSI_NULLS OFF
SELECT * FROM Exam WHERE TestNull1 = NULL
And this code does not:
SET ANSI_NULLS ON
SELECT * FROM Exam WHERE TestNull1 = NULL
But this codes won't return my table no matter what the ANSI_NULLS setting:
SELECT * FROM Exam WHERE TestNull1 = TestNull2
First question: Why doesn't the database setting determine the behavior in
the first two samples, instead of the setting I'm making in the query window?
Second question: Since both the fields are null, why aren't they considered
equal in the third sample? The documentation says:
"When OFF is specified, comparisons of non-UNICODE values to a null value
evaluate to TRUE if both values are NULL."
I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
letters, numbers, and symbols that SQL Server recognizes in the nchar,
nvarchar, and ntext data types." And I thought I got around that my defining
the two columns as char(10).Bev Kaufman,
I would suggest to move on and forget about setting it to OFF, and keep it
always set to ON. You should use IS [NOT] NULL instead, if you want you are
script to work as intended.
AMB
"Bev Kaufman" wrote:
> I tried this experiment:
> The database setting for ANSI_NULL is OFF. I set up two columns in a table
> with null values. The following code returns my table:
> SET ANSI_NULLS OFF
> SELECT * FROM Exam WHERE TestNull1 = NULL
> And this code does not:
> SET ANSI_NULLS ON
> SELECT * FROM Exam WHERE TestNull1 = NULL
> But this codes won't return my table no matter what the ANSI_NULLS setting:
> SELECT * FROM Exam WHERE TestNull1 = TestNull2
> First question: Why doesn't the database setting determine the behavior in
> the first two samples, instead of the setting I'm making in the query window?
> Second question: Since both the fields are null, why aren't they considered
> equal in the third sample? The documentation says:
> "When OFF is specified, comparisons of non-UNICODE values to a null value
> evaluate to TRUE if both values are NULL."
> I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> letters, numbers, and symbols that SQL Server recognizes in the nchar,
> nvarchar, and ntext data types." And I thought I got around that my defining
> the two columns as char(10).
>|||In real life situations, I would always use IS [NOT] NULL. I'm just trying
to understand the database option settings, and I am puzzled when the setting
in the database properties has no effect on the behavior.
"Alejandro Mesa" wrote:
> Bev Kaufman,
> I would suggest to move on and forget about setting it to OFF, and keep it
> always set to ON. You should use IS [NOT] NULL instead, if you want you are
> script to work as intended.
>
> AMB
> "Bev Kaufman" wrote:
> > I tried this experiment:
> > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > with null values. The following code returns my table:
> > SET ANSI_NULLS OFF
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > And this code does not:
> > SET ANSI_NULLS ON
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> >
> > First question: Why doesn't the database setting determine the behavior in
> > the first two samples, instead of the setting I'm making in the query window?
> >
> > Second question: Since both the fields are null, why aren't they considered
> > equal in the third sample? The documentation says:
> > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > evaluate to TRUE if both values are NULL."
> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > nvarchar, and ntext data types." And I thought I got around that my defining
> > the two columns as char(10).
> >|||According to BOL, SET ANSI_NULLS OFF is deprecated and should be avoided.
The SET ANSI_NULLS OFF option is more or less a backwards-compatibility
feature going way back, and was probably originally designed for programmers
coming from other languages who couldn't understand the concept that NULL is
not equal to NULL.
NULLs are not equal to any other value, including other NULLs. Don't try to
use them in equality or comparison expressions; instead use IS NULL and IS
NOT NULL as Alejandro suggested. This will make your code that much easier
to upgrade to later versions of SQL Server, and easier for other developers
to maintain since they'll be able to reference the standard behavior of
NULLs in your code.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:C1B0E1B0-5D1E-4D5F-BA61-5A57BFCA0528@.microsoft.com...
>I tried this experiment:
> The database setting for ANSI_NULL is OFF. I set up two columns in a
> table
> with null values. The following code returns my table:
> SET ANSI_NULLS OFF
> SELECT * FROM Exam WHERE TestNull1 = NULL
> And this code does not:
> SET ANSI_NULLS ON
> SELECT * FROM Exam WHERE TestNull1 = NULL
> But this codes won't return my table no matter what the ANSI_NULLS
> setting:
> SELECT * FROM Exam WHERE TestNull1 = TestNull2
> First question: Why doesn't the database setting determine the behavior in
> the first two samples, instead of the setting I'm making in the query
> window?
> Second question: Since both the fields are null, why aren't they
> considered
> equal in the third sample? The documentation says:
> "When OFF is specified, comparisons of non-UNICODE values to a null value
> evaluate to TRUE if both values are NULL."
> I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> letters, numbers, and symbols that SQL Server recognizes in the nchar,
> nvarchar, and ntext data types." And I thought I got around that my
> defining
> the two columns as char(10).
>|||Many of the SET options are set on a connection-wide basis, so if your
connection specifies a different value for the setting or you set the value
differently during your connection, you will not override the default
database setting for that connection. BOL tells you which settings are
server-wide, database-wide or connection-wide.
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:590C3266-05DD-47BF-9CBA-607915549931@.microsoft.com...
> In real life situations, I would always use IS [NOT] NULL. I'm just
> trying
> to understand the database option settings, and I am puzzled when the
> setting
> in the database properties has no effect on the behavior.
> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the behavior
>> > in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >|||Ooops, should read "you will override the default database setting for that
connection."
"Mike C#" <xyz@.xyz.com> wrote in message
news:eTHgtnmHIHA.280@.TK2MSFTNGP03.phx.gbl...
> Many of the SET options are set on a connection-wide basis, so if your
> connection specifies a different value for the setting or you set the
> value differently during your connection, you will not override the
> default database setting for that connection. BOL tells you which
> settings are server-wide, database-wide or connection-wide.
> "Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
> news:590C3266-05DD-47BF-9CBA-607915549931@.microsoft.com...
>> In real life situations, I would always use IS [NOT] NULL. I'm just
>> trying
>> to understand the database option settings, and I am puzzled when the
>> setting
>> in the database properties has no effect on the behavior.
>> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the
>> > behavior in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >
>|||Bev,
For the most part you should ignore the database settings for query options.
SET options for a connection override the database options, and almost all
client interfaces set values for most of the query options including
ANSI_NULLS. So it doesn't matter what you set it to at the db level, as soon
as you open a connection with Query Analyzer or Management Studio, your
connection will set its own setting. I wrote an article about this several
years ago for SQL Server Magazine. It's very confusing to someone just
reading about database options.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:590C3266-05DD-47BF-9CBA-607915549931@.microsoft.com...
> In real life situations, I would always use IS [NOT] NULL. I'm just
> trying
> to understand the database option settings, and I am puzzled when the
> setting
> in the database properties has no effect on the behavior.
> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the behavior
>> > in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >|||Well, I think there is some inconsistency in the T-SQL behavior as Bev
described regardless what the best practices may be. Note that the ANSI_NULLS
seeting was not set by Query Analyzer or its driver, but set in the script
immediately before each SELECT.
I don't know whether this inconsistency is a feature by design or not. But
it appears to be a bug to me.
Linchi
"Alejandro Mesa" wrote:
> Bev Kaufman,
> I would suggest to move on and forget about setting it to OFF, and keep it
> always set to ON. You should use IS [NOT] NULL instead, if you want you are
> script to work as intended.
>
> AMB
> "Bev Kaufman" wrote:
> > I tried this experiment:
> > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > with null values. The following code returns my table:
> > SET ANSI_NULLS OFF
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > And this code does not:
> > SET ANSI_NULLS ON
> > SELECT * FROM Exam WHERE TestNull1 = NULL
> >
> > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> >
> > First question: Why doesn't the database setting determine the behavior in
> > the first two samples, instead of the setting I'm making in the query window?
> >
> > Second question: Since both the fields are null, why aren't they considered
> > equal in the third sample? The documentation says:
> > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > evaluate to TRUE if both values are NULL."
> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > nvarchar, and ntext data types." And I thought I got around that my defining
> > the two columns as char(10).
> >|||From BOL:
"SET ANSI_NULLS ON affects a comparison only if one of the operands of the
comparison is either a variable that is NULL or a literal NULL. If both
sides of the comparison are columns or compound expressions, the setting
does not affect the comparison."
http://msdn2.microsoft.com/en-us/library/ms188048.aspx
Whether or not SET ANSI_NULLS OFF is inconsistent in its behavior is
probably a moot point, since BOL also says this:
"This feature will be removed in a future version of Microsoft SQL Server.
Avoid using this feature in new development work, and plan to modify
applications that currently use this feature."
Don't use SET ANSI_NULLS OFF, and inconsistent (though documented) behavior
won't be an issue.
"Linchi Shea" <LinchiShea@.discussions.microsoft.com> wrote in message
news:08DB5BC1-4051-4B12-84B4-0A5D3F6A9634@.microsoft.com...
> Well, I think there is some inconsistency in the T-SQL behavior as Bev
> described regardless what the best practices may be. Note that the
> ANSI_NULLS
> seeting was not set by Query Analyzer or its driver, but set in the script
> immediately before each SELECT.
> I don't know whether this inconsistency is a feature by design or not. But
> it appears to be a bug to me.
> Linchi
> "Alejandro Mesa" wrote:
>> Bev Kaufman,
>> I would suggest to move on and forget about setting it to OFF, and keep
>> it
>> always set to ON. You should use IS [NOT] NULL instead, if you want you
>> are
>> script to work as intended.
>>
>> AMB
>> "Bev Kaufman" wrote:
>> > I tried this experiment:
>> > The database setting for ANSI_NULL is OFF. I set up two columns in a
>> > table
>> > with null values. The following code returns my table:
>> > SET ANSI_NULLS OFF
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > And this code does not:
>> > SET ANSI_NULLS ON
>> > SELECT * FROM Exam WHERE TestNull1 = NULL
>> >
>> > But this codes won't return my table no matter what the ANSI_NULLS
>> > setting:
>> > SELECT * FROM Exam WHERE TestNull1 = TestNull2
>> >
>> > First question: Why doesn't the database setting determine the behavior
>> > in
>> > the first two samples, instead of the setting I'm making in the query
>> > window?
>> >
>> > Second question: Since both the fields are null, why aren't they
>> > considered
>> > equal in the third sample? The documentation says:
>> > "When OFF is specified, comparisons of non-UNICODE values to a null
>> > value
>> > evaluate to TRUE if both values are NULL."
>> > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
>> > letters, numbers, and symbols that SQL Server recognizes in the nchar,
>> > nvarchar, and ntext data types." And I thought I got around that my
>> > defining
>> > the two columns as char(10).
>> >|||Linchi Shea,
As Mike stated, the comparison should be against the literal NULL. Setting
ANSI_NULLS to OFF, can yield many weird results, that I would prefer forget
about it, instead trying to understand it. Microsoft is thinking in
deprecating most of those settings, and I guess the future behavior will be
like having them ON.
NULL puzzle by Steve Kass
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/4693f68a7140bb80/daca91a7912bdc76?q=steve+kass+and+null+puzzle&rnum=2&hl=en
AMB
"Linchi Shea" wrote:
> Well, I think there is some inconsistency in the T-SQL behavior as Bev
> described regardless what the best practices may be. Note that the ANSI_NULLS
> seeting was not set by Query Analyzer or its driver, but set in the script
> immediately before each SELECT.
> I don't know whether this inconsistency is a feature by design or not. But
> it appears to be a bug to me.
> Linchi
> "Alejandro Mesa" wrote:
> > Bev Kaufman,
> >
> > I would suggest to move on and forget about setting it to OFF, and keep it
> > always set to ON. You should use IS [NOT] NULL instead, if you want you are
> > script to work as intended.
> >
> >
> > AMB
> >
> > "Bev Kaufman" wrote:
> >
> > > I tried this experiment:
> > > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > > with null values. The following code returns my table:
> > > SET ANSI_NULLS OFF
> > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > >
> > > And this code does not:
> > > SET ANSI_NULLS ON
> > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > >
> > > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> > >
> > > First question: Why doesn't the database setting determine the behavior in
> > > the first two samples, instead of the setting I'm making in the query window?
> > >
> > > Second question: Since both the fields are null, why aren't they considered
> > > equal in the third sample? The documentation says:
> > > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > > evaluate to TRUE if both values are NULL."
> > > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > > nvarchar, and ntext data types." And I thought I got around that my defining
> > > the two columns as char(10).
> > >|||It's good that at least it's documented in BOL. I missed it. Any clue why
comparing two columns is treated differently?
Linchi
"Alejandro Mesa" wrote:
> Linchi Shea,
> As Mike stated, the comparison should be against the literal NULL. Setting
> ANSI_NULLS to OFF, can yield many weird results, that I would prefer forget
> about it, instead trying to understand it. Microsoft is thinking in
> deprecating most of those settings, and I guess the future behavior will be
> like having them ON.
> NULL puzzle by Steve Kass
> http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/4693f68a7140bb80/daca91a7912bdc76?q=steve+kass+and+null+puzzle&rnum=2&hl=en
> AMB
> "Linchi Shea" wrote:
> > Well, I think there is some inconsistency in the T-SQL behavior as Bev
> > described regardless what the best practices may be. Note that the ANSI_NULLS
> > seeting was not set by Query Analyzer or its driver, but set in the script
> > immediately before each SELECT.
> >
> > I don't know whether this inconsistency is a feature by design or not. But
> > it appears to be a bug to me.
> >
> > Linchi
> >
> > "Alejandro Mesa" wrote:
> >
> > > Bev Kaufman,
> > >
> > > I would suggest to move on and forget about setting it to OFF, and keep it
> > > always set to ON. You should use IS [NOT] NULL instead, if you want you are
> > > script to work as intended.
> > >
> > >
> > > AMB
> > >
> > > "Bev Kaufman" wrote:
> > >
> > > > I tried this experiment:
> > > > The database setting for ANSI_NULL is OFF. I set up two columns in a table
> > > > with null values. The following code returns my table:
> > > > SET ANSI_NULLS OFF
> > > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > > >
> > > > And this code does not:
> > > > SET ANSI_NULLS ON
> > > > SELECT * FROM Exam WHERE TestNull1 = NULL
> > > >
> > > > But this codes won't return my table no matter what the ANSI_NULLS setting:
> > > > SELECT * FROM Exam WHERE TestNull1 = TestNull2
> > > >
> > > > First question: Why doesn't the database setting determine the behavior in
> > > > the first two samples, instead of the setting I'm making in the query window?
> > > >
> > > > Second question: Since both the fields are null, why aren't they considered
> > > > equal in the third sample? The documentation says:
> > > > "When OFF is specified, comparisons of non-UNICODE values to a null value
> > > > evaluate to TRUE if both values are NULL."
> > > > I noticed the "non-UNICODE" stipulation, but "Unicode defines a set of
> > > > letters, numbers, and symbols that SQL Server recognizes in the nchar,
> > > > nvarchar, and ntext data types." And I thought I got around that my defining
> > > > the two columns as char(10).
> > > >
ANSI Padding
How can one find out the ANSI Padding setting (i.e. ON or
OFF) of an existing table without drawing a conclusion
from playing with the data in the subject table?sp_help <table>
look at the column "trimtrailingblanks"
if trimtrailingblanks = yes --ansi padding off
if truntraukubgblanks = no --ansi padding on
--
- Vishal|||oops, spelling mistake ,
if trimtrailingblanks = yes --ansi padding off
if trimtrailingblanks = no --ansi padding on
--
- Vishal|||In addition to what Vishal said, you can use the
UsesAnsiTrim COLUMNPROPERTY. Notice that it is not a table
property, but a column property. Try:
SET ANSI_PADDING ON
create table Test (col1 varchar(10))
SET ANSI_PADDING OFF
alter table Test add col2 varchar(10)
select COLUMNPROPERTY(OBJECT_ID('Test'), 'col1',
N'UsesAnsiTrim')
select COLUMNPROPERTY(OBJECT_ID('Test'), 'col2',
N'UsesAnsiTrim')
drop table Test
HTH
Vern
>--Original Message--
>How can one find out the ANSI Padding setting (i.e. ON or
>OFF) of an existing table without drawing a conclusion
>from playing with the data in the subject table?
>.
>
OFF) of an existing table without drawing a conclusion
from playing with the data in the subject table?sp_help <table>
look at the column "trimtrailingblanks"
if trimtrailingblanks = yes --ansi padding off
if truntraukubgblanks = no --ansi padding on
--
- Vishal|||oops, spelling mistake ,
if trimtrailingblanks = yes --ansi padding off
if trimtrailingblanks = no --ansi padding on
--
- Vishal|||In addition to what Vishal said, you can use the
UsesAnsiTrim COLUMNPROPERTY. Notice that it is not a table
property, but a column property. Try:
SET ANSI_PADDING ON
create table Test (col1 varchar(10))
SET ANSI_PADDING OFF
alter table Test add col2 varchar(10)
select COLUMNPROPERTY(OBJECT_ID('Test'), 'col1',
N'UsesAnsiTrim')
select COLUMNPROPERTY(OBJECT_ID('Test'), 'col2',
N'UsesAnsiTrim')
drop table Test
HTH
Vern
>--Original Message--
>How can one find out the ANSI Padding setting (i.e. ON or
>OFF) of an existing table without drawing a conclusion
>from playing with the data in the subject table?
>.
>
ANSI Nulls SQL Server Setting
Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI null
s
since I'm using the ANSI-92 joins in my T-SQL statements?
What effect will this have on database performance with this disable?
What reason should you have that SQL Server ANSI warnings, ANSI padding,
ANSI nulls are turn off?
Please help me with these answer?
Thank You,ANSI_NULLS, ANSI_PADDING and ANSI_WARNINGS are not needed to use ANSI-92
joins.
The most important of these to consider in your queries is ANSI_NULLS, which
defines the behavior when comparing null values. But this is important even
if you are using ANSI-92 joins or not.
See 'Setting Database Options' on SQL Server 2000 BOL for more information.
Ben Nevarez
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D9C612A2-4B76-4A13-A226-D85BDC2C1422@.microsoft.com...
> Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI
> nulls
> since I'm using the ANSI-92 joins in my T-SQL statements?
> What effect will this have on database performance with this disable?
> What reason should you have that SQL Server ANSI warnings, ANSI padding,
> ANSI nulls are turn off?
> Please help me with these answer?
> Thank You,
s
since I'm using the ANSI-92 joins in my T-SQL statements?
What effect will this have on database performance with this disable?
What reason should you have that SQL Server ANSI warnings, ANSI padding,
ANSI nulls are turn off?
Please help me with these answer?
Thank You,ANSI_NULLS, ANSI_PADDING and ANSI_WARNINGS are not needed to use ANSI-92
joins.
The most important of these to consider in your queries is ANSI_NULLS, which
defines the behavior when comparing null values. But this is important even
if you are using ANSI-92 joins or not.
See 'Setting Database Options' on SQL Server 2000 BOL for more information.
Ben Nevarez
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D9C612A2-4B76-4A13-A226-D85BDC2C1422@.microsoft.com...
> Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI
> nulls
> since I'm using the ANSI-92 joins in my T-SQL statements?
> What effect will this have on database performance with this disable?
> What reason should you have that SQL Server ANSI warnings, ANSI padding,
> ANSI nulls are turn off?
> Please help me with these answer?
> Thank You,
ANSI Nulls SQL Server Setting
Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI nulls
since I'm using the ANSI-92 joins in my T-SQL statements?
What effect will this have on database performance with this disable?
What reason should you have that SQL Server ANSI warnings, ANSI padding,
ANSI nulls are turn off?
Please help me with these answer?
Thank You,
ANSI_NULLS, ANSI_PADDING and ANSI_WARNINGS are not needed to use ANSI-92
joins.
The most important of these to consider in your queries is ANSI_NULLS, which
defines the behavior when comparing null values. But this is important even
if you are using ANSI-92 joins or not.
See 'Setting Database Options' on SQL Server 2000 BOL for more information.
Ben Nevarez
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D9C612A2-4B76-4A13-A226-D85BDC2C1422@.microsoft.com...
> Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI
> nulls
> since I'm using the ANSI-92 joins in my T-SQL statements?
> What effect will this have on database performance with this disable?
> What reason should you have that SQL Server ANSI warnings, ANSI padding,
> ANSI nulls are turn off?
> Please help me with these answer?
> Thank You,
since I'm using the ANSI-92 joins in my T-SQL statements?
What effect will this have on database performance with this disable?
What reason should you have that SQL Server ANSI warnings, ANSI padding,
ANSI nulls are turn off?
Please help me with these answer?
Thank You,
ANSI_NULLS, ANSI_PADDING and ANSI_WARNINGS are not needed to use ANSI-92
joins.
The most important of these to consider in your queries is ANSI_NULLS, which
defines the behavior when comparing null values. But this is important even
if you are using ANSI-92 joins or not.
See 'Setting Database Options' on SQL Server 2000 BOL for more information.
Ben Nevarez
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D9C612A2-4B76-4A13-A226-D85BDC2C1422@.microsoft.com...
> Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI
> nulls
> since I'm using the ANSI-92 joins in my T-SQL statements?
> What effect will this have on database performance with this disable?
> What reason should you have that SQL Server ANSI warnings, ANSI padding,
> ANSI nulls are turn off?
> Please help me with these answer?
> Thank You,
ANSI Nulls SQL Server Setting
Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI nulls
since I'm using the ANSI-92 joins in my T-SQL statements?
What effect will this have on database performance with this disable?
What reason should you have that SQL Server ANSI warnings, ANSI padding,
ANSI nulls are turn off?
Please help me with these answer?
Thank You,ANSI_NULLS, ANSI_PADDING and ANSI_WARNINGS are not needed to use ANSI-92
joins.
The most important of these to consider in your queries is ANSI_NULLS, which
defines the behavior when comparing null values. But this is important even
if you are using ANSI-92 joins or not.
See 'Setting Database Options' on SQL Server 2000 BOL for more information.
Ben Nevarez
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D9C612A2-4B76-4A13-A226-D85BDC2C1422@.microsoft.com...
> Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI
> nulls
> since I'm using the ANSI-92 joins in my T-SQL statements?
> What effect will this have on database performance with this disable?
> What reason should you have that SQL Server ANSI warnings, ANSI padding,
> ANSI nulls are turn off?
> Please help me with these answer?
> Thank You,
since I'm using the ANSI-92 joins in my T-SQL statements?
What effect will this have on database performance with this disable?
What reason should you have that SQL Server ANSI warnings, ANSI padding,
ANSI nulls are turn off?
Please help me with these answer?
Thank You,ANSI_NULLS, ANSI_PADDING and ANSI_WARNINGS are not needed to use ANSI-92
joins.
The most important of these to consider in your queries is ANSI_NULLS, which
defines the behavior when comparing null values. But this is important even
if you are using ANSI-92 joins or not.
See 'Setting Database Options' on SQL Server 2000 BOL for more information.
Ben Nevarez
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:D9C612A2-4B76-4A13-A226-D85BDC2C1422@.microsoft.com...
> Should I be using the SQL Server 2000 ANSI warnings, ANSI padding, ANSI
> nulls
> since I'm using the ANSI-92 joins in my T-SQL statements?
> What effect will this have on database performance with this disable?
> What reason should you have that SQL Server ANSI warnings, ANSI padding,
> ANSI nulls are turn off?
> Please help me with these answer?
> Thank You,
ANSI NULLS setting in 6.5
How can I find the current ANSI_NULLS setting in 6.5?
thanksThere no such setting in 6.5 at the server level. This is something you set
in the client app, using the SET command. (In fact, the setting at the
database level in 7.0 and 2000 is mostly useless as the client will override
anyhow, and some API's and tools will set this regardless whether the
developer want to or not.)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"peter feakins" <anonymous@.discussions.microsoft.com> wrote in message
news:92E1AC2B-3162-43AF-A04B-71CD6533CB37@.microsoft.com...
> How can I find the current ANSI_NULLS setting in 6.5?
> thanks
thanksThere no such setting in 6.5 at the server level. This is something you set
in the client app, using the SET command. (In fact, the setting at the
database level in 7.0 and 2000 is mostly useless as the client will override
anyhow, and some API's and tools will set this regardless whether the
developer want to or not.)
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"peter feakins" <anonymous@.discussions.microsoft.com> wrote in message
news:92E1AC2B-3162-43AF-A04B-71CD6533CB37@.microsoft.com...
> How can I find the current ANSI_NULLS setting in 6.5?
> thanks
订阅：
博文 (Atom)