Re: foreign key references only part of composite PK - table scan issue

On Aug 9, 1:19 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Dave (djohanns...@xxxxxxxxx) writes:
yeah you're right, you cannot have a foreign key that references only
part of a composite key.  i should have left that part out.  the join
DOES however only reference 1 of the 2 fields in the composite PK in
table ABC, causing the table scan.

So there are two join queries where this happen? Well, the first course
of action must be to determine what is the correct ways to write
these queries. Then you can start to add columns if there are any
missing.

You say joins, and if they are true joins it sounds fishy to join on
only part of a key, because that tend to bring in duplicates. But if
the SELECT includes a DISTINCT, but no column from table ABC, it could
be the case of a join that should have been written with EXISTS. And
in that case, at least the join from DEF to the first column in PK could
be OK.

As for the table scan, there is nothing that says that you would get
a table scan because you join only against the first column in the PK.
You could just as well get a table scan if you add a second column as
well, if the optimizer finds that a merge or a hash join is the best
way to do the join.

It's another matter with the join from MNO where you join only on the
second column in the key; here you can at best get an index scan if
the key is nonclustered. So here you would need to add an index on
this second column in ABC. All provided that the query is correctly
written, that is.

--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

thanks for your efforts, you may have a miscommunication though.
there is only 1 join with 3 tables at play, where an index scan
(essentially a table scan) is at play. i won't explain the table
layouts again though - so no worries. thanks again.

dave
.

Relevant Pages

  • Re: foreign key references only part of composite PK - table scan issue
    ... part of a composite key. ... a table scan because you join only against the first column in the PK. ... You could just as well get a table scan if you add a second column as ... Links for SQL Server Books Online: ...
    (comp.databases.ms-sqlserver)
  • Re: Query a table w/o using column names?`
    ... Are you ready to upgrade to SQL Server? ... So for instance, the first column in the linked sheet might be called for one person, for another person, and for still another. ... I use it to dynamically build SQL statements, and from a purely functional standpoint, it works. ...
    (microsoft.public.access.queries)
  • Re: Log Parser - Telephone call log analysis
    ... Looking for a SQL Server replication book? ... I have a PBX that dumps call log data ... column to sql-db1.table1.field1, second column to sql-db1.table1.field2, ...
    (microsoft.public.sqlserver.tools)
  • Re: Using SQL substring query in vb.net app
    ... the first column will be coming ... > I have a webform form app in vb.Net that uses a SQL query. ... use the substring function, ...
    (microsoft.public.dotnet.languages.vb)
  • Re: SPs which return one value
    ... >> first row and first column of the SQL query, so it is up to you to ensure ... >> ignore everything execept the value from the first column and row. ... SQL then that's what the return value from ExecuteNonQuery is ...
    (microsoft.public.dotnet.framework.adonet)