The information schema is supposed to contain a view SQL_SIZING which is
defined thus:

         List the sizing items defined in this standard and, for each of
         these, indicate the size supported by the SQL-implementation.

But the standard does not define any "sizing item" or anything like that
anywhere.

Unless someone can offer an explanation, I am inclined to just supply an
empty table and check off this item.

-- 
Peter Eisentraut   peter_e@gmx.net


Mon, 6 Jan 2003 19:48:41 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
SQL_SIZING view
Peter Eisentraut wrote:
> Unless someone can offer an explanation, I am inclined to just supply an
> empty table and check off this item.
> 

I found the definition in FIPS 127-2:
   http://www.itl.nist.gov/fipspubs/fip127-2.htm

The relevant section is section 16.6.

Joe



Mon, 06 Jan 2003 13:57:07 -0800
Joe Conway <mail@joeconway.com>
Re: SQL_SIZING view
According to:

http://www.itl.nist.gov/fipspubs/fip127-2.htm#FIPS_TOP


15.2 SQL_Sizing table. The SQL_SIZING table shall consist of exactly one
row for each FIPS SQL database construct defined in Section 16.6 of this
standard. The SIZING_ID and DESCRIPTION columns identify the database
construct by the integer and description assigned to it in Section 16.6.
The ENTRY_VALUE column is equal to the default Entry SQL value defined
for that construct by FIPS SQL in Section 16.6, with "*" converted to a
Null value. The INTERMEDIATE_VALUE column is equal to the default
Intermediate SQL value defined for that construct by FIPS SQL in Section
16.6, with "*" converted to a Null value. The VALUE_SUPPORTED column
indicates a value for the construct that is supported by an
implementation when data in the identified catalog is accessed through
that implementation; if this value is Null, then there is no explicit
restriction on the size of that construct. A user must be able to depend
upon these values when executing SQL-statements against data in the
catalog. If a given catalog spans multiple SQL-Server implementations,
then the VALUE_SUPPORTED shall be valid in all of them. It is important
to recognize that FIPS sizing defaults are not requirements for
conformance to FIPS SQL; instead, they identify a default value that is
assumed to be specified if a Federal SQL procurement is silent on that
topic. For this reason, the VALUE_SUPPORTED may sometimes be less than
the FIPS default for the ENTRY_VALUE or the INTERMEDIATE_VALUE, even for
a FIPS SQL conforming implementation. The SIZING_COMMENTS column is
intended for any vendor comments pertinent to the identified FIPS SQL
database construct.


16.6 Sizing for database constructs. References to this standard in a
procurement should indicate minimum requirements for the precision,
size, or number of occurrences of database constructs. Failure to make
this indication means that the Entry Values detailed below are by
default the minimum requirements for Entry SQL or Transitional SQL
procurements and the Intermediate Values detailed below are by default
the minimum requirements for Intermediate SQL or Full SQL procurements.

   Sizing                                                 Entry    Interm.
     Id        Description                                Value    Value

     1.  Length of an identifier                            18      128
     2   CHARACTER max length                              240     1000
     3.  CHARACTER VARYING max length                      254     1000
     4.  BIT max length in bits                              *     8000
     5.  BIT VARYING max length in bits                      *     8000
     6.  NATIONAL CHARACTER max length                       *      500
     7.  NATIONAL CHAR VARYING max length                    *      500
     8.  NUMERIC decimal precision                          15       15
     9.  DECIMAL decimal precision                          15       15
     10. INTEGER decimal precision                           9        *
     11. INTEGER binary precision                            *       31
     12. SMALLINT decimal precision                          4        *
     13. SMALLINT binary precision                           *       15
     14. FLOAT binary mantissa precision                    20       47
     15. FLOAT binary exponent precision                     *        9
     16. REAL binary mantissa precision                     20       23
     17. REAL binary exponent precision                      *        7
     18. DOUBLE PRECISION binary mantissa precision         30       47
     19. DOUBLE PRECISION binary exponent precision          *        9
     20. TIME decimal fractional second precision            *        0
     21. TIMESTAMP decimal fractional second precision       *        6
     22. INTERVAL decimal fractional second precision        *        6
     23. INTERVAL decimal leading field precision            *        7
     24. Columns in a table                                100      250
     25. Values in an INSERT statement                     100      250
     26. Set clauses in UPDATE statement                    20      250
     27. Length of a row (see Note 1)                     2000     8000
     28. Columns in UNIQUE constraint                        6       15
     29. Length of UNIQUE columns (Note 1)                 120      750
     30. Columns in GROUP BY column list                     6       15
     31. Length of GROUP BY column list (Note 1)           120      750
     32. Sort items in ORDER BY clause                       6       15
     33. Length of ORDER BY column list (Note 1)           120      750
     34. Referencing columns in FOREIGN KEY                  6       15
     35. Length of FOREIGN KEY column list (Note 1)        120      750
     36. Table references in an SQL statement (Note 3)      15       50
     37. Cursors simultaneously open                        10      100
     38. WHEN clauses in a CASE expression                   *       50
     39. Columns in a named columns JOIN                     *       15
     40. Length of JOIN column list (Note 1)                 *      750
     41. Items in a SELECT list                            100      250
     42. Length of SQL  (Note 2)                             *    30000
     43. Length of  (Note 2)                                 *     4000
     44. Length of  (Note 2)                                 *    
4000
     45. Occurrences in an ALLOCATE DESCRIPTOR               *      100
     46. Default occurrences in ALLOCATE DESCRIPTOR          *      100


        Note 1: The length of a collection of columns is conservatively
        estimated to be no larger than the sum of: twice the number of
        columns,OCTET_LENGTH of each character or bit column (see
        Subclause 6.6, (numeric value function), of X3.135-1992),
        decimal precision plus 1 of each exact numeric column, binary
        precision divided by 4 plus 1 of each approximate numeric
        column, 10 for each DATE column, 8 for each TIME column, 14 for
        each TIME WITH TIME ZONE column, 19 for each TIMESTAMP column,
        25 for each TIMESTAMP WITH TIME ZONE column, and 20 for each
        INTERVAL column. In addition, if any DATE, TIME, TIMESTAMP, or
        INTERVAL column has a non-zero fractional seconds precision,
        then add that precision plus 1 to the length of the column. 
        
        
        Note 2: The length of an SQL statement is defined to be the
        result of applying the OCTET_LENGTH function (see Subclause 6.6,
        (numeric value function), of X3.135-1992) to the SQL statement
        with the SQL statement considered to be an instance of a
        CHARACTER VARYING data type. 
        
        
        Note 3: The number of table references in an SQL statement is
        the sum of: the number of views and base tables named in the
        statement, the number of underlying views and tables (see
        Subclause 4.9, "Tables", of X3.135-1992) for each derived table
        or cursor, and the number of ) (either given in the SQL
        statement or contained in some view named in the SQL statement)
        not directly associated with a named table or view. 
        
Some applications may have requirements for CHARACTER VARYING or BIT
VARYING data types with lengths much longer than the Entry SQL or
Intermediate SQL values specified above. This is particularly true for
applications that need to manage large Audio, Graphics, Text, or Video
objects. Some applications have requirements for Audio, Text, or
Graphics objects in excess of 2-3 million bytes, or Video objects in
excess of multiple gigabytes. Implementations that provide such data
types often impose severe restrictions in how these very large objects
can be referenced in SQL definitions and statements. For example, a very
long CHARACTER VARYING data type may not be allowed to participate in a
PRIMARY KEY, a UNIQUE constraint, a REFERENTIAL constraint, a ) a GROUP
BY or HAVING clause, or an ORDER BY in a cursor definition. Applications
that stay within the limits specified above should not encounter any
unexpected restrictions in how these constructs can be used or
referenced in SQL language. 

Some implementations address user requirements for very large objects,
with a minimum number of restrictions, by allowing arbitrarily large
maximum length declarations for CHARACTER VARYING or BIT VARYING, with
an internal representation using some sort of indirect addressing
mechanism. In this way they can keep the physical length of the row in
which the object is represented less than the physical page size of the
operating system environment, often necessary for lock management, while
at the same time meet user requirements for storing, retrieving, and
managing large objects. SQL procurements that anticipate requirements
for very long CHARACTER VARYING or BIT VARYING data types should be very
explicit in procurement specifications about additional requirements for
how these large data types interface to external processors or how they
need to be processed by SQL language. 

On Mon, 2003-01-06 at 13:48, Peter Eisentraut wrote:
> The information schema is supposed to contain a view SQL_SIZING which is
> defined thus:
> 
>          List the sizing items defined in this standard and, for each of
>          these, indicate the size supported by the SQL-implementation.
> 
> But the standard does not define any "sizing item" or anything like that
> anywhere.
> 
> Unless someone can offer an explanation, I am inclined to just supply an
> empty table and check off this item.
-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
06 Jan 2003 16:59:24 -0500
Rod Taylor <rbt@rbt.ca>
Re: SQL_SIZING view
Joe Conway writes:

> I found the definition in FIPS 127-2:
>    http://www.itl.nist.gov/fipspubs/fip127-2.htm
>
> The relevant section is section 16.6.

The table described there does not match the schema of the SQL_SIZING
table defined in the SQL standard.  I'm also suspicious because the
SQL_FEATURES table described nearby does not match the SQL_FEATURES table
in the standard in both schema and content.  So even though the concept
seems to be the same I would rather not follow the definition there.

-- 
Peter Eisentraut   peter_e@gmx.net


Tue, 7 Jan 2003 18:33:33 +0100 (CET)
Peter Eisentraut <peter_e@gmx.net>
Re: SQL_SIZING view