确定不存在索引时添加的最新行 [英] Determine latest row added when no index is present

问题描述

我想检索最近添加的客户.我没有任何列存储添加行的日期/时间，我的主键是 Cust_ID，它不是 IDENTITY 列，不必输入按升序排列.

I want to retrieve the most recently added customer. I don't have any column which stores the date/time the row was added, and my primary key is Cust_ID, which is not an IDENTITY column and is not necessarily entered in ascending order.

这是我的表，Customer，假设已按此顺序添加了三行:

This is my table, Customer, and imagine three rows have been added in this order:

Cust_ID       Cust_Name      Cust_Age
-------------------------------------
2             C              23
6             A              25
3             B              22

在上面的例子中，我想获得最后一条记录(B).SQL Server 中是否有任何预定义函数可以返回表的最后一行(不依赖升序)?

in above example I want to get last record (B). Is there any predefined function in SQL Server which will return the last row of a table (without relying on ascending order)?

推荐答案

不，SQL Server 中没有预定义的函数会返回表的最后一行".

No, there is no predefined function in SQL Server which will return the "last row" of a table.

根据定义，表是一组无序的行.想象一下，你在一个袋子里扔了一堆弹珠.现在打开袋子，问其他人哪个大理石先进入或最后进入.现在把它们全部扔在地板上，当其他人进入房间时，问他们哪个先落地或最后落地.你不能这样做，因为没有额外的信息表明它们下降的顺序.

By definition, a table is an unordered set of rows. Imagine you throw a bunch of marbles in a bag. Now open the bag and ask someone else which marble went in first or last. Now throw them all on the floor, and when someone else comes in the room, ask them which hit the floor first or last. You can't do it, because there is no additional information that indicates anything about the order in which they fell.

对于 SQL Server 中的表也是如此.除非您添加 IDENTITY 列、日期时间列或触发器，或者使用外部功能(如更改跟踪、CDC、审核等)，否则 SQL Server 无法告诉您最后插入了哪一行.您可能认为只是从没有 order by 子句的表中选择看起来就像它以正确的顺序返回数据，这纯粹是巧合.下面是一个例子:

The same is true for a table in SQL Server. Unless you add an IDENTITY column, or a datetime column, or a trigger, or use external functionality like change tracking, CDC, auditing, etc., there is no way for SQL Server to tell you which row was inserted last. You may think that just selecting from the table without an order by clause looks like it is returning data in the right order, this is pure coincidence. Here is an example:

CREATE TABLE dbo.floobat
(
  ID INT PRIMARY KEY, 
  n VARCHAR(16), 
  x CHAR(4000) NOT NULL DEFAULT ''
);

INSERT dbo.floobat(ID,n) VALUES(1,'Sparky');
INSERT dbo.floobat(ID,n) VALUES(2,'Aaron');
INSERT dbo.floobat(ID,n) VALUES(3,'Norbert'); -- <-- inserted last

SELECT ID, n FROM dbo.floobat;

好的，所以默认情况下，这似乎没问题.结果:

Ok, so by default, this seems to be okay. Results:

ID    n
--    -------
1     Sparky
2     Aaron
3     Norbert -- < yes, this is right

但是，让我们对您的应用程序或其他依赖上述顺序的任何内容不知道的表进行更改:

However, let's make a change to the table that your application or whatever else relies on the above ordering will have no idea about:

CREATE NONCLUSTERED INDEX x ON dbo.floobat(n);

SELECT ID, n FROM dbo.floobat;

哦哦！结果:

ID    n
--    -------
2     Aaron
3     Norbert
1     Sparky -- < oops, this is no longer right

您必须记住这一点:如果您不包含 ORDER BY 子句，您就告诉 SQL Server 您不关心顺序.所以它将找到最有效的方式来返回数据，这可能会导致不同的观察顺序.添加上面的索引为 SQL Server 提供了一个更好的访问路径来检索数据.它仍然使用扫描，但该索引比聚集索引(只能在页面上容纳两行)瘦得多.

You have to remember this: if you don't include an ORDER BY clause, you are telling SQL Server that you don't care about order. So it is going to find the most efficient way to return the data, and that could lead to different observed ordering. Adding the index above gave SQL Server a better access path to retrieving the data. It still used a scan, but that index was way skinnier than the clustered index (which could only fit two rows on a page).

即使没有索引，您也可能不会得到您期望的结果，因为您的 Cust_ID 列未按升序插入这一事实令人费解.因此，如果您插入 5 而不是 2，那么在没有 ORDER BY 的情况下进行选择实际上会导致 2 然后 5 (假设不存在更好的索引).

Even without the index, you probably wouldn't get the results you expect, since it is convoluted by the fact that your Cust_ID column is not being inserted in ascending order. So if you insert 5 and than 2, selecting without an ORDER BY will actually result in 2 then 5 (assuming no better index exists).

除了创建(或删除、更改或重建)索引之外的其他事情可能会导致排序行为发生同样的变化.应用服务包、CU 或修补程序；刷新过程缓存；使用各种 RECOMPILE 选项；更新统计数据；重新启动服务器；添加或禁用跟踪标志；更改服务器选项选项；将数据库移动到不同的服务器；等等等等

Other things other than creating (or dropping, or changing, or rebuilding) an index can cause the same sort of change in ordering behavior. Apply a service pack, CU or hotfix; flushing the procedure cache; using various RECOMPILE options; updating statistics; restarting the server; adding or disabling a trace flag; changing a server option options; moving the database to a different server; etc. etc.

因此，如果您想跟踪此信息，您需要以某种方式自己添加它，正如其他几个答案已经解决的那样.

So, if you want to track this information, you'll need to add it yourself in some way, as several of the other answers have addressed.

这篇关于确定不存在索引时添加的最新行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！