După o serie de articole, „Bazele SQL“ am decis să încerc să amintesc unele utilizări non-standard ale Transact-SQL și a începe o serie de articole „Trucuri SQL“.
Baza de date pentru exemple
Dacă în ultima serie, am folosit baza de date cea mai simplă, care a fost creat și script-uri pline, dar acum am decis să utilizeze exemplele standard de la Microsoft. Motivul este că va lua structuri și date mai complexe, și exemple vor fi mai aproape de viața reală.
plan de execuție
Serios și fără a intra în detalii, motorul de baze de date trebuie să știe în ce ordine și modul de conectare al tabelului, care indexurile de a utiliza, etc. Cu alte cuvinte, este necesar să se construiască (sau în cache) planul de execuție înainte de a executa interogarea.
Planul real de execuție, puteți vedea în SSMS (SQL Server Management Studio), selectând-o din elementul de meniu „Solicitare / Include planul de execuție real“ sau apăsând „Ctrl + M“. Ca urmare, după interogarea pachetul veți vedea o filă „plan de execuție“, în care, pentru fiecare cerere din executarea pachetului în termeni grafice vor fi afișate forma.
Analiza punerii în aplicare a planurilor este un subiect interesant diferit, probabil, vă spun vreodată despre asta. Între timp, dacă sunteți interesat în această temă (doar trebuie să știe dacă te angajeze serios în optimizare interogare), puteți citi secțiunea relevantă a MSDN. În același timp, are sens pentru a citi despre punerea în cache a planurilor de execuție.
sintaxa CASE
Doar în cazul în care, permiteți-mi amintesc de sintaxă „CASE“. deoarece utilizarea sa este relativ rară:
Aș dori să vă atrag atenția asupra faptului că „else“ poate fi omisă în cazul în care, în absența NULL corespunzător „când“ se va întoarce.
De asemenea, este de remarcat faptul că valoarea de returnare trebuie să fie compatibile. Asta este, astfel încât să puteți scrie:
Vă rugăm să rețineți: aceeași regulă, după cum s-ar putea ghici, funcționează nu numai pentru constante, dar, de asemenea, pentru coloane de tabele. Deci, fii atent - cererea dumneavoastră poate lucra pe aceleași date (de exemplu, în cazul în care codul produsului stocat în linie, dar conține doar cifre) și să nu lucreze pentru alții (adăugat la scrisoarea de cod de produs).
USM CASE
Astfel, în primul rând, de dreapta - cu condiția însumarea în (revenind 0 sau 1). Această metodă permite în loc de mai multe cereri similare pentru a scrie, și că este adesea o idee bună pentru a îmbunătăți performanța.
Un exemplu de unele sintetice, dar simplu - Imaginați-vă că trebuie să știți din când în când numărul total de mărfuri, precum și numărul de bunuri roșu și negru. Deci, este posibil pentru a rezolva problema „cap“:
Și astfel, folosind „sumă de CASE“:
Dacă SSMS (SQL Server Management Studio) pentru a include producția de punere în aplicare efectivă a planului. putem vedea că costul ultimei interogare în două ori mai mic decât costul total al primelor trei.
Într-o digresiune lirică - o bună înțelegere a Microsoft SQL Server am încălzit de multe ori sufletul atunci când, după „destul de bun“, pentru a îmbunătăți performanța de interogare, începe să efectueze zeci de ori mai rapid. Și un alt fapt interesant - împreună cu colegul meu (SQL administrator) fără să spună un cuvânt în prealabil a fost de acord că manipularea liberă a „sumă de CASE“ indică o bună cunoaștere a SQL. Deci, ceea ce am spus doar unul dintre secretele cum să treacă de un expert SQL :)
ORDINUL CASE
Acum vom încerca să „CASE“:
Asta e mai bine. Cu toate acestea, noi suntem, dacă te uiți, tocmai am primit noroc cu ID-urile de ordine. Dacă vom sorta elemente copil după nume, vedeți imediat problema.
Pentru a sorta corect rândurile din cadrul fiecărui grup de bază, avem nevoie de un alt mic truc:
Sper că ai fost interesant să învețe aceste tehnici sau reîmprospăta memoria lor.
Există o altă nuanță puțin în sortarea după caz. În cazul în care caz descrie mai multe domenii de sortare posibile (în cazul dumneavoastră ProductCategoryID și ParentProductCategoryID) tipul de toate domeniile posibile ar trebui să fie de așa natură încât pot fi exprimate în mod implicit la serverul de primul tip câmp descris în CASE-e.
Exemplu - scârțâind de mai jos.
@SortMode la valori egale cu 1, 2 sau chiar 3 script vіpolnyat este bine, dar dacă 4 - bate eroare
Msg 245, nivel 16, statul 1, linia 28
Conversia eșuat atunci când conversia valoarea nvarchar „3.14159“ la tipul de date int.
Deci, fii atent!
--------
IF object_id ( 'tempdb .. # TmpTbl', 'U') NU ESTE NULL
DROP TABLE #TmpTbl
CREATE TABLE #TmpTbl (lld INT, IIdExt INT, nCode nvarchar (10), NCodeExt nvarchar (10))
; CU LineNumber AS (
SELECT F1.INo + F2.INo * 10 AS Ino
DE LA (
SELECT 0. ino
UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT UNION SELECT 4 5 6 UNION SELECT
UNION SELECT UNION SELECT 7 8 9 UNION SELECT
) AS F1
CROSS JOIN (
SELECT 0. ino
UNION SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3
UNION SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
UNION SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
) AS F2
)
INSERT INTO #TmpTbl
SELECT ln.Ino, ln.Ino * SIN (ln.Ino), STR (ln.Ino), ln.Ino * PI () DIN LineNumber AS ln
DECLAR @SortMode INT
SET @SortMode = 4
SELECT t. * DE LA #TmpTbl CA t ORDER BY
CASE @SortMode
CÂND 1 ATUNCI t.IId
CÂND 2 ATUNCI t.IIdExt
CÂND 3 APOI t.NCode
CÂND 4 ATUNCI t.NCodeExt
END
ASC
Nu susțin, în plus, că am scris despre acest lucru în capitolul despre caz :) Sau nu suficient de concentrat?
Bună ziua, și aici este o întrebare:
Să presupunem că în procesul vine o serie de opțiuni câteva filtre.
Utilizați Case și în loc kopipasta convenabil, de exemplu, obținut după cum urmează:
selectați * de la masa
în cazul în care țara =
caz @par
1 când apoi „Ucraina“
când 2, apoi „Rusia“
.
atunci când null, atunci tara
(Adică, ultimul rând indică faptul că toate țările se încadrează în eșantion) - în măsura în care decizia corectă, dacă prea mult comparație cu viteza de impact?
Și a doua întrebare: dacă într-un caz nevoie de ispolzovant „cum ar fi“ - pentru coincidență non-stricte, iar pentru stricte ar fi mai bine „=“, atunci este posibil ca în cazul de proiectare să se aplice în acest caz; până când toate au trebuit să se filtrează printr-ca (țară ca țară strict) sau copiați-lipiți întreaga procedură cu schimbarea în prima linie (posibil pentru că toți fac, și nimic în ea nu este, în general, aceasta este întrebarea))
mulțumesc
În general, în cazul în care îngrijirea despre performanța, este mai bine pentru a implementa prin intermediul „SAU“.
Pentru cazuri specifice, puteți vedea planul de execuție pentru cele două opțiuni (presupunând că volumul de date este suficient) și selectați punctul de vedere al diferenței (sau lipsa acestora), costurile de implementare.