Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
deepakugale
Starting Member
33 Posts |
 Posted - 2010-02-16 : 04:51:32
|
| HI All ,Can i partition a existing with millions of rows ? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-16 : 05:50:53
|
| Yes, but you will have to "move" some of the rows to new tables I think.I think you can use the CREATE PARTITION function / scheme which makes the job easier, but AFAIK you still have to create the individual tables and move the data into them (I don't think SQL Server does it for you!) |
 |
|
|
deepakugale
Starting Member
33 Posts |
Posted - 2010-02-16 : 06:14:27
|
| Thanks for your great help Kristen ,But in my requirement,i have to maintain all data in same table ,so this means table partition will not help me ,right ? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-16 : 07:39:34
|
"i have to maintain all data in same table ,so this means table partition will not help me , right ?"Correct, but why do you think you have to maintain all data in the same table?For example: you have a table, say, MyOrdersYou make new tables called MyOrders01 ... MyOrders99 and you MOVE data from MyOrders into the appropriate table (lets say based on Order_Date, or maybe based on Order_Number).Then you DROP MyOrders tableThen you create MyOrders VIEW - which is a UNION ALL of MyOrders01 ... MyOrders99(You have to do some other things to make this into a Horizontal Partition, but what I have described is basically how it works)Now the important bit:Your application carries on SELECTing, INSERTing, UPDATEing, DELETEing from "MyOrders" as if nothing had changed ...... only it does it much faster because SQL knows that the View MyOrders is a Partiion of tables MyOrders01 ... MyOrders99 and knows which table to query - thus reducing the size of each query by up to 99%  |
|
|
|
deepakugale
Starting Member
33 Posts |
Posted - 2010-02-17 : 00:18:34
|
| Hi Kristen ,Its nice idea of making different tables as you suggested (MyOrders01 ... MyOrders99) and then creating view on these tables ,surely it will boost the performance.But in my case there are some stored procedure already written on table myorder which are responsible for SELECTing, INSERTing, UPDATEing, DELETEing.So if i change the main table MyOrders in to multiple table these stored procedures will have to be change. Will SELECTing, INSERTing, UPDATEing, DELETEing on view will affect the base tables respectively ? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-17 : 02:49:08
|
| "So if i change the main table MyOrders in to multiple table these stored procedures will have to be change."probably no changes required."Will SELECTing, INSERTing, UPDATEing, DELETEing on view will affect the base tables respectively"Yes "should do" - that's the theory anyway. |
|
|
|
|
|
|
|
|
Table Partition