-- ************************************************************************** 
-- * COALESCE FUNCTION * 
-- * * 
-- * This functions returns the first non-null value of its arguments. * 
-- * When all arguments are null; null is returned. * 
-- ************************************************************************** 
exec spDeleteObj 't1', 3, 0      
go      
create table t1    
(    
 id int identity(1,1) not null,    
 cola int,    
 colb int,    
 colc int    
)    
set nocount on    
insert into t1 values (1, null, null)    
insert into t1 values (null, 2, null)    
insert into t1 values (null, null, 3)    
insert into t1 values (null, null, null)    
set nocount off    
-- When none of the columns contains a value 999 is returned 
select cast(coalesce(cola, colb, colc, 999) as money) * 2 ColX from t1    
-- When all values are null the row is in the resultset 
select id from t1 where coalesce(cola, colb, colc) IS NULL   

-- ************************************************************************** 
-- * GROUP BY and HAVING clause * 
-- * * 
-- * Table "Customer" and table "Region" * 
-- * - a customer belongs to 1 region. A region has 0 or more customersn * 
-- * * 
-- * Only the column on which is grouped can be in the selectlist. All other* 
-- * columns in the selectlist must have a aggregate function. * 
-- ************************************************************************** 
exec spDeleteObj 'Customer', 3, 0    
exec spDeleteObj 'Region', 3, 0    
go    
create table Region    
(    
  RegionId   int identity(1,1) not null,    
  RegionName  varchar(30) not null    
  constraint pk_region primary key clustered (RegionId)    
)    
set nocount on    
insert into region (RegionName) values ('friesland')    
insert into region (RegionName) values ('groningen')    
set nocount off    
create table Customer    
(    
  CustomerId   int identity(1,1) not null,    
  CustomerName  varchar(30) not null,    
  CustomerAmount  int not null,    
  CustomerRegion  int not null    
  constraint fk_region foreign key (CustomerRegion) references region(RegionId)    
)    
set nocount on    
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values ('a', 1, 1)    
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values ('b', 2, 1)    
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values ('c', 3, 1)    
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values ('a', 4, 2)    
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values ('b', 5, 2)    
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values ('c', 6, 2)    
set nocount off  
select   
  region.RegionName,   
  sum(CustomerAmount) as Total    
from     
  Customer inner join region on Customer.CustomerRegion = region.RegionId    
group by   
  region.RegionName    
select     
  region.RegionName,     
  sum(CustomerAmount) as total     
from     
   Customer inner join region on Customer.CustomerRegion = region.RegionId    
group by    
   region.RegionName    
having     
   sum(CustomerAmount) > 7

-- ***************************************************************************************** 
-- * Tables, pages and extends: 
-- * - Max rowsize is 8060 bytes 
-- * - Max key length is 900 bytes 
-- * 
-- * - 1 page = 8 Kb = 8192 bytes 
-- * - 1 extend = 8 pages = 8 * 8 = 64 Kb = 123136 bytes 
-- * 
-- * - Table contig has a rowsize of 900 + 7135 = 8060 bytes. So every row is 1 page. 
-- * When we insert the first row 1 page (on the first extend is allocated). 
-- * DBCC SHOWCONTIG scanning 'contig' table... 
-- * Table: 'contig' (1461580245); index ID: 1, database ID: 8 
-- * TABLE level scan performed. 
-- * - Pages Scanned................................: 1 
-- * - Extents Scanned..............................: 1 
-- * .... 
-- ***************************************************************************************** 
exec spDeleteObj 'contig', 3, 0    
go  
create table contig    
(    
  id int identity(1,1) not null,   
  keyval char(900),   
  long1 char(7135)  
)   
create clustered index c_id on contig(keyval)   
set nocount on  
insert into contig values ('1', '1')   
set nocount off  
dbcc showcontig(contig)

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)