BEGIN TRANSACTION …

(by: Jasmin Azemović)

Archive for the ‘TSQL’ Category

Demistificirani SQL (1)

with 3 comments

Odlučio sam da studentima povremeno postavim rješenje isptnih zadataka sa predmeta “Upravljanje bazama podataka” djela vježbi i workshoop(a) gdje se najviše radi sa SQL komandama (DML i DDL). Odabrit ću one upite koji koji su bili  “teži” tj. studenti su imali poteškoća prilikom implementacije rješenja. Naravno, ako osjetim da je to potrebno ova serija postova može postati redovna…ali to nije do mene nego do auditorijuma.

Cilj postova nije da studenti memorišu upite i na takav način pokušaju proći ispit (SQL kodiranje nije nešto što se može “nabubati”), nego baš suprotno da ih podstakne da samostalnu sjednu i nad oglednim bazam sami sebi zadaju određene probleme (tj. upite). Svako programiranje pa tako i SQL nije ništa više nego trening i to sigurno 90%, talenat je 10 %. Međutim, treningom se može namiriti “manjak” talenta.

Svi upiti se rade kroz SQL Server okruženje (SSMS) i sample baze koje su prevljenje za ovu database platformu.

Šta nam je potrebno:

  1. SQL Server 2008 R2, 2008 ili 2005 (Denali je također u opciji)
  2. Sample baze podataka (u zavisnosti od verzije SQL Servera preuzmite odgovarajuće baze: http://msftdbprodsamples.codeplex.com/)
  3. Nekada koristim “stare” sample baze Northwind i Pubs. Iste se mogu preuzeti na :http://www.microsoft.com/download/en/details.aspx?id=23654)
  4. Ako niste nikada prije instalirali SQL Server imate moj video tutorijal koji to objašnjava http://www.youtube.com/watch?v=ahG4EF8G-3c
  5. Ako imate problema sa sample bazama podataka pogledajte ovdje: http://www.youtube.com/watch?v=xG258yGjLG0

Za početak sam odabrao prvi upit sa roka odražnog 06.09.2010. Teksta zadatka glasi ovako:

Baza podataka je: AdventureWorksLT

Za svaku kategoriju proizvoda, prikazati njoj pripadajuće proizvode i to sa sljedećim atributima (naziv kategorije, naziv proizvoda, cijenu proizvoda, boju, datum početka prodaje, datum kraja prodaje i veličinu proizvoda). Pored tražene liste atributa upit treba zadovljiti sljedeće kriterije:

  • Potrebno je kreirati novu kolonu koja će na osnovu početka i kraja prodaje prikazati ukupan broj dana u kojim se prikazani proizvod prodaje ili se prodavao;
    Uslovi ili filteri za upit su:
    • Naziv proizvoda počinje oznakom HL ili je broj dana prodaje veći ili jednak od 364 i prodaja proizvoda je završena
  • Izlaz ne smije imati sljedeće
    • U imenu proizvoda oznaku M
    • Cijenu manju od 1000

Rješenje je:

USE AdventureWorksLT
SELECT PC.Name, P.Name, P.ListPrice, P.Color, P.SellStartDate, P.SellEndDate,
DATEDIFF (DAY, P.SellStartDate, SellEndDate) AS 'Period prodaje',
P.Size

FROM SalesLT.ProductCategory AS PC
INNER JOIN SalesLT.Product AS P
ON PC.ProductCategoryID = P.ProductCategoryID

WHERE  (P.Name LIKE 'HL%' OR DATEDIFF (DAY, P.SellStartDate, SellEndDate) >= 364)
AND SellEndDate IS NOT NULL
AND (P.Name NOT LIKE '%M' AND P.ListPrice > 1000 )

ORDER BY P.Size DESC

Toliko za ovaj put.

END TRANSACTION
Advertisements

Written by Jasmin Azemović

08/09/2011 at 10:55

Posted in Database, FIT, SQL, TSQL

Random data and SQL Server (re-write)

with 2 comments

Prije par sedmica imao sam potrebu da iz baze podataka izdvojim određeni broj zapisa i to slučajnim izborom. E sada tu su se pojavili razni problemi, SQL jezik nema komandu koja bi “zagrabila” TOP n zapisa, malo promiješala i izbacila potpuno “RANDOM“.

Npr. Ako želim da iz baze AdventureWorks (tabela Person.Contact) uzmem prvih 5 zapisa kucam komandu koja izgleda ovako:

SELECT TOP 5 ContactID, FirstName
FROM Person.Contact

Retultat izvršenja će biti poredan prema PK u ovom slučaju ContactID (jer nisam drugačije definisao sa ORDER BY klauzulom).

ContactID     FirstName
———–        ————-
1                    Gustavo
2                    Catherine
3                    Kim
4                    Humberto
5                    Pilar

(5 row(s) affected)

Koliko god se mi trudili da sa ORDER BY korigujemo goren navedeni upit i promjenimo redosljed sortiranja…nemamo baš puno opcija, ako želimo RANDOM listu.

Pokušao sam i sa TABLESAMPLE.

SELECT ContactID, LastName
FROM Person.Contact
TABLESAMPLE (10 PERCENT)

Međutim, nije baš random. BOL takođe kaže da ovo nije najbolje rješenje ako želimo slučajni izbor podataka. Šta sad?

Rješenje je generalno vrlo jednostavno, mada ima svojih prednosti i mana. Sigurno svi znamo za funkciju NEWID(). Ista generiše uniqueidentifier vrijednost koja je definitvno jedinstvena. To je svima nama dobro poznati 16 bitni GUID. Ako NEWID() svaki put generiše drugačiju GUID, zašto isti ne bi mogli primjenili u ORDER BY klauzili? Vratimo se na prvi upit:

SELECT TOP 5 ContactID, FirstName
FROM Person.Contact
ORDER BY NEWID()

Nakon izvršenja, redosljed sortiranja će bit drugačiji nego u listingu 1..pritisnimo ponovo F5, pa opet F5, pa opet F5….rezultat izvršenja će svaki put vratiti drugih 5 zapisa. Ovo je definitno riješilo moj problem. Možda će ilustrativnije biti ako se TOP 5 promjeni na TOP 1. Sada će te nakon F5 svaki put dobiti drugi zapis (kao i u prvom slučaju), ali se bolji primjeiti razlika.

Koji su side effects ove metode? Ima jedna mana (koliko sam ja mogao promjetiti..nisam puno ulazio u problematiku). Naime, na jako velikim tabalema  (par stotina hiljada zapisa ili nekoliko miliona) NEWID() treba da se generiše za svaki od tih zapisa i pa tek onda uradi ORDER BY. To je procedura koja može uzeti JAKO puno vremena. Tabela Person. Contact ima malo više od 19 000 zapisa tako da problem sa performansama nije baš uočljiv, ali ako pogledamo execution plan izvršenja našeg upita gdje se generiše NEWGUID() i po istom sortira (nije indekisran) onda stvar postaje malo jasnija.

Kako god,  meni je ovo riješilo problem, vjerujem da ako neko od Vas bude imao slične zahtjeve..NEWID() je nešto što definitivno trebate uzeti u razmatranje.

END TRANSACTION

Written by Jasmin Azemović

08/08/2011 at 22:28

Posted in Database, SQL, SQL Server, TSQL

sp_MSforeachtable (re-write)

with 2 comments

Da, pokušam rezimirati problem koji sam imao prije par sedmica. Pojavila se potreba da u “staroj” bazi podataka, u svaku tabelu, dodam po jedan novi atribut tipa uniqueidentifier. Ništa posebno, ALTER TABLE ili desni klik na istu pa odabir opcije “Design”. Naravno, nema nikavih problema…osim jednog. Baza ima preko 150 tabela !!

Nisam mogao prihvatiti činjenicu da nema nekog  lakšeg, skrivenog načina za kaskadnu promjenu objekata bez obzira na njihov broj. Poznato je da je Microsoft, posebno u SQL Serveru krije određeni broj undocumented procedures. Radi se o skrivenim blokovima TSQL koda koji po običaju rade fantastične i cool stvari, ali problem je što je sve to “skriveno” od očiju globalne bazaške populacije. Kao po definiciji svaka nedokumentovane procedure nema podršku tj. drugim riječima, nemamo koga da pitamo. Parola je snađi se.

Rješene moga problema je pronađeno (nakon par sati lutanja) u proceduri:

sp_MSforeachtable

Iz samog imena “foreachtable” se vidi kako bi stvar trebala da funkcioniše. Na primjeru baze podataka pubs će Vam sve biti kristalno jasno. Dole navedeni primjer prvo dodaje GUID atribut tipa uniqueidentifier u sve tabele, a zatim isti i uklanja.

USE pubs
GO

-- Dodavanje iste kolone u sve tabele
EXEC sp_MSforeachtable 'ALTER TABLE ? ADD GUID uniqueidentifier NULL'

-- Uklanjanje prethodno dodane kolone
EXEC sp_MSforeachtable 'ALTER TABLE ? DROP COLUMN GUID'

Zaista bih volio da ima „skrivena“ procedura za promjenu imena svih objekata unutar baze ili servera, npr. da u ime svih sp’s dodamo „new“. Možda i ima 🙂

END TRANSACTION

Written by Jasmin Azemović

11/07/2011 at 11:32

Posted in Database, SQL, SQL Server, TSQL