I detached a SQL 7 DB, copied it to a server running SQL
2000 and attached it.
The indexes don't appear to be working. I've reindexed
the tables and executed a simple query that should
display the data in date order, but it's displayed in a
random order.
The same query on the original DB in SQL 7 shows the data
in date order.
Anyone know what's going on?
There's only one index. It's a nonclustered index on two
fields. Name+date.
Thanks,
DonThis is a multi-part message in MIME format.
--=_NextPart_000_02C4_01C3B29B.F1DDDD30
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Two things:
1. Order is not guaranteed, unless you include an ORDER BY in your
SELECT.
2. You should update statistics on all user tables (preferably WITH
FULLSCAN) when upgrading a database from 7.0 to 2000.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Don" <ddachner@.hotmail.com> wrote in message
news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
I detached a SQL 7 DB, copied it to a server running SQL
2000 and attached it.
The indexes don't appear to be working. I've reindexed
the tables and executed a simple query that should
display the data in date order, but it's displayed in a
random order.
The same query on the original DB in SQL 7 shows the data
in date order.
Anyone know what's going on?
There's only one index. It's a nonclustered index on two
fields. Name+date.
Thanks,
Don
--=_NextPart_000_02C4_01C3B29B.F1DDDD30
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Two things:
1. Order is not =guaranteed, unless you include an ORDER BY in your SELECT.
2. You should update =statistics on all user tables (preferably WITH FULLSCAN) when upgrading a database =from 7.0 to 2000.
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Don"
wrote =in message news:047601c3b2c4$e3=e7cfa0$a301280a@.phx.gbl...I detached a SQL 7 DB, copied it to a server running SQL 2000 and =attached it.The indexes don't appear to be working. I've reindexed the =tables and executed a simple query that should display the data in date order, =but it's displayed in a random order. The same query on the original DB =in SQL 7 shows the data in date order.Anyone know what's going =on?There's only one index. It's a nonclustered index on two fields. Name+date.Thanks,Don
--=_NextPart_000_02C4_01C3B29B.F1DDDD30--|||Hi Don
The only queries that 'should' display data in a particular order are
queries that contain ORDER BY. If you were formerly getting data back in a
preferred order, you were just lucky. It was never documented or supported
that any query, using any index, would return data in any speficic order
without using ORDER BY.
OTOH, having a good index can help speed up the order by, and possibly make
a sort unnecessary, but the ORDER BY must be there in order to guarantee the
order of the rows in the result set.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Don" <ddachner@.hotmail.com> wrote in message
news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
> I detached a SQL 7 DB, copied it to a server running SQL
> 2000 and attached it.
> The indexes don't appear to be working. I've reindexed
> the tables and executed a simple query that should
> display the data in date order, but it's displayed in a
> random order.
> The same query on the original DB in SQL 7 shows the data
> in date order.
> Anyone know what's going on?
> There's only one index. It's a nonclustered index on two
> fields. Name+date.
> Thanks,
> Don
>|||Thanks for you help. I didn't realize I was just being
lucky :-)
Don
>--Original Message--
>Hi Don
>The only queries that 'should' display data in a
particular order are
>queries that contain ORDER BY. If you were formerly
getting data back in a
>preferred order, you were just lucky. It was never
documented or supported
>that any query, using any index, would return data in
any speficic order
>without using ORDER BY.
>OTOH, having a good index can help speed up the order
by, and possibly make
>a sort unnecessary, but the ORDER BY must be there in
order to guarantee the
>order of the rows in the result set.
>--
>HTH
>--
>Kalen Delaney
>SQL Server MVP
>www.SolidQualityLearning.com
>
>"Don" <ddachner@.hotmail.com> wrote in message
>news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
>> I detached a SQL 7 DB, copied it to a server running
SQL
>> 2000 and attached it.
>> The indexes don't appear to be working. I've reindexed
>> the tables and executed a simple query that should
>> display the data in date order, but it's displayed in a
>> random order.
>> The same query on the original DB in SQL 7 shows the
data
>> in date order.
>> Anyone know what's going on?
>> There's only one index. It's a nonclustered index on
two
>> fields. Name+date.
>> Thanks,
>> Don
>
>.
>|||I didn't know that about the ORDER BY.
Thanks for your help. I'll do the Scan update statistic.
Don
>--Original Message--
>Two things:
>1. Order is not guaranteed, unless you include an
ORDER BY in your
>SELECT.
>2. You should update statistics on all user tables
(preferably WITH
>FULLSCAN) when upgrading a database from 7.0 to 2000.
>--
>Tom
>----
--
>Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>SQL Server MVP
>Columnist, SQL Server Professional
>Toronto, ON Canada
>www.pinnaclepublishing.com/sql
>
>"Don" <ddachner@.hotmail.com> wrote in message
>news:047601c3b2c4$e3e7cfa0$a301280a@.phx.gbl...
>I detached a SQL 7 DB, copied it to a server running SQL
>2000 and attached it.
>The indexes don't appear to be working. I've reindexed
>the tables and executed a simple query that should
>display the data in date order, but it's displayed in a
>random order.
>The same query on the original DB in SQL 7 shows the data
>in date order.
>Anyone know what's going on?
>There's only one index. It's a nonclustered index on two
>fields. Name+date.
>Thanks,
>Don
>
