So, I've picked up a habit from reverse-engineering List Manager queries of always adding the condition of
ISNULL(c.inactive, 1) = 1
However, scalar functions are a performance disaster on large sets, particularly I imagine when in outside of a select.(with a select I only need to run the function on each returned row, but in a where presumably I have to run it on every row in the table, and T_CUSTOMER is a big table).
Does anyone know if this null condition is a historical thing, and customer records are no longer going to ever have that inactive value? That is, if I updated all rows where inactive is null to 1, could I safely forego using ISNULL on my future queries without worry of a null value slipping back in?
I do not know the answer to your question, but honestly, I have been wondering the same thing myself lately, because I have done the same thing as you in reverse engineering things. We went LIVE with Tessitura in 2010, and not a single one constituent in our database has a NULL value in that column. And no extra back end things were done to make it that way (unless a Tessitura staff member did it themselves during an upgrade or something like that since we are on RAMP). And I would know, I am the only staff member who has or has ever had access to the database.
That's interesting. We have quite a few, but we've been around since 2004 and...
Woah. I swear that the other day when I was looking at this we had tens of thousands of records without inactive set. Now when I query I get none. Crazier: when I look at "Show Query" now, it isn't using the ISNULL(a.inactive, 1) = 1; it just does a.inactive = 1.
Someone at RAMP is screwing with me.
Have you upgraded to V14 yet? We are still on V12.5.1, and I just double-checked the queries that come up with standard List Manager elements, and it still gives me the ISNULL(a.inactive, 1) = 1.
Maybe this IS an upgrade thing; a V14 upgrade thing. Either way... that would seem to imply that you are correct and the ISNULL() should be able to be ditched at this point, no? I suppose a simple confirmation from a Tessitura staff member would not go amiss, but in any case, we are probably pretty safe here.
We upgraded to v14 way back last year in October or November, and to 14.1 about a month ago. I promise I was looking at all this after that. I promise!