How To Write SQL Server Queries Correctly: IN and NOT IN

[@exupload93]

Thank you so much for the stream of videos as each of it is a gem of its own. Anyone finding these videos anywhere through their MSSQL journey would find themselves in a far better position to understand what works under the hood of the Sql server engine. I hope more people find this channel and I'm definitely going to make this channel a must watch for anyone who wants to get sufficiently better at understanding how to write better quality queries. If i need any expert advice, i know who to connect with 🤣

[@ErikDarlingData]

Thanks for the endorsement 😃

[@SQLDevDBA]

11:33 NTEXT or XML is safest for all columns. Message brought to you by Storage Vendors.

[@ErikDarlingData]

Pure-ly coincidental.

[@SQLDevDBA]

@@ErikDarlingData wooooooah I didn’t have my helmet on for that. 🤣

[@coding3438]

It’s called 3VL. Three valued logic!

[@ErikDarlingData]

Rolls right off the tongue, like the German word for squirrel.

At least explicitly creating temp tables with a proper primary key solves so many headaches (as in the stuff you will join it on). And if you make a mistake in the joins or do a union all instead of union filling the temp table you get an error that tells you exactly what is wrong instead of multiplying all the data... But what I mostly see people do is: select into and then create index on the filled table and then only read from it once. So I get why people are told to just not do it also....

[@ErikDarlingData]

There are many upsides to that approach. Fully parallel insert and index created with full scan statistics to name my favorites.

@@ErikDarlingData Right, that nonsense... I think that it can be faster to drop an index, then insert then re-create the index, then just doing an insert is just so stupid i refused to remember it. But usually you get the same benefit without re-sorting that much.

[@ErikDarlingData]

The sorting can be painful. It would be nice if indexing temp tables had some shortcuts.

[@FlaggedStar]

I totally understand picking NOT EXISTS instead of NOT IN, but I haven't seen any convincing argument for using EXISTS instead of IN.

[@ErikDarlingData]

There isn’t much of one generally, unless you are playing additional tricks with the IN clause.

[@SQLDevDBA]

…Mom… 😂