#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Central Valley California
    Posts
    2
    Rep Power
    0

    Question 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.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by DCSS
    Is there such a capability in MS SQL?
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2012
    Location
    Central Valley California
    Posts
    2
    Rep Power
    0

    Using Foreign Keys


    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.

IMN logo majestic logo threadwatch logo seochat tools logo