Yes, SQL Server can, under some circumstances read one column's value from the "old" version of the row, and another column's value from the "new" version of the row.
Setup:
CREATE TABLE Person
(
Id INT PRIMARY KEY,
Name VARCHAR(100),
Surname VARCHAR(100)
);
CREATE INDEX ix_Name
ON Person(Name);
CREATE INDEX ix_Surname
ON Person(Surname);
INSERT INTO Person
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY @@SPID),
'Jonny1',
'Jonny1'
FROM master..spt_values v1,
master..spt_values v2
In the first connection, run this:
WHILE ( 1 = 1 )
BEGIN
UPDATE Person
SET Name = 'Jonny2',
Surname = 'Jonny2'
UPDATE Person
SET Name = 'Jonny1',
Surname = 'Jonny1'
END
In the second connection, run this:
DECLARE @Person TABLE (
Id INT PRIMARY KEY,
Name VARCHAR(100),
Surname VARCHAR(100));
SELECT 'Setting intial Rowcount'
WHERE 1 = 0
WHILE @@ROWCOUNT = 0
INSERT INTO @Person
SELECT Id,
Name,
Surname
FROM Person WITH(NOLOCK, INDEX = ix_Name, INDEX = ix_Surname)
WHERE Id > 30
AND Name <> Surname
SELECT *
FROM @Person
After running for about 30 seconds I get :

The SELECT
query is retrieving the columns from the non clustered indexes rather than the clustered index (albeit due to the hints).

The update statement gets a wide update plan...

... and updates the indexes in sequence so it is possible to read "before" values from one index and "after" from the other.
It is also possible to retrieve two different versions of the same column value.
In the first connection, run this:
DECLARE @A VARCHAR(MAX) = 'A';
DECLARE @B VARCHAR(MAX) = 'B';
SELECT @A = REPLICATE(@A, 200000),
@B = REPLICATE(@B, 200000);
CREATE TABLE T
(
V VARCHAR(MAX) NULL
);
INSERT INTO T
VALUES (@B);
WHILE 1 = 1
BEGIN
UPDATE T
SET V = @A;
UPDATE T
SET V = @B;
END
And then in the second, run this:
SELECT 'Setting intial Rowcount'
WHERE 1 = 0;
WHILE @@ROWCOUNT = 0
SELECT LEFT(V, 10) AS Left10,
RIGHT(V, 10) AS Right10
FROM T WITH (NOLOCK)
WHERE LEFT(V, 10) <> RIGHT(V, 10);
DROP TABLE T;
Straight away, this returned the following result for me
+------------+------------+
| Left10 | Right10 |
+------------+------------+
| BBBBBBBBBB | AAAAAAAAAA |
+------------+------------+