------------INCLUDE------------------------------

-- Naredbom INCLUDE se u indeks dodaju kolone iz tabele koje nisu deo kljuca pretrage.
-- Ove kolone su pridruzene indeksu kao sto je pridruzena npr. kolona klasterovanog kljuca.
-- Od vrednosti pridruzenih kolona ne zavisi raspored zapisa u indeksu.

-- Kreiranje indeksa nad kolonom FirstName sa pridruzenom kolonom LastName:

create nonclustered index idx_first_incl
on person(firstname) include (lastname)

-- Neklasterovanom indeksu se moze pridruziti i vise kolona

create nonclustered index idx_incl_sve
on person(firstname) include (lastname, middlename, suffix, ModifiedDate, PersonType, Title)

-- Nema potrebe pridruzivati i primarni kljuc jer je on vec pridruzen automatski.

-- Kakva je razlika izmedju indeksa sa include idx_first_incl i sledeceg kompozitnog indeksa:
create nonclustered index idx_first_last
on person(firstname, lastname)

-- I jedan i drugi indeks sadrze kolone firstname i lastname. 
-- Razlika je u tome sto su kod kompozitnih svi zapisi sortirani po celom kljucu pretrage,
-- po FirstName, pa po LastName.
-- Kod idx_first_incl su zapisi sortirani samo po FirstName, po LastName nisu,
-- LastName kolona je samo pridruzena.

-- Zasto bismo onda uopste koristili indekse sa include?

-- 1. razlog - SQL server dopusta duzinu kljuca od 16 kolona ili 900 bajtova. 
-- Medjutim, sa include mozemo da dodamo proizvoljan broj non-key kolona u indeks.

-- 2. razlog - Include-ovane kolone se cuvaju samo u listovima, ne koriste se za pretragu,
-- nisu sortirane, pa ih nema u cvorovima stabla. Samim tim, zauzimaju manje memorije
-- nego dodatna kolona u kljucu pretrage, koja se nalazi i u listovima i u stablu.


alter table person
add constraint PK_id
primary key (BusinessEntityID)

create nonclustered index idx_first
on person(firstname)
drop index person.idx_first

create nonclustered index idx_first_last
on person(firstname, lastname)
drop index person.idx_first_last

create nonclustered index idx_first_incl
on person(firstname) include (lastname)
drop index person.idx_first_incl

exec sp_spaceused person


select firstname, lastname
from person
where firstname like 'Martin'
--samp PK 0.17, data 1600KB, index 16KB
--sa idx_first 0.063, data 1600KB, index 544KB
--sa idx_first_last 0.0033, data 1600KB, index 808KB
--sa idx_first_incl 0.0033, data 1600KB, index 808KB

-- Kompozitni indeks i indeks sa include su dali isto vreme izvrsavanja.
-- Razlika u memoriji je trenutno zanemarljiva, nije cak ni na nivou KB.
-- Ova razlika ce doci do izrazaja kod vecih indeksa.


create nonclustered index idx_sve
on person(firstname, lastname, middlename, suffix, ModifiedDate, PersonType, Title)
drop index person.idx_sve

create nonclustered index idx_sve_incl
on person(firstname) include (lastname, middlename, suffix, ModifiedDate, PersonType, Title)
drop index person.idx_sve_incl

exec sp_spaceused person


select * from person
where firstname like 'Martin'
-- sa idx_sve 0.0033, data 1600KB, index 1600KB
-- sa idx_sve_incl 0.0033, data 1600KB, index 1576KB

-- Vreme izvrsavanja je i dalje isto, 
-- ali je sada razlika u memoriji dosla do izrazaja


-- ZAKLJUCAK: Kolone koje se koriste za pretragu, grupisanje ili sortiranje
-- treba staviti u kompozitni indeks, 
-- a one koje se nalaze samo u SELECT delu treba pridruziti indeksu
-- jer nam za pretragu nisu potrebne, pa ne moraju biti deo kljuca pretrage,
-- a ovako mogu da ustede memoriju.


---------KLASTEROVANI INDEKSI-------------------

-- Klasterovani indeksi ne moraju da budu samo primarni kljucevi.
-- Npr. moguce je napraviti klasterovani indeks nad kolonom LastName.
-- Ali prvo treba ukloniti postojeci klasterovani indeks.
-- Jedna tabela moze imati samo jedan kl. indeks koji fizicki sortira podatke.

alter table person
drop constraint PK_id

-- Sada moze da se napravi novi klasterovani indeks

create clustered index cl_last
on person(lastname)

exec sp_spaceused person
--sa PK_id, data 1600KB, index 16KB
--sa cl_last, data 1736KB, index 16KB

-- Sama tabela zauzima vise memorije sa cl_last nego sa primarnim kljucem.
-- Razlog - Kada je klasterovani kljuc PK, i uz to jos auto-increment,
-- memorijske stranice tabele se popunjavaju bez ostavljanja slobodnog prostora,
-- jer se racuna da nece biti INSERT operacija koje ubacuju vrste u sredinu tabele.
-- Kada klasterovani kljuc nije PK, a nije ni UNIQUE, postoji realna mogucnost
-- da se cesto ubacuju nove vrste u svaki deo tabele. Zato se u svakoj memorijskoj stranici
-- ostavlja dodatni prazan prostor za buduce nove vrste. Samim tim, cela tabela zauzima
-- vise memorijskih stranica.

select *
from person
where lastname like 'Adams'
-- sa cl_last 0.0033
-- nema potrebe za include-ovanjem svih select-ovanih kolona,
-- jer se sve vec nalaze u klasterovanom indeksu,
-- jer je klasterovani indeks zapravo sama tabela, sortirana po klasterovanom kljucu.
-- Poprilicna usteda memorije u odnosu na resenje sa neklasteerovanim indeksom.


-- Koriscenje INCLUDE naredbe sa klasterovanim indeksom nema smisla, 
-- sve kolone van kljuca pretrage su vec pridruzene indeksu.
-- Sa druge strane, moguce je napraviti klasterovani kompozitni indeks:

drop index person.cl_last

create clustered index cl_last_first
on person(lastname, firstname)
-- data 1664KB, index 32KB
-- data je manji nego kod cl_last jer ima manje ponavljanja vrednosti kljuca,
-- pa je ostavljeno manje slobodnog prostora u mmorijskim stranicama
-- indeks zauzima duplo vise nego kod cl_last zbog duplo veceg kljuca.

select *
from person
order by lastname, firstname
-- bez indeksa 1.49
-- sa cl_last_first 0.178

select lastname, firstname, count(*)
from person
group by lastname, firstname
-- bez indeksa 1.5
-- sa cl_last_first 0.2

-- Ako zelimo da klasterovani indeks ostane nad kolonom LastName,
-- a da ipak napravimo primarni kljuc nad ID kolonom,
-- moguce je napraviti neklasteerovani primarni kljuc:

alter table person
add constraint PK_id
primary key nonclustered (BusinessEntityID)

-------------------------------------------------------------------------

alter table person drop constraint PK_id

drop index person.cl_last_first

-- Indeksi mogu i da uspore izvrsavanje nekih upita.
-- Takav ja slucaj sa INSERT, UPDATE i DELETE naredbama.

-------------INSERT-------------------------------

insert into person(BusinessEntityID, PersonType, 
FirstName, LastName, ModifiedDate)
values (30000,'EM','Pera','Peric','2015-12-3')

-- bez indeksa 0.01
-- table insert 100% - 0.01, ubacivanje nove vrste u tabelu
-- assert 0% - provera da li nova vrsta narusava ogranicenja nad tabelom
-- sa PK_id 0.01
-- clustered index insert - isto sto i table insert, 100% - 0.01
-- assert 0%
-- klasterovani indeks ne usporava INSERT naredbu

-- sa 1 ncl (idx_last), 0.02
-- clustered index insert 0.02, duplo vise nego bez neklasterovanog
-- razlog: nova vrsta, osim u samu tabelu, sada mora da se ubaci i u ncl indeks

-- sa 2 ncl (idx_last i idx_first), 0.03
-- jos jedan ncl indeks, i u njega mora da se ubaci nova vrsta

alter table person
add constraint PK_id
primary key (BusinessEntityID)

create nonclustered index idx_last
on person(lastname)

create nonclustered index idx_first
on person(firstname)

drop index person.idx_last
drop index person.idx_first


-- Neklasterovani indeksi usporavaju naredbe modifikacije,
-- jer svaka promena tabele mora da se odrazi i u indeksima.

-------------UPDATE-----------------------------------------

update person
set lastname = 'Jovanovic'
where firstname = 'Pera'
-- samo klasterovani nad id 0.1862
-- clustered index scan 93%, 0.1726
-- compute scalar 0.003, racunanje izraza pod set (pretpostavlja da moze postojati neki izraz)
-- clustered index update 0.01, promena vrednosti u jednoj vrsti tabele

create nonclustered index idx_last
on person(lastname)
-- sa idx_last 0.2
-- clustered index update 10% - 0.02
-- update traje duplo duze jer treba azurirati vrstu i u tabeli i u neklasterovanom indeksu

-- Za razliku od INSERT naredbe, UPDATE naredbu je moguce i ubrzati indeksom, jer ima WHERE deo

drop index person.idx_last
create nonclustered index idx_first
on person(firstname)
-- sa idx_first 0.013
-- index seek 25%-0.003, pretraga kroz indeks umesto citanja cele tabele, znatno ubrzanje
-- clustered index update 75%-0.01
-- mora da azurira tabelu, ali ne mora i neklasterovani indeks
-- jer se azurira samo kolona LastName, a ona nije deo indeksa idx_first

-- DELETE ima slicno ponasanje kao UPDATE