Frequently Used Pseudocolumns

A Pseudo-column is a virtual column that behaves like a table column, but is not actually stored in a table. You can SELECT but cannot INSERT, UPDATE or DELETE the values. With this article, I will talk about the most used pseudo-columns in Oracle and SQL Server database. Every DBMS has some pseudo-columns that makes it easy for developer and dba to perform some task.

ORACLE:

ROWNUM: Each returned query result has the ROWNUM pseudo-column which is a number indicating the order in which the Oracle selects the row from a table or set of join. The first row has a ROWNUM of 1, the second has 2 and so on.

Displaying ROWNUM

SELECT rownum, 
       fname, 
       lname 
FROM   employee;

When do you use ROWNUM?
To Limit the Query Output:

SELECT * 
FROM   employee 
WHERE  ROWNUM < 10;

To Display Top -N

SELECT * 
FROM   ( 
         SELECT   fname, 
                  lname, 
                  salary 
         FROM     employee 
         ORDER BY salaray DESC) 
WHERE  ROWNUM <11)

ROWID: For each row in the database, the ROWID pseudo-columns returns the address of the row. Oracle Database ROWID values contain information necessary to locate the row. ROWID values have several uses. They are the fastest way to access a single row. It shows you how the rows in a table are stored and it uniquely identify for rows in a table. The ROWID may change if you delete and re-insert the row.You cannot insert, update, or delete a value of the ROWID pseudo-column.

Displaying ROWID

SELECT ROWID, last_name 
   FROM employees
   WHERE dept = 'CIS';

The statement selects the address of all rows that contain data for employees in CIS department. There are various use of ROWID but I am going to show you the one example where it is used the most. We have an employee table which isn't designed correctly. Therefore, it accepts duplicate record. Duplicate cause problem and you are asked to remove the one record from the duplicate row.

How are you going to remove the duplicate record?

1. Get the ROWIDs' for the duplicate records.
2. Delete one record using ROWID in a where clause. Even though the records are duplicate, the ROWID is unique for each row.

The ROWID can also be use to check and delete duplicates records from a table. The ROWID doesn't change for a table unless, you delete and re-insert the record or enable the row moment.  Where possible use ROWID along with Primary Key, this makes the SQL query strong for DELETE and INSERT.

SQL SERVER:

Well, I searched everywhere and made a conclusion that SQL Server does not seem to have a function or command that is equivalent to ROWID and ROWNUM. I found ROW_NUMBER() which is closer to ROWNUM but not the same. I would like to know if anyone is aware of  these equivalent.