Yes. Shaun

The Sybase and SQL Server Update command allows the use of variables in the SET statement. In the case of SQL Server 2005, this is....

SET

{ column_name = { expression | DEFAULT | NULL }

| { udt_column_name.{ { property_name = expression

| field_name = expression }

| method_name ( argument [ ,...n ] )

}

}

| column_name { .WRITE ( expression , @Offset , @Length ) }

| @variable = expression

| @variable = column = expression [ ,...n ]

} [ ,...n ]

All I'm doing is talking advantage of the order of update in order to flag each row that is different from the one before (the table must be in the physical order of the columns that you are comparing). I'm just writing a 'd' (meaning delete) in every row that is different to the one before and then deleting each row that has that 'd'. I used the hash just to make the code more compact, but the logic should have been expanded to make it more readable. I should have saved each significant column to a separate variable, and compared them separately with an AND.

Note a couple of things.

Updates seem to be done in the order of the clustered index. In the example, it was done in the order we inserted them into the table variable. with each row, all updates that assign to a variable are done first, in order left to right, followed by the column assignments, left to right. You have to be careful, though as there is no definition of this behaviour in BOL as far as I can find, but it seems to work in all versions.

It is very fast. I am confident I can win the speed race against the other solutions as long as the table is in the correct physical order!