hash table (#) order by problem with more records

[tilak.negi]

We have one single hash (#) table, in which we insert data processing
priority wise (after calculating priority).
for. e.g.

Company Product Priority Prod. Qty Prod_Plan_Date
C1 P1 1 100
C1 P2 2 50
C1 P3 3 30
C2 P1 1 200
C2 P4 2 40
C2 P5 3 10

There is a problem when accessing data for usage priority wise.
Problem is as follows:

We want to plan production date as per group (company) sorted order and
priority wise.

==>With less data, it works fine.
==>But when there are more records for e.g. 100000 or more , it changes
the logical order of data

So plan date calculation gets effected.

==Although I have solved this problem with putting identity column and
checking in where condition.

But, I want to know why this problem is coming.

If anybody have come across this similar problem, please let me know
the reason and your solution.

IS IT SQL SERVER PROBLEM?

Thanks & Regards,
T.S.Negi

[David Portas]

> when there are more records for e.g. 100000 or more , it changes

the logical order of data

Are you referring to the perceived order in the table? Rows in tables
have NO logical order in a relational database. If you require a
particular order you have to query them using a SELECT statement with
an ORDER BY clause otherwise the ordering is undefined.

If that doesn't answer your question then please describe your problem
with DDL (including keys), sample data INSERT statements and show your
required end result.

--
David Portas
SQL Server MVP
--

[tilak.negi]

While inserting records in hash table. It is already order by on some
fields.
But when selecting/updating records, I want the same order of records
should be updated/selected.

"Rows in tables have NO logical order in a relational database"
I think, True for hash(#) and permanent table.

T.S.Negi

David Portas wrote:

when there are more records for e.g. 100000 or more , it changes
the logical order of data
Are you referring to the perceived order in the table? Rows in tables
have NO logical order in a relational database. If you require a
particular order you have to query them using a SELECT statement with
an ORDER BY clause otherwise the ordering is undefined.

If that doesn't answer your question then please describe your

problem with DDL (including keys), sample data INSERT statements and show your required end result.

--
David Portas
SQL Server MVP
--

[tilak.negi]

There is an update condition. Which I want to make sure, performing on
ordered data (order by used at the time of insert).
I want to avoide loop.

Reason: "Rows in tables have NO logical order in a relational database"
!!!!

So Please advice.
Thanks,
T.S.Negi

Sample SQL:
===========

UPDATE #WK_PDR_ProcessingData SET
@Opn_Stock_Qty= CASE WHEN (
@Customer_Cd = Customer_Cd
AND @Product_No = Product_No
AND @Product_Site_Cd = Product_Site_Cd
AND @Assy_Company_Cd = Assy_Company_Cd
AND @Assy_Section_Cd = Assy_Section_Cd
AND @Line_Cd = Line_Cd
) THEN @Opn_Stock_Qty + @Production_Qty - @Requirement_Qty
ELSE begin_Stock_Qty END,
Calc_Stock_Qty= @Opn_Stock_Qty + Production_Qty - Requirement_Qty,
@Customer_Cd = Customer_Cd,
@Product_No = Product_No,
@Product_Site_Cd = Product_Site_Cd,
@Assy_Company_Cd = Assy_Company_Cd,
@Assy_Section_Cd = Assy_Section_Cd,
@Line_Cd = Line_Cd,
@Production_Qty = Production_Qty,
@Requirement_Qty = Requirement_Qty
FROM #WK_PDR_ProcessingData

[Erland Sommarskog]

ti********@mind-infotech.com (ti********@mind-infotech.com) writes:

While inserting records in hash table. It is already order by on some
fields.
And once it is inserted, there is no longer any order.
But when selecting/updating records, I want the same order of records
should be updated/selected.

"Rows in tables have NO logical order in a relational database"
I think, True for hash(#) and permanent table.

Well, obviously you have some operation that does not give you the
desired result, and you posted an UPDATE statement, which is a little
funny, because all you do is to assign a variable.

I suggest that you follow the standard recommendation and post:

o CREATE TABLE statement for your table(s)
o INSERT statements with sample data.
o The desired result given the sample.
o A short narrative of what ou are trying to achieve.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[David Portas]

UPDATEs are not ordered either. The result of your UPDATE statement is
undefined, unreliable and, in my view, not useful.

Please specify the whole problem rather than post fragments of your
non-working solution. The best way to specify the problem is to post
DDL, sample data and required end results. See:
http://www.aspfaq.com/etiquette.asp?id=5006

--
David Portas
SQL Server MVP
--