[MSSQL] = null <> is null

[siroki]

Hoi,

Stel je hebt de volgende stored procedure:

------------------------------------
create procedure spTest

@langID int

as

select * from tabel where langID = @langID
-----------------------------------

Op zich heel simpel. Vanuit de applicatie stuur ik een ID mee, en dan geeft de stored procedure netjes een record terug. Edoch! LangID kan ook NULL zijn! Als ik een lege waarde naar @langID stuur dan zou hij dat in de stored procedure omzetten naar:

select * from tabel where langID = '';

Logisch dat je dan niets terug krijgt, want '' is niet gelijk aan NULL.

Wat ik dan had bedacht is deze regel in de stored procedure erbij te zetten:

if(@langID = '') set @langID = null

Je zou zeggen dat dat dan goed komt. Helaas krijg ik dan ook geen regels terug.

Ik kan natuurlijk de query in de stored procedure zodanig aanpassen dat ik voor lege waardes en niet lege waardes twee queries uit ga voeren, maar dit is natuurlijk een simpel voorbeeld. Mijn applicatie heeft honderden stored procedures die bijna allemaal van LangID gebruik maken.

Iemand een idee hoe ik dit kan oplossen?

Ik wil dus met 1 query regels met een gevulde als een null langID kunnen ophalen.

Bedankt alvast.

[DamadmOO]

Om te kijken of een waarde in een veld NULL is moet je gebruik maken van de IS NULL expressie in je query.

Dus het moet dan worden:
SELECT * FROM tabel WHERE langID IS NULL

[Snake]

Set de default waarde op

code:

''

, en controlleer op

code:

var = ''

[P_de_B]

NULL is per definitie ongelijk aan NULL.

code:

WHERE ISNULL(langId, "") = ""

Dit vervangt alle waardes NULL in <langId> door "". Nog beter zou zijn om gewoon "" niet toe te staan als waarde voor 'onbekend'....

[Niemand_Anders]

Quickfix: maak LangID non-nullable en zet als default value '-1'. Vervolgens kun je leeg ('') gelijkstellen aan -1 en werkt je query wel. Ik ben er wel vanuit gegaan dat LangID normaal alleen positief is.

[.oisyn]

@Niemand_Anders: ja, handig, laten we een speciale database feature om dingen als ongedefinieerd te kunnen markeren met perfect gedefinieerde tristate logic omzeilen door zelf maar wat te prutsen middels een arbitraire waarde die staat voor ongedefinieerd . Bovendien gaat dat al niet werken als er een fatsoenlijke foreign key constraint op die landID zit (aangenomen dat het een id is die naar een andere tabel wijst). En de eerste die voorstelt om dan ook maar een dummy rij in de Lang tabel te zetten met id=-1 verdient een bitch-slap

SQL:

select * from tabel where langID = @langID OR (@langID = '' AND langID IS NULL)

.edit:
Damn you Bosmonster... *slap*

[ Voor 15% gewijzigd door .oisyn op 11-02-2008 12:32 ]

[Bosmonster]

.oisyn schreef op maandag 11 februari 2008 @ 12:07:
@Niemand_Anders: ja, handig, laten we een speciale database feature om dingen als ongedefinieerd te kunnen markeren met perfect gedefinieerde tristate logic omzeilen door zelf maar wat te prutsen middels een arbitraire waarde die staat voor ongedefinieerd . Bovendien gaat dat al niet werken als er een fatsoenlijke foreign key constraint op die landID zit (aangenomen dat het een id is die naar een andere tabel wijst). En de eerste die voorstelt om dan ook maar een dummy rij in de Lang tabel te zetten met id=-1 verdient een bitch-slap

SQL:

select * from tabel where langID = @langID OR (@langID = '' AND langID IS NULL)

Ik zou denk ik toch een dummy rij in de Lang tabel zetten met id=-1

[justmental]

.oisyn schreef op maandag 11 februari 2008 @ 12:07:
En de eerste die voorstelt om dan ook maar een dummy rij in de Lang tabel te zetten met id=-1 verdient een bitch-slap

SQL:

select * from tabel where langID = @langID OR (@langID = '' AND langID IS NULL)

Zo heb ik er ook eentje:
De eerste die voorstelt 2 functionaliteiten in 1 query te vatten met 'OR parameter = constante' verdient een bitch-slap
Een database maakt per query 1 executieplan, door die switch moet hij uitgaan van beide mogelijkheden en een plan verzinnen die beide kan, wat hij dan toepast op alle parameter waarden.
Als je bijvoorbeeld een index hebt op langID dan zal deze nooit gebruikt kunnen worden omdat de query parser niet weet wat er in de binds staat.
_{Tenzij mssql een soort bind peeking hanteert bij elke executie en vervolgens het plan aanpast, maar dat lijkt me sterk}

[LuCarD]

code:

create procedure spTest

@langID int 

as
if @langID = "" 
 select * from tabel where langID is null
else
 select * from tabel where langID = @langID

zoiets?

of

code:

create procedure spTest

@langID int = NULL

as
if @langID is NULL
 select * from tabel where langID is null
else
 select * from tabel where langID = @langID

[ Voor 33% gewijzigd door LuCarD op 11-02-2008 12:48 ]

[EfBe]

Altijd 'IF' statements vermijden in queries als je kunt, want een IF is in de regel de oorzaak van een recompile per executie.

Wat je moet gebruiken is COALESCE. In je voorbeeld geef je aan '' als leeg, wat onzin is, want je LangID is van het type int. Je moet dus een magic value verzinnen die 'leeg' representeert. Aangezien LangID waarschijnlijk een FK is, is LangID altijd positief neem ik aan. Dus als je NULL meegeeft aan de proc, je dus ook alleen de regels terugkrijgt die GEEN LangID value hebben:

select * from table where COALESCE(LangID, -1) == COALESCE(@LangID, -1)

[.oisyn]

justmental schreef op maandag 11 februari 2008 @ 12:40:
De eerste die voorstelt 2 functionaliteiten in 1 query te vatten met 'OR parameter = constante' verdient een bitch-slap

Nou nou, ik vind een fout datamodel wat zorgwekkender dan een ongeoptimaliseerde query

Als je bijvoorbeeld een index hebt op langID dan zal deze nooit gebruikt kunnen worden omdat de query parser niet weet wat er in de binds staat.

Nou ben ik geen db guru, maar ik zie niet in waarom er bij een dergelijke query geen index gebruikt kan worden eigenlijk... Hij moet @langID en NULL opzoeken, en die NULL velden kunnen vervolgens worden gefiltered aan de hand van de waarde van @langID. Fair enough, aparte queries is waarschijnlijk optimaler, maar dat dit meteen al een index bypassed?

[.oisyn]

EfBe schreef op maandag 11 februari 2008 @ 12:57:
Wat je moet gebruiken is COALESCE.

Just out of interest, is jouw query beter dan de mijne waar de OR in staat? En zo ja, waarom?

[justmental]

.oisyn schreef op maandag 11 februari 2008 @ 12:57:
Nou nou, ik vind een fout datamodel wat zorgwekkender dan een ongeoptimaliseerde query

Eens, ik reageerde op de bewoording.

Nou ben ik geen db guru, maar ik zie niet in waarom er bij een dergelijke query geen index gebruikt kan worden eigenlijk... Hij moet @langID en NULL opzoeken, en die NULL velden kunnen vervolgens worden gefiltered aan de hand van de waarde van @langID. Fair enough, aparte queries is waarschijnlijk optimaler, maar dat dit meteen al een index bypassed?

Nulls zitten vaak niet in een index.

[curry684]

.oisyn schreef op maandag 11 februari 2008 @ 13:03:
[...]

Just out of interest, is jouw query beter dan de mijne waar de OR in staat? En zo ja, waarom?

Nee, SQL Server optimalizeert beiden weg tot een simpele index scan waarbij de functions irrelevant zijn voor performance.

[curry684]

justmental schreef op maandag 11 februari 2008 @ 13:10:
Nulls zitten vaak niet in een index.

Bij mij regelmatig eigenlijk in optionele foreign keys?

[siroki]

Peoples,

De oplossing met ISNULL(...,'') werkt prima. Bedankt hiervoor!

[ Voor 119% gewijzigd door siroki op 11-02-2008 13:29 ]

[RobIII]

justmental schreef op maandag 11 februari 2008 @ 13:10:
Nulls zitten vaak niet in een index.

Volgens mij gaan die in een NULL bitmap? En is dus prima te indexen?

[ Voor 6% gewijzigd door RobIII op 11-02-2008 13:24 ]

[justmental]

curry684 schreef op maandag 11 februari 2008 @ 13:12:
Bij mij regelmatig eigenlijk in optionele foreign keys?

RobIII schreef op maandag 11 februari 2008 @ 13:22:
Volgens mij gaan die in een NULL bitmap?

Het kan wel, maar vaak zitten ze niet in de normale b-tree's.
In ieder geval is het vaak handig om er anders mee te optimaliseren, alleen al om het geval dat ze meestal een hele andere frequentie van voorkomen hebben dan een 'gewone' waarde.

[EfBe]

.oisyn schreef op maandag 11 februari 2008 @ 13:03:
[...]

Just out of interest, is jouw query beter dan de mijne waar de OR in staat? En zo ja, waarom?

Niet per-se beter, ik had jouw query niet goed bekeken, maar COALESCE is een native SQL statement en daardoor makkelijker intern te optimaliseren in de engine. Een OR query is dat niet (SQL is een geinterpreteerde taal nl. )

[Verwijderd]

EfBe schreef op maandag 11 februari 2008 @ 12:57:
select * from table where COALESCE(LangID, -1) == COALESCE(@LangID, -1)

Performance? En recompile inwisselen voor een table-scan... dan liever een recompile.

Edit> Volgens mij krijg je zelfs in 2005 geen recompile...

[ Voor 10% gewijzigd door Verwijderd op 11-02-2008 19:30 ]

[curry684]

En waar haal je die table scan vandaan? Op een LangId zet je een index (foreign key ole) en dan krijg je in beide gevallen een (non)clustered index scan. Ik heb ze in SQL2k5 getest en allebei gaven hetzelfde execution plan.

[EfBe]

Verwijderd schreef op maandag 11 februari 2008 @ 18:36:
[...]
Performance? En recompile inwisselen voor een table-scan... dan liever een recompile.
Edit> Volgens mij krijg je zelfs in 2005 geen recompile...

Een IF statement leidt vaak tot een recompile, omdat execution plans wijzigen omdat per setje input parameter values, het code-path in de routine anders is (de ene keer wel de true clause, de andere keer niet).

Ik zie ook de relevantie niet mbt de table-scan en de recompile, het zijn 2 totaal verschillende dingen. Als je met NULL values aan het kloten bent in stored procedures heb je COALESCE nodig of de OR truuk maar that's it, meer heb je niet. Een IF statement maakt het echt alleen maar trager.