Red Gate forums :: View topic - SQL Compare 9 generates unnecessary PK drop/add
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Compare 10
SQL Compare 10 forum

SQL Compare 9 generates unnecessary PK drop/add

Search in SQL Compare 10 forum
Post new topic   Reply to topic
Jump to:  
Author Message
ktrock



Joined: 16 Dec 2011
Posts: 6

PostPosted: Fri Dec 16, 2011 10:05 pm    Post subject: SQL Compare 9 generates unnecessary PK drop/add Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Mon Dec 19, 2011 4:51 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
ktrock



Joined: 16 Dec 2011
Posts: 6

PostPosted: Mon Dec 19, 2011 6:09 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Mon Dec 19, 2011 6:57 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
ktrock



Joined: 16 Dec 2011
Posts: 6

PostPosted: Mon Dec 19, 2011 8:57 pm    Post subject: Reply with quote

Yes, SQL Compare is behaving exactly as it should.

Thanks,
Ken
Back to top
View user's profile Send private message Send e-mail
ktrock



Joined: 16 Dec 2011
Posts: 6

PostPosted: Mon Dec 19, 2011 9:01 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Thu Dec 22, 2011 11:08 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
ktrock



Joined: 16 Dec 2011
Posts: 6

PostPosted: Thu Dec 22, 2011 4:07 pm    Post subject: Reply with quote

It's 10.0.2531.0, which comes with or is part of SQL 2008 SP1.
Back to top
View user's profile Send private message Send e-mail
Chris Auckland



Joined: 24 Oct 2006
Posts: 755
Location: Red Gate Software Ltd.

PostPosted: Tue Jan 03, 2012 5:13 pm    Post subject: Reply with quote

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
Back to top
View user's profile Send private message Send e-mail
ktrock



Joined: 16 Dec 2011
Posts: 6

PostPosted: Mon Jan 09, 2012 10:56 pm    Post subject: Reply with quote

Maybe an option setting somewhere in SSMS but nothing stands out at me.

Ken
Back to top
View user's profile Send private message Send e-mail
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group