Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Table Partitioning Expand / Collapse
Author
Message
Posted Saturday, April 5, 2014 2:19 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 1:54 AM
Points: 30, Visits: 84
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?


SpeedySQL
Post #1558702
Posted Tuesday, April 8, 2014 7:41 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:40 PM
Points: 19, Visits: 60
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.
Post #1559487
Posted Tuesday, April 8, 2014 10:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:40 PM
Points: 19, Visits: 60
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;
Post #1559572
Posted Wednesday, April 9, 2014 9:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 1:54 AM
Points: 30, Visits: 84
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.



SpeedySQL
Post #1560013
Posted Thursday, April 10, 2014 1:00 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:40 PM
Points: 19, Visits: 60
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.
Post #1560588
Posted Thursday, April 10, 2014 3:01 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:40 PM
Points: 19, Visits: 60
Ok the problem has nothing to do with partitioning. I still don't know what the problem is but at least I know partitioning isn't the cause.

If I check the original table for duplicates based on the ID column there are none.

SELECT <IdColumn>, COUNT(*) FROM <originalTable> GROUP BY <IdColumn> HAVING COUNT(*) > 1 -- no rows returned.

If I query the original table for all rows with the particular ID value that SQL thinks is a duplicate I get only one row; no duplicates. Even trimming both sides of the nchar value or using "LIKE '%<value>%' still only returns one row in the original table.

SELECT * FROM <originalTable> WHERE <IdColumn> = '<IdValue>' -- one row; no dupes.
SELECT * FROM <originalTable> WHERE LTRIM(RTRIM(<IdColumn>)) = '<IdValue>' -- one row; no dupes.
SELECT * FROM <originalTable> WHERE <IdColumn> LIKE'%<IdValue>%' -- one row; no dupes.


If I script the table creation and run that to create a new table (specifying a new name obviously) and then try to insert into the new table I get the primary key violation error showing the key value that is not a duplicate:

INSERT INTO <newTable> SELECT * FROM <originalTable> --"Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object '<newTable>'. The duplicate key value is (<IdValue>).

????? ANYONE: Any ideas?????
Post #1560635
Posted Friday, April 11, 2014 2:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, June 5, 2014 1:54 AM
Points: 30, Visits: 84
Can you post the DDL for the original table and the new table without the partitioning and with a CSV file with the record in question? Are you doing this within the same database or cross-database?

SpeedySQL
Post #1560759
Posted Friday, April 11, 2014 7:23 PM This worked for the OP Answer marked as solution


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:03 AM
Points: 36,975, Visits: 31,492
todd 87764 (4/10/2014)
Ok the problem has nothing to do with partitioning. I still don't know what the problem is but at least I know partitioning isn't the cause.

If I check the original table for duplicates based on the ID column there are none.

SELECT <IdColumn>, COUNT(*) FROM <originalTable> GROUP BY <IdColumn> HAVING COUNT(*) > 1 -- no rows returned.

If I query the original table for all rows with the particular ID value that SQL thinks is a duplicate I get only one row; no duplicates. Even trimming both sides of the nchar value or using "LIKE '%<value>%' still only returns one row in the original table.

SELECT * FROM <originalTable> WHERE <IdColumn> = '<IdValue>' -- one row; no dupes.
SELECT * FROM <originalTable> WHERE LTRIM(RTRIM(<IdColumn>)) = '<IdValue>' -- one row; no dupes.
SELECT * FROM <originalTable> WHERE <IdColumn> LIKE'%<IdValue>%' -- one row; no dupes.


If I script the table creation and run that to create a new table (specifying a new name obviously) and then try to insert into the new table I get the primary key violation error showing the key value that is not a duplicate:

INSERT INTO <newTable> SELECT * FROM <originalTable> --"Violation of PRIMARY KEY constraint. Cannot insert duplicate key in object '<newTable>'. The duplicate key value is (<IdValue>).

????? ANYONE: Any ideas?????


Glad it's not the partitioning and, yes, I have an idea. What is the collation of the column in question on the original table? Is it a case sensitive collation? The other question is, what is the default collation that is used when you create the new table? Is it case insensitive? That could certainly be the cause of "duplicates".


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561160
Posted Monday, April 14, 2014 9:47 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, April 14, 2014 1:40 PM
Points: 19, Visits: 60
Thanks so much, Jeff. I can't believe I had not thought of this!
Post #1561534
Posted Monday, April 14, 2014 11:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:03 AM
Points: 36,975, Visits: 31,492
You bet. Thanks for the feedback, Todd.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1561593
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse