SELECT

In addition to supporting a GROUP BY on a column-list, Pervasive PSQL supports a GROUP BY on an expression-list or on any expression in a GROUP BY expression-list. See GROUP BY for more information on GROUP BY extensions. HAVING is not supported without GROUP BY.

Result sets and stored views generated by executing SELECT statements with any of the following characteristics are read-only (they cannot be updated). That is, a positioned UPDATE, a positioned DELETE and an SQLSetPos call to add, alter or delete data is not allowed on the result set or stored view if:

•

SQL_CONCUR_READ_ONLY was specified as the SQL_CONCURRENCY type via SQLSetStmtOption

•

The selection-list contains an aggregate:
SELECT SUM(c1) FROM t1

•

The selection-list specifies DISTINCT:
SELECT DISTINCT c1 FROM t1

•

The view contains a GROUP BY clause:
SELECT SUM(c1), c2 FROM t1 GROUP BY c2

•

The view is a join (references multiple tables):
SELECT * FROM t1, t2

•

The view uses the UNION operator and UNION ALL is not specified or all SELECT statements do not reference the same table:
SELECT c1 FROM t1 UNION SELECT c1 FROM t1
SELECT c1 FROM t1 UNION ALL SELECT c1 FROM t2

•

Note that stored views do not allow the UNION operator.

•

The view contains a subquery that references a table other than the table in the outer query:
SELECT c1 FROM t1 WHERE c1 IN (SELECT c1 FROM t2)

Dynamic parameters (?) are not supported as SELECT items. You may use dynamic parameters in any SELECT statement if the dynamic parameter is part of the predicate. For example, SELECT * FROM faculty WHERE id = ? is valid because the dynamic parameter is part of the predicate.

Note that the SQLExecDirect statement does not support the use of dynamic parameters. For this reason, you cannot use SQL Editor to execute an SQL statement with a dynamic parameter in the predicate. (SQL Editor uses SQLExecDirect.)

You may use variables as SELECT items only within stored procedures. See CREATE PROCEDURE.

Use of Aliases

Aliases may appear in a WHERE, HAVING, ORDER BY, or GROUP BY clause. Alias names must differ from any column names within the table. The following statement shows the use of aliases, “a” and “b,” in a WHERE clause and in a GROUP BY clause.

SELECT Student_ID a, Transaction_Number b, SUM (Amount_Owed) FROM Billing WHERE a < 120492810 GROUP BY a, b UNION SELECT Student_ID a, Transaction_Number b, SUM (Amount_Paid) FROM Billing WHERE a > 888888888 GROUP BY a, b

SUM and DECIMAL Precision

When using the SUM aggregate function to sum a field that is of type DECIMAL, the following rules apply:

The precision of the result is 74, while the scale is dependent on the column definition.

The result may cause an overflow error if a number with precision greater than 74 is calculated (a very large number indeed). If an overflow occurs, no value is returned, and SQLSTATE is set to 22003, indicating a numeric value is out of range.

Subqueries

A subquery is a SELECT statement with one or more SELECT statements within it. A subquery produces values for further processing within the statement. The maximum number of nested subqueries allowed within the topmost SELECT statement is 16.

The following types of subqueries are supported:

•

•

•

•

•

•

Note: ORDER BY clauses are allowed in table subqueries only. They are not allowed in any other type of subquery clause.

Correlated subquery predicates in the HAVING clause which contain references to grouped columns are not supported.

Expression subqueries allow the subquery within the SELECT list. For example, SELECT (SELECT SUM(c1) FROM t1 WHERE t1.c2 = t1.(c2) FROM t2. Only one item is allowed in the subquery SELECT list. For example, the following statement returns an error because the subquery SELECT list contains more than one item: SELECT p.id, (SELECT SUM(b.amount_owed), SUM(b.amount_paid) FROM billing b) FROM person p.

A subquery as an expression may be correlated or non-correlated. A correlated subquery references one or more columns in any of the tables in the topmost statement. A non-correlated subquery references no columns in any of the tables in the topmost statement.

The following illustrates an example of a correlated subquery in a WHERE clause:

SELECT * FROM student s WHERE s.Tuition_id IN

(SELECT t.ID FROM tuition t WHERE t.ID = s.Tuition_ID);

A subquery connected with the operators IN, EXISTS, ALL, or ANY is not considered an expression.

Scalar subqueries may appear in the DISTINCT, GROUP BY, and ORDER BY clause.

You may use a subquery on the left-hand side of an expression:

Expr-or-SubQuery CompareOp Expr-or-SubQuery

where Expr is an expression, and CompareOp is one of:


< (less than)	> (greater than)	<= (less than or equal to)	>= (greater than or equal to)	= (equals)
<> (not equal)	!= (not equal)	LIKE	IN	NOT IN

Optimization

Left-hand subquery behavior has been optimized for IN, NOT IN, and =ANY in cases where the subquery is not correlated and any join condition is an outer join. Other conditions may not be optimized. Here is an example of a query that meets these conditions:

SELECT count(*) FROM person WHERE id IN (SELECT faculty_id FROM class)

Performance improves if you use an index in the subquery because Pervasive PSQL optimizes a subquery based on the index. For example, the subquery in the following statement is optimized on student_id because it is an index in the Billing table:

SELECT (SELECT SUM(b.amount_owed) FROM billing b WHERE b.student_id = p.id) FROM person p

UNION in Subquery

Parentheses on different UNION groups within a subquery are not allowed. Parentheses are allowed within each SELECT statement.

For example, the parenthesis following “IN” and the last parenthesis are not allowed the following statement:

SELECT c1 FROM t5 WHERE c1 IN ( (SELECT c1 FROM t1 UNION SELECT c1 FROM t2) UNION ALL (SELECT c1 FROM t3 UNION SELECT c1 from t4) )

Table Subqueries

Table subqueries can be used to combine multiple queries into one detailed query. A table subquery is a dynamic view, which is not persisted in the database. When the topmost SELECT query completes, all resources associated with table subqueries are released.

Note: Only non-correlated subqueries are allowed in table subqueries. Correlated subqueries are not allowed.

The following example of pagination (1500 rows with 100 rows per page) illustrates the use of table subqueries with an ORDER BY clause:

The first 100 rows

SELECT * FROM ( SELECT TOP 100 * FROM ( SELECT TOP 100 * FROM person ORDER BY last_name asc ) AS foo ORDER BY last_name desc ) AS bar ORDER BY last_name ASC

The second 100 rows

SELECT * FROM ( SELECT TOP 100 * FROM ( SELECT TOP 200 * FROM person ORDER BY last_name asc ) AS foo ORDER BY last_name DESC ) AS bar ORDER BY last_name ASC

…

The fifteenth 100 rows

SELECT * FROM ( SELECT TOP 100 * FROM ( SELECT TOP 1500 * FROM person ORDER BY last_name ASC ) AS foo ORDER BY last_name DESC ) AS bar ORDER BY last_name ASC

Table Hint

The table hint functionality allows you to specify which index, or indexes, to use for query optimization. A table hint overrides the default query optimizer used by the database engine.

If the table hint specifies INDEX(0), the engine performs a table scan of the associated table. (A table scan reads each row in the table rather than using an index to locate a specific data element.)

If the table hint specifies INDEX(index-name), the engine uses index-name to optimize the table based on restrictions of any JOIN conditions, or based on the use of DISTINCT, GROUP BY, or ORDER BY. If the table cannot be optimized on the specified index, the engine attempts to optimize the table based on any existing index.

If you specify multiple index names, the engine chooses the index that provides optimal performance or uses the multiple indexes for OR optimization. An example helps clarify this. Suppose that you have the following:

CREATE INDEX ndx1 on t1(c1)

CREATE INDEX ndx2 on t1(c2)

CREATE INDEX ndx3 on t1(c3)

SELECT * FROM t1 WITH (INDEX (ndx1, ndx2)) WHERE c1 = 1 AND c2 > 1 AND c3 = 1

The database engine uses ndx1 to optimize on c1 = 1 rather than using ndx2 for optimization. Ndx3 is not considered because the table hint does not include ndx3.

Now consider the following:

SELECT * FROM t1 WITH (INDEX (ndx1, ndx2)) WHERE (c1 = 1 OR c2 > 1) AND c3 = 1

The engine uses both ndx1 and ndx2 to optimize on (c1 = 1 OR c2 > 1).

The order in which the multiple index names appear in the table hint does not matter. The database engine chooses from the specified indexes the one(s) that provides for the best optimization.

Duplicate index names within the table hint are ignored.

For a joined view, specify the table hint after the appropriate table name, not at the end of the FROM clause. For example, the following statement is correct:

SELECT * FROM person WITH (INDEX(Names)), student WHERE student.id = person.id AND last_name LIKE 'S%'

Contrast this with the following statement, which is incorrect:

SELECT * FROM person, student WITH (INDEX(Names)) WHERE student.id = person.id AND last_name LIKE 'S%'

Note: The table hint functionality is intended for advanced users. Typically, table hints are not required because the database query optimizer usually picks the best optimization method.

Restrictions

•

The maximum number of index names that can be used in a table hint is limited only by the maximum length of an SQL statement (64 KB).

•

The index name within a table hint must not be fully qualified with the table name.


Incorrect SQL:	SELECT * FROM t1 WITH (INDEX(t1.ndx1)) WHERE t1.c1 = 1
Returns:	SQL_ERROR
szSqlState:	37000
Message:	Syntax Error: SELECT * FROM t1 WITH (INDEX(t1.<< ??? >>ndx1)) WHERE t1.c1 = 1

•

Table hints are ignored if they are used in a SELECT statement with a view.


Incorrect SQL:	SELECT * FROM myt1view WITH (INDEX(ndx1))
Returns:	SQL_SUCCESS_WITH_INFO
szSqlState:	01000
Message:	Index hints supplied with views will be ignored

•

Zero is the only valid hint that is not an index name.


Incorrect SQL:	SELECT * FROM t1 WITH (INDEX(85))
Returns:	SQL_ERROR
szSqlState:	S1000
Message:	Invalid index hint

•

The index name in a table hint must specify an existing index.


Incorrect SQL:	SELECT * FROM t1 WITH (INDEX(ndx4))
Returns:	SQL_ERROR
szSqlState:	S0012
Message:	Invalid index name; index not found

•

A table hint cannot be specified on a subquery AS table.


Incorrect SQL:	SELECT * FROM (SELECT c1, c2 FROM t1 WHERE c1 = 1) AS a WITH (INDEX(ndx2)) WHERE a.c2 = 10
Returns:	SQL_ERROR
szSqlState:	37000
Message:	syntax Error: SELECT * FROM (SELECT c1, c2 FROM t1 WHERE c1 = 1) AS a WITH<< ??? >>(INDEX(ndx2)) WHERE a.c2 = 10

Examples

This simple SELECT statement retrieves all the data from the Faculty table.

SELECT * FROM Faculty

This statement retrieves the data from the person and the faculty table where the id column in the person table is the same as the id column in the faculty table.

SELECT Person.id, Faculty.salary FROM Person, Faculty WHERE Person.id = Faculty.id

============

The following example retrieves student_id and sum of the amount_paid where it is greater than or equal to 100 from the billing table. It then groups the records by student_id.

SELECT Student_ID, SUM(Amount_Paid)

FROM Billing

GROUP BY Student_ID

HAVING SUM(Amount_Paid) >=100.00

If the expression is a positive integer literal, then that literal is interpreted as the number of the column in the result set and ordering is done on that column. No ordering is allowed on set functions or an expression that contains a set function.

============

FOR UPDATE

The following example uses table t1 to demonstrate the use of FOR UPDATE. Assume that t1 is part of the DEMODATA sample database. The stored procedure creates a cursor for the SELECT FOR UPDATE statement. A loop fetches each record from t1 and, for those rows where c1=2, sets the value of c1 to four.

The procedure is called by passing the value “2” as the IN parameter.

The example assumes that two users, A and B, are logged in to DEMODATA. User A performs the following:

DROP TABLE t1

CREATE TABLE t1 (c1 INTEGER, c2 INTEGER)

INSERT INTO t1 VALUES (1,1)

INSERT INTO t1 VALUES (2,1)

INSERT INTO t1 VALUES (1,1)

INSERT INTO t1 VALUES (2,1)

CREATE PROCEDURE p1 (IN :a INTEGER)

BEGIN

DECLARE :b INTEGER;

DECLARE :i INTEGER;

DECLARE c1Bulk CURSOR FOR SELECT * FROM t1 WHERE c1 = :a FOR UPDATE;

FETCH NEXT FROM c1Bulk INTO :i;

IF SQLSTATE = '02000' THEN

LEAVE BulkLinesLoop;

END IF;

UPDATE SET c1 = 4 WHERE CURRENT OF c1Bulk;

END LOOP;

CLOSE c1Bulk;

SET :b = 0;

WHILE (:b < 100000) DO

Notice that a WHILE loop delays the COMMIT of the transaction. During that delay, assume that User B attempts to update t1 with call SQLExecDirect “UPDATE t1 SET c1=3 WHERE c1=2.” A status code 84 is returned to User B because those rows are locked by the SELECT FOR UPDATE statement from User A.

============

The following example uses table t1 to demonstrate how SELECT FOR UPDATE locks records when the statement is used outside of a stored procedure. Assume that t1 is part of the DEMODATA sample database.

The example assumes that two users, A and B, are logged in to DEMODATA. User A performs the following:

DROP TABLE t1

CREATE TABLE t1 (c1 INTEGER, c2 INTEGER)

INSERT INTO t1 VALUES (1,1)

INSERT INTO t1 VALUES (2,1)

INSERT INTO t1 VALUES (1,1)

INSERT INTO t1 VALUES (2,1)

Call SQLSetConnectAttr with option SQL_AUTOCOMMIT and attribute SQL_AUTOCOMMIT_OFF.

Call SQLExecDirect “SELECT * FROM t1 WHERE c1 = 2 FOR UPDATE.”

The two records where c1 = 2 are locked until User A issues a COMMIT WORK or ROLLBACK WORK statement.

User B attempts to update t1 with call SQLExecDirect “UPDATE t1 SET c1=3 WHERE c1=2.” A status code 84 is returned to User B because those rows are locked by the SELECT FOR UPDATE statement from User A.

Now assume that User A issues call SQLEndTran with option SQL_COMMIT. The two records where c1 = 2 are unlocked.

User B could now issue call SQLExecDirect “UPDATE t1 SET c1=3 WHERE c1=2” and change the values for c1.

============

approximate-numeric-literal

SELECT * FROM results WHERE quotient =-4.5E-2

INSERT INTO results (quotient) VALUES (+5E7)

============

between-predicate

The syntax expression1 BETWEEN expression2 and expression3 returns TRUE if expression1 >= expression2 and expression1<= expression3. FALSE is returned if expression1 >= expression3, or is expression1 <= expression2.

Expression2 and expression3 may be dynamic parameters (for example, SELECT * FROM emp WHERE emp_id BETWEEN ? AND ?)

The next example retrieves the first names from the person table whose ID fall between 10000 and 20000.

SELECT First_name FROM Person WHERE ID BETWEEN 10000 AND 20000

============

correlation-name

Both table and column correlation names are supported.

The following example selects data from both the person table and the faculty table using the aliases T1 and T2 to differentiate between the two tables.

SELECT * FROM Person t1, Faculty t2 WHERE t1.id = t2.id

The correlation name for a table name can also be specified in using the FROM clause, as seen in the following example.

SELECT a.Name, b.Capacity FROM Class a, Room b

WHERE a.Room_Number = b.Number

============

exact-numeric-literal

SELECT car_num, price FROM cars WHERE car_num =49042 AND price=49999.99

============

in-predicate

This selects the records from table Person table where the first names are Bill and Roosevelt.

SELECT * FROM Person WHERE First_name IN ('Roosevelt', 'Bill')

============

set-function

The following example selects the minimum salary from the Faculty table.

SELECT MIN(salary) FROM Faculty

MIN(expression), MAX(expression), SUM(expression), STDEV(expression), AVG(expression), COUNT(*), and COUNT(expression) are supported.

COUNT(expression) counts all non-NULL values for an expression across a predicate. COUNT(*) counts all values, including NULL values.

The following example counts all the rows in q where a+b does not equal NULL.

SELECT COUNT(a+b) FROM q

The STDEV function returns the standard deviation of all values based on a sample of the data. The expression must be a numeric data type and an eight-byte DOUBLE is returned. A “floating point overflow” error results if the difference between the minimum and maximum values of the expression is out of range. Expression cannot contain aggregate functions. There must be at least two rows with a value in the expression field or STDEV is not calculated and returns a NULL.

The following returns the standard deviation of the grade point average field in the Student sample table.

SELECT STDEV(Cumulative_GPA) FROM student

============

date-literal

Date constants may be expressed in SQL statements as a character string or embedded in a vendor string. SQL_CHAR and the vendor string representation are treated as a value of type SQL_DATE. This becomes important when conversions are attempted.

Pervasive PSQL partially supports extended SQL grammar, as outlined in this function.

The next two statements return all the classes whose start date is after 1995-06-05.

SELECT * FROM Class WHERE Start_Date > '1995-06-05'

SELECT * FROM Class WHERE Start_Date > {d '1995-06-05'}

Pervasive PSQL supports the following date literal format: 'YYYY-MM-DD'.

Dates may be in the range of year 0 to 9999.

============

time-literal

The following two statements retrieve records from the class table where the start time for the classes is 14:00:00.

SELECT * FROM Class WHERE Start_time = '14:00:00'

SELECT * FROM Class WHERE Start_time = {t '14:00:00'}

Time constants may be expressed in SQL statements as a character string or embedded in a vendor string. Character string representation is treated as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_TIME.

Pervasive PSQL partially supports extended SQL grammar, as outlined in this function.

============

timestamp-literal

Timestamp constants may be expressed in SQL statements as a character string or embedded in a vendor string. Pervasive PSQL treats the character string representation as a string of type SQL_CHAR and the vendor string representation as a value of type SQL_TIMESTAMP. Pervasive PSQL partially supports extended SQL grammar, as outlined in this function.

The next two statements retrieve records from the Billing table where the start day and time for the log is 1996-03-28 at 17:40:49.

SELECT * FROM Billing WHERE log = '1996-03-28 17:40:49.0000000'

SELECT * FROM Billing WHERE log = {ts '1996-03-28 17:40:49.0000000'}

Pervasive PSQL supports the following time literal format: 'YYYY-MM-DD HH:MM:SS.MMMMMMM'.

string-literal

Literal strings are represented as single-quoted strings. If the string itself contains a single-quote or apostrophe, the character must be preceded by another single-quote.

SELECT * FROM t1 WHERE c1 = ’Roberta’s Restaurant’

SELECT STREET FROM address WHERE city LIKE ’San%’

============

date arithmetic

SELECT * FROM person P, Class C WHERE p.Date_Of_Birth < ' 1973-09-05' and c.Start_date >{d '1995-05-08'} + 30

Pervasive PSQL supports adding or subtracting an integer from a date where the integer is the number of days to add or subtract, and the date is embedded in a vendor string. (This is equivalent to executing a convert on the date).

You may also subtract one date from another to yield a number of days.

============

The IF system scalar function provides conditional execution based on the truth value of a condition

This expression prints the column header as “Prime1” and amount owed as 2000 where the value of the column amount_owed is 2000 or it prints a 0 if the value of the amount_owed column is not equal to 2000.

SELECT Student_ID, Amount_Owed,

IF (Amount_Owed = 2000, Amount_Owed, Convert(0, SQL_DECIMAL)) “Prime1”

FROM Billing

From table Class, the following example prints the value in the Section column if the section is equal to 001, else it prints “xxx” under column header Prime1

Under column header Prime2, it prints the value in the Section column if the value of the section column is equal to 002, or else it prints “yyy.”

SELECT ID, Name, Section,

IF (Section = '001', Section, 'xxx') “Prime1”,

IF (Section = '002', Section, 'yyy') “Prime2”

FROM Class

You can combine header Prime1 and header Prime2 by using nested IF functions. Under column header Prime, the following query prints the value of the Section column if the value of the Section column is equal to 001 or 002. Otherwise, it print “xxx.”

SELECT ID, Name, Section,

IF (Section = '001', Section, IF(Section = '002', Section, 'xxx')) Prime

FROM Class

============

Multi-database Join

When needed, a database name may be prepended to an aliased table name in the FROM clause, to distinguish among tables from two or more different databases that are used in a join.

All of the specified databases must be serviced by the same database engine and have the same database code page settings. The databases do not need to reside on the same physical volume. The current database may be secure or non-secure, but all other databases in the join must be non-secure. With regard to Referential Integrity, all RI keys must exist within the same database. (See also Encoding.)

Literal database names are not permitted in the select-list or in the WHERE clause. If you wish to refer to specific columns in the select-list or in the WHERE clause, you must use an alias for each specified table. See examples.

Assume two separate databases, “accounting” and “customers,” exist on the same server. You can join tables from the two databases using table aliases and SQL syntax similar to the following example:

SELECT ord.account, inf.account, ord.balance, inf.address

FROM accounting.orders ord, customers.info inf

WHERE ord.account = inf.account

============

In this example, the two separate databases are “acctdb” and “loandb.” The table aliases are “a” and “b,” respectively.

SELECT a.loan_number_a, b.account_no, a.current_bal, b.balance

FROM acctdb.ds500_acct_master b LEFT OUTER JOIN loandb.ml502_loan_master a ON (a.loan_number_a = b.loan_number)

WHERE a.current_bal <> (b.balance * -1)

ORDER BY a.loan_number_a

============

left outer join

The following example shows how to access the “Person” and “Student” tables from the DEMODATA database to obtain the Last Name, First Initial of the First Name and GPA of students. With the LEFT OUTER JOIN, all rows in the “Person” table are fetched (the table to the left of LEFT OUTER JOIN). Since not all people have GPA’s, some of the columns have NULL values for the results. This is how outer join works, returning non-matching rows from either table.

SELECT Last_Name,Left(First_Name,1) AS First_Initial,Cumulative_GPA AS GPA FROM “Person”

LEFT OUTER JOIN “Student” ON Person.ID=Student.ID

ORDER BY Cumulative_GPA DESC, Last_Name

Assume that you want to know everyone with perfectly rounded GPA’s and have them all ordered by the length of their last name. Using the MOD statement and the LENGTH scalar function, you can achieve this by adding the following to the query:

WHERE MOD(Cumulative_GPA,1)=0 ORDER BY LENGTH(Last_Name)

============

right outer join

The difference between LEFT and RIGHT OUTER JOIN is that all non matching rows show up for the table defined to the right of RIGHT OUTER JOIN. Change the query for LEFT OUTER JOIN to include a RIGHT OUTER JOIN instead. The difference is that the all non-matching rows from the right table, in this case “Student,” show up even if no GPA is present. However, since all rows in the “Student” table have GPA’s, all rows are fetched.

SELECT Last_Name,Left(First_Name,1) AS First_Initial,Cumulative_GPA AS GPA FROM “Person”

RIGHT OUTER JOIN “Student” ON Person.ID=Student.ID

ORDER BY Cumulative_GPA DESC, Last_Name

============

Cartesian join

A Cartesian join is the matrix of all possible combinations of the rows from each of the tables. The number of rows in the Cartesian product equals the number of rows in the first table times the number of rows in the second table.

Assume you have the following tables in your database:

Table 46

Table 47

The following performs a Cartesian JOIN on these tables:

SELECT * FROM Addr,Loc

This results in the following:

Table 48

SELECT Statement with Cartesian JOIN

============

You can use DISTINCT with SUM, AVG, COUNT, MIN, and MAX (but it does not change results with MIN and MAX). DISTINCT eliminates duplicate values before calculating the sum, average or count.

Suppose you want to know the salaries for different departments including the minimum, maximum and salary, and you want to remove duplicate salaries. The following statement would do this, excluding the computer science department:

SELECT dept_name, MIN(salary), MAX(salary), AVG(DISTINCT salary) FROM faculty WHERE dept_name<>'computer science' GROUP BY dept_name

If you wanted to include duplicate salaries, you would use:

SELECT dept_name, MIN(salary), MAX(salary), AVG(salary) FROM faculty WHERE dept_name<>'computer science' GROUP BY dept_name

============

TOP

You may limit the number of rows returned by a single SELECT statement by using the keyword TOP in the statement and specifying a value for the maximum number of rows to return.

The specified number of rows must be a literal positive number. It is defined as a 32-bit unsigned integer.

A SELECT statement can include both TOP and an ORDER BY clause. If so, the database engine generates a temporary table and populates it with the query’s entire result set if no index can be used for the ORDER BY. The rows in the temporary table are ordered as specified by the ORDER BY clause and the TOP number of rows in the ordered result set are returned.

Views that contain a TOP clause may be joined with other tables or views.

The main difference between TOP and SET ROWCOUNT is that TOP affects only the current statement, while SET ROWCOUNT affects all subsequent statements issued during the current database session.

If both SET ROWCOUNT and TOP are applied to a given query, the query returns a number of rows equal to the lesser of the two values.

============

Cursor Types and TOP

A SELECT query with a TOP clause used in a cursor implicitly changes the cursor type under several circumstances. When using the table below, remember that any SELECT with ORDER BY on an unindexed column requires a temporary table. If the ORDER BY is on an indexed column, then a temporary table is not required.

Table 49

Effect of TOP Clause on Cursor Types

Original Cursor Type

Converted to This Type if SELECT query requires a temporary table:

Converted to This Type if SELECT query does not require a temporary table:

Dynamic

Forward-only

Static