Can referential integrity column be indexed.

Question

Can referential integrity column be indexed.

kiran-670359

SSC Eights!

Points: 853
More actions
March 11, 2008 at 12:33 am

#120456

Hi all,
🙂 In my application,when i try to query data from two tables giving the joining condition as where tableB.bid=tableA.id,here my parent table is tableA and child table is tableB,
1.Will the order of joining have any effect on performance?
(tableA.id=tableB.bid or tableB.id=tableA.id)
2.i have non-clustered index on id of tableA and non-clustered index on id of tableB,suppose if i also index the foriegn key column of tableB bid,will that effect my performance?
Kiran

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Hi

1 - Order should not affect the performance. Order does not affect the execution plans made by SQL Server. It then uses these plans when u execute the queries/procs.

2 - Is'nt id in tableB Fkey to id in tableA. Or do u mean creating a clustered index on id column in tableb.

"Keep Trying"

Ian Yates SSCoach Points: 19738 More actions · Answer 2

You should index the foreign key constraint column.

In Query Analyzer, before you execute your query, press Ctrl+K. This will turn on showing the execution plan. Then, once you hit F5 to run your query, you can see if SQL Server is doing table/clustered index scans on your table B. If so, this is not ideal. If you create an index on the join column then you should see it use that index instead.

After that you could avoid the bookmark / RowID lookups by making the index a "covering" index. Pretty much this means that you add as subsequent columns of the index those columns that you're also retrieving from table B in the query. This means that SQL Server can get everything for your query from the single index rather than using both the index and the table (which is still often faster than just using the table on its own).

kiran-670359 SSC Eights! Points: 853 More actions · Answer 3

currently i'm in a project that deals with more than 130 tables,apart from the indexing primary keys if i start indexing foriegn keys then apart from performance being improved ,will it does not lead to disadvantages caused by overindexing?Or u mean to say that index only those foriegn keys that i'm using for joining?

Kiran.

Kiran

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Kiran,

I recommend that you spend a couple of hours reading about indexes in Books Online (comes free with SQL Server). Start by reading "Indexes, Overview" in the index tab and drill down from there.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)