Paginated Query

The statements to retrieve partial rows from selected data are like followings according to the DBMS.

Oracle (8i or higer)

Use ROW_NUMBER() function and subquery. In some cases, such as hierarchical query (START WITH ~ CONNECT BY ~ clause) using ORDER SIBLINGS BY, ROW_NUMBER() function can't be used in subquery, so in those cases, ROWNUM with double-nested subquery can be used.

• Syntax :

SELECT *
FROM (
   SELECT ROW_NUMBER() OVER (ORDER BY ~) AS row_no, ~
   FROM ~ 
   WHERE ~ )
WHERE row_no BETWEEN m AND n

• Example :

select *
from(
  select row_number(order by product_name) as row_no,
      product_id, product_name, price, manufacturer
  from tb_product
  where category_id = 101)
where row_no between 21 and 30

Microsoft SQL Server (2005 or higher)

• Syntax :

WITH ~ AS(
   SELECT ~ , ROW_NUMBER() OVER(~) AS row_no
   FROM ~
   WHERE ~
)
SELECT ~
FROM ~
WHERE row_no BETWEEN m AND n

• Example :

with v_product as (
   select row_number() over(order by product_name) as row_no,
      product_id, product_name, price, manufacturer
   from tb_product
   where category_id = 101)
select *
from v_product
where row_no between 21 and 30

MySQL

• Syntax :

SELECT ~ 
FROM ~ 
WHERE ~ 
ORDER BY ~
LIMIT offset, row_count

• Example :

select product_id, product_name, price, manufacturer
from tb_product
where category_id = 101
order by product_name
limit 20, 10

The query with both LIMIT clause and ORDER BY clause sort the result before limit unlike Oracle so, you need not use subquery.

PostgresSQL

• Syntax :

SELECT ~ 
FROM ~ 
WHERE ~ 
ORDER BY ~ 
LIMIT row_count OFFSET offset

• Example :

select product_id, product_name, price, manufacturer
from tb_product
where category_id = 101
order by product_name
limit 10 offset 20

The query with both LIMIT/OFFSET clause and ORDER BY clause sort the result before limit unlike Oracle so, you need not use subquery.

Informix

• Syntax :

SELECT SKIP offset FIRST row_count selection_list
FORM ~
WHERE ~
ORDER BY ~

Readings

• Limiting result rows of SELECT (from Wikipedia)

UNIQUE constraint of Microsoft SQL Server and CHECK constraint of MySQL

Micsoft SQL Server allows only one null value for the column having UNIQUE constraint, as with any other(non-null) value. This is quiet different from Oracle.

MySQL provides CHECK contraint syntax in its SQL, but it doesn't really check the contraint in run-time, at leat as of version 5.0. The syntax is for compatibility of the SQL.

Maximum Length of VARCHAR Type in Major DBMS

• Oracle 8 ~ 10g : 4,000 Byte
• Microsoft SQL Server : 8,000 Byte (not sure)
• MySQL 5.0.3 before : 255 Byte
• MySQL 5.0.3 or higher : 65,535 Byte

Default TCP/IP Port of Microsoft SQL Server 2005 Express

After Install, the default settings for Microsoft SQL Server 2005 Express are different from those which are expected in previous versions.

• TCP/IP is not enabled for the Server
• The default TCP port is not 1433 (which is default previous versions)

So, if you would make JDBC connections from your applications, you should setup followings first.

• Enable TCP/IP for the serve :
  Load SQL Server Configuration Manager, in SQL Server 2005 Network Configuration/Protocols for SQLEXRPRESS dialog, make enable TCP/IP (SQLEXPRESS is instance name of SQL Server, so is dependent on your installation)
• Check the TCP port of your server :
  In above SQL Server 2005 Network Configuration/Protocols for SQLEXRPRESS dialog, double click TCP/IP item. At the loaded dialog, seek into IP Addresses tab, IP All section, and TCP Dynamic Ports and that is TCP port for JDBC connection.

Those may be same with SQL Server 2005.