SQL 无需表扫描即可获取标识列的最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5501142/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Get max value for identity column without a table scan
提问by Blorgbeard is out
I have a table with an Identity column Id.
我有一个带有 Identity 列的表Id。
When I execute:
当我执行：
select max(Id) from Table
SQL Server does a table scan and stream aggregate.
SQL Server 执行表扫描和流聚合。
My question is, why can it not simply look up the last value assigned to Id? It's an identity, so the information must be tracked, right?
我的问题是，为什么它不能简单地查找分配给 Id 的最后一个值？这是一个identity，所以必须跟踪信息，对吗？
Can I look this up manually?
我可以手动查找吗？
回答by RB.
You can use IDENT_CURRENTto look up the last identity value to be inserted, e.g.
您可以使用IDENT_CURRENT查找要插入的最后一个标识值，例如
IDENT_CURRENT('MyTable')
However, be cautious when using this function. A failed transaction can still increment this value, and, as Quassnoistates, this row might have been deleted.
但是，使用此功能时要小心。失败的事务仍然可以增加此值，并且作为Quassnoi状态，此行可能已被删除。
It's likely that it does a table scan because it can't guarantee that the last identity value is the MAX value. For example the identity might not be a simple incrementing integer. You could be using a decrementing integer as your identity.
它很可能会进行表扫描，因为它不能保证最后一个标识值是 MAX 值。例如，身份可能不是一个简单的递增整数。您可以使用递减整数作为您的身份。
回答by Quassnoi
What if you have deleted the latest record?
如果您删除了最新的记录怎么办？
The value of IDENTITYwould not correspond to the actual data anymore.
的值IDENTITY将不再对应于实际数据。
If you want fast lookups for MAX(id), you should create an index on it (or probably declare it a PRIMARY KEY)
如果您想要快速查找MAX(id)，您应该在其上创建一个索引（或者可能将其声明为 a PRIMARY KEY）
回答by Tomas
Is the table clustered on that column? Can you use Top 1:
该表是否聚集在该列上？你能用Top 1吗：
SELECT TOP 1 [ID]
FROM [Table]
order by ID desc
回答by Srinivasan Anandan
You can run this following statement and remove the last UNION ALL. Run this statement to get the current Identity values.
您可以运行以下语句并删除最后一个UNION ALL. 运行此语句以获取当前标识值。
SELECT
' SELECT '+char(39)+[name]+char(39)+' AS table_name, IDENT_CURRENT('+char(39)+[name]+char(39)+') AS currvalue UNION ALL'
AS currentIdentity
FROM sys.all_objects WHERE type = 'U'
回答by Joe Phillips
I'm pretty sure you could set up an index on that field in descending order and it would use that to find the largest key. It should be fast.
我很确定您可以按降序在该字段上设置索引，它会使用它来查找最大的键。它应该很快。
回答by Cade Roux
Is the Id the primary key or indexed? Seems like it should do a seek in those cases.
Id 是主键还是索引？在这些情况下，它似乎应该进行搜索。
