how to Order By Insert Command in sql server 2005 using vb.net

Question

hi fellows

I have a table in sql server 2005 like Voucher and in Voucher table there 5 columns like VoucherID,Date,AccountCode,AccountName & Type. Type column datatype=nvarchar(15) and I want to Order By clause in Type column.

I am saving data from 5 different forms of vb.net into voucher table and I want to Order By Clause against Type column when I saving data like below

VoucherID      AccountCode   AccountName        Type

BP1                           0001        ABC                     BP

BP2                           0002        ABC                     BP

CP1                           0001        ABC                     CP

CP2                           0002        ABC                     CP

BR1                           0001        ABC                     BR

BR2                           0002        ABC                     BR

I want to typ column should be order by first all of the vouchers where Type= BP then Type=CP then Type=BR 

how to do this

 

Answer 1

Unfortunatly, the storage of your data will always be nondeterministic.  The SQL engine will never guarentee your data will ever be stored in some deterministic way.  Pages of data (and the data inside the pages) may be sorted at first, but then over time, the data within a page will be moved out of order due to data modifications (INSERTS/UPDATES/DELETES).

The only way to guarentee order is during the ORDER BY ...   clause of a SELECT query against the data.

Answer 2

you cannot save the data in a particular way but why do you care?? when you are reading the data, Try the below code and you should get the desired output....

select * from Temp1 where TYPE = 'BP'
union all
select * from Temp1 where TYPE = 'CP'
union all
select * from Temp1 where TYPE = 'BR'

Sample Ouput

 I think data will be saved as it submitted to the table unless one of these columns are clustered indexes...so, if (CP2,0001,ABC,CP) is inserted before  (BP2,0001,ABC,BP) , then (CP2,0001,ABC,CP) is saved first and then (BP2,0001,ABC,BP).So, when you do select * from table, you would see (CP2,0001,ABC,CP) row before (BP2,0001,ABC,BP) because that's how it was inserted but can you guarantee this is the way inserts would be done??(It changes if it has clustered index or if any of these columns has clustered index and I am not going into it)..so, i think if you want get the data as per your requirement, you should modify your read query(select) and get it in your way... try the above code and see..

---

Hope it Helps!!

Answer 3

thanks

your suggestion is right. Now I have decided that I have manage data in ordery by clause in only in select query which give me ever best result.