SQL Server 表默认排序 [英] SQL Server table is sorted by default

问题描述

我有一个简单的 SSIS 包，我将数据从平面文件导入 SQL Server 表 (SQL Server 005).文件包含 70k 行，表没有主键.导入成功，但是当我打开 SQL Server 表时，行的顺序与文件的顺序不同.仔细观察后，我看到表中的数据默认按第一列排序.为什么会发生这种情况?以及如何避免默认排序?谢谢.

I have simple SSIS package where I import data from flat file into SQL Server table (SQL Server 005). File contains 70k rows and table has no primary key. Importing is sucessful but when I open SQL Server table the order of rows is different from the that of file. After observing closely I see that data in table is sorted by default by first column. Why this is happening? and how I can avoid default sort? Thanks.

推荐答案

除非在 SQL 查询中指定 order by，否则不能依赖排序.SQL 是一种适用于集合的关系代数.这些集合是无序的.数据库表没有固有的顺序.

You cannot rely on ordering unless you specify order by in your SQL query. SQL is a relational algebra that works with sets. Those sets are unordered. Database tables do not have an intrinsic ordering.

很可能由于从表中检索数据的方式而对集合进行了排序.这可能基于主键、插入顺序、聚集键、基于查询的执行计划或表中的实际数据甚至月相的看似随机的顺序.

It may well be that the sets are ordered due to the way the data is retrieved from the tables. This may be based on primary key, order of insertion, clustered key, seemingly random order based on the execution plan of the query or the actual data in the table or even the phase of the moon.

底线，如果您想要特定订单，请使用order by.如果您不想要特定的顺序，DBMS 可以按任何顺序免费提供您的行，包括基于第一列的顺序.

Bottom line, if you want a specific order, use order by. If you don't want a specific order, the DBMS is free to deliver your rows in any order, including one based on the first column.

如果您确实希望根据导入文件中的位置对它们进行排序，则应向表中添加另一列以根据其在该文件中的位置存储递增的数字.然后使用 order by 使用该列.但这是一个相当随意的排序顺序，您通常最好选择对数据更有意义的排序顺序(交易 ID、日期/时间、客户编号或您拥有的任何其他内容).

If you really want them sorted depending on the position in the import file, you should add another column to the table to store an increasing number based on its position in that file. Then use order by using that column. But that's a pretty arbitrary sort order, you're generally better off choosing one that makes more sense to the data (transaction ID, date/time, customer number or whatever else you have).

如果您想避免默认排序(但可能是可变的)，请使用特定排序.

If you want to avoid the default sort (however variable that may be), use a specific sort.

这篇关于SQL Server 表默认排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！