March 27, 2014 at 7:51 pm
I am trying to partition a large table. The table is wide as well as long. There is a clustered primary key on an ID column (but it is of type NCHAR. The partition key needs to be placed on a DATETIME column.
I thought I had it figured out and created a clustered index on the datetime column and nonclustered primary key on the char id column, but something is not right.
Below is the test script I am using which creates a test database and test table then populates the table with 1M rows. (this creates the environment similar to what I am working with) The script then copies the table to a staging table, drops the table and recreates the table as a partitioned table then copies the data from the staging table to the new table. Also contained in the script are the partition function and partition scheme as well as creation of the clustered index and primary key constraint.
Can anyone with table partitioning experience please take a look at this script and tell me what is wrong with it? Script follows:
Can anyone tell me what is wrong with the code below and why the table is not being partitioned?
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
SET NOCOUNT ON;
GO
USE master
go
--START: Create test environment **********************************************
IF EXISTS(select 1 from sys.databases where [name] = 'zPartitionTest')
begin
PRINT 'dropping the old zPartitionTest database';
DROP DATABASE zPartitionTest;
end
GO
PRINT 'Creating zPartitionTest database';
CREATE DATABASE zPartitionTest;
GO
USE zPartitionTest
GO
PRINT 'Creating LargeTable';
CREATE TABLE [dbo].LargeTable
(
AbcID NCHAR(14) NOT NULL -- unique key
, AbcDate DATETIME NOT NULL -- partition key
, Column01 VARCHAR(20) NULL
, Column02 VARCHAR(50) NULL
, Column03 VARCHAR(50) NULL
, Column04 VARCHAR(50) NULL
, Column05 VARCHAR(50) NULL
, Column06 VARCHAR(50) NULL
, Column07 VARCHAR(50) NULL
, Column08 VARCHAR(50) NULL
, Column09 VARCHAR(50) NULL
, Column10 VARCHAR(50) NULL
, Column11 VARCHAR(50) NULL
, Column12 VARCHAR(50) NULL
, Column13 VARCHAR(50) NULL
, Column14 VARCHAR(50) NULL
, Column15 VARCHAR(50) NULL
, Column16 VARCHAR(50) NULL
, Column17 VARCHAR(50) NULL
, Column18 VARCHAR(50) NULL
, Column19 VARCHAR(50) NULL
, Column20 VARCHAR(50) NULL
, Column21 VARCHAR(50) NULL
, Column22 VARCHAR(50) NULL
, Column23 VARCHAR(50) NULL
, Column24 VARCHAR(50) NULL
, Column25 VARCHAR(50) NULL
, Column26 VARCHAR(50) NULL
, Column27 VARCHAR(50) NULL
, Column28 VARCHAR(50) NULL
, Column29 VARCHAR(50) NULL
, Column30 VARCHAR(50) NULL
, CONSTRAINT [PK_LargeTable_AbcID] PRIMARY KEY CLUSTERED ( [AbcID] )
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90)
ON [PRIMARY]
);
GO
PRINT 'Adding test data to LargeTable';
DECLARE
@i INTEGER
, @PadChars VARCHAR(9);
SELECT
@PadChars = '000000000'
, @i = 0;
WHILE @i < 1000000 -- add 10 million rows with unique AbcID's.
BEGIN
SET @i = @i + 1;
IF @i % 50000 = 0 PRINT ' ' + CONVERT(VARCHAR(20), @i);
INSERT INTO LargeTable
(AbcID, AbcDate,
Column01, Column02, Column03, Column04, Column05, Column06, Column07, Column08, Column09, Column10,
Column11, Column12, Column13, Column14, Column15, Column16, Column17, Column18, Column19, Column20,
Column21, Column22, Column23, Column24, Column25, Column26, Column27, Column28, Column29, Column30)
SELECT
'AbcID' + RIGHT(@PadChars + CAST(@i AS VARCHAR (9)), 9)
, CASE WHEN @i < 100001
THEN CONVERT(DATETIME, '2013-08-15') -- first 100k rows should be in the "current" partition (and primary file group)
ELSE CONVERT(DATETIME, '2013-07-15') -- next 900k rows should be in the "archive" partition (and DatabaseArchivePartition file group)
END
, 'xxx01', 'xxx02', 'xxx03', 'xxx04', 'xxx05', 'xxx06', 'xxx07', 'xxx08', 'xxx09', 'xxx10'
, 'xxx11', 'xxx12', 'xxx13', 'xxx14', 'xxx15', 'xxx16', 'xxx17', 'xxx18', 'xxx19', 'xxx20'
, 'xxx21', 'xxx22', 'xxx23', 'xxx24', 'xxx25', 'xxx26', 'xxx27', 'xxx28', 'xxx29', 'xxx30';
END
--END: Create test environment ************************************************
-- Now backup the test table by Selecting into a new (staging) table, dropping the test table and recreating it
PRINT 'Copying current table LargeTable to LargeTable_PartitionStaging (partition staging, from which we will load the new table).'
IF EXISTS(select 1 from sys.tables where [name] = 'LargeTable_PartitionStaging')
begin
DECLARE @StgTable VARCHAR(50)
SET @StgTable = 'LargeTable_PartitionStaging_' + CONVERT(VARCHAR(23), GETDATE(),121)
PRINT 'renaming old LargeTable_PartitionStaging table to: ' + @StgTable
EXEC sp_rename 'LargeTable_PartitionStaging', @StgTable;
end
GO
--EXEC sp_rename 'dbo.LargeTable', 'LargeTable_PartitionStaging';
PRINT 'Copying LargeTable to LargeTable_PartitionStaging.'
SELECT * INTO LargeTable_PartitionStaging FROM LargeTable;
go
IF NOT EXISTS(select 1 from sys.tables where [name] = 'LargeTable_PartitionStaging')
PRINT ' ***** Table copy failed *****';
go
PRINT 'Dropping table LargeTable';
DROP TABLE LargeTable;
-- Create new filegroup, new data file in the new filegroupm Partition Function, Partition Scheme...
IF EXISTS(select 1 from sys.filegroups where [name] = 'DatabaseArchivePartition')
begin
PRINT 'dropping the old filegroup, DatabaseArchivePartition.'
ALTER DATABASE zPartitionTest
REMOVE FILEGROUP DatabaseArchivePartition;
end
GO
PRINT 'Adding filegroup, DatabaseArchivePartition, for archive partition data.'
ALTER DATABASE zPartitionTest
ADD FILEGROUP DatabaseArchivePartition; -- separate filegroup to hold the archive table partitions
GO
IF EXISTS(select 1 from sys.master_files where [name] = 'zPartitionTestArchive.NDF')
begin
PRINT 'dropping the old data file, zPartitionTestArchive.NDF.'
ALTER DATABASE zPartitionTest
REMOVE FILE zPartitionTestArchive;
end
GO
PRINT 'Adding new data file, zPartitionTestArchive.NDF, to the new archive partition filegroup.'
ALTER DATABASE zPartitionTest
ADD FILE
( NAME = zPartitionTestArchive
,FILENAME = 'G:\data\SQLSERVER\zPartitionTestArchive.NDF'
,SIZE = 512 KB
,FILEGROWTH = 10%
)
TO FILEGROUP DatabaseArchivePartition;
GO
PRINT 'Creating the partition function that will be used to split out the archive data from the student_attendance table.'
CREATE PARTITION FUNCTION LargeTable_PartitionFunction (DATETIME)
AS RANGE LEFT FOR VALUES ('2013/08/01 00:00:00.000');
GO
PRINT 'Creating the partition scheme, LargeTable_PartitionScheme, to specify where the archive data will be stored.'
CREATE PARTITION SCHEME LargeTable_PartitionScheme
AS PARTITION LargeTable_PartitionFunction
TO ([PRIMARY], DatabaseArchivePartition);
GO
-- Now recreate the LargeTable table with the partition scheme.
PRINT 'Creating new LargeTable table, specifying how to partition data.'
CREATE TABLE [dbo].LargeTable
(
AbcID [nchar](14) NOT NULL -- primary key
, AbcDate DATETIME NOT NULL -- partition key
, Column01 VARCHAR(20) NULL
, Column02 VARCHAR(50) NULL
, Column03 VARCHAR(50) NULL
, Column04 VARCHAR(50) NULL
, Column05 VARCHAR(50) NULL
, Column06 VARCHAR(50) NULL
, Column07 VARCHAR(50) NULL
, Column08 VARCHAR(50) NULL
, Column09 VARCHAR(50) NULL
, Column10 VARCHAR(50) NULL
, Column11 VARCHAR(50) NULL
, Column12 VARCHAR(50) NULL
, Column13 VARCHAR(50) NULL
, Column14 VARCHAR(50) NULL
, Column15 VARCHAR(50) NULL
, Column16 VARCHAR(50) NULL
, Column17 VARCHAR(50) NULL
, Column18 VARCHAR(50) NULL
, Column19 VARCHAR(50) NULL
, Column20 VARCHAR(50) NULL
, Column21 VARCHAR(50) NULL
, Column22 VARCHAR(50) NULL
, Column23 VARCHAR(50) NULL
, Column24 VARCHAR(50) NULL
, Column25 VARCHAR(50) NULL
, Column26 VARCHAR(50) NULL
, Column27 VARCHAR(50) NULL
, Column28 VARCHAR(50) NULL
, Column29 VARCHAR(50) NULL
, Column30 VARCHAR(50) NULL
)
ON LargeTable_PartitionScheme(AbcDate);
go
IF EXISTS(select 1 from sys.indexes where [name] = 'CX_LargeTable_AbcDate')
begin
PRINT 'dropping the old CX_LargeTable_AbcDate index'
DROP INDEX CX_LargeTable_AbcDate ON LargeTable;
end
PRINT 'Adding Clustered Index on AbcDate.'
CREATE CLUSTERED INDEX CX_LargeTable_AbcDate
ON LargeTable(AbcDate) -- Partition Key
ON [PRIMARY];
DECLARE @TableName VARCHAR(128), @SQL VARCHAR(300)
SELECT @TableName = ISNULL(o.[name], '')
FROM sys.indexes i
JOIN sys.objects o ON i.[object_id] = o.[object_id]
WHERE i.[name] = 'PK_LargeTable_AbcID'
IF @TableName <> ''
begin
PRINT 'dropping the old PK_LargeTable_AbcID constraint'
SET @SQL = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT PK_LargeTable_AbcID'
EXEC(@SQL)
end
PRINT 'Adding non-clustered Primary Key on AbcID'
ALTER TABLE [dbo].Largetable
ADD CONSTRAINT PK_LargeTable_AbcID PRIMARY KEY NONCLUSTERED (AbcID)
ON [PRIMARY];
PRINT 'Copying data from Staging table into the new partitioned table (LargeTable).' + CHAR(13)
INSERT INTO LargeTable SELECT * FROM LargeTable_PartitionStaging;
--check partition info
/* -- below is the query I was using which seems to show data in two separate partitions,
-- but the queries following this one show the table is not partitioned.
SELECT $PARTITION.LargeTable_PartitionFunction(AbcDate) AS PARTITIONID, COUNT(* ) AS ROW_COUNT
FROM dbo.LargeTable
GROUP BY $PARTITION.LargeTable_PartitionFunction(AbcDate)
ORDER BY PARTITIONID;
*/
select * from sys.filegroups
select * from sys.tables where [name] like 'LargeTable%'
select * from sys.indexes where [object_id] in (select [object_id] from sys.objects where [name] like 'LargeTable%') -- = object_id('LargeTable')
select * from sys.partition_schemes
select * from sys.partition_functions
select * from sys.partitions where [object_id] = object_id('LargeTable')
-- following query should eliminate the archive partition and retrieve data from the "live" partition
--select * from LargeTable where AbcDate > convert(datetime,'2013-08-01');
--select * from LargeTable where AbcDate = convert(datetime,'2014-03-26 15:44:21.207');
March 28, 2014 at 8:04 am
You created the clustered index on the primary filegroup instead of on the partition scheme. By doing this, you are effectively removing the partitioning. You can confirm this by running the following script before and after the creation of the clustered index:
SELECT * FROM sys.partitions
WHERE object_name(object_id) = 'LargeTable'
AND index_id = 1
ORDER BY index_id, partition_number
Rerun your script without specifying PRIMARY and the index will automatically be created on the partition scheme and your partitions will remain intact.
CREATE CLUSTERED INDEX CX_LargeTable_AbcDate
ON LargeTable(AbcDate) -- Partition Key
--ON [PRIMARY];
You will also need to add the partition key into the primary key or you will receive an error.
ALTER TABLE [dbo].Largetable
ADD CONSTRAINT PK_LargeTable_AbcID PRIMARY KEY NONCLUSTERED (AbcID, AbcDate)
--ON [PRIMARY];
April 3, 2014 at 12:42 pm
Thanks for your help. It worked in my test case. But my live table with 23M rows is another story.
For some reason when I create the new table on the partition scheme then try to insert the data I am getting a primary key violation error even though there are no duplicate records.
I can send you a test database with the one live table for you to restore, along with the test script I am using. The BAK file is 3.4 GB.
April 4, 2014 at 8:48 am
Where are you loading the data from? Does the source table not have the same unique index? Are you loading into an empty table? What is the error message? They duplicate key value should be included the error message.
Also, for date values it's better to use RANGE RIGHT otherwise your dates will end up in the wrong partitions in the example you gave. Remember also to leave empty partitions at the start and end in case you ever need to split a partition.
April 4, 2014 at 10:09 am
There is a clustered primary key on an ID column ... The partition key needs to be placed on a DATETIME column ... 23M rows
23M rows is not that many. You almost certainly really just need to cluster the table on the datetime column. Partitioning is just a make-up crutch for the wrong clustering key on the table, just like creating a large number of "covering indexes" to cover for the wrong clustering key.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 4, 2014 at 10:33 am
It depends on the growth projection for the table really. If it is going to stay at 23m rows then a clustered index is fine but if it's a table that is going to receive data regularly then partitioning is a good idea. You can't instantly load 1m rows with a clustered index and no partitioning as you can by switching partitions.
April 4, 2014 at 2:23 pm
The table will continue to grow as there are no plans for archiving, which is why I am thinking about partitioning. There COULD be as many as 3.6B rows per year in this table. The current year is what is relevant 95 percent of the time, so I would like two partitions - one for current year and one for archived historical data.
The current (unpartitioned) table has a clustered primary key on the ID (nchar) column. Most queries use this ID column in the where clause. The DATE column is not currently used in most queries, but this would change if partitioning is implemented.
April 4, 2014 at 2:30 pm
The current table has a clustered primary key on the ID column (nchar) and no index on the DATE column at all. This table is copied to a backup table then the real table is dropped and recreated with a clustered non-unique index on the DATE column and a non-clustered primary key on the nchar ID column. It's the same process as used in the test script.
Querying the real table or the backup version of the table shows that there are NO duplicate ID values. But when inserting the records from the backup table into the newly created (and partitioned) table there is a primary key violation error. The ID value specified in the error is NOT a duplicate value.
April 4, 2014 at 3:18 pm
todd 87764 (4/4/2014)
The table will continue to grow as there are no plans for archiving, which is why I am thinking about partitioning. There COULD be as many as 3.6B rows per year in this table. The current year is what is relevant 95 percent of the time, so I would like two partitions - one for current year and one for archived historical data.The current (unpartitioned) table has a clustered primary key on the ID (nchar) column. Most queries use this ID column in the where clause. The DATE column is not currently used in most queries, but this would change if partitioning is implemented.
True, but then most would have to use ID now, as that is the main index. The big thing is, is possible, to get the queries to specify the date, and then cluster on that date.
You would have a non-clustered index on id as well.
Two partitions, current and historical, make sense. But that doesn't mean the table still shouldn't be clustered on date if data can be, or should be, queried that way.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
April 4, 2014 at 3:28 pm
The new partitioned table IS clustered on DATE with a nonclustered primary key on ID. Problem I'm having (which is NOT a problem if I just use a test script and data) is when loading the new partitioned, clustered on DATE, nonclustered primary key on ID table with the real data I am getting a primary key violation error on an ID value that is NOT a duplicate.
April 5, 2014 at 2:19 am
What happens if you try to create the primary key on the backup table?
What is the result of the following query?
SELECT AbcID, COUNT(*) FROM backuptable
GROUP BY AbcID
HAVING COUNT(*) > 1;
Can you post the DDL for all three tables plus the script you're using to load the data into the backup table and the new table?
April 8, 2014 at 7:41 am
If I create a new, empty table that is NOT partitioned and place a primary key on the ID column then copy the data to the new table I do not get the primary key violation. It's only when the new table is partitioned that I get the primary key violation error.
April 8, 2014 at 10:30 am
No Problem adding PK to BKUP table. The duplicate check query you said to try, which I've already run myself on the original table and backup table, produces no results.
Here is the script (note: the DDL for the table creation is midway thru the script. You can copy and paste this to a new window to create the initial table. Just change to filegroup to PRIMARY.):
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
SET ANSI_PADDING ON;
DECLARE
@TableName VARCHAR(128)
, @SQL VARCHAR(300)
-------------------------------------------------------------------------------
-- Backup the STUDENT_ATTENDANCE table to STUDENT_ATTENDANCE_BKUP
-------------------------------------------------------------------------------
USE zPartitionTest;
-- first let's make a backup of the student_attendance table
IF NOT EXISTS(select 1 from sys.tables where [name] = 'STUDENT_ATTENDANCE_BKUP')
BEGIN
PRINT 'Copying student_attendance table to student_attendance_bkup table';
SELECT * INTO STUDENT_ATTENDANCE_BKUP FROM STUDENT_ATTENDANCE;
END
IF NOT EXISTS(select 1 from sys.tables where [name] = 'STUDENT_ATTENDANCE_BKUP')
PRINT ' ***** Table copy failed; STUDENT_ATTENDANCE_BKUP does not exist. Script stopped. *****';
ELSE
BEGIN --bkup table exists.
IF EXISTS(select 1 from sys.tables where [name] = 'STUDENT_ATTENDANCE')
BEGIN
PRINT 'Dropping table STUDENT_ATTENDANCE';
DROP TABLE STUDENT_ATTENDANCE;
END
USE master;
-----------------------------------------------------------------------
-- Create new database filegroup, new data file in the new filegroup,
-- Partition Function and Partition Scheme...
-----------------------------------------------------------------------
IF EXISTS(select 1 from sys.master_files where [name] = 'zPartitionTest_ARCHIVE.NDF')
BEGIN
PRINT '...dropping the old data file, zPartitionTest_ARCHIVE.NDF.'
ALTER DATABASE zPartitionTest
REMOVE FILE zPartitionTest_ARCHIVE;
END
IF EXISTS(select 1 from sys.filegroups where [name] = 'DatabaseArchivePartition')
BEGIN
PRINT '...dropping the old filegroup, DatabaseArchivePartition.'
ALTER DATABASE zPartitionTest
REMOVE FILEGROUP DatabaseArchivePartition;
END
PRINT 'Adding filegroup DatabaseArchivePartition.'
ALTER DATABASE zPartitionTest
ADD FILEGROUP DatabaseArchivePartition; -- separate filegroup to hold the archive table partitions
PRINT 'Adding new data file, zPartitionTest_ARCHIVE.NDF, to the new archive partition filegroup.'
ALTER DATABASE zPartitionTest
ADD FILE
( NAME = zPartitionTest_ARCHIVE
,FILENAME = 'G:\data\SQLSERVER\zPartitionTest_ARCHIVE.NDF'
,SIZE = 1024 MB
,FILEGROWTH = 1024 MB
)
TO FILEGROUP DatabaseArchivePartition;
USE zPartitionTest;
IF EXISTS(select 1 from sys.partition_schemes where [name] = 'STUDENT_ATTENDANCE_PRTN_SCHM')
BEGIN
PRINT '...dropping Partition Scheme, STUDENT_ATTENDANCE_PRTN_SCHM'
DROP PARTITION SCHEME STUDENT_ATTENDANCE_PRTN_SCHM
END
IF EXISTS(select 1 from sys.partition_functions where [name] = 'STUDENT_ATTENDANCE_PRTN_FNCTN')
BEGIN
PRINT '...dropping Partition Function, STUDENT_ATTENDANCE_PRTN_FNCTN'
DROP PARTITION FUNCTION STUDENT_ATTENDANCE_PRTN_FNCTN
END
PRINT 'Creating the partition function, STUDENT_ATTENDANCE_PRTN_FNCTN, that will be used to split out the archive data from the student_attendance table.'
CREATE PARTITION FUNCTION STUDENT_ATTENDANCE_PRTN_FNCTN (DATETIME)
AS RANGE RIGHT FOR VALUES ('2013/08/01 00:00:00.000'); -- all data less than this date goes into the first partition (archive).
PRINT 'Creating the partition scheme, STUDENT_ATTENDANCE_PRTN_SCHM, to specify where the archive data will be stored.'
CREATE PARTITION SCHEME STUDENT_ATTENDANCE_PRTN_SCHM
AS PARTITION STUDENT_ATTENDANCE_PRTN_FNCTN
TO (DatabaseArchivePartition, [PRIMARY]); -- old data to archive, new data to primary.
PRINT 'Creating new STUDENT_ATTENDANCE table, specifying how to partition data.'
CREATE TABLE [dbo].[STUDENT_ATTENDANCE]
(
[ATT_OID] [nchar](14) NOT NULL
,[ATT_STD_OID] [nchar](14) NULL
,[ATT_SKL_OID] [nchar](14) NULL
,[ATT_DATE] [datetime] NOT NULL
,[ATT_CODE_VIEW] [varchar](50) NULL
,[ATT_TIME_VIEW] [varchar](40) NULL
,[ATT_ABSENT_IND] [char](1) NULL DEFAULT(0)
,[ATT_TARDY_IND] [char](1) NULL DEFAULT(0)
,[ATT_DISMISSED_IND] [char](1) NULL DEFAULT(0)
,[ATT_REASON_CODE] [varchar](20) NULL
,[ATT_EXCUSED_IND] [char](1) NULL DEFAULT(0)
,[ATT_PORTION_ABSENT] [numeric](8, 4) NULL
,[ATT_OTHER_CODE] [varchar](20) NULL
,[ATT_OTHER_CODE_02] [varchar](20) NULL
,[ATT_COMMENT] [varchar](max) NULL
,[ATT_TIMESTAMP] [numeric](14, 0) NULL
,[ATT_FIELDA_001] [varchar](10) NULL
,[ATT_FIELDA_002] [varchar](10) NULL
,[ATT_FIELDB_001] [varchar](25) NULL
,[ATT_LAST_MODIFIED] [numeric](14, 0) NULL
,[ATT_ABSENT_IND_02] [char](1) NULL DEFAULT(0)
,[ATT_TARDY_IND_02] [char](1) NULL DEFAULT(0)
,[ATT_DISMISSED_IND_02] [char](1) NULL DEFAULT(0)
,[ATT_REASON_CODE_02] [varchar](20) NULL
,[ATT_EXCUSED_IND_02] [char](1) NULL DEFAULT(0)
,[ATT_COMMENT_02] [varchar](max) NULL
,[ATT_RESPONSE] [varchar](max) NULL
,[ATT_RESPONSE_02] [varchar](max) NULL
)
ON STUDENT_ATTENDANCE_PRTN_SCHM(ATT_DATE); --partition on att_date
-- Check for clustered index and primary key
SELECT @TableName = ISNULL(o.[name], '')
FROM sys.indexes i
JOIN sys.objects o ON i.[object_id] = o.[object_id]
WHERE i.[name] = 'CX_STUDENT_ATTENDANCE_ATT_DATE' ;
IF @TableName <> ''
BEGIN
PRINT '...dropping the old CX_STUDENT_ATTENDANCE_ATT_DATE constraint'
SET @SQL = 'DROP INDEX CX_STUDENT_ATTENDANCE_ATT_DATE ON ' + @TableName
EXEC(@SQL);
END
PRINT 'Adding Clustered Index on ATT_DATE.'
CREATE CLUSTERED INDEX CX_STUDENT_ATTENDANCE_ATT_DATE
ON STUDENT_ATTENDANCE(ATT_DATE);
SELECT @TableName = ISNULL(o.[name], '')
FROM sys.indexes i
JOIN sys.objects o ON i.[object_id] = o.[object_id]
WHERE i.[name] = 'PK_STUDENT_ATTENDANCE_ATT_OID'
IF @TableName <> ''
BEGIN
PRINT '...dropping the old PK_STUDENT_ATTENDANCE_ATT_OID constraint'
SET @SQL = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT PK_STUDENT_ATTENDANCE_ATT_OID'
EXEC(@SQL)
END
PRINT 'Adding non-clustered Primary Key on ATT_OID'
ALTER TABLE [dbo].STUDENT_ATTENDANCE_BKUP
ADD CONSTRAINT PK_STUDENT_ATTENDANCE_BKUP_ATT_OID PRIMARY KEY NONCLUSTERED (ATT_OID)
ON [PRIMARY];
PRINT 'Copying data from Staging table into the new partitioned STUDENT_ATTENDANCE table.' + CHAR(13);
INSERT INTO STUDENT_ATTENDANCE SELECT * FROM STUDENT_ATTENDANCE_BKUP; --PRIMARY KEY VIOLATION ERROR
--check partition counts
SELECT * FROM sys.partitions WHERE OBJECT_NAME(OBJECT_ID)='STUDENT_ATTENDANCE';
SELECT $PARTITION.STUDENT_ATTENDANCE_PRTN_FNCTN(ATT_DATE) AS PARTITIONID,
COUNT(* ) AS ROW_COUNT
FROM DBO.STUDENT_ATTENDANCE
GROUP BY $PARTITION.STUDENT_ATTENDANCE_PRTN_FNCTN(ATT_DATE)
ORDER BY PARTITIONID;
END --bkup table exists.
SET ANSI_PADDING OFF;
April 9, 2014 at 9:09 am
From the DDL you posted, the STUDENT_ATTENDANCE table doesn't have a primary key, only the backup table does. Let's assume it does though and I suggest creating the PK with the IGNORE_DUP_KEY option on. Then load the data and run the duplicate check again to see if it returns any records.
April 10, 2014 at 1:00 pm
That was a typo. The nonclustered primary key actually was created on the correct student_attendance table and not the bkup.
If I allow dupes then there will be dupes in the new, partitioned table, but this is not acceptable. The current, nonpartitioned table has no dupes. The new, partitioned table can NOT have any dupes or the data will be invalid.
In my testing I removed the primary key and instead placed a nonunique index on the ID column and after the insert from the original table into the new table of the new table had 514,785 duplicated ID values, resulting in 1,179,720 lost records (some of the ID values were duplicated more than once). 23,436,483 distinct ID values became 22,256,763 distinct values.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply