I'm working on a database that will be very read-intensive, and uses a junction table that's read often and in both directions.
The junction table contains two columns, IDA and IDB
The primary key for the junction table will be a composite key for both columns.
I want to create an index so the database can quickly seek the corresponding IDA for a specific IDB.
Should that index contain both IDB and IDA? Or should it just contain IDB?
To put it another way, should my table look like this:
CREATE TABLE ExJunction(
IDA INTEGER NOT NULL,
IDB INTEGER NOT NULL,
CONSTRAINT PK_ExJunction_IDA_IDB PRIMARY KEY CLUSTERED (IDA ASC, IDB ASC),
CONSTRAINT AK_ExJunction_IDB_IDA UNIQUE (IDB ASC, IDA ASC)
)
Or like this:
CREATE TABLE ExJunction(
IDA INTEGER NOT NULL,
IDB INTEGER NOT NULL,
CONSTRAINT PK_ExJunction_IDA_IDB PRIMARY KEY CLUSTERED (IDA ASC, IDB ASC),
INDEX IX_ExJunction_IDB NONCLUSTERED (IDB ASC)
)
Or, should I do something else altogether?
I'm having trouble conceptualizing this, since I've always thought of nonclustered indexes containing both the index key and the primary key. But the index key is already contained in the primary key, so, are these the same (both containing IDB and IDA), or different?