Feature Request - Sort Indexes Alphabeticaly (Script Export)

lewismoten

I store the changes of schema information in TFS. Each time I export the schema objects as individual SQL scripts, many objects appear to change in structure. Upon closer examination, they were not actually changing schema, but SQL Compare was exporting the indexes in a different order. This confuses me when looking at history for objects identifying when "actual" changes to the schema occurred.

I would like to request that additional information such as indexes, extended properties, permissions, foreign keys, constraints, etc. should always be ordered by dependency, then alphabetically in ascending order to prevent these abnormalities.

Here is one object that SQL Compare says that has changed often - however, I haven't touched this table in roughly 2 years. Notice that the indexes were originally listed as IX_AR_Object_1,2,3,4,5. The change is that they are now listed as IX_AR_Object_5,4,3,2,1 (Just the opposite). In addition, an extra line used to be just before the first GO statement, but has since been removed from the SQL generation.

I am using SQL Compare version 7
Before

CREATE TABLE [dbo].[AR_Object]
(
[ObjectID] [int] NOT NULL IDENTITY(1, 1),
[ObjectName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectAlias] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectTypeID] [int] NOT NULL,
[ObjectValidFrom] [datetime] NOT NULL,
[ObjectValidTo] [datetime] NULL,
[ObjectDescription] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ObjectCustomField1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ObjectCustomField2] [uniqueidentifier] NULL,
[OwnerID] [int] NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[AR_Object] ADD CONSTRAINT [PK_AR_Object] PRIMARY KEY CLUSTERED  ([ObjectID]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AR_Object] ON [dbo].[AR_Object] ([ObjectAlias]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AR_Object_1] ON [dbo].[AR_Object] ([ObjectTypeID]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AR_Object_2] ON [dbo].[AR_Object] ([ObjectName]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AR_Object_3] ON [dbo].[AR_Object] ([ObjectDescription]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AR_Object_4] ON [dbo].[AR_Object] ([ObjectCustomField2]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AR_Object_5] ON [dbo].[AR_Object] ([ObjectCustomField1]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AR_Object] ADD CONSTRAINT [FK_AR_Object_AR_ObjectType] FOREIGN KEY ([ObjectTypeID]) REFERENCES [dbo].[AR_ObjectType] ([ObjectTypeID])
GO

After

CREATE TABLE [dbo].[AR_Object]
(
[ObjectID] [int] NOT NULL IDENTITY(1, 1),
[ObjectName] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectAlias] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectTypeID] [int] NOT NULL,
[ObjectValidFrom] [datetime] NOT NULL,
[ObjectValidTo] [datetime] NULL,
[ObjectDescription] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ObjectCustomField1] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ObjectCustomField2] [uniqueidentifier] NULL,
[OwnerID] [int] NULL
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[AR_Object] ADD CONSTRAINT [PK_AR_Object] PRIMARY KEY CLUSTERED ([ObjectID]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AR_Object] ON [dbo].[AR_Object] ([ObjectAlias]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AR_Object_5] ON [dbo].[AR_Object] ([ObjectCustomField1]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AR_Object_4] ON [dbo].[AR_Object] ([ObjectCustomField2]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AR_Object_3] ON [dbo].[AR_Object] ([ObjectDescription]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AR_Object_2] ON [dbo].[AR_Object] ([ObjectName]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_AR_Object_1] ON [dbo].[AR_Object] ([ObjectTypeID]) WITH (FILLFACTOR=90) ON [PRIMARY]
GO
ALTER TABLE [dbo].[AR_Object] ADD CONSTRAINT [FK_AR_Object_AR_ObjectType] FOREIGN KEY ([ObjectTypeID]) REFERENCES [dbo].[AR_ObjectType] ([ObjectTypeID])
GO

Chris Auckland

Thanks for your post.

SQL Compare will sort the indexes alphabetically by column name, rather than alphabetically by index name. However, SQL Compare shouldn't identify the order of the indexes as a semantic difference between the objects. If I compare the 'before' and 'after' scripts with SQL Compare they are matched as identical.

Is the issue that you would rather have the indexes ordered by index name ascending in the script rather than the current sort order?

Can you let me know what kind of comparison you are performing that is identifying the two objects as different?

lewismoten

The order of the table columns has not changed.

Although the scripts are structurally the same, they are not written out the same. I am running a file diff between the two versions in TFS.

I have had database builds where there has only been structurally 1 change in a stored procedure. However, TFS is showing that practically the whole database has changed due to the way that SQL Compare changes the order of scripted indexes, extended properties, etc.

This is making it hard for us to track down structural changes when looking at the history of individual objects, or the files checked in with a change set.

Chris Auckland

Thanks for getting back to me.

It turns out that the order of index was changed fairly recently to fix a problem where the indexes didn't properly line up in the SQL Differences pane if you had two tables with the same index ordered differently.

We shouldn't be changing the ordering again, so in time all your scripts should be consistent.

I'm sorry that you have encountered this problem.

lewismoten

Thanks Chris.