use test
go

--database test, table Clienti
select * from Clienti



--verifica indecsii existenti
EXEC sys.sp_helpindex @objname = N'Clienti'

SELECT OBJECT_NAME(a.object_id) as table_name, COL_NAME(b.object_id,b.column_id) AS Column_Name, * 
FROM sys.indexes a inner join sys.index_columns b
on a.object_id = b.object_id
WHERE a.object_id = OBJECT_ID('Clienti')


--putem crea un index unique nonclustered pe Name? 
create unique nonclustered index NumeClientiIndex on Clienti(Nume);


--putem crea un index clustered pe Name?
create clustered index NumeClientiIndex on Clienti(Nume);


--putem crea un index nonclustered pe Name?
create nonclustered index NumeClientiIndex on Clienti(Nume);
drop index Clienti.NumeClientiIndex;

create nonclustered index VarstaClientiIndex on Clienti(Varsta);


--ce operatie va fi folosita pentru urmatoarea interogare? se foloseste vreun index?
select * from Clienti;
select Varsta from Clienti


--va fi vreo diferenta intre operatiile folosite pentru urmatoarele interogari? 

select * from Clienti where Varsta >22

select Id, Varsta from Clienti where Varsta >22

select Nume, Varsta from Clienti where Varsta >22


drop index Clienti.VarstaClientiIndex;

---------------------------------------------------
create nonclustered index VarstaClientiIndex on Clienti(Varsta) include (Nume);

--se schimba ceva la planul de executie?
select Nume,Varsta from Clienti where Varsta = 22;

drop index Clienti.VarstaClientiIndex;
---------------------------------------------------
-- Tabel: OrderStatistics(Id, OrderId, Amount)

select * from OrderStatistics
EXEC sys.sp_helpindex @objname = N'OrderStatistics'

create nonclustered index OrderAmount on OrderStatistics(Amount);
drop index OrderStatistics.OrderAmount;
-- ce operatii si indecsi apar in planul de executie?

select * from OrderStatistics 

select * from OrderStatistics where Id > 5000

select Amount from OrderStatistics

select Amount from OrderStatistics where Amount <=1500

select Amount, OrderId from OrderStatistics where Amount = 1500

select Amount, OrderId from OrderStatistics where Amount <= 1000

select * from OrderStatistics order by Amount

select Amount from OrderStatistics order by Amount
---------------------------------------------------

create nonclustered index OrderAmount1 on OrderStatistics(Amount) where Amount<2000;
create nonclustered index OrderAmount2 on OrderStatistics(Amount) where Amount>=2000;
drop index OrderStatistics.OrderAmount1;
drop index OrderStatistics.OrderAmount2;

select * from OrderStatistics where Amount <= 100

select * from OrderStatistics where Amount = 3000

select * from OrderStatistics where Amount < 3000

select * from OrderStatistics where Amount between 1800 and 2100

select Amount from OrderStatistics order by Amount

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

create nonclustered index OrderAmount2 on OrderStatistics(Amount ASC,OrderId DESC);
drop index OrderStatistics.OrderAmount2
select * from OrderStatistics WITH (INDEX(OrderAmount2))



--statistici despre index

select * from sys.dm_db_index_physical_stats(DB_ID(N'test'), OBJECT_ID(N'Clienti'),NULL,NULL,'DETAILED');
 --operanzi: database_id, object_id, index_id, partition_numer, mode


 -------------------------------------------------------------
 
 --utilizarea tabelelor si a view-urilor de sistem


 --ce afiseaza urmatoarele interogari?

SELECT COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + CONSTRAINT_NAME), 'IsPrimaryKey') = 1
AND TABLE_NAME = 'Clienti'


SELECT DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Clienti' and COLUMN_NAME = 'Nume'


SELECT  
    fk.name,
    OBJECT_NAME(fk.parent_object_id) 'Parent table',
    c1.name 'Parent column',
    OBJECT_NAME(fk.referenced_object_id) 'Referenced table',
    c2.name 'Referenced column'
FROM 
    sys.foreign_keys fk
INNER JOIN 
    sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
    sys.columns c1 ON fkc.parent_column_id = c1.column_id AND fkc.parent_object_id = c1.object_id
INNER JOIN
    sys.columns c2 ON fkc.referenced_column_id = c2.column_id AND fkc.referenced_object_id = c2.object_id




select * from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'Clienti' and DATA_TYPE like '%char%'



SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Clienti'
ORDER BY ORDINAL_POSITION

