Single With Union or Double Rows | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Single With Union or Double Rows

I have a friend table that defines a users friends.
[friendsortorder] Say I want to get User #2’s friends…I would query like this:
SELECT * FROM (SELECT *, [usersortorder] as order FROM friends WHERE userid = 2 UNION ALL SELECT *, [friendsortorder] as order FROM friends WHERE friend_userid = 2) ORDER BY order ASC; As you can tell, there is a SINGLE row for the relationship. Now my application has very complex logic and I’m wondering whether it would be better to have double rows (each friendship has 2 rows, user to friend and friend to user). Do you think single or double is better? This table will have about 500 million rows all properly index
Have you checked the Estimated execution plan for those 2 queries, it is better to investigate from that side. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
@ This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
As Satya mentioned above, you can see the differences in Execution Plan. Try avoiding SELECT * and use SELECT list of column names instead.