Zece comune erori de programare-sql

Utilizarea excesivă a cursoare

SQL-programare poate fi sarcina interesanta si provocatoare. Persoanele care au experiență în programarea de limbaje de programare tradiționale, cum ar fi Java, C, C ++ sau VB, de multe ori au dificultăți de adaptare la „set-based“ este imaginea de gândire. Chiar și programatori cu experiență și SQL-DBA poate cădea într-una din capcanele limbajului SQL. Am fost acolo, și pun pariu că o vei face prea. Aflarea noțiunilor de bază are nevoie de timp, și chiar cu unele erori greu de urmărit.

În acest articol, am de gând să evidențieze unele dintre cele mai frecvente greșeli făcute de oameni care scriu SQL-cod. Lista reflectă experiența mea de a lucra cu diferite echipe de dezvoltatori și programatori care efectuează recenzii de cod, etc. precum și problemele, discuția despre care văd zilnic în forum. Aceasta nu este o listă exhaustivă, și bug-uri în ea nu sunt sortate. De fapt, această listă poate fi privit ca rezultat următoarea interogare:

Iată lista:

  1. predicat NULL NOT IN.
  2. Funcția pentru o coloană indexată în predicate.
  3. Defecțiune a coloanei din interogării secundare.
  4. Discrepanța dintre tipurile de date din predicatele.
  5. Ordinea de evaluare a predicate.
  6. Legături externe (de la exterior se alătură) și plasarea predicatului.
  7. Subinterogarilor care returnează mai mult de un rezultat.
  8. Folosind SELECT *.
  9. UDF-urile scalare.
  10. Utilizarea excesivă a cursorului.

Exemplele folosesc dialectul Transact-SQL, dar cele mai multe dintre concepte sunt utile pentru orice implementare SQL.

NULL și nu în predicat

Una dintre cele mai frecvente întrebări - selecție bazată pe o anumită valoare a coloanei nu face parte din lista de valori. Următoarele două tabele ilustrează acest scenariu. Avem o masă cu flori și alimente.

Rețineți că parametrul aici @last_name are tipul de date nvarchar. Codul „funcționează“, dar SQL Server va trebui să facă un nume de coloane implicite de conversie în nvarchar, ca nvarchar - mai mare tip de date de prioritate. Acest lucru poate duce la pierderea de productivitate. conversie implicită este afișată în planul de interogare ca CONVERT_IMPLICIT. În funcție de tipul și de alți factori de nepotrivire de tipuri de date, de asemenea, pot interfera cu utilizarea de căutare index. Folosind tipul de date corect rezolvă problema:

În multe cazuri, această eroare este rezultatul responsabilității comune într-o echipă în cazul în care un membru al echipei a fost proiectarea tabelelor, precum și alte instrumente proceduri stocate sau scrierea de cod. Un alt motiv poate fi un compus de date din diferite surse, în care unele coloane pot avea diferite tipuri de date. Același sfat se aplică nu numai la erori în tipurile de date caracter, dar, de asemenea, la inconsecvențele tipurilor de date numerice (de exemplu, INT și FLOAT), sau pentru amestecarea tipurilor numerice și alfabetice.

Ordinea de evaluare a predicate

Dacă sunteți familiarizat cu ordinea logică a procesare a cererii, vă puteți aștepta ca interogarea se face în ordinea următoare:

Această secvență descrie ordinea logică a interogării. FROM expresie logic prelucrate în primul rând, determinarea setului inițial de date sunt apoi aplicate predicate UNDE, GROUP BY ei, etc.

Cu toate acestea, cererile sunt procesate nu sunt bine fizic, iar optimizatorul de interogare se poate deplasa liber expresia în planul de interogare pentru a realiza planul de date costurile cele mai eficiente și de prelevare de probe. Acest lucru duce la credința populară că filtrul din punct de vedere acolo unde este cazul prelucrării fazei următoare. În realitate, planul fizic în predicatul de interogare poate fi aplicat mult mai târziu. În plus, pentru punerea în aplicare a predicatului este nici un ordin de la stânga la dreapta. De exemplu, dacă aveți expresia în care, conținând „unde x = 1 și y = 2“, nu există nici o garanție că „x = 1“ va fi considerat mai întâi. Acestea pot fi executate în orice ordine.

În general, un tabel cu un design prost. account_reference Coloana să fie reprezentată ca două alt atribut separat pentru Business- și personale-conturi, fiecare tip de date corecte (chiar și care nu aparțin acestui tabel). Cu toate acestea, în practică, de multe ori trebuie să se confrunte cu sisteme de prost concepute, designul pe care nu se poate schimba.

Pentru a corecta cererea inițială, este suficient să se mute predicatul cazul în care în starea se alăture.

Acum, cererea va da rezultatul corect, deoarece Jeff și Julie filtrează predicate JOIN, dar apoi să se întoarcă înapoi atunci când liniile de retur externe.

Într-un exemplu mai complicat cu conexiuni multiple de filtrare incorecte pot să apară în următorii operatori de masă (de exemplu, compuși cu o altă masă), dar nu și în clauza WHERE. De exemplu, să presupunem că avem o masa ORDERDETAILS, care conține un produs de SKU și cantitate. Cererea ar trebui să dea o listă a tuturor clienților, volumul comenzii și suma pentru SKU selectată. Următoarea interogare pare a fi corect:

Cu toate acestea, există INTERIOARĂ alătur tabelul ORDERDETAILS joacă exact același rol ca și clauza WHERE în exemplul de mai sus, în vigoare, transformarea LEFT OUTER JOIN în interior JOIN. Valabil cerere care îndeplinește cerințele este de a utiliza LEFT JOIN pentru a se conecta la tabelul ORDERDETAILS:

Subinterogarilor care returnează mai mult de o valoare

Este adesea necesar pentru a obține o anumită valoare, pe baza corelației cu tabelul de interogare principală. De exemplu, luați în considerare următoarele două tabele, care stochează informații despre produsele și fabricile care produc aceste produse.

Rețineți că aceeași eroare poate avea loc în cazul în care predicatul este comparat o coloană sau o expresie cu o subinterogare, de exemplu, „... coloană = (valoarea SELECT FROM tabelul)“. În acest caz, soluția este de a utiliza predicatul IN în loc de „=“.

Utilizarea SELECT *

Când ne-am întâlnit pentru prima dată cu SQL exaltăm întotdeauna de geniu care a inventat sintaxa * SELECT! Este atât de convenabil și ușor de utilizat! În loc de listare în mod explicit toate numele coloanelor din interogare, este suficient pentru a utiliza un wildcard magic „*“ și de a lua toate coloanele. De exemplu, utilizarea abuzivă pe scară largă SELECT * - eliminarea tuturor produselor din plastic care urmează să fie introduse într-un alt tabel cu aceeași structură:

Gata! Cu toate acestea, odată ce se schimbă cerințele în Produse tabelul vor vedea două coloane noi:

Situația ar putea fi chiar mai rău dacă utilizați SELECT * creează o vizualizare și apoi în baza sunt adăugate tabele (sau eliminate) coloană.

Notă: În cazul în care o vizualizare este creat folosind opțiunea SCHEMABINDING, tabelele de bază nu poate fi modificată astfel încât să afecteze definiția de vizualizare.

Pentru a termina, nu utilizați SELECT * în codul de producție! O excepție ar putea fi utilizarea predicatului EXISTS. ListaSelect în interogarea secundară pentru existentele predicat este ignorată, deoarece este important doar pentru linii.

UDF scalar

Reutilizarea de cod - unul dintre principiile fundamentale pe care le învățăm la programarea în orice limbă, iar SQL - nu este o excepție. Acesta oferă o mulțime de instrumente pentru a vă la grupul logic codul de ajutor și utilizați-l în mod repetat.

Un astfel de instrument în SQL Server - UDFS scalare. Se pare foarte convenabil pentru a ascunde toate calculele complexe în funcție, și apoi pur și simplu suna în interogări. Cu toate acestea, un dezavantaj ascuns este că aceasta poate duce la pierderea substanțială a performanței. Atunci când este utilizat într-o interogare funcții scalare sunt calculate pentru fiecare rând. Pentru mese mari, acest lucru poate duce la o foarte lent interogări de funcționare. Acest lucru este valabil mai ales atunci când o funcție de scalare trebuie să se aplice pentru datele către o altă tabelă.

Aici este un exemplu. Conține tabele cu produsele și vânzările lor, pe care doriți să obțineți vânzările totale pentru fiecare produs. Deoarece valoarea sumei de vânzare pot fi utilizate în alte locuri, vă decideți să utilizați o funcție de scalare pentru a calcula totalul vânzărilor pentru produsul: