NicoLab5

CREATE TABLE Ta(
aid INT PRIMARY KEY NOT NULL,
a2 INT UNIQUE
);


CREATE TABLE Tb(
bid INT PRIMARY KEY NOT NULL,
b2 INT
);


CREATE TABLE Tc(
cid INT PRIMARY KEY NOT NULL,
aid INT FOREIGN KEY REFERENCES Ta(aid),
bid INT FOREIGN KEY REFERENCES Tb(bid));
-----------------------------------------------------------------------------------------------
EXECUTE sp_helpindex Ta

INSERT INTO Ta VALUES (1,2,'Nico1',20),(2,3,'Nico2',20),(3,4,'Nico3',20),(4,5,'Nico4',20),(5,6,'Nico5',20),(6,7,'Nico6',20),(7,8,'Nico7',20),(8,9,'Nico8',20),(9,10,'Nico9',20),(10,11,'Nico10',20),(11,12,'Nico11',20),(12,13,'Nico12',20),(13,14,'Nico13',20),(14,15,'Nico14',20);

DELETE Ta
DELETE Tb
DELETE Tc

EXECUTE sp_helpindex Tb
INSERT INTO Tb VALUES (1,2,'Oradea', 123456, 23),(2,3,'Oradea', 123456, 23),(3,4,'Oradea', 123456, 23),(4,5,'Oradea', 123456, 23),(5,6,'Oradea', 123456, 23),(6,7,'Oradea', 123456, 23),(7,8,'Oradea', 123456, 23),(8,9,'Oradea', 123456, 23),(9,10,'Oradea', 123456, 23),(10,11,'Oradea', 123456, 23),(11,12,'Oradea', 123456, 23),(12,13,'Oradea', 123456, 23),(13,14,'Oradea', 123456, 23),(14,15,'Oradea', 123456, 23);
INSERT INTO Tb VALUES (15,2,'Oradea', 123456, 23),(16,2,'Oradea', 123456, 23),(17,4,'Oradea', 123456, 23),(18,2,'Oradea', 123456, 23),(19,2,'Oradea', 123456, 23),(20,2,'Oradea', 123456, 23),(21,2,'Oradea', 123456, 23),(22,2,'Oradea', 123456, 23),(23,10,'Oradea', 123456, 23),(24,2,'Oradea', 123456, 23),(25,2,'Oradea', 123456, 23),(26,2,'Oradea', 123456, 23),(27,2,'Oradea', 123456, 23),(28,2,'Oradea', 123456, 23);

EXECUTE sp_helpindex Tc
INSERT INTO Tc VALUES (1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7),(8,8,8),(9,9,9),(10,10,10),(11,11,11),(12,12,12),(13,13,13),(14,14,14);


CREATE NONCLUSTERED INDEX NCIDX_name_Ta
ON Ta(nume ASC)
-------------------------------------------------------------------------------------------------------------------------------
--a)

--clustered index scan
SELECT * FROM Ta

--clustered index seek
SELECT * FROM Ta WHERE aid = 10

--nonclustered index scan
SELECT aid FROM Ta
--nonclustered index scan
SELECT a2 FROM Ta

--nonclustered index seek + keylookup
SELECT * FROM Ta WHERE a2 = 10

--clustered index seek
SELECT aid, age
FROM Ta
WHERE aid> 3

--clustered index scan
SELECT aid, age
FROM Ta
WHERE age > 3


---------------------------------------------------------------------------------
--b)
SELECT b2 FROM Tb WHERE b2 = 2
CREATE NONCLUSTERED INDEX NCIDX_b2_Tb
ON Tb(b2 ASC)


---------------------------------------------------------------------------------
--c)