0

Is that bug in SQL server or I don't know something?

I have table "Items" of structure:

Id - (PRIMARY)  int Auto Increment
FinalStatus - tinyint (NULLABLE)

With data it looks like:

Id Status
1 NULL
2 NULL
3 NULL
4 1
5 2

I'm trying to list all records that their Status is different than 2:

SELECT * FROM [Items] WHERE [Status] != '2'

SELECT * FROM [Items] WHERE [Status] != 2

SELECT * FROM [Items] WHERE [Status] NOT LIKE '2'

SELECT * FROM [Items] WHERE [Status] NOT IN (2)

All these queries returns only one row:

Id Status
4 1

However if I use query:

SELECT * FROM [Items] WHERE [Status] IS NULL

In that case I get all NULL rows displayed - but only them.

Since NULL is not the same as 2 or any other number, I feel confused with the result of the queries. However there is difference how I designed this table and all previous. This time I've forgot to allow nulls on creation, and I've used SQL studio to do it afterwards. IMHO this is the reason of the trouble, but I have no idea what exactly it can be. I could delete the table and recreate it, but would like to learn something. Thanks for all kind help.

Amikot40
  • 38
  • 7
  • For performance reasons, do not use any solution involving `ISNULL` or `CASE`. The best option is either `WHERE (Status != 2 OR Status IS NULL)` or the even better is is `WHERE NOT EXISTS (SELECT Status INTERSECT SELECT 2)` – Charlieface Mar 09 '22 at 21:45

2 Answers2

2

Is that bug in SQL server or I don't know something

Well, it's not a bug.

Think of NULL as a placeholder for "Unknown" and it will be clearer.

If I ask you to find me all the rows where the value is not 2 then you cannot return any NULL (unknown) value since you do not know that it is NOT 2.

If you want to include NULLs then the criteria should be

where value != 2 or value is null;
Stu
  • 30,392
  • 6
  • 14
  • 33
  • NULL means value don't exist - empty cell. Unknown means that value is defined but unknown. But Okay - if it's like that in Microsoft world - Okay. However I've created probably tens or maybe even hundreds tables with nullable fields without SQL studio and never had this problem. So why my previous tables with NULLs worked and work different way on the same server? – Amikot40 Mar 09 '22 at 17:55
  • 1
    This behaviour is standard Ansi SQL, it's nothing to do with **SQL Server Management Studio**. Every RDBMS will behave in the same way afaik, you can test that with [a DB Fiddle](https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=61efb2d5d7d796cb278e932153a25c93) Also FYI this is nothing to do with `ansi_nulls` setting. – Stu Mar 09 '22 at 18:03
  • Stu: So explain me, how database that contain tens of schemas and hundreds of tables suddenly behave different way then before and only to newly created table? If it's as you say - I'm not sure if this is about ANSI_NULLS which is global setting for whole database. – Amikot40 Mar 09 '22 at 18:15
  • Sorry I have no idea what you mean by *suddenly behave different way* - *before*... what?. Perhaps you can create a DBFiddle to demonstrate your expected behaviour, either in SQL Server or any RDBMS? – Stu Mar 09 '22 at 18:27
  • I think I've just gone mad. I've checked other tables with nulls and they behave the same way - so with ANSI_NULLS=ON, I've even checked on backup server and the same. So I must be mad. Now I've checked on the PostgreSQL server that I have and it's exactly the same. I need a break :) – Amikot40 Mar 09 '22 at 18:35
  • Note in the answer you accepted, you are better off doing what I put in my answer. The reason is that using *isnull* function in the *where clause* is *unsargable* and SQL Server will be forced to do a complete index/table scan; using the *or is null* criteria will allow an index seek. – Stu Mar 09 '22 at 21:51
-1

Use this query SELECT * FROM [Items] WHERE ISNULL([Status], 0) != 2

ilyazakharov
  • 288
  • 1
  • 12
  • This solves the problem, but is not an answer for question: what happen to the table that it behave deferent way and how to fix it :) – Amikot40 Mar 09 '22 at 17:44
  • It is ANSI Standart for NULLs. You can read documentation about it( e.g. ANSI_NULLS) – ilyazakharov Mar 09 '22 at 17:48
  • Okay - I've read about ANSI_NULLS in T-SQL and now I understand why it happen, but the question is if before I never had ANSI_NULLS switched ON and now suddenly new table is created and works on ANSI_NULL basis - is the SQL studio changing server settings without permission? or it is local setting for queries related to that table ? If so, where to change it ? – Amikot40 Mar 09 '22 at 18:01
  • You can set ANSI_NULLS in the db properties, you can use SET ANSI_NULLS in queries. The behaviuor you had is a common behaviour in RDBS. Whatdid you expect to see in your queries? – ilyazakharov Mar 09 '22 at 18:23
  • I have two instances of SQL server 2016 running (one is copy of another done 2 weeks ago) and both are having ANSI_NULLS=ON - so there is no way something has changed recently. I would expect also glitches in the apps that are executing queries. In that case it must be me getting mad. That's Okay - just was hoping I'm not mental and there is kind of setting per table. Thanks :) – Amikot40 Mar 09 '22 at 18:30
  • 1
    It’s not good practice to wrap a predicate in a function. It prevents indexes from being used, and causes table scans. This is probably why you’ve received a down vote on this answer. – Brendan McCaffrey Mar 09 '22 at 21:55