SQL Server general:
Physical allocation:
1 page == 8 Kb == 8 Kb == 8192 bytes
1 extend == 8 pages = 64 Kb == 123136 bytes
Two sort of extends; uniform and mixed. SQL Server allocates space in extends.
A page contains a 96 byte header followed by the data rows and then an offset table which point to the records (in reverse order).
Database files and filegroups
Primary data files *.mdf
Secondary data files *.ndf
Log files *.ldf
Allocation maps:
GAM: Global Allocation Map; keeps track of which uniform extends are in use. Holds 64000 extends (of 64000 * 64Kb =~ 4 Gb)
SGAM: Shared Global Allocation Map: keeps track of which mixed extends are in use AND with a least 1 unused page. Bit = 1; extend is mixed and contains free page(s); bit = 0; extend is uniform or does not have free pages.
SQL Server kent twee methoden voor het beheren van de data pages:
– Clustered tables
Dit zijn tabellen die een clustered index hebben.
– Heaps
Dit zijn tabellen die GEEN clustered index hebben.
Non-clustered indexen hebben geen invloed op de volgorde van de rijen in een tabel. Een tabel
met een clustered index is op volgorde van de clustering-key.
Relational Engine <=======> OLE DB <============> Storage Engine
I/O Architecture:
Het meeste virtuele geheugen wordt gealloceerd voor een buffer voor lees- en schrijfacties (cache).
Logical I/O:
– Read / Write kan beantwoord worden vanuit de cache.
Physical I/O
– Read moet fysiek van de schijf gedaan worden (niet in cache).
Periodiek worden de gegevens weggeschreven naar de disk.
Transaction Architecture:
Een transactie gaat door verschillende fasen:
– Voordat de transactie start is de database consistent
– BEGIN TRANSACTION
– —— modify data ——-
– Als de database wederom consistent is kan de applicatie een commit geven.
– Bij eventuele fouten gedurende het modificeren van de data kan een rollback worden gegeven.
Autocommit mode:
Iedere transactie wordt, bij succes, direct gecommit, bij een fout ge-rollbacked.
OLTP: Online Transaction Processing (order placing / tracking)
OLAP: Online Analytical Processing (data warehouses)
SQL Server indexen:
Twee typen indexen:
– Clustered
– Non Clustered
Er kan maximaal 1 clustered index per tabel bestaan. De tabel wordt fysiek gesorteerd op clustering index. De clustering-key wordt opgenomen bij alle non-clustered indexen. Neem deze key dus niet al te groot.
Het is verstandig om eerst de clustering index (indien nodig) aan te maken en vervolgens alle non-clustering indexen.
Het is zeer onverstandig om de primary key clustered te maken (Hoewel Enterprise Manager dit voorstelt)!
Als bij een tabel een clustered index een insert plaats vind kan dit een page-split tot gevolg hebben (als de betreffende page vol is). Dit veroorzaakt fragmentatie.
Bij een table-scan is fragmentatie slechts nieuws!
Range retrieval is goed nieuws met een clustered index; records worden dan ook gesorteerd opgeleverd (er is ook geen extra overhead als op de clustering key gesorteerd wordt).
Fill-factor: geeft aan hoe gevuld een tabel moet zijn. Read only tabellen kunnen een fill-factor van 100% hebben. Een fill factor van 50% is bij table-scans zeer slechts nieuws want dan moeten er 2x zoveel io’s worden uitgevoerd.
Covering query: het fysieke record hoeft niet gelezen te worden want de index levert al alle gegevens op.
Voorbeeld:
create table test1 ( id int, naam char(100), tekst char(200) ) create clustered index idx_id on test1(id) create index idx_naam on test1(naam) create index idx_tekst on test1(tekst) select * from test1 where id = 1 -– Clustered index seek select * from test1 where naam = ‘x’ -– Index seek met een bookmark lookup voor de rest van het record select naam from test1 where naam = ‘x’ -– Index seek zonder bookmark lookup want de index is covering select id, naam from test1 where naam = ‘x’ -– Index seek zonder bookmark lookup want de index is covering aangezien -– ook id (de clustering key) in de index is opgenomen
Foreign keys
Foreign keys verwijzen naar kolommen in andere tabellen. Nadat de benodigde tabellen zijn aangemaakt kunnen de foreign keys gedefinieerd worden:
create table book
(
id int identity(1,1) not null,
aut_id int not null,
title char(255)
)
create clustered index book_title_idx_ci ON book(title)
alter table book add constraint prim_key_book PRIMARY KEY (id)
create table author
(
id int identity(1,1) not null,
aut_name char(255)
)
create clustered index author_name_idx_ci ON author(aut_name)
alter table author add constraint prim_key_author PRIMARY KEY (id)
create table BookAuthor
(
id int identity(1, 1) not null,
book_id int not null,
aut_id int not null
)
create unique clustered index bookauthor_idx_ci ON bookauthor(book_id, aut_id)
alter table bookauthor add constraint prim_key_bookauthor primary key nonclustered (id)
insert into author (aut_name) values ('berend')
insert into author (aut_name) values ('marian')
insert into author (aut_name) values ('nynke')
insert into author (aut_name) values (‘marije’)
insert into author (aut_name) values (‘Lazy author’)
insert into book (title) values (‘Berends book’)
insert into book (title) values (‘Marians book’)
insert into book (title) values (‘Nynkes book’)
insert into book (title) values (‘Marijes book’)
insert into book (title) values (‘Marije en Berends book’)
insert into bookauthor (book_id, aut_id) values (1, 1)
insert into bookauthor (book_id, aut_id) values (2, 2)
insert into bookauthor (book_id, aut_id) values (3, 3)
insert into bookauthor (book_id, aut_id) values (4, 4)
insert into bookauthor (book_id, aut_id) values (5, 1)
insert into bookauthor (book_id, aut_id) values (5, 4)
-– Relations
alter table BookAuthor add constraint aut_fk foreign key(aut_id) references author(id)
alter table BookAuthor add constraint book_fk foreign key(book_id) references book(id)
INNER, OUTER, FULL JOIN
De inner-join is eigenlijk de equi join (=). Een voorbeeld van de inner join is (met
gebruik making van de bovenstaande tabel definities):
SELECT book.title, author.aut_name FROM bookauthor inner join author on author.id = bookauthor.aut_id inner join book on book.id = bookauthor.book_id
Met een left join worden in ieder geval alle rijen uit de linker tabel opgeleverd en, indien mogelijk, gekoppeld aan een entry (op basis van de join condition) uit de rechter tabel.
SELECT book.title, author.aut_name, bookauthor.id</span></div> FROM author left outer join bookauthor on author.id = bookauthor.aut_id left outer join book on book.id = bookauthor.book_id
Dit levert ALLE rijen uit de author tabel. De rijen uit de author tabel worden op basis van de join conditie samengevoegd. Voor de “Lazy author” lukt dit niet en hiervoor wordt een NULL in de resultset geplaatst:
title aut_name id
—————————— —————————— ———–
Berends book berend 1
Marije en Berends book berend 5
Marians book marian 2
Nynkes book nynke 3
Marijes book marije 4
Marije en Berends book marije 6
NULL Lazy author NULL
GROUP BY, HAVING
create table titles ( id int identity(1, 1) not null, type int, price decimal, title char(30) ) insert into titles (type, price, title) values (1, 1, ’1′) insert into titles (type, price, title) values (1, 2, ’2′) insert into titles (type, price, title) values (2, 2, ’3′) insert into titles (type, price, title) values (3, 2, ’4′) insert into titles (type, price, title) values (3, 3, ’4′) select type, avg(price) as avg_price from titles group by type
type avg_price
———– —————————————-
1 1.500000
2 2.000000
3 2.500000
(3 row(s) affected)
Er wordt gegroepeerd op type vandaar dat er drie records worden opgeleverd (er zijn
drie verschillende types). Per groep wordt de gemiddelde (avg) prijs bepaald.
Met behulp van de HAVING clause kan weer een selectie losgelaten worden op de groep; de HAVING clause is eigenlijk de WHERE van de GROUP BY.
select type, avg(price) as avg_price from titles group by type having sum(price) = 5
type avg_price
———– —————————————
3 2.500000
(1 row(s) affected)
