One of my regular gripes about SQL Server concerns the number of Transact SQL features that give nondeterministic results. Unfortunately SQL Server 2005 added to the list of those. One of the new ones is ROW_NUMBER(), which is strictly due to ANSI/ISO rather than Microsoft. ROW_NUMBER() is inherently non-deterministic unless the PARTITION / ORDER BY criteria define a unique criteria for each row - something that isn't mandatory. What I think is surprising however, is Microsoft's approach to updateable views and CTE's, which gives an interesting twist to the effect of ROW_NUMBER().
For example, ROW_NUMBER() can be used to solve the problem of removing duplicates from a set and it's undeniably useful for that purpose. Here's an apparently simple example. The goal is to delete all but one row for each distinct value of Z.
CREATE TABLE tbl (x INT NOT NULL PRIMARY KEY, z CHAR(1) NOT NULL);
INSERT INTO tbl (x,z) VALUES (1,'A');
INSERT INTO tbl (x,z) VALUES (2,'A');
INSERT INTO tbl (x,z) VALUES (3,'B');
INSERT INTO tbl (x,z) VALUES (4,'B');
WITH t(r) AS /* Updateable CTE */
(SELECT ROW_NUMBER() OVER (PARTITION BY z ORDER BY z)
FROM tbl)
DELETE t WHERE r > 1;
The problem is: which rows get deleted? The CTE doesn't contain a key, in fact it doesn't contain anything except the ROW_NUMBER() in column "r". The ORDER BY clause doesn't reference a key either which means the number returned for each row is nondeterministic. Yet despite these facts the CTE is still updateable. I was surprised when I first discovered that the DELETE statement above is legal. I was even more surprised to find that Books Online is totally silent on the point of exactly when a CTE is updateable and when it isn't. The lack of documentation is a serious omission because those who have been using SQL Server for a few years will know that undefined behaviour tends to get dropped or changed between releases.
Right now, my habit is that if I need to update a CTE then I make sure it contains a key. Usually it makes sense for the OVER... ORDER BY clause to use a key also. Like:
WITH t(x,r) AS /* Updateable CTE */
(SELECT x, ROW_NUMBER() OVER (PARTITION BY z ORDER BY x)
FROM tbl)
DELETE t WHERE r > 1;
(note the difference: SELECT x, ... ORDER BY x).