September 29, 2005 at 4:38 pm
I have a Question about the Internal Infrestructure of SQL Server?
It is true that SQL Server generates intenal Index when you creates a relation between 2 tables Child and Parent, its creates internal index by the fields related into the child table?
If this is not true, it is a good practice to create the index into the child table by every relation with a parent table.
I you have any documentation that can explainme better this, I will pareciate a lot.
September 29, 2005 at 6:16 pm
This is not true for SQL Server but is true for some other RDBMS such as Sybase's SQL Anywhere.
It is not good practice to ALWAYS create an index on the foreign key columns.
For example, if you had a table "Gender" with 2 rows for the values "Male" and "Female" and table Employees has a foreign key constraint to the Gender table but there is no SQL statement that has "WHERE Employee.Gender = ?", then the index would never be used but would decrease update performance on the Employee table.
SQL = Scarcely Qualifies as a Language
September 30, 2005 at 11:51 am
The field(s) referenced by a foriegn key must have a unique constraint, which will have an index. It could be a primary key or a unique index, but even a simple unique constraint will have an index.
This index then must exist before the foreign key relationship is added, so it doesn't have to generate another when you create the relation.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply