general comment is required

Hi,
I am trying to implement master detail kind of functionality. table1 has
primary key t1 and table2 has primary key t2. I have not placed any
relationship on these tables. these tables are free tables.
what will happen if I place foreign key will search sort become faster ?
assume data is perfect with/without relationship, so main purpose of having
foreign key relationship is almost equall to 0.
your comments are required.
Kishorkishor (kishor@.discussions.microsoft.com) writes:
> I am trying to implement master detail kind of functionality. table1 has
> primary key t1 and table2 has primary key t2. I have not placed any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become faster ?
> assume data is perfect with/without relationship, so main purpose of
> having foreign key relationship is almost equall to 0.
You don't add foreign keys fort sorts to make faster (and I find it
difficult a imagine where an FK would affect the speed of a sort). You
add foreign-key constraints to enforce referential integrity.
If you have, say, an Orders and an OrderDetails table, the Order table
would have OrderID as its primary key, and OrderDeatails would have a
two-column of OrderID and some other column (e.g. RowNo or ProductID).
Furthermore OrderDetails would have a FK constraint to say that any
OrderID must be an existing OrderID in Orders. This constraint applies
both when inserting into OrderDetails and when deleting from Orders.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Yes, there will be a difference in performance.
BOL states that a FK relationship b/w 2 tables indicates that the 2 tables
have been optimized to be combined in a query that uses the keys as its
criteria.
Ideally, u shud maintain an index on the FK columns for even better
performance of ur queries. I doubt if the index is maintained in the 2 cases
mentioned (with & without FK), will there be any speed difference or not?
Probably, not.
- R
"kishor" wrote:

> Hi,
> I am trying to implement master detail kind of functionality. table1 has
> primary key t1 and table2 has primary key t2. I have not placed any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become faster ?
> assume data is perfect with/without relationship, so main purpose of havin
g
> foreign key relationship is almost equall to 0.
> your comments are required.
> Kishor
>
>|||If a FK relationship makes sense there is always a benefit for using it
and keeping therefore referential integrity, but just using a FK does
NOT mean do use an index. FK can be also place on columns which aren=B4t
indexed, so you should be aware not to mix that up. But I hope that you
are in the design phase and not the reengineering phase, because your
thoughts should take place in the designing phase.
HTH, jens Suessmeyer.|||kishor wrote:

> Hi,
> I am trying to implement master detail kind of functionality. table1 has
> primary key t1 and table2 has primary key t2. I have not placed any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become faster ?
> assume data is perfect with/without relationship, so main purpose of havin
g
> foreign key relationship is almost equall to 0.
> your comments are required.
> Kishor
You say you assume the data is perfect but how will you enforce that
rule without a foreign key? If you enforce it only in your application
then you have to duplicate that integrity feature for each application
or data entry screen. A bug in any one place could affect all other
users of the data. When you come to write queries against the data, the
server won't see a referential integrity constraint so it won't be able
to use that constraint to help construct a better query plan.
Always declare the constraints that are required to enforce your
business rules.
David Portas
SQL Server MVP
--|||"kishor" <kishor@.discussions.microsoft.com> wrote in message
news:D8FDB80A-F7E6-4600-8BED-B20A80CD5815@.microsoft.com...
> Hi,
> I am trying to implement master detail kind of functionality.
table1 has
> primary key t1 and table2 has primary key t2. I have not placed
any
> relationship on these tables. these tables are free tables.
> what will happen if I place foreign key will search sort become
faster ?
> assume data is perfect with/without relationship, so main purpose
of having
> foreign key relationship is almost equall to 0.
> your comments are required.
> Kishor
>
kishor,
Establishing a foreign key constraint is done for the purpose of
enforcing referential integrity. These constraints should always
been done where cardinality indicates it. Referential integrity is
one of the key features of an RDBMS, avoiding its use would be
substantially less than ideal (I would almost go so far as to say,
"Why have an RDBMS if you don't bother with referential
integrity?").
The foreign key constraint may be enforced by the use of an index
(in SQL Server, it usually is). Indexes can improve performance on
some types of queries (SELECT), and the query optimizer may or may
not use that index on those queries depending on *many* other
factors. Indexes may degrade performance on some types of queries
(UPDATE, INSERT, DELETE). But these considerations are entirely
beside the consideration of enforcing referential integrity.
RI provides for database consistency. Without it, your database
may, without any warning, enter an "inconsistent state". If you
encounter such an error in your database(quite likely without
enforced RI, IMO), I wish you luck in fixing it.
Sincerely,
Chris O.