[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 222
  • Last Modified:

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.
0
Mr_Shaw
Asked:
Mr_Shaw
  • 2
  • 2
  • 2
  • +1
4 Solutions
 
Aaron TomoskyTechnology ConsultantCommented:
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.
0
 
Mr_ShawAuthor Commented:
There are a different number of pages on each server.

Do you think it could be a fragmentation issue?
0
 
lcohanDatabase AnalystCommented:
And NEVER do a SELECT *
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.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Aaron TomoskyTechnology ConsultantCommented:
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.
0
 
lcohanDatabase AnalystCommented:
Index fragmentation has nothing to do with order shown in a SELECT *
0
 
jogosCommented:
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.
0
 
Mr_ShawAuthor Commented:
thanks
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now