August 23, 2013 at 4:24 am
declare @t table (id int identity, name varchar(10))
insert into @t
select 'Bhuv'
union
select null
union
select 'Check'
select * from @t where name <> 'Check'
Why select not ginving NULL related reocrd , i am expecting two records here
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
August 23, 2013 at 4:29 am
Because NULL means "unknown" and therefore "NULL not equal to 'Check' " can't equate to True.
John
August 23, 2013 at 4:43 am
The only comparison with NULL that can return TRUE is the IS NULL/IS NOT NULL comparison.
'x' = NULL returns UNKNOWN, not TRUE or FALSE
'x'<> NULL returns UNKNOWN, not TRUE or FALSE
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2013 at 4:49 am
To get the NULL value in your results, you will need to change your WHERE clause to
WHERE name <> 'Check' OR name IS NULL
Or you need to rewrite your WHERE clause to:
WHERE COALESCE(name, '') <> 'Check'
This last WHERE clause is not sargable and cannot uake advantage of any indexes placed on the [name] column. That's because the column is put inside a function.
August 23, 2013 at 9:19 am
There is another, more cryptic way, if you wish:
select * from @t where case when name = 'Check' then 1 else 0 end = 0
:hehe:
August 23, 2013 at 9:29 am
Bhuvnesh (8/23/2013)
declare @t table (id int identity, name varchar(10))insert into @t
select 'Bhuv'
union
select null
union
select 'Check'
select * from @t where name <> 'Check'
Why select not ginving NULL related reocrd , i am expecting two records here
try
select * from @t where isnull(name,'') <> 'Check'
August 23, 2013 at 9:40 am
GilaMonster (8/23/2013)
The only comparison with NULL that can return TRUE is the IS NULL/IS NOT NULL comparison.'x' = NULL returns UNKNOWN, not TRUE or FALSE
'x'<> NULL returns UNKNOWN, not TRUE or FALSE
SET ANSI_NULLS OFF;
IF 'x' <> NULL PRINT 'Well, it is known, sometimes... '
August 23, 2013 at 9:44 am
Eugene Elutin (8/23/2013)
SET ANSI_NULLS OFF;
IF 'x' <> NULL PRINT 'Well, it is known, sometimes... '
In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 23, 2013 at 9:57 am
GilaMonster (8/23/2013)
Eugene Elutin (8/23/2013)
SET ANSI_NULLS OFF;
IF 'x' <> NULL PRINT 'Well, it is known, sometimes... '
In a future version of SQL Server, ANSI_NULLS will always be ON and any applications that explicitly set the option to OFF will generate an error. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.
Lost keys from my time machine...
Agree completely, but who knows what else will be changed in a near or far future? MS could buy Oracle and rename SQL Server to "Pythia Server".:w00t:
August 24, 2013 at 3:34 am
In addition to Gail's remark, keep in mind that there is plenty of functionality in SQL Server that requires ANSI_NULLS to be ON. SET ANSI_NULLS OFF is a true legacy option for system that no one ever touches.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 24, 2013 at 9:25 pm
We have to be a little careful here. The default for the setting at the database connection level is OFF and the default setting for connections within SSMS is ON. The fact that MS is going to take the option away with a setting to ON (always), really has some front end developers up in arms because they like to use NULL = NULL comparisons.
Personally, I like it to be ON because that's what I'm used to and I find great utility in being able to do things like find all rows that are NOT BLANK and NOT NULL simply by using something like WHERE SomeCharColumn > ''. That notwithstanding, I'd much rather see MS spend time on making useful changes instead of removing yet another useful feature (to some others) even if it's an option I don't use.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2013 at 2:20 am
Jeff Moden (8/24/2013)
We have to be a little careful here. The default for the setting at the database connection level is OFF and the default setting for connections within SSMS is ON. The fact that MS is going to take the option away with a setting to ON (always), really has some front end developers up in arms because they like to use NULL = NULL comparisons.
ANSI_NULLS is ON by default, period!
...unless you connect from DB-Library or a very old ODBC driver. In that case, the default is OFF.
Yes, there is a database setting ANSI_NULLS (as well as a few more ANSI-related database options) and this setting is OFF by default. But this setting only matters if you connect from a legacy client. That is, if you set this database option ON and then connect from DB-Library, you will find your session has ANSI_NULLS ON.
That notwithstanding, I'd much rather see MS spend time on making useful changes instead of removing yet another useful feature (to some others) even if it's an option I don't use.
It will be difficult for Microsoft to remove the option for backwards compatibility reasons. After all, there are still lots of old systems running that no one dares to touch out there. Nevertheless, I would really like to see all these settings go away, because they mainly serve to make the product more confusing for the users (and more difficult to test for Microsoft).
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 25, 2013 at 11:02 am
Erland Sommarskog (8/25/2013)
Jeff Moden (8/24/2013)
We have to be a little careful here. The default for the setting at the database connection level is OFF and the default setting for connections within SSMS is ON. The fact that MS is going to take the option away with a setting to ON (always), really has some front end developers up in arms because they like to use NULL = NULL comparisons.ANSI_NULLS is ON by default, period!
...unless you connect from DB-Library or a very old ODBC driver. In that case, the default is OFF.
Yes, there is a database setting ANSI_NULLS (as well as a few more ANSI-related database options) and this setting is OFF by default. But this setting only matters if you connect from a legacy client. That is, if you set this database option ON and then connect from DB-Library, you will find your session has ANSI_NULLS ON.
That notwithstanding, I'd much rather see MS spend time on making useful changes instead of removing yet another useful feature (to some others) even if it's an option I don't use.
It will be difficult for Microsoft to remove the option for backwards compatibility reasons. After all, there are still lots of old systems running that no one dares to touch out there. Nevertheless, I would really like to see all these settings go away, because they mainly serve to make the product more confusing for the users (and more difficult to test for Microsoft).
I have to disagree. Right click on the properties for an instance and look at the "Default Connection Option" under "Connections". If you haven't previously messed with it, none of the options are set to on. SSMS has its own settings (Tools, Options) that default to having it (and "concatenate null yields null" which is another tizzy the front-enders are having) on. What ODBC does or doesn't do to the settings isn't the point, semantically speaking. The settings currently default to off and MS is going to take that away. As you say, it WILL affect legacy systems and it WILL require some severe code changes if they actually do make the change.
So far as MS goes, I agree... it's going to be difficult for MS to make it (and other settings) set to permanently on. None the less, the last time I looked (which was a admittedly a couple of months ago), such permanent settings changes are scheduled for "a future version".
I disagree on not having the option. It's been there forever, a lot of people do use the option (sometimes on-the-fly even in T-SQL code) and MS should have standard automated tests to make sure that it still works after changes they make to the product. Although I don't turn the settings off, I also think most users are more confused by the fact that, with the default settings, even NULL <> NULL isn't true.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 25, 2013 at 2:09 pm
Jeff Moden (8/25/2013)
I have to disagree. Right click on the properties for an instance and look at the "Default Connection Option" under "Connections". If you haven't previously messed with it, none of the options are set to on. SSMS has its own settings (Tools, Options) that default to having it (and "concatenate null yields null" which is another tizzy the front-enders are having) on. What ODBC does or doesn't do to the settings isn't the point, semantically speaking.
Again, these settings - the database options and the configuration option - applies only to clients that connect with TDS 4.2. If a client connects with TDS 7.x - and all modern client API does - SQL Server will set ANSI_NULLS, ANSI_WARNINGS etc on for these clients, entirely database and configuration options.
There are some contexts where QUOTED_IDENTIFIER is OFF by default - SQLCMD, OSQL, BCP and Agent - but these tools issues an explicit SET command and will not honor the database setting QUOTED_IDENTIFIER.
I disagree on not having the option. It's been there forever
The COMPUTE BY clause had also been in a product for a long time - as long as I have worked with it. Nevertheless, Microsoft pulled it SQL 2012, like they pulled *= and =*. There is only reason to keep bad non-standard options: too many systems would suffer if the option goes away. But there is no reason to mention the option as an alterative for new users.
I also think most users are more confused by the fact that, with the default settings, even NULL <> NULL isn't true.
That's true, but
1) That is something they learn quickly.
2) That problem applies to all modern RDBMS, it is not specific to SQL Server.
Whereas ANSI_NULLS OFF can trip users in much more subtle ways. There are still people suffering from stored procedures not using indexed views because the procedures were created from Enterprise Manager in SQL 2000. And if you script a DDL trigger in SSMS, you will see that emits SET ANSI_NULLS OFF at the end. Not even people within Microsoft understands these options correctly.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
August 25, 2013 at 2:40 pm
Understood but, as you said, users can learn. I view it as another option scheduled for removal, possibly only for the convenience of MS, and I like to have options no matter how infrequently I may use them.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply