I need to be able to determine the order which rows have been inserted into a table (there are no updates). Can I use an identity column to do this? I know that there may be gaps, but are the values guaranteed to be increasing by insertion order?
我需要能够确定哪些行已插入表中的顺序（没有更新）。我可以使用标识列来执行此操作吗？我知道可能存在差距，但保证按插入顺序增加值吗？
3 个解决方案
#1
7
Largely yes, as long as you don't ever reset it or insert rows with bulk copy, or use IDENTITY_INSERT. And of course assuming that you don't overflow the data-type (which could be impressive).
很大程度上是的，只要你没有重置它或插入带有批量副本的行，或者使用IDENTITY_INSERT。当然假设您没有溢出数据类型（这可能令人印象深刻）。
#2
2
Yes. Any gaps caused by deletions will not be reused
是。由删除引起的任何间隙都不会被重复使用
#3
2
As discussed by Marc, yes you can with provisos
正如马克所讨论的，是的，你可以提供附带条件
What you should do however to definitively fix the problem is add a column
但是，为了明确解决问题，您应该做的是添加一列
dteInserted datetime not null default getdate()
Then you just select ordered by this.
然后你只需按此顺序选择。
Myself I automatically add such a column onto any 'data' table in any database I'm designing. Storage is cheap nowadays and timestamping the insertion date on a row is always useful at some point.
我自己将这样一个列自动添加到我正在设计的任何数据库中的任何“数据”表中。现在存储很便宜，并且在一行上对插入日期加时间戳在某些时候总是有用的。
#1
7
Largely yes, as long as you don't ever reset it or insert rows with bulk copy, or use IDENTITY_INSERT. And of course assuming that you don't overflow the data-type (which could be impressive).
很大程度上是的，只要你没有重置它或插入带有批量副本的行，或者使用IDENTITY_INSERT。当然假设您没有溢出数据类型（这可能令人印象深刻）。
#2
2
Yes. Any gaps caused by deletions will not be reused
是。由删除引起的任何间隙都不会被重复使用
#3
2
As discussed by Marc, yes you can with provisos
正如马克所讨论的，是的，你可以提供附带条件
What you should do however to definitively fix the problem is add a column
但是，为了明确解决问题，您应该做的是添加一列
dteInserted datetime not null default getdate()
Then you just select ordered by this.
然后你只需按此顺序选择。
Myself I automatically add such a column onto any 'data' table in any database I'm designing. Storage is cheap nowadays and timestamping the insertion date on a row is always useful at some point.
我自己将这样一个列自动添加到我正在设计的任何数据库中的任何“数据”表中。现在存储很便宜，并且在一行上对插入日期加时间戳在某些时候总是有用的。