SET STATISTICS IO ON

-- Primjer 1.
SELECT DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
-- logical reads 202

CREATE NONCLUSTERED INDEX ix1 ON Racun(DatumIzdavanja)

SELECT DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
-- logical reads 2

DROP INDEX ix1 ON Racun
GO

-- Primjer 2.
SELECT IDRacun, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
-- logical reads 202

CREATE NONCLUSTERED INDEX ix1 ON Racun(DatumIzdavanja)

SELECT IDRacun, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
-- logical reads 2

DROP INDEX ix1 ON Racun
GO

-- Primjer 3.
SELECT IDRacun, BrojRacuna, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
-- logical reads 202

CREATE NONCLUSTERED INDEX ix1 ON Racun(DatumIzdavanja)

SELECT IDRacun, BrojRacuna, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
-- logical reads 10 - mora iæi na klasterirani indeks (key lookup) jer mu nedostaje vrijednost za BrojRacuna.

DROP INDEX ix1 ON Racun

CREATE NONCLUSTERED INDEX ix1 ON Racun(DatumIzdavanja) INCLUDE (BrojRacuna)

SELECT IDRacun, BrojRacuna, DatumIzdavanja FROM Racun WHERE DatumIzdavanja BETWEEN '20010702' AND '20010702 23:59:59'
-- logical reads 2 - sad ne mora iæi na klasterirani indeks.

DROP INDEX ix1 ON Racun
GO

-- Primjer 4.
SELECT r.IDRacun, SUM(s.Kolicina) AS Kolicina, SUM(s.UkupnaCijena) AS UkupnaCijena
FROM Racun AS r
INNER JOIN Stavka AS s ON r.IDRacun = s.RacunID
WHERE 
	r.DatumIzdavanja BETWEEN '20040126' AND '20040126 23:59:59' 
	AND BrojRacuna LIKE 'SO6275%'
GROUP BY r.IDRacun
-- Stavka: logical reads 847
-- Racun: logical reads 202

CREATE NONCLUSTERED INDEX ix1 ON Racun(DatumIzdavanja) INCLUDE (BrojRacuna)

CREATE NONCLUSTERED INDEX ix1 ON Stavka(RacunID) INCLUDE (Kolicina, UkupnaCijena)

SELECT r.IDRacun, SUM(s.Kolicina) AS Kolicina, SUM(s.UkupnaCijena) AS UkupnaCijena
FROM Racun AS r
INNER JOIN Stavka AS s ON r.IDRacun = s.RacunID
WHERE 
	r.DatumIzdavanja BETWEEN '20040126' AND '20040126 23:59:59' 
	AND BrojRacuna LIKE 'SO6275%'
GROUP BY r.IDRacun
-- Stavka: logical reads 18
-- Racun: logical reads 3

DROP INDEX ix1 ON Racun
DROP INDEX ix1 ON Stavka
GO