docs.intersystems.com
Using Caché SQL
View ID: %VID
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Search:    

View ID: %VID
Caché 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 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 "We have a problem: ",SQLCODE,! }
 
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 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 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:
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
 


Send us comments on this page
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA