I am using SQL-Server 2005.
我正在使用SQL-Server 2005。
I have a dev and prod database that have essentially the same data in them. When I do a compare with RedGate SQL Data Compare 5, it says that only 4 records differ. However, when I open up the tables and view them, they are in a completely different sort order. Neither table has an index or anything forcing the sort order, and I'm trying to make sure my dev is sorted in the same order as prod. But RedGate won't tell me when I'm close, because it is apparently finding records that match, even if they aren't in the same sort order. How do I override that?
我有一个dev和prod数据库,其中包含基本相同的数据。当我与RedGate SQL Data Compare 5进行比较时,它表示只有4条记录不同。但是,当我打开表并查看它们时,它们的排序顺序完全不同。两个表都没有索引或任何强制排序顺序的东西,我正在尝试确保我的开发顺序与prod一样。但RedGate不会在我关闭时告诉我,因为它显然找到了匹配的记录,即使它们的排序顺序不同。我该如何覆盖呢?
I'd like to use the tool to tell when I've figured out the sort order, to make sure I have it right.
我想用这个工具告诉我何时找出排序顺序,以确保我做对了。
1 个解决方案
#1
Differing sort order just means that the rows are placed in the data files in a physically different manner, but the data still matches exactly, so DC still reports that they match. There's no option to get it to "Respect physical disk order" (which is essentially what you're asking), because even if it noticed a difference, there's no way to synchronize the difference, as SQL Server has no method to control of modify disk order.
不同的排序顺序只是意味着行以物理上不同的方式放置在数据文件中,但数据仍然完全匹配,因此DC仍然报告它们匹配。没有选择让它“尊重物理磁盘顺序”(这基本上就是你所要求的),因为即使它注意到差异,也无法同步差异,因为SQL Server没有方法可以控制修改磁盘顺序。
The order that data is pulled from the disk when you query a table shouldn't ever be relied on - if you require a certain order, you should include an "ORDER BY" clause in your query to force a certain sort order. If there's some reason that you can't use ORDER BY, your only other option to force a certain sorting order is adding a clustered index on the field by which you'd like to sort the table.
不应依赖查询表时从磁盘中提取数据的顺序 - 如果需要某个顺序,则应在查询中包含“ORDER BY”子句以强制执行某个排序顺序。如果有一些原因导致您无法使用ORDER BY,则强制执行某个排序顺序的唯一其他选项是在您要对表进行排序的字段上添加聚簇索引。
If you really need to re-order the data, then you'll need to truncate the table that's "wrong" and populate it with an "INSERT INTO [bad order table] SELECT * FROM [good order table]". Even then, while it should have the desired effect, there's no guarantee.
如果你真的需要重新排序数据,那么你需要截断“错误”的表并用“INSERT INTO [错误的订单表] SELECT * FROM [good order table]”填充它。即使这样,虽然它应该具有预期的效果,但是不能保证。
I'd recommend the order by clause as your best option. If there's a reason none of these options will work, let me know and I can try to suggest something else.
我建议将order by子句作为您的最佳选择。如果有一个原因,这些选项都不起作用,请告诉我,我可以尝试提出其他建议。
#1
Differing sort order just means that the rows are placed in the data files in a physically different manner, but the data still matches exactly, so DC still reports that they match. There's no option to get it to "Respect physical disk order" (which is essentially what you're asking), because even if it noticed a difference, there's no way to synchronize the difference, as SQL Server has no method to control of modify disk order.
不同的排序顺序只是意味着行以物理上不同的方式放置在数据文件中,但数据仍然完全匹配,因此DC仍然报告它们匹配。没有选择让它“尊重物理磁盘顺序”(这基本上就是你所要求的),因为即使它注意到差异,也无法同步差异,因为SQL Server没有方法可以控制修改磁盘顺序。
The order that data is pulled from the disk when you query a table shouldn't ever be relied on - if you require a certain order, you should include an "ORDER BY" clause in your query to force a certain sort order. If there's some reason that you can't use ORDER BY, your only other option to force a certain sorting order is adding a clustered index on the field by which you'd like to sort the table.
不应依赖查询表时从磁盘中提取数据的顺序 - 如果需要某个顺序,则应在查询中包含“ORDER BY”子句以强制执行某个排序顺序。如果有一些原因导致您无法使用ORDER BY,则强制执行某个排序顺序的唯一其他选项是在您要对表进行排序的字段上添加聚簇索引。
If you really need to re-order the data, then you'll need to truncate the table that's "wrong" and populate it with an "INSERT INTO [bad order table] SELECT * FROM [good order table]". Even then, while it should have the desired effect, there's no guarantee.
如果你真的需要重新排序数据,那么你需要截断“错误”的表并用“INSERT INTO [错误的订单表] SELECT * FROM [good order table]”填充它。即使这样,虽然它应该具有预期的效果,但是不能保证。
I'd recommend the order by clause as your best option. If there's a reason none of these options will work, let me know and I can try to suggest something else.
我建议将order by子句作为您的最佳选择。如果有一个原因,这些选项都不起作用,请告诉我,我可以尝试提出其他建议。