Is there any situation where setting ansi_nulls off is a good idea?

bitbucket-25253 (3/5/2010)

---

May I point out that the SSC community includes members from countries world...

I think it's sleeping on top of the duck 🙁

I'm not sure. But I do know that one of its legs is...

Lynn Pettis (3/3/2010)

---

Second, it doesn't matter how many rows are in the table tblUsers (unless it only contained one row, but everyone so far has assumed more than one), this...

Well it seems a reasonable assumption that tblUsers exists, and is a table.

You then have to guess about data volumes -...

It's an image rather than text, presumably...

Marry Krissmess (2/5/2010)

---

In programming languages this may be treated as boolean but here it should be a numeric range and any thing else than 0 or 1 should throw an...

Jamie Longstreet-481950 (2/6/2010)

---

it makes far more sense (to me) that an "exists" functionality will provide a faster and more efficient query than one searching the index for a value (IN...

Interesting question, thanks 🙂

I got it right, but largely down to guesswork - my initial thought was 0, but the name of the temp table "#funny_test" made me expected something,...

Bob Cullen-434885 (1/28/2010)

---

Ah! So a BOOLEAN type, then. That's different, and if so should also require that the language provides proper TRUE and FALSE keywords, rather than hijacking numeric values...

Here's a slightly better reference

http://msdn.microsoft.com/en-us/library/ms191530(SQL.90).aspx#_bit

"Converting to bit promotes any nonzero value to 1"

"if a number larger than one is used, it is converted to one."

I thought the actual explanation was that anything other than 0 (false) is treated as true and hence...

Hugo Kornelis (1/27/2010)

---

the strangely popular but really rather odd EXISTS 1 instead of EXISTS * (EXISTS checks for rows, not values, so what you put there is immaterial - except...

I recently optimised a query and tried exactly these two constructs. The query plan for both was identical.

So the correct answer is either "they are equal", or (more likely I...