NOT LIKE Alternatives in WHERE clause

Question

NOT LIKE Alternatives in WHERE clause

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Dennis Jensen SSChasing Mays Points: 604 More actions · Answer 1

Jeff Moden wrote:

Heh... they really don't want this to be fast, do they? 😉
Instead of TempDB, how about a very small database in the SIMPLE Recovery Model that requires no backups nor any index maintenance to contain the two small tables?
If the answer is also "No" there, then I'm afraid that the answer will be "Sorry... until {they} allow for a bit a change, {they'll} need to live with the slowness that {they've} built into this table". And, to be sure... that's not directed at you. It's directed at "them/they", whoever they are that are providing such restrictions.
So far, this is the equivalent of someone pouring sugar into a car's gas tank and saying "make the car run fast" when almost everyone else knows that they shouldn't even start the car because of the damage that will be caused. 😀

Oh dang, and here I was looking for some enlightening magic to transpire that could transform a very bad idea into at least a workable idea.

Someone should tell them that there databases are extremely horribly designed and they should let you work at fixing that or at least get someone competent to facilitate quality changes. I would have no issues with providing free consultation to get this moving in the right direction.

BOR15K SSCertifiable Points: 5894 More actions · Answer 2

BOR15K

SSCertifiable

Points: 5894

May 31, 2023 at 7:31 am

#4201269

Thank you, Jeff.

Much appreciated.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

BOR15K wrote:

Thank you, Jeff.
Much appreciated.

I just remembered a bit of trick code using "Cascading CTEs" that I did about a million years ago for the exact opposite of what you're trying to do. If I use it as a "core" lookup, it might just work a bit more quickly that what you've been seeing in your attempts. I can't do it tonight because I have a deadline that I'm working on and I need to setup a large test.

It may not work but the answer is always "No" unless we try. 😀

In the mean time, can you provide a list of, say, 10 reason codes that you good folks actually use?

p.s. Although it won't help because of all the other restrictions, using a pipe delimiter to separate the reasons can help for other things that will likely crop up.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 4

SELECT rp.* 
FROM returned_products rp
WHERE rp.returned_products_reason NOT LIKE '%reason_[2-3]%'
;

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 5

That's a great idea, Jonathan. Unfortunately and as the OP revealed in the post at the link below, the reasons aren't quite that simple.

https://www.sqlservercentral.com/forums/topic/not-like-alternatives-in-where-clause#post-4200971

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)