Red Gate forums :: View topic - Partition differences innacurate
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

Partition differences innacurate

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



Joined: 16 Sep 2011
Posts: 14

PostPosted: Tue Sep 10, 2013 11:04 pm    Post subject: Partition differences innacurate Reply with quote

We have a database in two different environment with a large partitioned table. The table has 5 indexes on it, 4 which are partitioned on one field, 1 which is partitioned differently. This was done in order to support specific query patterns.

When observing the table in the system views, all partitioning and indexing matches, however SQL Compare (10.4.8.87) is showing the tables as partitioned differently. Running the following query provides the index/partition key combinations and shows them as matching across environments:
Code:
select
   i.name,
   c.name,
   case
      when i.index_id in (0,1) then 'TABLE'
      else 'INDEX' end
from sys.indexes i
   join sys.index_columns ic on (i.object_id = ic.object_id and i.index_id = ic.index_id)
   join sys.columns c on (ic.object_id = c.object_id and ic.column_id = c.column_id)
where i.object_id = object_id('TableName') and ic.partition_ordinal=1
order by i.name


What is SQL Compare's process for identifying partition schemes and functions? Shouldn't it show the tables and indexes in both environments as partitioned identically (since they are)?
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Fri Sep 13, 2013 11:03 am    Post subject: Reply with quote

Hello,

I'd be happy to help if you could provide examples on what's different in your case/shouldn't be different. It's not using a single query for this.

Also it would be useful to know if you see the problem as an identical object being scripted in the synchronization, or being shown in the results of the comparison, as the two sets use different logic (and sometimes the visual differences do not accurately reflect the comparison result).
Back to top
View user's profile Send private message
mfal



Joined: 16 Sep 2011
Posts: 14

PostPosted: Fri Sep 20, 2013 3:50 pm    Post subject: Reply with quote

Not sure what examples you're looking for, but Data Compare creates the following statements for our two environments:

Code:
PROD
CREATE TABLE [dbo].[ContractOrgItem]
(
[ID] [bigint] NOT NULL IDENTITY(1, 1),
[ContractOrgID] [int] NOT NULL,
[VendorItemID] [bigint] NOT NULL,
[UOM] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Quantity] [int] NOT NULL,
[Price] [money] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[IsMyItem] [tinyint] NOT NULL CONSTRAINT [DF_ContractOrgItem_IsMyItem] DEFAULT ((0)),
[Surcharge] [smallmoney] NOT NULL CONSTRAINT [DF_ContractOrgItem_Adjustment] DEFAULT ((0))
) ON [ContractOrgIDPartScheme] ([ContractOrgID])
WITH (DATA_COMPRESSION = PAGE)
GO
 
DEV
CREATE TABLE [dbo].[ContractOrgItem]
(
[ID] [bigint] NOT NULL IDENTITY(1, 1),
[ContractOrgID] [int] NOT NULL,
[VendorItemID] [bigint] NOT NULL,
[UOM] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Quantity] [int] NOT NULL,
[Price] [money] NOT NULL,
[StartDate] [datetime] NOT NULL,
[EndDate] [datetime] NOT NULL,
[IsMyItem] [tinyint] NOT NULL CONSTRAINT [DF_ContractOrgItem_IsMyItem] DEFAULT ((0)),
[Surcharge] [smallmoney] NOT NULL CONSTRAINT [DF_ContractOrgItem_Adjustment] DEFAULT ((0))
) ON [ContractOrgIDPartScheme] ([VendorItemID])
WITH (DATA_COMPRESSION = PAGE)
GO


Again, both of these tables and all indexes are partitioned in the same way as validated by my previously supplied query. So the question remains why Data Compare shows them as being different.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Tue Oct 01, 2013 10:07 am    Post subject: Reply with quote

On one you are passing the column "ContractOrgId" and the other "VendorItemId"
Back to top
View user's profile Send private message
mfal



Joined: 16 Sep 2011
Posts: 14

PostPosted: Wed Oct 02, 2013 8:17 pm    Post subject: Reply with quote

These two statements are not what we're passing, they are the SQL output by SQL compare. Our create statements build the appropriate indexes on the same fields.
Back to top
View user's profile Send private message
mfal



Joined: 16 Sep 2011
Posts: 14

PostPosted: Wed Oct 02, 2013 9:49 pm    Post subject: Reply with quote

I'm struggling with how to make this clear. Have you reviewed the repro script I attached to the second ticket(#4457)? As stated in the original post, we've created the same table in two different databases (one in each of our environments). Both tables were created with exactly the same SQL statements, partitioned the same way on the same fields as described in the initial post. SQL Compare, when reviewing the different tables (one in each environment) shows the two tables as being different when they're not, as validated by the system views query I posted initially.

We would like to know why SQL Compare is incorrectly reporting a difference. Please let me know what I have not made clear.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Mon Oct 07, 2013 4:37 pm    Post subject: Reply with quote

You have sent a script to create one database, so I can't say what the problem is without knowing what's in the other database.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6582

PostPosted: Fri Oct 11, 2013 12:33 pm    Post subject: Reply with quote

Problem is SQL Compare starts getting confused about which field is going to serve as the partition field when you have a table on a partition scheme and have multiple indexes on the table. Removing the nonclustered index [IX_ContractOrgItem_2] from the table creation script you sent results in SQL Compare using the correct ContactOrgId as the field used in the partition scheme that the table is on. The bug reference number is SC-6590.
Back to top
View user's profile Send private message
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