SQL server indexen

[NLCOMP]

Bericht 1 van 6

NL Computer Forum ~ SQL & Programmeren

Van	:	John Kopmels (Sysop)	Datum	:	14-08-2004
Aan	:	All	MsgID	:	1456.1
Onderwerp	:	SQL server indexen	Forum	:	ws-nlcomputer

Hallo,

mbt tot een andere discussie een vraag
over optionele parameters in een sp

wat is de beste manier om alles terug te geven
als er geen waarde in de aanroep wordt meegegeven

@par1 int = Null,
@par2 int = Null

WHERE a = ISNULL(@par1,a)
AND b = ISNULL(@par2 ,b)

of

@par1 int = "%",
@par2 int = "%"

WHERE a = @par1
AND b = @par2

of iets anders

wordt er in genoemde gevallen nog gebruik gemaakt van evt indexen?
ps (hoe test ik dit het beste)

en wordt er in het 2e geval een impliciete conversie uitgevoerd
van string naar numeriek in geval een parameter bv INT is ?

bvb mijn dank

--john


Bericht 2 van 6

NL Computer Forum ~ SQL & Programmeren

Van	:	Hugo Kornelis	Datum	:	14-08-2004
Aan	:	John Kopmels (Sysop)	MsgID	:	1456.2
Onderwerp	:	SQL server indexen	Forum	:	ws-nlcomputer

Beste John,

> wat is de beste manier om alles terug te geven
> als er geen waarde in de aanroep wordt meegegeven
>
>
> @par1 int = Null,
> @par2 int = Null
>
>
> WHERE a = ISNULL(@par1,a)
> AND b = ISNULL(@par2 ,b)
>
> of
>
> @par1 int = "%",
> @par2 int = "%"
>
>
> WHERE a = @par1
> AND b = @par2
>
> of iets anders

De tweede manier die je noemt werkt niet. Dan krijg je alleen de rijen waarin de inhoud van a en b precies gelijk is aan de opgegeven tekst: n &-teken. Ik denk dat je in de war bent met de LIKE-operator: LIKE '%' geeft je wel alle waarden terug.

> wordt er in genoemde gevallen nog gebruik gemaakt van evt indexen?
> ps (hoe test ik dit het beste)

Durf ik uit mijn hoofd niet te zeggen. Wil je het zeker weten, kijk dan naar het executieplan van de query. In Query Analyzer (als je die hebt) kun je via Query / Show Execution Plan deze optie gebruiken; je krijgt dan een extra tabblad in het uitvoerscherm waar je een grafische weergave van het executieplan ziet. Meer informatie over elke stap zie je als je de muis boven het betreffende onderdeel houdt.

Heb je alleen een basis tekst-interface met SQL Server (zoals osql.exe), dan kun je de tekst-versie van het executieplan te zien krijgen met de optie SET SHOWPLAN_ALL ON. De uitvoer is helaas niet makkelijk te lezen.

> en wordt er in het 2e geval een impliciete conversie uitgevoerd
> van string naar numeriek in geval een parameter bv INT is ?

Als je een numerieke kolom in een LIKE operatie opneemt zal er inderdaad conversie plaatsvinden; in dat geval zal er dus ook zeker geen index gebruikt worden.

Tenslotte nog een tip: als het aantal argumenten waarvan je niet zeker weet of ze worden opgegeven of niet beperkt is, dan kun je natuurlijk ook een IF THEN ELSE boom in je procedure opnemen en op basis van de wel of niet opgegeven parameters het juiste SELECT statement uitvoeren. Dan weet je in elk geval zeker dat je optimaal gebruik maakt van de indexen!!

Groetjes, Hugo

Bericht 3 van 6

NL Computer Forum ~ SQL & Programmeren

Van	:	John Kopmels (Sysop)	Datum	:	15-08-2004
Aan	:	Hugo Kornelis	MsgID	:	1456.3
Onderwerp	:	SQL server indexen	Forum	:	ws-nlcomputer

Hoi Hugo,

> De tweede manier die je noemt werkt niet.
> Ik denk dat je in de war bent met de LIKE-operator:
> LIKE '%' geeft je wel alle waarden terug.

ja natuurlijk <blush> daar had ook WHERE a
LIKE @par1 AND b LIKE @par2 moeten staan

>> wordt in genoemde gevallen nog gebruik gemaakt van evt indexen?

> kijk dan naar het executieplan van de query

Gedaan en :
Zoals je al zegt: bij de LIKE versie wordt de index genegeerd
en wordt het een Tablescan, maar idem bij de ISNULL versie

>> en wordt er in het 2e geval een impliciete conversie uitgevoerd
>> van string naar numeriek in geval een parameter bv INT is ?

> Als je een numerieke kolom in een LIKE operatie
> opneemt zal er inderdaad conversie plaatsvinden

dat dacht ik ook, maar wist het niet zeker

> Tenslotte nog een tip: als het aantal argumenten waarvan je
> niet zeker weet of ze worden opgegeven of niet beperkt is,
> dan kun je natuurlijk ook een IF THEN ELSE boom in je procedure
> opnemen en op basis van de wel of niet opgegeven parameters
> het juiste SELECT statement uitvoeren. Dan weet je in elk geval
> zeker dat je optimaal gebruik maakt van de indexen!!

OK dat werkt ja (hier wel gebruik van indexen)

kan je het SELECT deel in een variabele opnemen en binnen de
IF ELSE deze gebruiken zodat je geen 2x de hele SELECT moet
schrijven (rommelig) en zo ja hoe ziet het raamwerk er dan uit

wederom bedankt voor de heldere uitleg !

Groetjes --john


Bericht 4 van 6

NL Computer Forum ~ SQL & Programmeren

Van	:	John Kopmels (Sysop)	Datum	:	18-08-2004
Aan	:	John Kopmels (Sysop)	MsgID	:	1456.4
Onderwerp	:	SQL server indexen	Forum	:	ws-nlcomputer

> kan je het SELECT deel in een variabele opnemen en binnen de IF
ELSE deze gebruiken zodat je geen 2x de hele SELECT moet schrijven

Heb ik inmiddels uitgevogeld, maar heeft niet veel zin zie ik
de sp moet steeds opnieuw gecompileerd worden dus heeft alleen
nadelig effect, bovendien is de variabele wat lastig samen te
stellen indien er veel functies in de sql opgesloten zitten ..

groetjes --john


Bericht 5 van 6

NL Computer Forum ~ SQL & Programmeren

Van	:	Hugo Kornelis	Datum	:	18-08-2004
Aan	:	John Kopmels (Sysop)	MsgID	:	1456.5
Onderwerp	:	SQL server indexen	Forum	:	ws-nlcomputer

Hoi John,

> > kan je het SELECT deel in een variabele opnemen en binnen de IF
> ELSE deze gebruiken zodat je geen 2x de hele SELECT moet schrijven
>
> Heb ik inmiddels uitgevogeld, maar heeft niet veel zin zie ik

Oeps. Ik had helemaal gemist dat je nog een extra vraag had gesteld. Sorry dat ik niet geantwoord hebt - maar ik zie dat je inmiddels ook zelf al een antwoord hebt gevonden.

Het is me niet helemaal duidelijk wat je nu precies hebt gedaan, maar het lijkt erop alsof je hier dynamische SQL gebruikt (SQL opbouwen in een string en dan die string via EXEC(@variable) of via sp_executesql laten uitvoeren). Als dat inderdaad is wat je geprobeerd hebt, dan is het belangrijk te weten dat je met deze aanpak een achterdeur openzet voor hackers, die dit kunnen misbruiken voor zogenaamde "SQL injection attacks". Zie de onderstaande website voor nadere uitleg over dit verschijnsel:

http://www.sommarskog.se/dynamic_sql.html#Security2

Groetjes, Hugo

Bericht 6 van 6

NL Computer Forum ~ SQL & Programmeren

Van	:	John Kopmels (Sysop)	Datum	:	20-08-2004
Aan	:	Hugo Kornelis	MsgID	:	1456.6
Onderwerp	:	SQL server indexen	Forum	:	ws-nlcomputer

Hoi Hugo

> had helemaal gemist dat je nog een extra vraag had gesteld.

geeft niet, ik was er al mee bezig en 't is
ook wel eens goed er zelf door te klnen :-)

> lijkt erop alsof je hier dynamische SQL gebruikt (sql
opbouwen in een string via EXEC(@variable) uitvoeren)

was de overweging ja, in Access ben ik vrij snel met dynamische
sql te schrijven met kleine recordsets, makkelijk in onderhoud
(alles op 1 plek) en zoals het woord al zegt heel dynamisch,
maar in sqlserver (in sp's) zie ik wel meer nadeel ...

> belangrijk te weten dat je met deze aanpak een achterdeur
openzet voor hackers, die dit kunnen misbruiken voor zg
"SQL injection attacks". Zie de onderstaande website

bedankt voor deze tip en de (goede) link !

groetjes --john