24 results found

1. index

   SQL compare regularly identifies a difference between the same index between the same table stored on 2 different databases as it seems to get confused with a different index then says the the same index underneath the 'different' one, should be deleted! It must not compare on the name of the index but maybe an order? Is this a bug?

   1 vote

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   0 comments  ·  Edit…  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Submit Rating

2. Constraint and Index Names

   Currently, there is an option to ignore constraint and index names. Please separate out indexes from constraint names.

   4 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   1 comment  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Submit Rating

3. Option to "Ignore index named like nci_wi_%

   Please add an option to "Ignore index named like nciwi%

   These index are added by SQL Server (starting with Azure SQL DB), as part of optimizing the workload on a database.

   They make it impossible to compare two databases, as too many "false" differences exists.

   28 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   0 comments  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Submit Rating

4. SQL Compare store options for index create\rebuild

   First this may be more apparent as tedious in my case, we happen to have large tables with decent sized indexes. I would appreciate if through the setup of a SQL Compare project I could impact index create or rebuild behavior through the selection of on\off toggle for the switches that are available upon creation of an index.

   I use some options more than others with the most frequently added option being SORTINTEMPDB = ON. I use this because when building large indexes I want to try and avoid as much as possible any unintended file growth by specifying that TempDB is used for the sort operation of the index create.

   Since there isn't a mechanism to specify this in the project options, I must always generate a deployment script and manually modify the index create portions of a given script to specify any switches I want to throw at the index create.

   So for each possible modifier:

   PADINDEX = ON
   STATISTICSNORECOMPUTE = ON
   SORTINTEMPDB = ON
   DROPEXISTING = ON
   ONLINE = ON
   ALLOWROWLOCKS = ON
   ALLOWPAGE_LOCKS = ON

   I would like to be able to select that these options be used and specify the value (ON or OFF). Since this is a project level setting, I could then set the desired values as a project default ensuring that when the deployment is done through SQL Compare or a generated migration script that I don't have to step through each index create and add the options I want to use manually.

   Using the previously listed modifiers as an example in that all are turned on, the deployment script for an index would include the following:

   CREATE NONCLUSTERED INDEX [IDXIndexOne] ON [Schema].[Table]
   (
   [Column1] ASC
   )
   WITH (
   PADINDEX = ON, STATISTICSNORECOMPUTE = ON, SORTINTEMPDB = ON, DROPEXISTING = ON, ONLINE = ON, ALLOWROWLOCKS = ON, ALLOWPAGELOCKS = ON
   )

   GO

   First this may be more apparent as tedious in my case, we happen to have large tables with decent sized indexes. I would appreciate if through the setup of a SQL Compare project I could impact index create or rebuild behavior through the selection of on\off toggle for the switches that are available upon creation of an index.

   I use some options more than others with the most frequently added option being SORTINTEMPDB = ON. I use this because when building large indexes I want to try and avoid as much as possible any unintended… more

   78 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   10 comments  ·  Delete…  ·  Admin →
   completed  ·  AdminTim Dalton (Product Manager, Redgate) responded

   We shipped this as part of SQL Compare 14.1.3

   https://documentation.red-gate.com/sdc/release-notes-and-other-versions/sql-data-compare-14-1-release-notes

5. Separate primary key from other indexes for ignore index option.

   Our indexes are tuned and maintained in production. Database development is done elsewhere and moved using SQL Compare. Therefore we don't want to compare indexes. However, if we don't compare indexes, then the primary keys are not created. If we do compare indexes, there is a real risk that production will be damaged.
   This problem could be prevented by treating primary key indexes differently from other indexes. That this is even necessary is an implementation quirk of SQL Server.

   126 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   6 comments  ·  Delete…  ·  Admin →
   completed  ·  Andrew Farries responded

   In SQL Compare 12.2.3.4239, released today, we have made a start on giving users more fine grained control over exactly which indexes are deployed.

   We have added a new `Ignore performance indexes’ option which will ignore all indexes except primary keys and unique constraints.

   We realise that there is more we can do in this area, so we would like to hear your feedback about whether this new option is helpful and how we can do more.

   Feedback on this feature can be posted on the forum thread:

   https://forum.red-gate.com/discussion/80770/new-feature-ignore-performance-indexes

6. Please fix columnstore index comparisons

   Currently SQL Compare detects the order of columnstores as a difference. Columnstores have been around for a while now, can't this please get fixed?

   18 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   0 comments  ·  Delete…  ·  Admin →
   completed  ·  AdminMichael Upton (Development Lead, Redgate) responded

   I’m glad to tell you that this was fixed in Compare v11.0. If you’re using a later version and are still having trouble, please contact support@red-gate.com for help.

7. Manage indexes separately from tables

   When comparing tables, additions/changes in indexes are included as part of the differences to the table. I would like a way to separate index management from table management. In other words, it would be nice to be able to select a table for modification without including the indexes. I know we can do this from a project level with settings, but if there are many tables selected for change, I would like to be able to include the indexes for some tables and not others. Also, it would be nice to be able to select individual indexes on a given table. Sometimes multiple indexes would display for a given table and it would be nice to hand pick which indexes are being selected for promotion.

   When comparing tables, additions/changes in indexes are included as part of the differences to the table. I would like a way to separate index management from table management. In other words, it would be nice to be able to select a table for modification without including the indexes. I know we can do this from a project level with settings, but if there are many tables selected for change, I would like to be able to include the indexes for some tables and not others. Also, it would be nice to be able to select individual index… more

   192 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   29 comments  ·  Delete…  ·  Admin →
   declined  ·  AdminStephanie (Product Manager, Redgate) responded

   At this time, we don't have any plans to separate indexes because it's a big architectural change.

8. Compare the OPTIMIZE_FOR_SEQUENTIAL_KEY property of an Index

   By default this is 'off' in SQL 2019. But we have found some tables have significant performance improvements for load if its 'on'. Thus tuning requires these indexes to be on. For SQL Compare to remain useful it needs to highlight were where there is a difference in the property - and support deployments with the property to retain it as on.

   1 vote

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   0 comments  ·  Edit…  ·  Delete…  ·  Admin →
   How important is this to you?

   Not at all You must login first! Important You must login first! Critical You must login first!

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   Submit Rating

9. distinguish between unique and non-unique indexes

   (This is actually relevant to both SQL Compare and SQL Source Control.)

   We really need to be able to distinguish between unique and non-unique indexes when comparing, checking in, and deploying database schema changes. ("Unique indexes" include primary keys, unique constraints, and unique indexes on tables and unique indexes on views; "non-unique indexes" include non-unique indexes on tables and views.)

   Unique indexes are essential to the database development process. They make a significant functional difference to the behavior of the database: they enforce business rules and referential integrity; they limit the data that can be entered into the database; foreign keys depend on them. Developers should be responsible for creating and maintaining all of the unique indexes on the development database, and an automated deployment process that uses SQL Compare and SQL Source Control should be able to deploy all database schema changes, including unique indexes, from the development database to the production database.

   On the other hand, non-unique indexes do not affect the database's behavior; they only affect the database's performance. Developers should not be responsible for maintaining non-unique indexes; they can't. A development database usually has a relatively small amount of test data in it, and a production database usually has a much larger amount of real data. SQL Server uses different indexes and different query execution plans depending on the amount and quality of data in the database, and the servers recommend different indexes depending on which queries get used the most frequently. A developer, running queries on the development database and looking at the execution plans to determine which indexes are needed, will get results that are not valid for the production database. In our case, it is also common for one set of queries to be heavily used one month and an entirely different set of queries to be heavily used the next month, so the developers would never be able to keep up with the production server's changing needs.

   Instead, we have a SQL Agent job that runs periodically on the production server to maintain non-unique indexes. The job reads and analyzes the system view sys.dmdbindexusagestats to determine which indexes are the least-recently used and the most-frequently updated, and drops them. It also reads and analyzes the system views sys.dmdbmissingindexgroupstats, sys.dmdbmissingindexgroups, and sys.dmdbmissingindex_details to determine which missing indexes are the most needed, and creates them. This index maintenance job frees up developers to do actual development instead of tracking down performance problems and fixing them.

   The problem is that SQL Compare and SQL Source Control do not distinguish between unique indexes and non-unique indexes; it will compare and synchronize either all of them or none of them. If we ignore indexes, then we can't deploy primary keys, unique constraints, unique indexes, or foreign keys (since foreign keys depend on unique indexes); if we include indexes, then the non-unique indexes created on the production server will be dropped in favor of the non-unique indexes that happen to exist on the development server, undoing all of the performance improvements made by the automatic index maintenance job.

   We would like the "Ignore indexes" option of SQL Compare and SQL Source Control to be split into two options, "Ignore unique indexes" and "Ignore non-unique indexes".

   (This is actually relevant to both SQL Compare and SQL Source Control.)

   We really need to be able to distinguish between unique and non-unique indexes when comparing, checking in, and deploying database schema changes. ("Unique indexes" include primary keys, unique constraints, and unique indexes on tables and unique indexes on views; "non-unique indexes" include non-unique indexes on tables and views.)

   Unique indexes are essential to the database development process. They make a significant functional difference to the behavior of the database: they enforce business rules and referential integrity; they limit the data that… more

   27 votes

   Vote Vote Vote

   Vote

   We're glad you're here

   Please sign in to leave feedback

   Signed in as (Sign out)

   Close

   Close

   1 vote 2 votes 3 votes Remove votes

   You have left! (?) (thinking…)
   completed  ·  0 comments  ·  Delete…  ·  Admin →

10. Create scripts folder ignoring fill factor

    File->Create scripts folder should script index fill factor. When the created folder is compared against a DB I can optionally include/exclude the fill factor as I need

    6 votes

    Vote Vote Vote

    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    1 vote 2 votes 3 votes Remove votes

    You have left! (?) (thinking…)
    0 comments  ·  Delete…  ·  Admin →
    How important is this to you?

    Not at all You must login first! Important You must login first! Critical You must login first!

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    Submit Rating

11. compression

    When applying data compression differences (table or index), use rebuild partition or rebuild index rather than drop & create, both to minimize impact, and in the case of primary keys on replicated tables, avoid failure because replicated tables can't be dropped.
    e.g.,
    ALTER TABLE schema.TableName REBUILD PARTITION = ALL WITH (DATACOMPRESSION = PAGE);
    ALTER INDEX [IndexName] ON schema.TableName REBUILD PARTITION = ALL WITH (DATACOMPRESSION=PAGE);

    1 vote

    Vote Vote Vote

    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    1 vote 2 votes 3 votes Remove votes

    You have left! (?) (thinking…)
    0 comments  ·  Edit…  ·  Delete…  ·  Admin →
    How important is this to you?

    Not at all You must login first! Important You must login first! Critical You must login first!

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    Submit Rating

12. Add an option to the behavior section to include SORT_IN_TEMPDB = ON on all of the CREATE INDEX statements.

    For those of us working with data warehouses, using SORTINTEMPDB is the default behavior we need.

    9 votes

    Vote Vote Vote

    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    1 vote 2 votes 3 votes Remove votes

    You have left! (?) (thinking…)
    1 comment  ·  Delete…  ·  Admin →
    How important is this to you?

    Not at all You must login first! Important You must login first! Critical You must login first!

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    Submit Rating

13. SQL Compare to create indexes using WITH (ONLINE = ON)

    When creating indexes on large tables, it would be very beneficial to include the "WITH (ONLINE = ON)" in the create statement for the index to prevent table locking. This will need to be optional because not all server versions support it.

    1 vote

    Vote Vote Vote

    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    1 vote 2 votes 3 votes Remove votes

    You have left! (?) (thinking…)
    0 comments  ·  Delete…  ·  Admin →
    completed  ·  AdminStephanie (Product Manager, Redgate) responded

    This is an option in the project:

    https://documentation.red-gate.com/sc14/setting-up-the-comparison/setting-project-options#Settingprojectoptions-AddONLINE=ONwhencreatingindexes

14. When tables have different number of indexes the compare will show differences when in reality the indexes are the same, just not aligned.

    A table on server 1 has 2 indexes. The same table on server 2 has 4 indexes. Depending on the order that the script generates the DDL the compare might show all 4 indexes as different/new where it should show 2 as the same and 2 new.

    90 votes

    Vote Vote Vote

    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    1 vote 2 votes 3 votes Remove votes

    You have left! (?) (thinking…)
    5 comments  ·  Delete…  ·  Admin →
    How important is this to you?

    Not at all You must login first! Important You must login first! Critical You must login first!

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    Submit Rating

15. Allow indexes to be excluded by filtering

    I have 2 databases - production, and archive. Archive has all the indexes of production (starting with "IX") but also has some archive only ones (starting with "AIX"). Would be nice if I could add a filter to "exclude indexes starting with 'AIX-'" to my comparison - currently I have to export to a script, and manually delete all the "drop index AIX_..." sections.

    1 vote

    Vote Vote Vote

    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    1 vote 2 votes 3 votes Remove votes

    You have left! (?) (thinking…)
    0 comments  ·  Edit…  ·  Delete…  ·  Admin →
    How important is this to you?

    Not at all You must login first! Important You must login first! Critical You must login first!

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    Submit Rating

16. Option to script indexes and triggers into their own sub-folders

    Project level option to script indexes and triggers into their own sub-folders. This allow for a more granular check-in and makes text diffs more clear.

    37 votes

    Vote Vote Vote

    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    1 vote 2 votes 3 votes Remove votes

    You have left! (?) (thinking…)
    1 comment  ·  Delete…  ·  Admin →
    How important is this to you?

    Not at all You must login first! Important You must login first! Critical You must login first!

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    Submit Rating

17. risk

    There are some changes that are riskier than others, not because of the type of change, but because of the object. For example, some changes require rebuilding a table or index. This can be a simple process if there are a few hundred rows, but it's a risky, slow process if there are a few billion.

    It would be nice to add a "risk" value to certain tables, so that when I see a list of changes, I can determine if this particular object ought to be separated out as a different deployment.

    6 votes

    Vote Vote Vote

    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    1 vote 2 votes 3 votes Remove votes

    You have left! (?) (thinking…)
    1 comment  ·  Delete…  ·  Admin →
    How important is this to you?

    Not at all You must login first! Important You must login first! Critical You must login first!

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    Submit Rating

18. Ignore indexes option should not query indexes

    If indexes & partition schemas/functions are set to be ignored, SQL Compare should not attempt to query them. Additionally would be helpful for other object types that are ignored so that the queries are streamlined to only what is selected for compare. This would be a performance enhancement, the pain is where there is a high number of partitions where the intention is just to compare something simple like stored procedures and UDFs.

    1 vote

    Vote Vote Vote

    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    1 vote 2 votes 3 votes Remove votes

    You have left! (?) (thinking…)
    0 comments  ·  Edit…  ·  Delete…  ·  Admin →
    How important is this to you?

    Not at all You must login first! Important You must login first! Critical You must login first!

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    Submit Rating

19. Filter Improvements

    I use SQL Compare very rarely. Most of the time I use it to find the differences between two databases with different structure version. And therefore I need to see in a blink of an eye what dml triggers are missing or outdated or whatever. Currently there is no possibility to filter for table objects like dml triggers.
    It would be very nice if there is an option to restrict the view depending on sub elements like Index, Column, Collation, Trigger.
    Maybe it would be enough if the seach bar at the top would also search the SQL view of each entry, so that it restricts the list view and I am able to see quickly what matters most to me.

    I use SQL Compare very rarely. Most of the time I use it to find the differences between two databases with different structure version. And therefore I need to see in a blink of an eye what dml triggers are missing or outdated or whatever. Currently there is no possibility to filter for table objects like dml triggers.
    It would be very nice if there is an option to restrict the view depending on sub elements like Index, Column, Collation, Trigger.
    Maybe it would be enough if the seach bar at the top would also search the SQL view… more

    7 votes

    Vote Vote Vote

    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    1 vote 2 votes 3 votes Remove votes

    You have left! (?) (thinking…)
    0 comments  ·  Delete…  ·  Admin →
    How important is this to you?

    Not at all You must login first! Important You must login first! Critical You must login first!

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    Submit Rating

20. Allow migration of Indexes ONLY

    I have multiple databases which have the same columns (mostly) but sometimes differing defaults.

    They also have different indexes.

    I'd like to choose to ONLY sync indexes across.

    Obviously the system would need to be intelligent enough to sync only those that have the columns they use/include in both source and destination.

    1 vote

    Vote Vote Vote

    Vote

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    1 vote 2 votes 3 votes Remove votes

    You have left! (?) (thinking…)
    0 comments  ·  Edit…  ·  Delete…  ·  Admin →
    How important is this to you?

    Not at all You must login first! Important You must login first! Critical You must login first!

    We're glad you're here

    Please sign in to leave feedback

    Signed in as (Sign out)

    Close

    Close

    Submit Rating