Geek Answers Handbook

Composite primary key / clustered index, fragmentation, performance

After 20 years of professional development, I still feel completely ignorant when it comes to some aspects of database performance. This is just the case. There are thousands of questions here and elsewhere regarding fragmentation of the table and index and its impact on performance. I know the basic things and don’t do it, but there are times when it seems that there is no “good” answer. Here is my problem, and I often come across it:

The table is only used to store pairs of identifiers that define a one-to-many relationship, and use the friends example. The friends table contains only personId (int), friendId (int) . Each pair, of course, is unique. (And, therefore, but probably not related to the issue, an inverse pair to each relationship is also present.) Thus, a very small sample of data will be:

1001, 1011
1001, 1012
1001, 1013
1011, 1001
1012, 1001
1013, 1001
etc...

1001 3 , , , 1001 .. , , (), , . ( , , ) . personId, friendId, , .

, , .., , " ". , , , , , . :

SELECT p.Name FROM Friends f
INNER JOIN People p ON f.friendId = p.id
WHERE f.personId = @personId

, , personId, friendId, SQL Server . , , . , , . :

  • ?

  • , int , , , , , , , ?

( RDBMS - , , , - . , , - . BTW, SQL Azure, .)

, , DBA !

+4
2

, . - , , . , , , ( row_id), . , .

, :)

+4

(CTAS ..) .

, - , SQL wd JOINing "" "", .

, , -, SQL :

SELECT f.*, p.NAME FROM 
(
SELECT personId, friendId FROM Friends f
WHERE f.personId = @personId
) f
, People p ON f.friendId = p.ID

...

-1

More articles:


All Articles