First time user question - From a Non DBA

mikener

I just used SQL Compare 6.0 for the first time. I loved the comparision feature. I do have a question about synchronization. I added a few new fields to the middle of one of my development database tables. When I did the synchronization to the corresponding table in my beta database, these fields were added to the end the beta database table (not the middle as I expected). I was therefore wondering the following:

1. I am not a database expect so I was wondering if the order of the fields in the table could in anyway affect anything from performance to how the data is stored?

2. Why did the subsequent compare show the tables were identical if the fields between development and beta are in a different order?

3. While I currently never use an Select * from table, I was wondering if the fact that my fields in the development table being in a different order then beta (or production) table would be a problem for me if I ever did use a Select * from table?

4. Am I correct that the different ordering of fields will affect a Select * from Table

Thanks

Robert C

Hi,

Most implementations of SQL (including SQL Server) work on the basis that the order of fields isn't important, which is why you just see the fields added at the end. This also means that SQL Compare doesn't show up any difference between the tables. I don't think there's any way of inserting a field in a certain place in a table except by dropping and recreating the entire table.

Performance wise, there shouldn't be a difference, or at least none of any significance.

If you're using SELECT * statements, then you'll see the different ordering like you suggest, but as long as you index into them from your code using field names rather than numbers, you should be fine. So don't rely on a field being 3rd from the start, for example, as that might change. If you're working in ADO.NET, then you can index into an IDataRecord using a string, or you can call GetOrdinal() to get the number of the column with a given name. Both of these should be safe.

Hope that helps,
Robert

Michelle T

You can use the 'Force Column Order' option (in the options tab of the project configuration dialog) to maintain column order.

We don't do this by default for the reasons given by Robert above (it means having to rebuild the table, including copying all the data over into a temporary table and then back into the new rebuilt table, and it's rarely very important what order the columns are in).

If 'Force Column Order' is not selected, then our comparison will show the tables to be equal despite changes in column order.

mikener

I want to thank both Robert and Michelle for the fast and informative responses. I thought I would be okay since I am using the GetOrdinal method from ADO.NET, but its nice to get confirmation.

Thanks again.