SQL Compare 9 generates unnecessary PK drop/add

ktrockktrock Posts: 7
edited January 9, 2012 4:56PM in SQL Compare Previous Versions
Hello. SQL Compare 9 generated a syncronization script for me on a single SP and syncronizing dependencies. A referenced table in the source had only a PK. The destination had the exact same PK and a non-clustered index.

The generated script wanted to delete the non-clustered index which is fine. But it also wanted to drop and re-create the PK. Why?

Thanks,
Ken

Comments

  • Thanks for your post.

    The PK shouldn't be recreated unnecessarily. I set up a quick test following your instructions, and only the index was dropped in my test.

    Would you be able to post (or send me) the DDL for your source and target table, and I'll see if I can reproduce the problem?
    Chris
  • USE [dbACSS]
    GO

    /****** Object: Table [Summary].[tblScreenUsageByTypeID] Script Date: 12/19/2011 11:56:53 ******/
    --This is the source
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [Summary].[tblScreenUsageByTypeID](
    [ScreenUsageTypeID] [smallint] NOT NULL,
    [UsageDate] [smalldatetime] NOT NULL,
    [EmployeeID] [varchar](20) NOT NULL,
    [TotalClicks] [int] NULL,
    [UniqueClicks] [int] NULL,
    CONSTRAINT [PK_tblScreenUsageByTypeID] PRIMARY KEY CLUSTERED
    (
    [ScreenUsageTypeID] ASC,
    [UsageDate] ASC,
    [EmployeeID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARY]
    ) ON [SECONDARY]

    GO

    SET ANSI_PADDING OFF
    GO

    EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Total screen clicks by ACSS Call Id per day' , @level0type=N'SCHEMA',@level0name=N'Summary', @level1type=N'TABLE',@level1name=N'tblScreenUsageByTypeID', @level2type=N'COLUMN',@level2name=N'TotalClicks'
    GO






    USE [dbACSS]
    GO

    /****** Object: Table [Summary].[tblScreenUsageByTypeID] Script Date: 12/19/2011 11:55:49 ******/
    --This is the destination
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    SET ANSI_PADDING ON
    GO

    CREATE TABLE [Summary].[tblScreenUsageByTypeID](
    [ScreenUsageTypeID] [smallint] NOT NULL,
    [UsageDate] [smalldatetime] NOT NULL,
    [EmployeeID] [varchar](20) NOT NULL,
    [TotalClicks] [int] NULL,
    [UniqueClicks] [int] NULL,
    CONSTRAINT [PK_tblScreenUsageByTypeID] PRIMARY KEY NONCLUSTERED
    (
    [ScreenUsageTypeID] ASC,
    [UsageDate] ASC,
    [EmployeeID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [INDEXES]
    ) ON [SECONDARY]

    GO

    SET ANSI_PADDING OFF
    GO


    USE [dbACSS]
    /****** Object: Index [ixUsageDate] Script Date: 12/19/2011 11:55:49 ******/
    CREATE CLUSTERED INDEX [ixUsageDate] ON [Summary].[tblScreenUsageByTypeID]
    (
    [UsageDate] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [SECONDARY]
    GO


    I see it! The PK is on different filegroups in the source and destination. Funny that the upgrade script wants to put it back on the default filegroup.

    Ken
  • Thanks for your reply.

    By default, SQL Compare will ignore 'filegroups, partition schemes and partition functions'.

    Do you get the correct result if you turn off this project option?
    Chris
  • Yes, SQL Compare is behaving exactly as it should.

    Thanks,
    Ken
  • While I'm here let me ask you, why does launching SQL Compare from within SSMS ask to save the contents of unsaved windows? It's not like starting Compare shuts down SSMS.

    Ken
  • Sorry for the delay, I missed your last query.

    This doesn't seem to happen for me.

    Which version of SSMS are you using?
    Chris
  • It's 10.0.2531.0, which comes with or is part of SQL 2008 SP1.
  • Hi,

    I tried this with 10.0.2531.0, and It didn't ask me to save the open queries.

    Are you able to try this on another workstation with SSMS and see if the same thing happens?
    Chris
  • Maybe an option setting somewhere in SSMS but nothing stands out at me.

    Ken
Sign In or Register to comment.