2

I have the following table situation

  • Movie (int movieId PK)
  • Cast (int castId PK)
  • MovieHasCast (movieId, castId Unique Constraint)

When I insert the cast, they are inserted in a specific order (i.e. as they would appear on say IMDB). However, when I do a select they seem to come back in a different order.

My problem with this is 2 things.

  1. I have an Equals method that is failing due to the order and don't want to implement additional code to handle the out of order names, and
  2. The order really should be maintained for purposes of "top billing" where the primary or most well known cast members appear first. Obviously this would not be done on the Cast or the Movie table, and I don't think I have ever heard of anyone ever having an Identity or Sequence column on a relational table.

So I am wondering:

  1. Why are they not returning back to me in the order inserted
  2. Is my only solution to add an additional column on the relational table (whether it is a rank value or identity column and do a sort)
Kairan
  • 5,342
  • 27
  • 65
  • 104

1 Answers1

1

As has been asked and answered before on Stack Overflow, you cannot get the records out in the same order in which they were inserted. More specifically, the SQL Server engine makes no such gurantee. The reason for this is that your database engine needs to be free to store those inserted records how ever it wants to be efficient and optimal.

If you want to maintain the order during insertion, you could create an autoincrement column. This column would keep count of the insertion order for cast members, and you would be able to query out the original order.

Community
  • 1
  • 1
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thanks, my google fu skills were not coming up with much of a different answer than my initial assumption of using an Identity column – Kairan Mar 29 '15 at 03:55