Set database to Read Only alters order of pk index?

Question

Set database to Read Only alters order of pk index?

Dennis-509860

Old Hand

Points: 351
More actions
February 3, 2011 at 4:41 am

#233053

Sorry for the confusing subject line 😛
In short: I have a database with a table. The table has a PK (ascending) on an ID column (int).
When I do a select * from TheTable I get the rows in order of the PK, e.g.,
ID TheData
1 Data1
2 Data2
3 Data3
But when I set the database to ReadOnly, if I run the same query the "ID" column is not in order anymore. Does anybody know why this may be?
Thanks!
Dennis

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

The only way to guarantee the order of returned data is to use ORDER BY on your query, the index order does not guarantee it everytime.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

The technical reason is that once the DB is read only, there's no need to worry about pages changing underneath the query, so an allocation order scan can be run rather than a index order scan.

Short answer - no Order by, no guarantee of order, the query optimiser and query processor are free to use operators and scan types without concern as to the order of the returned data.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dennis-509860 Old Hand Points: 351 More actions · Answer 3

Thanks to everybody for the quick responses.

Yes, I understand that without a "where" clause there is no guarantee of the row order, but I just thought it was very strange how the ID's would immediately be scattered about just by setting the DB to read only.

Thanks for the answer!