I've the next tables:

Financial:

  • PK_FinancialID
  • FK_SchoolID

School:

  • PK_SchoolID

Class:

  • PK_ClassID
  • FK_SchoolID
  • ClassName

Both Class and Financial have Foreign Key associations to college. I wish to create a query that will show all classes that are based on Financial rows that meet certain criteria.

Initially I believe to create the query the following:

Select Class.ClassName
From Class
Join School on Class.FK_SchoolID = School.PK_SchoolID
Join Financial on Financial.FK_SchoolID = Schol.PK_SchoolID
Where Financial ... -- define criteria

However, since both Financial and sophistication are became a member of around the PK_SchoolID column, it ought to be easy to rewrite the query the following (eliminating the college table and joining Class and Financial directly):

Select Class.ClassName
From Class
Join Financial on Financial.FK_SchoolID = Class.FK_SchoolID
Where Financial ... -- define criteria

Which approach is more suitable from the sql perspective? Would such as the School table make performance better since the actual PK record is recommended (and therefore a Clustered Index could be recommended)? Or does that does not really matter? Anything that i'm missing?

Platform: Sql Server 2005. All tables get their PK and FK posts correctly declared and defined.

If you do not need School, don't join School. Should you wan't this question to operate fast, create index on FK_SchoolID of monetary table. It appears just like you have n-1-1 relation between Class-School-Financial, which means you should even create unique index on Financial. You should not (generally) add more tables to create query faster, just optimize used.

EDIT

Should you choose only ClassName, maybe the thing you need is:

Select Class.ClassName
From Class
Where Exists 
    (select * from Financial 
    where (Financial.FK_SchoolID = Class.FK_SchoolID) and (...))

It might be faster than other solutions and much more understandable.

Yes, the index most certainly affects the performance.

Just add a catalog for that FK_SchoolID within the Financial table to ensure that there's a catalog the query may use.

Observe that adding another index provides a slight performance hit whenever you add or remove records within the table. This really is frequently outweighed through the large performance gain you receive when querying the table, but it is the reasons you ought to be somewhat limited with adding indexes and do not just add indexes to any or all fields.

Try the next:

Select Class.ClassName
From Class
Inner Join Financial on Financial.FK_SchoolID = Class.FK_SchoolID
Where Financial....yourcriteria

You don't need to join school table.

Appears in my experience that each of your good examples are wrong. The truth that a college shows up as financial which the college offers classes, does not necessarily mean that the specific class is really a financial class -- it may be a skill class from an another course. Appears that this can be a weakness from the whole model, nothing related to your SQL technique -- or possibly I don't comprehend the underlying model and all sorts of special constraints you might have. However, here's one particular similar model:

  • One school can provide many courses a course could be provided by several schools.
  • Each school might have specific title and outline for any "generic" course.
  • One certificate requires several courses a course might be needed by many people certificates.

    alt text

I'd say you are fine to omit the particular school table. Aren't seeing anything wrong with this.

So far as performance goes: I am not necessarily sure, but I'd say it might be faster since you have one less table to become listed on - but I am no expert for the reason that area...