April 26, 2011 at 10:16 pm
Hello,
We have a program written in Visual Dataflex however despite the index showing in SQL 2005 as ascending the data shown in our program is not reflecting that.
We had this running on SQL Server 2005 on another server and it was fine. But this issue has occurred since we were forced to move everything to a new machine. We tried a fresh install of SQL Server 2005 on another machine and had exactly the same problem. We have tried using the latin1_CI_AS collation and SQL_LATIN1_CI_AS as default collation but neither has made any difference.
Environment:
Original was: Server 2008 R1 with SP1
New is: Server 2003 with SP3
All are 64 bit environments.
Database Server: SQL Server 2005 SP 2 and SP3
We have tried service packing SQL but to no avail. We've tried different collations as above. The studio enforces that ARTHIBORT must be set to on in SQL in order to use uppercase indexes.
We have done a thorough search on the internet and can't find any clues as to how to solve this issue so any suggestions would be greatly appreciated.
It happened after we moved the databases to another server
Thanks In Advance
April 27, 2011 at 12:12 am
Indexes do not ensure that data is returned in sorted order. To ensure that a dataset is sorted you need to include an ORDER BY clause in the outer query returning the data.
April 27, 2011 at 12:20 am
Just Try to rebuild column index in ASC order and see the result
April 27, 2011 at 12:48 am
srikant maurya (4/27/2011)
Just Try to rebuild column index in ASC order and see the result
The only way to guarantee order in a result set is to include an ORDER BY clause in the outer query (final) query returning the data. The indexes do not ensure order of the returned data.
April 27, 2011 at 4:40 pm
Listen to Lynn. If you do not have "ORDER BY", by definition, the order of rows is arbitrary! Remeber that. That means db engine will give you results in any order it currently thinks it is easier. For example, most of the times it will use index and read rows in that order, but sometimes some pages of data will be cached and others not, so it will return you first those cached, which are not necessarily the first and in the order of the index. ORDER BY is the only guarantee of the order - don't be afraid to use it 😉
April 27, 2011 at 4:51 pm
Do not count on the index rebuild to fix this. As mentioned, an ORDER BY is required.
If anything was "working" before, it was coincidence, not causality. You could get the index order, but it could also return out of order if that were more efficient for SQL Server.
April 27, 2011 at 5:18 pm
Also asked here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159951
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
April 28, 2011 at 3:44 am
I would suggest you ask your question on one of the Visual Dataflex forums:
April 28, 2011 at 12:16 pm
My 2cents,
Enable Full text search functionality, Create a Full Text Index, which will organize your data according to your liking Asc or Desc on your choice of columns. Then drop the index.
April 28, 2011 at 12:35 pm
There is still no guarantee of the ordering of the data. Regardless of how it is stored, it will not be returned by a SELECT in that order without an ORDER BY. It may be, but it is not assured.
If you need it ordered, you have to use an ORDER BY or sort on the client.
April 28, 2011 at 12:40 pm
yes, ORDER BY Clause will ensure a sorted list.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply