Partition differences innacurate

mfal

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:

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

Brian Donahue

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).

mfal

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

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.

Brian Donahue

On one you are passing the column "ContractOrgId" and the other "VendorItemId"

mfal

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.

mfal

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.

Brian Donahue

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.

Brian Donahue

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.