What is a SQL “pseudocolumn”?

问题:

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.

回答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.

回答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.

回答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.

回答4:

A simple Google search brings up this from Oracle's reference:

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.

I think that the error you got is simply because there is no column $FOO, so the query parser tests to see if there's a psuedocolumn named $FOO as a fallback. And since there is no pseudocolumn named "$FOO" (and there are no other fallback) you get the error "Invalid pseudocolumn $FOO". This is a guess, though. I'm no expert when it comes to databases.

回答5:

Pseudocolumns are virtual columns that are available in special cases. In an Oracle database, there's a ROWNUM pseudocolumn that will give you the row number. SQL server, as far as I know, doesn't actually support pseudocolumns but there are errors and stored procedures that refer to pseudo columns, probably for Oracle migration.

回答6:

One example of a pseudo-column is ROWID in Informix. It is a 32-bit number that can be used to find the data page more quickly than any other way (subject to caveats, such as the table is not fragmented) because it is basically the page address for the data. You can do SELECT * FROM SomeTable and it won't show up; you can do SELECT ROWID, * FROM SomeTable and it will show up in your data. Because it is not actually stored on disk (you won't see the ROWID on the disk with the data, though the ROWID tells you where to look on the disk for the data), it is a pseudo-column. There can be other pseudo-columns associated with tables - they tend to be similarly somewhat esoteric.

They can also be called hidden columns, particularly if they are (contrary to pseudo-columns) actually stored in the database, but are not selected by *; you have to specifically request the column to see it.

回答7:

pseudo columns are the false columns. any table will support pseudo columns as same to that of it own column strictly speaking they are functions: 1.SYSDATE; 2.ROWNUM; 3.ROWID; 4.NEXTVAL; 5.CURRVAL; 6.LEVEL;