I am moving a very large MDB to SQLServer v2005. The MDB has two tables, a primary table with s TEXT field as the primary key field and a second table with a TEXT field and a SINGLE field as the key:
there is full referential integrity between these 2 tables (cascade update, cascade delete)
I read in a SQL book that when defining relationships I should determine if the primnary key in the parent table can be improved upon by using a Integer data types to increase the speed of the relationship. So add an indentiy field to the parent table and use that as the primary key instead of the text field.
it goes on to say that "with the new key, queries that join the table to other tables based on this key will preform much faster and db will be smaller"
what I don't understand is should I add an indentiy field to the secondary table? otherwise how does the indenty field in the child table get to be the same as the parent table's indentity field.