DBCC INDEXDEFRAG

We are currently running SQL Server 2000 on single box for the last 2 years.
There were some performance issues on the database and after analysing I saw
that the database required defragmentation. So I executed DBCC INDEXDEFRAG
on all the tables and things went back to normal in terms of the
performance. After two days our application users complained about some un
expected results. After the analysis following is the information.
The table that has 3 columns
1. TarifTypeKey, LanguageKey and Description
2. There is a composite clustered index on TariffTypeKey and LanguageKey
3. The table returns only 5 rows in the following format
1. Download
2. Upgrade
3. Deletion
4. Force
5. Print
4. These values are returned back from the database from a Stored Procedure
and following is the code
SELECT TariffTypeKey, Description
FROM TariffTypesDescription
WHERE LanguageKey = 'EN'
The same query worked fine since the first day. But since I have executed
the script to defragment the indexes we get back results in the following
order
5. Print
3. Deletion
1. Download
2. Upgrade
4. Force
Nothing has changed in the stored procedure. I did some more research and
tried to break down the query into two parts
1. SELECT TariffTypeKey, Description
FROM TariffTypesDescription (until here we get the desired results)
2. WHERE LanguageKey = 'EN' (as soon as we use this the results are changed)
Kindly provide some of your knowledge on this as this is on the live
database. Please do not hesitate to contact me if you need further
information.
-Saj> 1. SELECT TariffTypeKey, Description
> FROM TariffTypesDescription (until here we get the desired results)
> 2. WHERE LanguageKey = 'EN' (as soon as we use this the results are
> changed)
The engine is free to produce the resultset in ANY ORDER if the query does
not have an order-by clause. Your assumption based on past experience is
the problem.|||Thanks a ton for everyone who replied to this question as I already fixed
the problem using ORDER BY clause but I was wondering how come the orders of
the rows changed.
I appreciate it.
"Sajid S. Malik" <sajid_malick@.yahoo.com> wrote in message
news:ucoSH0D$GHA.4704@.TK2MSFTNGP04.phx.gbl...
> We are currently running SQL Server 2000 on single box for the last 2
> years. There were some performance issues on the database and after
> analysing I saw that the database required defragmentation. So I executed
> DBCC INDEXDEFRAG on all the tables and things went back to normal in terms
> of the performance. After two days our application users complained about
> some un expected results. After the analysis following is the information.
> The table that has 3 columns
> 1. TarifTypeKey, LanguageKey and Description
> 2. There is a composite clustered index on TariffTypeKey and LanguageKey
> 3. The table returns only 5 rows in the following format
> 1. Download
> 2. Upgrade
> 3. Deletion
> 4. Force
> 5. Print
> 4. These values are returned back from the database from a Stored
> Procedure and following is the code
> SELECT TariffTypeKey, Description
> FROM TariffTypesDescription
> WHERE LanguageKey = 'EN'
> The same query worked fine since the first day. But since I have executed
> the script to defragment the indexes we get back results in the following
> order
> 5. Print
> 3. Deletion
> 1. Download
> 2. Upgrade
> 4. Force
> Nothing has changed in the stored procedure. I did some more research and
> tried to break down the query into two parts
> 1. SELECT TariffTypeKey, Description
> FROM TariffTypesDescription (until here we get the desired results)
> 2. WHERE LanguageKey = 'EN' (as soon as we use this the results are
> changed)
> Kindly provide some of your knowledge on this as this is on the live
> database. Please do not hesitate to contact me if you need further
> information.
> -Saj
>