Partition SWITCH fails

• Express12

  SSCoach

  Points: 18526

  More actions

  June 3, 2010 at 7:22 am

  #223369

  PARTITION SWITCH fails.. on LAST statement in this script...

  (this script creates a tiny Test DB, a part function, a part scheme, a part table, populates the table, SPLITS the partition, creates a new WORK table then attempt to SWITCH the WORK table w/ the Partition and FAILS. Thoughts?

  -- Just run this script on a DEVELOPMENT server... it works all the way up to the final ALTER SWITCH SQL.. can't figure out why !!

  --*******************************************************************************************

  --Step 1 : Create New Test Database with two different filegroups

  USE Master;

  GO

  IF EXISTS (SELECT name FROM sys.databases WHERE name = N'TestPartitionDB')

  DROP DATABASE TestPartitionDB;

  GO

  CREATE DATABASE TestPartitionDB

  ON PRIMARY

  (NAME='TestPartitionDB',FILENAME= 'C:\TestPartitionDB.mdf', SIZE=3, FILEGROWTH=1)

  GO

  --Step 2 : Create Partition Range Function

  USE TestPartitionDB;

  GO

  CREATE PARTITION FUNCTION TestPartitionFunction (INT)

  AS RANGE LEFT FOR VALUES (10, 20);

  GO

  --Step 3 : Attach Partition Scheme to FileGroups

  USE TestPartitionDB;

  GO

  CREATE PARTITION SCHEME TestPartitionedScheme

  AS PARTITION TestPartitionFunction ALL TO ([PRIMARY])

  GO

  --Step 4 : Create Table with Partition Key and Partition Scheme

  USE TestPartitionDB;

  GO

  CREATE TABLE TestPartitionTable

  (ID INT NOT NULL, Date DATETIME) ON TestPartitionedScheme (ID);

  GO

  -- Step 5 : (Optional/Recommended) Create Index on Partitioned Table

  USE TestPartitionDB;

  GO

  CREATE UNIQUE CLUSTERED INDEX IX_TestPartitionTable

  ON TestPartitionTable(ID) ON TestPartitionedScheme (ID);

  GO

  --Step 6 : Insert Data in Partitioned Table

  USE TestPartitionDB;

  GO

  --- Step 6 : Insert Data in Partitioned Table

  INSERT INTO TestPartitionTable (ID, Date) -- Inserted in Partition 1

  VALUES (1,GETDATE());

  INSERT INTO TestPartitionTable (ID, Date) -- Inserted in Partition 2

  VALUES (11,GETDATE());

  INSERT INTO TestPartitionTable (ID, Date) -- Inserted in Partition 2

  VALUES (12,GETDATE());

  INSERT INTO TestPartitionTable (ID, Date) -- Inserted in Partition 3

  VALUES (25,GETDATE());

  INSERT INTO TestPartitionTable (ID, Date) -- Inserted in Partition 3

  VALUES (27,GETDATE());

  INSERT INTO TestPartitionTable (ID, Date) -- Inserted in Partition 3

  VALUES (29,GETDATE());

  INSERT INTO TestPartitionTable (ID, Date) -- Inserted in Partition 3

  VALUES (31,GETDATE());

  INSERT INTO TestPartitionTable (ID, Date) -- Inserted in Partition 3

  VALUES (53,GETDATE());

  INSERT INTO TestPartitionTable (ID, Date) -- Inserted in Partition 3

  VALUES (69,GETDATE());

  INSERT INTO TestPartitionTable (ID, Date) -- Inserted in Partition 3

  VALUES (72,GETDATE());

  GO

  --***********************************************************************

  --Step 7 : Test Data from TestPartitionTable

  USE TestPartitionDB;

  GO

  --Step 8 : Verify Rows Inserted in Partitions

  USE TestPartitionDB;

  GO

  --- Step 8 : Verify Rows Inserted in Partitions

  /*

  SELECT * FROM sys.partitions WHERE OBJECT_NAME(OBJECT_ID)='TestPartitionTable';

  */

  GO

  --

  --Step 9: Adding and Removing Partitions

  -- ***

  -- *** SPLIT RANGE <--- SPLITS PARTITIONS apart

  -- ***

  USE TestPartitionDB;

  GO

  ALTER PARTITION FUNCTION TestPartitionFunction () SPLIT RANGE (80)

  GO

  -- Create a new, temporary WORK table independent of the partitioned table:

  USE TestPartitionDB;

  GO

  CREATE TABLE MyNewPartitionWORKtable

  (ID INT NOT NULL CHECK (ID >= 30 AND ID IS NOT NULL),

  Date DATETIME)

  USE TestPartitionDB;

  GO

  CREATE UNIQUE CLUSTERED INDEX IX_MyNewPartitionWORKtable

  ON MyNewPartitionWORKtable(ID)

  GO

  --

  -- Now we add some data to the new table

  INSERT INTO MyNewPartitionWORKtable (ID, Date) -- Inserted in Partition 3

  VALUES (80,GETDATE());

  INSERT INTO MyNewPartitionWORKtable (ID, Date) -- Inserted in Partition 3

  VALUES (95,GETDATE());

  GO

  -- Delete any preexisting data residing in the Partition prior to the SWITCH

  DELETE TestPartitionTable WHERE ID > 29

  GO

  -- Now, swap (SWITCH) the temporary WORK table with the empty partition

  ALTER TABLE MyNewPartitionWORKtable SWITCH TO TestPartitionTable PARTITION 4

  BT

• Express12

  SSCoach

  Points: 18526

  More actions

  June 3, 2010 at 8:57 am

  #1176011

  thanks. your feedback helped resolve my issue.

  much appreciated..

  BT