Using Foreign Keys

For years I used a version of SQL called KBSQL developed for use on Unix systems running Mumps databases. One thing I really miss was the ability to use index tables that basically had necessary columns and Foreign Keys to other tables.

So, instead of a Join, I could search against these indexes and link to other tables using the Foreign keys.

For example:

MED_LINK@DSCHRG_DT
MED_LINK@PAT_NAME
MED_LINK@DEMOG_LINK@PAT_SSN

The columns with the _LINK were the Foreign keys. The third example shows linking to another index to use it's Foreign Keys to link to a table.

Using this made the queries run much faster than using the linked to table only or even using Joins instead.

I have been looking for this same capability in MS SQL with no luck. Is there such a capability in MS SQL?

Thanx in advance for any help on this.

i think the concept is called a covering index

a covering index contains all the columns needed to resolve the sql, consequently there is no need to access table rows at all

this happens all the time... the compiler first inspects all indexes looking for a covering index for the query being parsed, before deciding to retrieve table rows

Thanx r937 for the response.

I checked into covering index and that's not quite what I had in mind. The index tables I used in KBaseSQL only had the fields needed for the Foreign Key(s). The actual data I wanted to retireve was found in the tables being linked to by the Foreign Key.

An index that contains all information required to resolve the query is known as a "Covering Index"; it completely covers the query.

If I am missing something, please let me know.

Thanx again for your response.