Duplicate primary key & partitioned views

  • Ok, here's my situation. I want to partition out a table into two. The only problem is that the table CAN have duplicate rows. (it's coming from a different source that I do not have control over)

    I have my tables setup as follows:

    CREATE TABLE [CT_2008] (

    [IDCOL] [int] NOT NULL ,

    [SSN] [int] NOT NULL ,

    [COL_DAT] [char] (10) NOT NULL ,

    [TRADE_CODE] [char] (1) NOT NULL ,

    CONSTRAINT [PK__CT_2008__47DBAE45] PRIMARY KEY CLUSTERED ([COL_DAT],[IDCOL],[SSN]) ON [PRIMARY] ,

    CONSTRAINT [CK_CT_2008] CHECK ([COL_DAT] >= '2008-01-01' and [COL_DAT] < '2009-01-01')
    ) ON [PRIMARY]
    GO

    CREATE TABLE [CT_2009] (
    [IDCOL] [int] NOT NULL ,
    [SSN] [int] NOT NULL ,
    [COL_DAT] [char] (10) NOT NULL ,
    [TRADE_CODE] [char] (1) NOT NULL ,
    CONSTRAINT [PK__CT_2009__4316F928] PRIMARY KEY CLUSTERED ([COL_DAT], [IDCOL], [SSN]) ON [PRIMARY] ,
    CONSTRAINT [CK_CT_2009] CHECK ([COL_DAT] >= '2009-01-01')

    ) ON [PRIMARY]

    GO

    CREATE VIEW CT

    AS

    SELECT * FROM CT_2009

    UNION ALL

    SELECT * FROM CT_2008

    This works fine as long as I don't insert a duplicate row into the view. When I try to turn the identity column on, I can't insert into the view.

    I have tried to put all the columns in the two selects except for the identity like this:

    CREATE VIEW CT2

    AS

    SELECT SSN, COL_DAT,TRADE_CODE FROM CT_2009

    UNION ALL

    SELECT SSN, COL_DAT,TRADE_CODE FROM CT_2008

    But then I cannot insert into the view.

    The problem is that this table is getting too big to manage. We try to manage a 30gb limit on a single database. The reason for this is because a 30gb backup usually compresses enough to fit on a single-layer dvd. This way we can easily/cheaply backup our whole server to DVDs and move offsite.

    The single table will approach 30gb within the next year probably. If I can partition this table, then I can span it across databases and join it back together with a view.

    Thanks in advance for any responses to this post.


    Live to Throw
    Throw to Live
    Will Summers

  • hmm why dont you try to take out the distinct values from your subquery result, something like

    select distinct (x.a) from

    ( select a from #temp1

    Union all

    Select a from #temp2)x

    Hope this is what you are looking for 🙂

  • That's the problem. I have to preserve the data that I'm getting. I can't modify it.

    I figured that I could just setup a staging table with the same structure and turn the identity column on on it, then insert the rows from the staging table into the view.

    I would have to set the initial identity column to the greatest value in the current table first, then just hope that I don't create any duplicates in the future.


    Live to Throw
    Throw to Live
    Will Summers

  • Looks to me like the IDENTITY column should be a SID. It should have NO RELATION to the data being inserted other than providing a unique ID for each record. This simply means an extra column in the table that even the user doesn't need to know about. You would then place a separate index on the user known values that allows for duplicate values.

  • Looks to me like the IDENTITY column should be a SID

    What you are talking about is what I am trying to do. What is a SID identity column and how do I set a identity column to be SID?


    Live to Throw
    Throw to Live
    Will Summers

  • Given the follwoing table:

    create table dbo.MyTable (

    DataCol1 int,

    DataCol2 datetime,

    DataCol3 varchar(25)

    );

    The new table would be like this:

    create table dbo.MyNewTable (

    MyNewtableID int identity(1,1),

    DataCol1 int,

    DataCol2 datetime,

    DataCol3 varchar(25)

    );

    Does the visual help? You could place the "ID" column at the end of the table so that you don't have to move data between tables.

  • Noticed you are using SQL Server 2000. Any chance of upgrading in the near future? Partitioned tables in SQL Server 2005/2008 would greatly simplify your problem, IMHO.

  • Upgrade this server to 2005/2008? Uh . . . nope. No upgrades on this server anytime soon.


    Live to Throw
    Throw to Live
    Will Summers

  • When creating a partitioned view in SQL Server 2000, you also need to place a constraint on each table that ensures a record belongs to only one table, for example a datetime column where you want each table to have one calendar year of data.

  • Lynn Pettis (5/26/2009)


    When creating a partitioned view in SQL Server 2000, you also need to place a constraint on each table that ensures a record belongs to only one table, for example a datetime column where you want each table to have one calendar year of data.

    Um . . . that's what I have in the example above.


    Live to Throw
    Throw to Live
    Will Summers

  • Will Summers (5/26/2009)


    Lynn Pettis (5/26/2009)


    When creating a partitioned view in SQL Server 2000, you also need to place a constraint on each table that ensures a record belongs to only one table, for example a datetime column where you want each table to have one calendar year of data.

    Um . . . that's what I have in the example above.

    You have to pardon me, been a really busy weekend (oldest daughter graduated from high school, graduation party, cleaning up afterward) and could have used a couple of more days off.

  • No problem. I appreciate any help.


    Live to Throw
    Throw to Live
    Will Summers

  • You aren't adding records to previous year data, are you?

  • I am getting updates to previous years data. Which could be an insert, delete or an update to data I already have.


    Live to Throw
    Throw to Live
    Will Summers

  • Just to make sure, the "IDCOL" which u have on both the tables, is it "Identity" type column (that autoincrements by itself) or just an integer column.

Viewing 15 posts - 1 through 15 (of 19 total)

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