Table Partitioning

Question

Table Partitioning

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply

todd 87764 SSC Enthusiast Points: 153 More actions · Answer 1

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?????

SpeedySQL Ten Centuries Points: 1019 More actions · Answer 2

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

todd 87764 SSC Enthusiast Points: 153 More actions · Answer 4

Thanks so much, Jeff. I can't believe I had not thought of this!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 5

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.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions