January 12, 2022 at 2:09 pm
I'm rewritting some data warehouse load procs. The current process is filled with isnull(name,'n/a') on the select that is inserting. They are wrapping all the values in the insert with isulls and setting to 'n/a' if null. Is this faster than simply having a default value set to 'n/a' on the table that we are inserting into?
I was under the impression that the isnull function affects sargability in a negative way. Based on my research so far isull appears to only affect sargability when it's used in a contraint.
January 12, 2022 at 2:47 pm
ISNULL will affect performance if the ISNULL is being used in WHERE, ON or HAVING. As part of the SELECT or VALUES listing of columns, ISNULL won't be a big performance hit.
It might have a small amount. Could be an interesting test there.
Anyway, I'd lean towards setting default values instead just because it'll make the code so much easier and much more clear. Anything that lets me be lazy is good.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 12, 2022 at 2:48 pm
Function in a select won't affect sargability. It would if it were being used in a where clause or join.
I doubt that you'd notice a difference in speed. Code would probably be more concise/readable if you just used a default, and would handle any ad-hoc cases if those ever occur. Using the default might make the logic less visible to others, but defaults are a pretty common tool.
January 12, 2022 at 4:06 pm
You shouldn't use 'n/a' as a "value" in place of NULL. Effectively you're corrupting the data. You should just use NULL itself instead.
If all readers of the table need to see 'n/a' rather than NULL, use a view to do that.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 12, 2022 at 4:15 pm
If you include the column in the insert and don't specify the default keyword - then the value will be inserted as you sent it. That is - if the input value is a NULL value and the column allows nulls, then it will insert the null regardless of the default defined for the column.
The only way to get a default value is to either use the default keyword - or not include the column in the insert statement.
Drop Table If Exists #tempDefault;
Create Table #tempDefault (
MyID int Identity(1,1) Primary Key
, FirstColumn varchar(10) Not Null
, SecondColumn varchar(10) Null Default('NA')
);
Declare @myNull varchar(10) = Null;
Insert Into #tempDefault (FirstColumn, SecondColumn)
Values ('Value1', Default)
, ('Value2', isnull(@myNull, 'NA'))
, ('Value3', @myNull);
Insert Into #tempDefault (FirstColumn)
Values ('Value4'), ('Value5')
Select *
From #tempDefault td;
*** even the BBCodes aren't working as expected ***
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply