Well, after how many hours of troubleshooting and making queries, I found out that the queries were not the source of error but the database table itself. The ordering of tables are not what I expected to happen, it should be ascending.
I added data on tblCarFranchise table, as expected, this will happen. The ID column is well sorted in ascending order.
Next, I deleted row 2 with the ID = 12.
Then added another data row.
This is where the problem lies, it is inserted between 11 and 13! I don't exactly know why this is happening. Again, I added another row to see if it still goes in between 11 and 13, and this is the thing that happened:
Can someone tell me how to fix this? I have already searched for solutions like "SQL Server index ordering", "SQL Server index ascending" but the search results give me a sql queries instead. -_-
Not really an answer, because this isn't really a question; however, my response would be too long to fit in the comments section. As Gordon already pointed out, tables are inherently unordered. Any order that you are seeing in other tables is probably the result of the following circumstances:
If you want rows to be returned in a certain order, then use an ORDER BY clause. Otherwise, don't worry about it. Add indexes as appropriate, and maintain them, but let the optimizer choose the best way to work with your tables.