create nonclustered index idx_first
on person(firstname)

drop index person.idx_first

alter table person
drop constraint PK_id

alter table person
add constraint PK_id primary key(BusinessEntityID)


-------ORDER BY---------------------------------

select * from person
order by firstname
-- samo sa klasterovanim PK 1.49
-- clustered index scan 12%-0.1726,i/o 0.15, citanje cele tabele
-- sort 88% - 1.32, cpu 1.3, sortiranje po koloni firstname
-- sa neklasterovanim idx_first isto
-- zapisi u indeksu jesu sortirani po koloni firstname,
-- ali za svaki zapis bi moralo da se pristupa tabeli da bi se ucitale ostale kolone
-- neke memorijske stranice bi se ucitavale po vise puta,
-- i zato je efikasnije uopste ne iskoristiti neklasterovani indeks

select firstname from person
order by firstname
-- bez neklasterovanog 1.49
-- sa neklasterovanim 0.0719 (21 puta brze)
-- index scan 100%, i/o 0.049, uopste ne pristupa tabeli
-- sve sto je potrebno upitu se nalazi u indeksu, a tamo su zapisi vec sortirani po imenu

select businessEntityID, firstname --, lastname
from person
order by firstname
-- bez neklasterovanog 1.49
-- sa neklasterovanim 0.0719
-- kljuc klasterovanog se nalazi u indeksu, ne pristupa se tabeli
-- sa lastname 1.49, ova kolona se ne nalazi u indeksu, pa mora da se pristupa tabeli,
-- i onda je efikasnije uopste ne koristiti neklasterovani indeks

-- uklonimo klasterovani PK
select firstname, businessEntityID
from person
order by firstname
-- bez klasterovanog PK, sa neklasterovanim idx_first 1.49
-- ID vise nije PK, pa ne ulazi u sastav neklasterovanih indeksa,
-- vec mora da se ucita iz tabele, pa se neklasterovani uopste i ne koristi

select BusinessEntityID 
from person
order by BusinessEntityID
-- bez klasterovanog 1.49
-- sa klasterovanim PK nad ID 0.1726
-- tabela je vec sortirana po primarnom kljucu, samo je treba ucitati

select * from person
order by BusinessEntityID
-- bez klasterovanog 1.49
-- sa klasterovanim 0.1726
-- klasterovani indeks je zapravo cela tabela, samo fizicki sortirana,
-- pa su i kolone van kljuca na neki nacin deo indeksa

--zakljucak: klasterovani bolje sortira za sve kolone
--neklasterovani bolje sortira za kolone svog i klasterovanog kljuca pretrage

select top 5 * from Person
order by FirstName
-- samo sa klasterovanim PK 1.49
-- sa neklasterovanim idx_first 0.022
-- index scan 14%, ucitavanje prvih 5 zapisa iz indeksa
-- key lookup 86%, ucitavanje ostalih kolona iz tabele

select top 5000 * from Person
order by FirstName
-- samo sa klasterovanim PK 1.49
-- sa neklasterovanim idx_first 1.457
-- index scan 1%, ucitavanje prvih 5000 zapisa iz indeksa
-- key lookup 97%, ucitavanje ostalih kolona iz tabele za tih 5000 vrsta

select top 6000 * from Person
order by FirstName
-- samo sa klasterovanim PK 1.49
-- sa neklasterovanim idx_first isto

--5000/20000 koristi ncl indeks
--6000/20000 ne koristi ncl indeks

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

create nonclustered index idx_first
on person(firstname)

drop index person.idx_first

alter table person
drop constraint PK_id

alter table person
add constraint PK_id primary key(BusinessEntityID)

----------KOMPOZITNI INDEKSI---------------------

-- Kompozitni indeks je indeks nad vise kolona, indeks koji ima slozeni kljuc pretrage

create nonclustered index idx_flm
on person(firstname, lastname, middlename)
-- zapisi su u ovom indeksu sortirani po koloni firstname
-- oni sa istim imenom su sortirani po koloni lastname
-- oni sa istim imenom i prezimenom su sortirani po koloni middlename

drop index idx_flm on person

select BusinessEntityID, FirstName
from Person
order by FirstName
-- sa neklasterovanim nad firstname (idx_first) 0.072
-- sve select-ovane kolone se nalaze u indeksu
-- treba samo ucitati indeks (index scan)
-- sa kompozitnim nad firstname, lastname, middlename (idx_flm) 0.108
-- ponovo se sve select-ovane kolone nalaze u indeksu
-- i ponovo treba samo ucitati indeks (index scan)
-- ali je indeks sada veci ima vise kolona, zauzima vise memorije
-- potrebno je vise vremena za njegovo ucitavanje

select BusinessEntityID, FirstName, LastName
from Person
order by FirstName
-- sa neklasterovanim idx_first 1.49
-- kolona lastname se ne nalazi u indeksu, pa se indeks uopste ne koristi
-- sa kompozitnim idx_flm 0.108
-- u kompozitnom se nalaze sve select-ovane kolone, nema pristupa tabeli
-- isto vreme izvrsavanja kao prethodni upit, broj select-ovanih kolona ne pravi razliku

select BusinessEntityID, FirstName--, LastName
from Person
order by LastName
-- sa neklasterovanim idx_first 1.49
-- kolona lastname se ne nalazi u indeksu, a potrebna je za sortiranje
-- pa se indeks uopste ne koristi
-- clustered index scan (ucitavanje cele tabele) 12% - 0.1726, sortiranje po lastname 88% - 1.32
-- sa kompozitnim idx_flm 1.429
-- sve potrebne kolone se nalaze u indeksu pa nema pristupa tabeli (index scan 8% - 0.108)
-- medjutim, zapisi u indeksu su sortirani po firstname
-- upit treba da vrati sortirano po lastname, pa se vrsi sortiranje indeksa (sort 92% - 1.32)
-- sa kompozitnim se upit izvrsava nesto brze jer se ucitava samo indeks, a ne cela tabela
-- vreme sortiranja ostaje isto
-- sa LastName u select-u sve isto

-- kompozitni indeksi su od najvece koristi za ubrzanje upita koji se
-- sortiraju ili grupisu vise kolona
select BusinessEntityID, FirstName--, LastName
from Person
order by firstname, LastName
-- bez kompozitnog idx_flm 1.49
-- sa kompozitnim 0.108
-- samo citanje iz indeksa (index scan 100%)
-- u indeksu je vec sve sortirano onako kako je trazeno u upitu

select firstname, lastname, count(*)
from Person
group by firstname, LastName
-- bez kompozitnog indeksa 1.51
-- clustered index scan - citanje cele tabele 11% - 0.1726
-- sortiranje po firstname i lastname 87% - 1.32
-- stream aggregate - prolazak kroz sortirane zapise i prebrajanje 1%
-- sa kompozitnim idx_flm 0.129
-- index scan 83% - 0.108, ucitava se samo indeks, nema pristupa tabeli
-- indeks je vec sortiran po imenu i prezimenu
-- stream aggregate - prolazak kroz sortirane zapise i prebrajanje

select lastname, count(*)
from Person
group by LastName
-- bez indeksa 0.38
-- clustered index scan 45% - 0.1726
-- hash match 55% - 0.208
-- sa idx_first isto
-- sa kompozitnim idx_flm 0.316
-- index scan umesto citanja cele tabele 34% - 0.108
-- has match 66% - 0.208, isto kao i bez kompozitnog
-- malo ubrzanje je postignuto jer se ucitava samo kompozitni indeks

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

alter table person
add constraint PK_id primary key(BusinessEntityID)

alter table person
drop constraint PK_id

create nonclustered index idx_first
on person(firstname)

drop index person.idx_first

create nonclustered index idx_flm
on person(firstname, lastname, middlename)

drop index idx_flm on person

----------POMOCNE PROCEDURE------------------------------------------

exec sp_helpindex person
-- daje listu svih indeksa nad tabelom uz kratak opis i prikaz kljuca

exec sp_spaceused person
-- prikazuje koliko memorije zauzima prosledjena tabela
-- koliko zauzimaju sami podaci, a koliko indeks

-- poredjene zauzeca memorije pri upotrebi razlicitih kombinacija indeksa
exec sp_spaceused person
-- bez ikakvih indeksa:				data 1600KB
-- sa klasterovanim PK:				data 1600KB, index 16KB
-- sa cl PK i ncl idx_first:		data 1600KB, index 544KB
-- sa cl PK i kompozitnim idx_flm:  data 1600KB, index 936KB
-- sa ncl idx_first (bez cl PK):	data 1600KB, index 616KB!
-- vise memorije zauzima samo neklasterovani nego kad ide u kombinaciji sa klasterovanim
-- razlog: neklasterovani ima jednu kolonu za PK, odnosno za klasterovani indeks, tip int, zauzima 4KB po vrsti
-- kada nema klasterovanog PK, neklasterovani ima jednu kolonu za RID (Row ID), zauzima 8KB po vrsti