http://ayende.comAyende @ RahienCopyright (C) Ayende Rahien 2004 - 2021 (c) 202660Working with legacy db is somewhat a "panic". Just the last week I did the very same modification you did, adding a nullable column as last column of some table to add a unique primary key, and this broke some code that is doing INSERT without specifying names of the columns, so adding a column broke the insert :( This is a very similar problem mentioned by Steve, I saw databases with tons of views, and SELECT * in all the view.... http://ayende.com/132097/legacy-etl-solutions#comment9http://ayende.com/132097/legacy-etl-solutions#comment9Wed, 16 Nov 2011 08:26:17 GMTCollation keys FTW. Did not (atleast off hand) know about the rowversion. Will have to remember that one if I need to ever implement change tracking in SQL to make sure rows haven't changed prior to my pending update.http://ayende.com/132097/legacy-etl-solutions#comment8http://ayende.com/132097/legacy-etl-solutions#comment8Mon, 14 Nov 2011 18:07:43 GMTThis is great information! I did not know about the rowversion data type. That won't have saved quite a few headaches in the past.http://ayende.com/132097/legacy-etl-solutions#comment7http://ayende.com/132097/legacy-etl-solutions#comment7Fri, 11 Nov 2011 16:53:52 GMTI worked on a dev project where one day the offshore team called us up and said "Hey, your data changes broke our code". Turned out that they were doing SELECT * with ordinal numbering on a multi table join. We had a nice discussion about why it's important to use column names in your SELECT. Especially since they only needed about 10 fields out of 50. :-)http://ayende.com/132097/legacy-etl-solutions#comment6http://ayende.com/132097/legacy-etl-solutions#comment6Fri, 11 Nov 2011 15:20:47 GMTDamien, Yes, that is fairly accurate case of where this can break. It can also break if they are making assumptions over the number of columns involved. In that particular database, there are _so many_ columns, that they are always using column names, if only to reduce the data loaded, so that isn't a real issuehttp://ayende.com/132097/legacy-etl-solutions#comment5http://ayende.com/132097/legacy-etl-solutions#comment5Fri, 11 Nov 2011 12:55:39 GMT@Karep - that is the reason for the 3rd rule; If you were to have a query like the following, then the ordinal numbering would change for the columns included in AnotherTable and AndAnother: SELECT * FROM TableWithAddedColumn JOIN AnotherTable ... JOIN AndAnother ... http://ayende.com/132097/legacy-etl-solutions#comment4http://ayende.com/132097/legacy-etl-solutions#comment4Fri, 11 Nov 2011 12:28:17 GMT@Damiem: Doesn't ADD COLUMN add column at the end/after all columns? Then it can't break anything even if someone is consuming values from resultsets using ordinals.http://ayende.com/132097/legacy-etl-solutions#comment3http://ayende.com/132097/legacy-etl-solutions#comment3Fri, 11 Nov 2011 11:59:06 GMTPrevious post had some of my stars (**) eaten in the second line, hopefully you can work out where. Incidentally, I'd never recommend that code be written where those first two circumstances are true, but if this is legacy code, who knows?http://ayende.com/132097/legacy-etl-solutions#comment2http://ayende.com/132097/legacy-etl-solutions#comment2Fri, 11 Nov 2011 11:33:31 GMT"An operation that it is safe to make since it can't break..." - that may be true with the current system, but isn't generally true. Under a combination of 3 circumstances, it isn't safe: One or more consumers of result sets access values using the ordinal position of the columns in the result set The result set is constructed using "SELECT *" (or at least, "t.*", where t is the name of the table you're adding the columns to or an alias of such) The table isn't the "rightmost" table in the FROM clause. If all of those are true, then these new columns will cause existing columns in the result set to move, and the consumers will be misaligned.http://ayende.com/132097/legacy-etl-solutions#comment1http://ayende.com/132097/legacy-etl-solutions#comment1Fri, 11 Nov 2011 11:31:15 GMT