October 24, 2007 at 12:04 am
is thisposible to Partitioned the existing Tables in SQL Server 2005
if yes tell me how with demo...
October 24, 2007 at 1:40 am
varun-jha (10/24/2007)
is thisposible to Partitioned the existing Tables in SQL Server 2005if yes tell me how with demo...
Similarly to changing filegroups you cannot repartition existing tables with a single statement. What you can do is to rebuild the table.
The easiest way to get a good enough script is to use Management Studio. Click on table, edit, then in the properties change the partitioning (set dataspace type, partition scheme and the partition column).
Then you can click in the top left corner the "Save Change Script"
An example script that tells you more or less what to do is:
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_toBePartitioned1
(
a int NULL,
b int NULL
) ON ps1(a)
GO
IF EXISTS(SELECT * FROM dbo.toBePartitioned1)
EXEC('INSERT INTO dbo.Tmp_toBePartitioned1 (a, b)
SELECT a, b FROM dbo.toBePartitioned1 WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.toBePartitioned1
GO
EXECUTE sp_rename N'dbo.Tmp_toBePartitioned1', N'toBePartitioned1', 'OBJECT'
GO
COMMIT
Note that Management Studio does not always get this right, so it is important that you check what it generates. You could use this as a skeleton. Alternatively you could use third party tools too.
As always, please make a backup before moving to partitioning 🙂
Regards,
Andras
October 24, 2007 at 3:17 am
thanks for reply andras..
it will clear my concepts
thanks.
October 24, 2007 at 10:42 pm
You have a couple options to take an existing non-partitioned table and convert it to an partitioned table.
Hint: see BOL, Look for: "partitioned tables [SQL Server], modifying" in the index. In the topic, scroll down to "Converting a Nonpartitioned Table to a Partitioned Table"
Option #1
Similar to changing filegroups, you can simply recreate the clustered index on the partition function, including the DROP_EXISTING clause.
Option #2
You can also perform a partition switch (ALTER TABLE...SWITCH), where SQL Server will switch the metadata about where the data in a table is stored between two tables. The basic concept:
* Create a new empty partitioned table with only a single partition defined using the same DDL (plus the partition clause) as the existing unpartitioned table.
* Using ALTER TABLE...SWITCH, SQL Server will switch the data between the two tables by simply updating the metadata - the data from the original unpartitioned table will suddenly be 'owned' by the new partitioned table, and the data from the partitioned table (of which there is none) becomes 'owned' by the unpartitioned table. Drop/rename the original, then rename the new, and you're done. No copying necessary. (There are restrictions, of course - check the BOL topics and try it out a few times in a test environment)
-Eddie
Eddie Wuerch
MCM: SQL
October 24, 2007 at 11:19 pm
try out this link and see to that if this works for you...
October 26, 2007 at 12:12 pm
Great responses. Also, planning is KEY! I have a script that illustrates some of the partitioning limitations in 2005 at http://www.ricksql.com. I use it in my presentations when discussing this concept.
November 18, 2007 at 2:52 pm
Hello,
as I can see there are the understandalbe question and answers but can anyone explain to me about this part of SQL code
...
) on ps1(a)
...
(it is a Partition Schema)
so where the code for this schema! - little bit confused!!!???
thnx!
November 19, 2007 at 1:43 am
Dugi (11/18/2007)
Hello,as I can see there are the understandalbe question and answers but can anyone explain to me about this part of SQL code
...
) on ps1(a)
...
(it is a Partition Schema)
so where the code for this schema! - little bit confused!!!???
thnx!
The included code sample is what Management Studio generates for rebuilding a particular table. In order to select the new partition scheme in Management Studio (or to use it in case you do the above process manually), you need to create the partition function (http://technet.microsoft.com/en-us/library/ms187802.aspx) and partition scheme (http://msdn2.microsoft.com/en-us/library/ms179854.aspx) (and the filegroups) yourself. This you can do by using statements like:
CREATE PARTITION FUNCTION pf1 (int)
AS RANGE RIGHT FOR VALUES (1000,2000,3000);
CREATE PARTITION SCHEME ps1
AS PARTITION pf1
ALL TO ( [PRIMARY] );
Regards,
Andras
November 19, 2007 at 2:26 pm
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply