What is a SQL "pseudocolumn"?

Question

I accidentally coded SELECT $FOO.. and got the error "Invalid pseudocolumn "$FOO".

I can't find any documentation for them. Is it something I should know?

Edit: this is a MS SQL Server specific question.

Solution 1

Pseudocolumns are symbolic aliases for actual columns, that have special properties, for example, $IDENTITY is an alias for the column that has the IDENTITY assigned, $ROWGUID for the column with ROWGUIDCOL assigned. It's used for the internal plumbing scripts.

Solution 2

I don't know why most of the answers are Oracle specific the Question is about SQL Server!

As well as RobsonROX's answer an other example of their use is in the output clause of a merge statement. $action indicates whether the row was inserted or deleted.

Solution 3

A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.

Comments

[Flupkear]

Google: best programmer friend (after Stack Overflow :-)

[rapidfyre]

I'm asking about SQL Server, not Oracle. The error message for selecting a merely non-existent column like select foo, is different than select $foo

[Martin Smith]

+1 I knew you could use identitycol but didn't know that syntax.