asked
Add Order by column to table
In my project, I have 12 tables. Let take 2 of them as sample: SaleOrder PurchaseOrder Date SaleOrdId Qty UnitPrice Amount || Date PurchOrdId Qty UnitPrice Amount 4/2/12 S_1 2 600 1200 6/2/12 P_1 50 500 25000 7/2/12 S_2 5 600 3000 SaleordId and Purchordid are the primary keys. From these, I am Generating a Product Inquiry Report like: Product Inquiry Report Date TranId QtyPurchased QtySold Balance 4/2/12 S_1 2 20 (previous) 6/2/12 P_1 50 70 7/2/12 S_2 5 65 For Now,When i Select data from both the tables by union, I use Order by 'Date'. But In practice I think it may cause problems if there is some problem with system Date and time. So what i have decided is to add an extra Column 'OrdCol' to both the tables and maintain a separate table for that and keep it primary key there like: SaleOrder PurchaseORder OrderTbl Date OrdCol ----- Date OrdCol ----- OrdCol 1 2 1 3 2 3 Is my idea correct in terms of DB? Is Not, then any other idea? I cant apply order by 'Primary keys' of the tables(SO and PO) as they are in nvarchar and there numeric part can be same for both.
This web site operates by you voting. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer below lead to a solution, indicate this by clicking on the check mark next to that answer.
I'm not sure why you'd need a table for that. Isn't the order by column just a priority more than anything? Or are you planning on using the other table to create an incremental value and assign that incremental value? If the latter, yeah, a table might be the best way to go with SQL Server 2008. If you were on 2012 or better you could look at the [SEQUENCE object][1] instead. [1]:
http://msdn.microsoft.com/en-us/library/ff878091.aspx
The use of the date as your clustering key is fine. As a primary key, I wouldn't use it. But your primary key is not required to be your clustering key. So, if you choose to use a Sequence or Identity or even a GUID as your primary key that is fine because you can still use the Date field as your ordering key (your clustered index / clustering key) to achieve better performance for those queries that utilize that order.
16 People are following this question.
