hash table (#) order by problem with more records

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

Company Product Priority Prod. QtyProd_Plan_Date
C1 P11100
C1 P22 50
C1 P33 30
C2 P11200
C2 P42 40
C2 P53 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> 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
--|||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
> --|||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|||tilak.negi@.mind-infotech.com (tilak.negi@.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, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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
--

has data

hi

tbl_cams_uploaddetails
tbl_cms_uploadDetails
if above 2 table has data than only i want to proceed other wise it will return 1

how to do this?

Try this, although it isn't totally clear what you mean by "it will return 1".

Chris

Code Snippet

IF NOT EXISTS ( SELECT 1

FROM tbl_cams_uploaddetails )

OR NOT EXISTS ( SELECT 1

FROM tbl_cms_uploadDetails )

BEGIN

RETURN 1

END

--Insert the code you wish to run after the condition check

|||

I agree with Chirs..

Suppose if you want to proceed the data should not available on any one table then use the following code..

Code Snippet

IF NOT EXISTS ( SELECT 1 FROM tbl_cams_uploaddetails )

OR NOT EXISTS ( SELECT 1 FROM tbl_cms_uploadDetails )

BEGIN

RETURN 1

END

Suppose if you want to proceed the data should not available on both the table then use the following code..

Code Snippet

IF NOT EXISTS ( SELECT 1 FROM tbl_cams_uploaddetails )

AND NOT EXISTS ( SELECT 1 FROM tbl_cms_uploadDetails )

BEGIN

RETURN 1

END

Hardware Recommendation

Hello,
I plan to run Microsoft CRM, Navision SQL, and Project Server off the
same SQL box. What would you recommend hardware wise?
Dual 2.8 Xeon? How much RAM? 2GB? 4GB?
Thanks in advanceFirst, put as much memory as you can afford in the box. Be aware that
anything over 4GB will require Enterprise Edition to take advantage of the
extra RAM. Dual Procs are a minimum. Go with procs with more cache rather
than faster procs, but only by 300-500 MHz. Then we geet to the disk
system. Definitely separate your SQL logs and data onto different spindles.
RAID 1 or 1+0 for logs will improve performance significantly. RAID 1+0 for
data if you can afford it, RAID5 otherwise, but it will impact performance.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"CB" <chadb@.dentistryonline.com> wrote in message
news:esoMsGvMEHA.3096@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I plan to run Microsoft CRM, Navision SQL, and Project Server off the
> same SQL box. What would you recommend hardware wise?
> Dual 2.8 Xeon? How much RAM? 2GB? 4GB?
> Thanks in advance
>