CODEWITHSUNDEEP
sqlsql-serverindexing
Matt
Matt

Reputation: 26971

How to index a SQL table on a column in another table

I have the schema below - Let's pretend that there are 2 countries, A and B. Country A has 1000 teams whereas country B has 100,000,000 - If I want to quickly query results based off which country the team is in, how would I construct my index?

Teams cannot change country if that helps.

enter image description here

Upvotes: 0

Views: 1662

Answers (1)

KumarHarsh
KumarHarsh

Reputation: 5094

Indexing a table depend upon knowing real schema.

For this simple table schema, I will create only Trusted FK between tables, at least this will be my first try.

Assuming Countryid,Teamid,Resultid are auto increment.

CREATE TABLE Country
(
    id          INT IDENTITY(1, 1) PRIMARY KEY, 
    CountryName VARCHAR(100) NOT NULL
);

CREATE TABLE Team
(
    id        INT IDENTITY(1, 1) PRIMARY KEY, 
    TeamName  VARCHAR(100) NOT NULL, 
    CountryID INT NOT NULL
);

ALTER TABLE dbo.Team WITH CHECK
    ADD CONSTRAINT FK_Team_CountryID 
        FOREIGN KEY(CountryID) REFERENCES dbo.Country(id);

ALTER TABLE dbo.Team WITH CHECK 
    CHECK CONSTRAINT FK_Team_CountryID;

--Just verify that newly created FK is trusted or not.
SELECT 
    name, 
    is_disabled, 
    is_not_trusted
FROM
    sys.foreign_keys
WHERE 
    name = 'FK_Team_CountryID';

CREATE TABLE Result
(
    id     INT IDENTITY(1, 1) PRIMARY KEY, 
    TeamId INT NOT NULL, 
    Result INT NOT NULL
);

-- I have no idea how you are storing Result,so ignore it

ALTER TABLE dbo.Result WITH CHECK
    ADD CONSTRAINT FK_Result_TeamId 
        FOREIGN KEY(TeamId) REFERENCES dbo.Team(id);

ALTER TABLE dbo.Result WITH CHECK 
    CHECK CONSTRAINT FK_Result_TeamId;

May be after seeing query plan of real query, I will De-normalise Result table to add Countryid , but for now it is not require since country table will be small

Upvotes: 1

Related Questions