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.
John A. Moskal II said:not a single one constituent in our database has a NULL value in that column.
The T_CUSTOMER table definition for that column is
[inactive] [int] NOT NULL,
...so I think it's safe to assume that there will be no nulls there.
I see that C_CUSTOMER allows nulls in inactive, which fits with my memories, i.e. I don't think we've had nulls in that column since we converted our data back in 2008.
Little old me did not bother to check the table definition, though that would have been the solid move. I suppose that pretty much ends the discussion then.
I had hoped to run some analysis on our customers with null values to get a better sense of their age and recency of being touched, but now they're all gone. Anyway, that's my question answered, if obliquely by Tessitura elves.
Tessitura Elf, artist's impression