none
Schema Comparisons - Ignore column order RRS feed

  • Question

  • I'm using SSDT for the first time and I want to be able to ignore column ordering when doing schema comparisons.

    There was a setting in GDR that I cannot find called - "Force table column order to be identical" - does this still exist? Also in the old sqldeployment file there was an IgnoreColumnOrder setting. Cannot find either of these.

    Monday, April 16, 2012 3:59 PM

Answers

All replies

  • As you have discovered, the 'ignore column order' setting is not available in SSDT, either for schema compare or deployment. There are tentative plans to reintroduce this option in the future.

    Sorry for the trouble this is causing you, and I will definitely send your feedback to the team.


    -Genevieve Orchard (SQL Server Data Platform team)

    Monday, April 16, 2012 6:53 PM
  • This issue is VERY important. We release database updates to production environments incrementally, and in the past,  new columns were not always added to the end of the table. (We have since made it a standard practice to always append to the end of the table, but we have to live with things as they are.) Be that as it may, we have some tables where column order varies between the target database and the model. If it is a very large table, with tens of millions of rows, a rebuild during an upgrade could take hours of production downtime to complete. Not acceptable.

    We are going to look at other ways to mitigate this concern, but we may have to abandon using SSDT for deployment if this issue cannot be resolved.

    Friday, June 29, 2012 7:11 PM
  • So, if the issue is out-of-band changes to the table, why can't you just change the column order within the SSDT project to match what is now in production? Ideally, SSDT should match what is in production, not a close approximation of it. Maybe I'm missing something?
    Monday, July 2, 2012 3:07 PM
  • I would second this feature request. It is very important.
    • Proposed as answer by BuildJedi Friday, February 22, 2013 6:10 PM
    Monday, July 30, 2012 4:23 AM
  • The September 2012 SSDT update does not appear to include this feature. I would also like to see it and hope that the team will see fit to include it in a future release.
    Wednesday, October 31, 2012 12:00 PM
  • Why would this feature be removed?  Please bring it back.  You are forcing VSDB developers to use SSDT (No sql2012 support in vsdb).

    Wednesday, November 7, 2012 8:08 PM
  • it's crazy to remove this feature.  sometimes ordering cannot be guaranteed across multiple environments (dev, test, prod) and having a script get generated that creates a temp table and copies data from table to table, especially on million row tables, because is unacceptable!  I cannot use this as I have VS 2010 for the past few years. 
    Friday, November 16, 2012 3:38 PM
  • I am also looking for this feature.  New to SSDT but totally agree that it will be pretty important...thanks!
    Wednesday, February 20, 2013 5:59 PM
  • This is definately a big problem especially when dealing with a lot of data.
    Tuesday, August 13, 2013 6:28 PM
  • How come this was not included in the last update? Please, please add it...

    Also, how about ignoring schema so [dbo].[sometable] should match sometable

    Friday, August 30, 2013 1:12 PM
  • I need to re-stress how important this feature has become for us.  We are faced with literally re-ordering thousands of columns in hundreds of our databases now.  Also, it will be an ongoing issue as people add properties to classes, then re-order their classes, then consequently update the database project accordingly, essentially re-ordering the entire table.  It will also be a problem in non-domain-driven-design systems (we have both) where a column is simply added to the middle of a table.

    Should I open another thread since this one is open?  Any updates?  Pretty please?

    Thank you!

    Ryan

    Thursday, January 9, 2014 5:14 PM
  • Agreed with everyone else, guys. This is insane that you removed it. When can we expect it back in?
    Thursday, April 24, 2014 5:27 AM
  • Just posting a link to another thread I started, which is slightly related.  

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/0fc65348-7d1e-4f35-a5c2-2630ffa8e868/schemacompare-and-dacservices-deploy-and-generatescript-are-all-way-too-slow-for-large-database?forum=ssdt

    Anyway, the part I wanted people to realize, if they didn't, as mentioned in there, is the RedGate SQL Compare tool can help you work around this issue, in part, if you have many large databases, like we do, that you need to physically re-arrange so that the physical order matches.  I tried with SSDT, but unfortunately it died unexpectedly several times in the past.  I'm hoping that improves over time, which is why I started the other post.

    Still waiting on this like everyone else though...

    Monday, April 28, 2014 7:31 PM
  • More than two years later and that option has still not returned.  Because SQL Server does not permit reordering of columns, the lack of an "ignore column order" option makes schema compare useless for any project in which columns are added or removed from tables which is essentially every project that remains under active development.

    Thursday, September 18, 2014 9:30 PM
  • I think it's safe to say that SSDT is another half baked product for visual studio.  It's predecessor (vsdbcmd) had column order functionality, but they did not migrate it to SSDT.
    Friday, September 19, 2014 9:10 PM
  • It's unfortunate that the general attitude of Microsoft seems to be geared towards coming up with excuses and explanations instead of solutions. I was excited to use schema/data compare in VS2013 but found that it's basically useless to us because of the lack of this feature. I'm just glad there are other reasonable programs on the market to do comparisons (RedGate, for example).

    It's evident that each year Microsoft becomes even more detached from reality. People complained about IE, Microsoft didn't solve the problems, and now hardly anyone uses IE. No one is going to use this shoddy comparison tool for the same reasons. You're just wasting people's time with incomplete products like this, and that's frustrating and builds resentment in the people who are paying to use your products and services. I'll have no problem jumping ship for any given Microsoft product the second a competitor releases something better.

    Friday, November 14, 2014 2:37 PM
  • With all due respect, to call SSDT a half baked product sounds a little strong. Our company has 15+ databases, some with 2500+ SPs, hundreds of tables. SSDT has been a godsend in terms of easing our deployment and synchronization burden across our various environments. Are there additional features I'd like to see in it? Sure. Frankly, I wouldn't mind seeing them open source SSDT like they've been doing for stuff like ASP.NET and other parts of .NET. I've taken the concept and applied limited replication management. There's a number of places that it could be enhanced.
    Friday, November 14, 2014 5:32 PM
  • There are several other comparison tools that work perfectly well and have been around for a long time. This establishes somewhat of a "standard" for what the minimum functionality of such a tool should be.

    Generally, if a business decides to enter a new product into a market where there is already competition, the attitude is: "we can do better," and a superior product is created as a result. That's not what Microsoft did with this tool - not even close - and that's why people are calling it "half-baked," among other things. Microsoft came to the game late and didn't show up with a full team.

    Friday, January 16, 2015 5:24 PM
  • Are there any updates on this feature - I can't see it in there yet.

    Thanks!

    Thursday, March 5, 2015 1:29 PM
  • I've submitted it to the more "official" product support/suggestion connect site. Everyone please go upvote and get more visibility into this.

    https://connect.microsoft.com/SQLServer/Feedback/Details/1221587

    Thursday, April 2, 2015 11:33 PM
  • Does not help in case you have multiple different production systems.
    Wednesday, February 24, 2016 11:46 AM
  • Thanks for adding this to connect.

    Everyone should against vote for this who did not yet... TY

    Wednesday, February 24, 2016 11:55 AM
  • I know people have differences in their environments but we should all be moving towards having our environments in sync, otherwise things like testing and automatic deployments are hard.

    Once you have your environments in sync you don't need to ever do it again.

    The one edge case is where you have schemas that you deploy to that are out of yoru control and in that case you should look at different solutions *or* extend the deployment using a deployment contributor.


    SSDT Dev Pack - Open source (free) Visual Studio Add-in: -Extract select statements into TVF's -Create stub tSQLt tests -Change keyword casing -Name constraints -Ui for Merge statements https://the.agilesql.club/Projects/SSDT-Dev-Pack

    Wednesday, February 24, 2016 3:35 PM
  • Meh, Data Dude used to support the ignore column order option. Microsoft needs to open source this thing so we can continue its development. They have apparently stopped.
    Wednesday, February 24, 2016 4:06 PM
  • If you want to do something about it, vote this up which would give me enough to write a deployment contributor to detect when the only thing that has changed is column order and remove it from the deployment:

    https://connect.microsoft.com/SQLServer/feedback/details/2400056


    SSDT Dev Pack - Open source (free) Visual Studio Add-in: -Extract select statements into TVF's -Create stub tSQLt tests -Change keyword casing -Name constraints -Ui for Merge statements https://the.agilesql.club/Projects/SSDT-Dev-Pack

    Wednesday, February 24, 2016 9:05 PM
  • As others have stressed this is critical -- a show-stopper.  

    Similarly disturbing & critical, I've discovered that SSDT will reorder included columns in indexes.  SSDT should honor the scripted order, & further, should provide an option to ignore column order in included columns in indexes since that does not affect performance.

    I want to use SSDT, but cannot -- I cannot afford to have deployments unnecessarily rebuild tables or indexes on production tables.

    Friday, February 26, 2016 2:20 PM
  • As others have stressed this is critical -- a show-stopper.  

    Similarly disturbing & critical, I've discovered that SSDT will reorder included columns in indexes.  SSDT should honor the scripted order, & further, should provide an option to ignore column order in included columns in indexes since that does not affect performance.

    I want to use SSDT, but cannot -- I cannot afford to have deployments unnecessarily rebuild tables or indexes on production tables.


    Don't not use Ssdt because of this, use Ssdt but use redgate sqlcompare to create your qa/prod deployment scripts. Ssdt is much more than just the final deployment.

    SSDT Dev Pack - Open source (free) Visual Studio Add-in: -Extract select statements into TVF's -Create stub tSQLt tests -Change keyword casing -Name constraints -Ui for Merge statements https://the.agilesql.club/Projects/SSDT-Dev-Pack

    Friday, February 26, 2016 2:23 PM
  • SQL Data Tools team has revised this ask and started an effort to reintroduce Ignore Column Order option to SSDT. We will keep you updated with more information as we make a progress for the feature design and implementation.

    Thanks,

    Eric Kang

    SQL Data Tools team

     
    Monday, February 29, 2016 11:19 PM
  • "We will keep you updated with more information"

    Hi Eric, do you have any news on this? 


    Tuesday, August 9, 2016 2:33 PM
  • SQL Data Tools team has revised this ask and started an effort to reintroduce Ignore Column Order option to SSDT. We will keep you updated with more information as we make a progress for the feature design and implementation.

    Thanks,

    Eric Kang

    SQL Data Tools team

     

    Hello Eric,

    why are my postings getting removed??? Once again I would like to have some information regarding this announcement to "keep us updated"... Please, after about 6 months of silence, let people know what is going on.

    Wednesday, August 17, 2016 10:23 AM
  • Hi Markus,

    The Ignore Column Order feature work made into our engineering execution stage and it is in progress. It is a good news for all in this thread. Please understand that I cannot disclose the exact release month or date but we plan to release it this year. 

    Thursday, August 18, 2016 5:00 PM
  • Please understand that I cannot disclose the exact release month or date but we plan to release it this year. 

    Half a decade has passed in this thread alone.  What else couldn't we understand ... ...
    Friday, August 19, 2016 3:04 AM
  • I came here searching for an answer because I couldn't find an "ignore column order" option. I am amazed to find out this issue is 4 years old! At least there are some good news, I hope it gets released asap.
    Tuesday, August 30, 2016 1:32 PM
  • SQL Data Tools team has revised this ask and started an effort to reintroduce Ignore Column Order option to SSDT. We will keep you updated with more information as we make a progress for the feature design and implementation.

    That's phantastic!

    Holding my breath. Any news yet?

    Friday, September 2, 2016 4:35 PM
  • Hi Markus,

    The Ignore Column Order feature work made into our engineering execution stage and it is in progress. It is a good news for all in this thread. Please understand that I cannot disclose the exact release month or date but we plan to release it this year. 

    Any update on this, please?
    Tuesday, September 27, 2016 6:20 PM
  • The ability to ignore the column order is still not implemented in the current release of SQL Server Data Tools. It's a significant inconvenience when comparing databases where one of the databases has the fields in the order they were added and another is a new copy of the database with the columns sorted.
    Wednesday, November 9, 2016 8:45 PM
  • Hi Markus,

    The Ignore Column Order feature work made into our engineering execution stage and it is in progress. It is a good news for all in this thread. Please understand that I cannot disclose the exact release month or date but we plan to release it this year. 


    So we're days away from the end of "this year" any news?
    Friday, December 30, 2016 4:48 PM
  • Hi RJ,

    There is some bad news -- ignore column order isn't finished yet.

    There is some good news, though -- work on the feature is (I promise) in-progress.  (I'm working on it.)

    I'll avoid providing an estimate as to when it will be available (because that can change due to unforeseen circumstances, as happened with Eric's estimate), but I will provide a guarantee that the feature will not be released prior to February 2017.

    Saturday, December 31, 2016 12:47 AM
    Moderator
  • Hi Steven,

    I, like everyone else, I suppose, will be very happy to have "ignore column order" back in as a feature. I'm often in Visual Studio and using the Schema Compare features exposed there. Will the work you're doing flow into Visual Studio, or do we need to get a "compensating feature request" going with the Visual Studio  team?

    Thanks,

    -Brian Eriksen

    Wednesday, January 25, 2017 10:40 PM
  • The SQL Server tools team that I'm part of owns the Schema Compare, Data Compare, SQL Server Object Explorer, and SQL Server Database Project system inside of VS.  So I'm doing the work for this functionality to appear in VS, SqlPackage.exe and the DACFx API.

    Friday, January 27, 2017 9:49 PM
    Moderator
  • OMG... I have to say that because I'm as giddy as a school girl. Years after posting about this (connect link below) and about to give up on this, an actual MSFT says work is being done!? Hell hath frozen over!

    https://connect.microsoft.com/SQLServer/Feedback/Details/1221587

    Monday, February 13, 2017 11:52 PM
  • This feature has been completed and checked-in. It's currently planned to ship in the March release of SSDT.

    Saturday, February 18, 2017 1:18 AM
    Moderator
  • Hi, 

    If you are open to using third party tools, I suggest you have a look at xSQL Schema Compare. The setting you are looking for, can be found on Comparison options > Compare column ordinal. 

    Wednesday, March 8, 2017 10:09 AM
  • This feature has been completed and checked-in. It's currently planned to ship in the March release of SSDT.

    AAAAAAHHHHH!!!!!!!!! how did I miss this?!?!?!

    Thanks to you and your team Steven :)


    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.


    Kevin3NF on Twitter
    DallasDBAs

    Thursday, March 9, 2017 7:44 PM
  • Hi,

    It doesn't appear to be in SSDT in the VS2017 RTM release, could you elaborate on the specific version we can expect it in?

    Also, will it be available in SQLPackage.exe & DACFx too? When? Which version?

    Please provide details.

    Thursday, March 16, 2017 7:50 AM
  • This feature has been completed and checked-in. It's currently planned to ship in the March release of SSDT.

    We are not finding it...

    Where is the change? which version? 

    March release - Dates plz.

    Thursday, March 16, 2017 7:52 AM
  • We're almost at the end of March. I wonder if it's still coming?
    Wednesday, March 29, 2017 7:41 PM
  • An RC build with ignore column order is available. See this blog post: 

    https://blogs.msdn.microsoft.com/ssdt/2017/03/10/sql-server-data-tools-17-0-rc-and-ssdt-in-vs2017/

    Thursday, April 6, 2017 1:07 AM
    Moderator
  • An RC build with ignore column order is available. See this blog post: 

    https://blogs.msdn.microsoft.com/ssdt/2017/03/10/sql-server-data-tools-17-0-rc-and-ssdt-in-vs2017/

    Will the ignore column order be available as part of SQLPackage.exe as well ?

    22 years of database experience, most with SQL Server. Please 'Mark as answered' those posts that helped you.

    Friday, April 7, 2017 1:03 PM
  • Yes. For some reason (dunno why) they decided not to provide a standalone download of DacFramework.msi as part of this RC release. Presumably that will come later.  But, a version of SqlPackage.exe that supports ignore column order is included with the RC -- it's just inside the Visual Studio folder instead of the SQL Server folder. After installing the RC look for SqlPackage.exe here:

    C:\Program Files (x86)\Microsoft Visual Studio 14.0\Common7\IDE\Extensions\Microsoft\SQLDB\DAC\140

    Running sqlpackage /a:publish /? should produce a long list of options, with /p:IgnoreColumnOrder being among them.


    Monday, April 10, 2017 8:22 PM
    Moderator