Skip to main content

View ID: %VID

View ID: %VID

InterSystems IRIS assigns an integer view ID (%VID) to each row returned by a view or by a FROM clause subquery. Like table row ID numbers, these view row ID numbers are system-assigned, unique, non-null, non-zero, and non-modifiable. This %VID is commonly invisible to the user, and is only returned when explicitly specified. It is returned as data type INTEGER. Because %VID values are sequential integers, they are far more meaningful if the view returns ordered data; a view can only use an ORDER BY clause when it is paired with a TOP clause. The following Embedded SQL example creates a view named VSrStaff:

   &sql(CREATE VIEW Sample.VSrStaff 
        AS SELECT TOP ALL Name AS Vname,Age AS Vage
        FROM Sample.Person WHERE Age>75
        ORDER BY Name)
   IF SQLCODE=0 {WRITE "Created a view",!}
   ELSEIF SQLCODE=-201 {WRITE "View already exists",!}
   ELSE {WRITE "Serious SQL problem: ",SQLCODE," ",%msg,! }

The following example returns all of the data defined by the VSrStaff view (using SELECT *) and also specifies that the view ID for each row should be returned. Unlike the table row ID, the view row ID is not displayed when using asterisk syntax; it is only displayed when explicitly specified in the SELECT:

SELECT *,%VID AS ViewID FROM Sample.VSrStaff

The %VID can be used to further restrict the number of rows returned by a SELECT from a view, as shown in the following example:

SELECT *,%VID AS ViewID FROM Sample.VSrStaff WHERE %VID BETWEEN 5 AND 10

Thus %VID can be used instead of TOP (or in addition to TOP) to restrict the number of rows returned by a query. Generally, a TOP clause is used to return a small subset of the data records; %VID is used to return most or all of the data records, returning records in small subsets. This feature may be useful, especially for porting Oracle queries (%VID maps easily to Oracle ROWNUM). However, the user should be aware of some performance limitations in using %VID, as compared to TOP:

  • %VID does not perform time-to-first-row optimization. TOP optimizes to return the first row of data as quickly as possible. %VID optimizes to return the full data set as quickly as possible.

  • %VID does not perform a limited sort (which is a special optimization performed by TOP) if the query specifies sorted results. The query first sorts the full data set, then restricts the return data set using %VID. TOP is applied before sorting, so the SELECT performs a limited sort involving only a restricted subset of rows.

To preserve time to first row optimization and limited sort optimization, you can use a FROM clause subquery with a combination of TOP and %VID. Specify the upper bound (in this case, 10) in the FROM subquery as the value of TOP, rather than using TOP ALL. Specify the lower bound (in this case, >4) in the WHERE clause with %VID. The following example uses this strategy to return the same results as the previous view query:

SELECT *,%VID AS SubQueryID
   FROM (SELECT TOP 10 Name,Age 
         FROM Sample.Person
         WHERE Age > 75
         ORDER BY Name)
   WHERE %VID > 4

Parallel execution cannot be performed on a query that specifies a %VID, even when the %PARALLEL keyword is explicitly specified.

FeedbackOpens in a new tab