74 lines
2.7 KiB
Transact-SQL
74 lines
2.7 KiB
Transact-SQL
-- Work on 3 tables of the form Ta(aid, a2, …), Tb(bid, b2, …), Tc(cid, aid, bid, …), where:
|
|
|
|
-- aid, bid, cid, a2, b2 are integers;
|
|
-- the primary keys are underlined;
|
|
-- a2 is UNIQUE in Ta;
|
|
-- aid and bid are foreign keys in Tc, referencing the primary keys in Ta and Tb, respectively.
|
|
-- a. Write queries on Ta such that their execution plans contain the following operators:
|
|
|
|
-- clustered index scan;
|
|
-- clustered index seek;
|
|
-- nonclustered index scan;
|
|
-- nonclustered index seek;
|
|
-- key lookup.
|
|
-- b. Write a query on table Tb with a WHERE clause of the form WHERE b2 = value and analyze its execution plan. Create a nonclustered index that can speed up the query. Examine the execution plan again.
|
|
|
|
-- c. Create a view that joins at least 2 tables. Check whether existing indexes are helpful; if not, reassess existing indexes / examine the cardinality of the tables.
|
|
|
|
--a. Write queries on Ta such that their execution plans contain the following operators:
|
|
|
|
-- clustered index scan;
|
|
|
|
--create unique constraint on name column
|
|
|
|
SELECT * FROM Players;
|
|
|
|
-- clustered index seek;
|
|
|
|
SELECT * FROM Players WHERE hmy = 22003;
|
|
|
|
-- nonclustered index seek;
|
|
|
|
-- add nonclustered index on name
|
|
CREATE NONCLUSTERED INDEX IX_Players_name ON Players(hmy, name) INCLUDE (totalMatches);
|
|
|
|
DROP INDEX IX_Players_name ON Players;
|
|
|
|
SELECT name, totalMatches FROM Players WHERE name = 'lerio2';
|
|
|
|
-- nonclustered index scan;
|
|
|
|
SELECT name, totalMatches FROM Players WHERE totalMatches > 0;
|
|
|
|
-- key lookup.
|
|
|
|
-- write a query that uses the nonclustered index and a key lookup
|
|
SELECT * FROM Players WHERE name = 'lerio2';
|
|
|
|
--b. Write a query on table Tb with a WHERE clause of the form WHERE b2 = value and analyze its execution plan. Create a nonclustered index that can speed up the query. Examine the execution plan again.
|
|
|
|
-- create nonclustered index on b2
|
|
CREATE NONCLUSTERED INDEX IX_Cards_faction_cardset ON Cards(hmy, faction, cardset) INCLUDE (name, cost);
|
|
|
|
DROP INDEX IX_Cards_faction_cardset ON Cards;
|
|
|
|
-- examine execution plan
|
|
SELECT hmy, name, cost, faction, cardset FROM Cards WHERE faction = (SELECT hmy FROM Factions WHERE name='Neutral') AND cardset = (SELECT hmy FROM CardSet WHERE name='Base Set');
|
|
|
|
--c. Create a view that joins at least 2 tables. Check whether existing indexes are helpful; if not, reassess existing indexes / examine the cardinality of the tables.
|
|
|
|
-- create view
|
|
GO
|
|
|
|
ALTER VIEW CardsOwners AS
|
|
SELECT Cards.name AS cardName, Players.name AS playerName, Cards.hmy AS cardId, Players.hmy AS playerId
|
|
FROM Cards
|
|
INNER JOIN CardsOwnership ON Cards.hmy = CardsOwnership.hCard
|
|
INNER JOIN Players ON Players.hmy = CardsOwnership.hPlayer
|
|
|
|
GO
|
|
|
|
-- check if indexes are helpful
|
|
|
|
SELECT * FROM CardsOwners;
|