Data order different on two servers.

I have a table which is replicated on to another server. On the each server the order of data is different when you do a SELECT * .

Apart from the clustered index are there any other factors which can affect data order in a table.
Who is Participating?
jogosConnect With a Mentor Commented:
Whithout order by you get random order. That random is not so random because off the used index, the order in datapages.... but if it seems correct it is just by chance.
Aaron TomoskyConnect With a Mentor SD-WAN SimplifiedCommented:
Never depend on order without an order by clause. Databases, like harddrives, put things wherever is most convenient. If order matters, make an autoinc field you can order by.
Mr_ShawAuthor Commented:
There are a different number of pages on each server.

Do you think it could be a fragmentation issue?
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

lcohanConnect With a Mentor Database AnalystCommented:
and NEVER EVER do a SELECT * without a WHERE clause in any piece of code unless...

you are 1000% you run it against a small lookup table.

Besides that you must add a ORDER BY as mentioned above and check the Query Plan to see what index(es) is needed to help your query.
Aaron TomoskyConnect With a Mentor SD-WAN SimplifiedCommented:
Ive never worried about why exactly, as it's not important. Even on one server it's not reliable. Always use order by if the order matters at all.
lcohanDatabase AnalystCommented:
Index fragmentation has nothing to do with order shown in a SELECT *
Mr_ShawAuthor Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.