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(at)rbt(dot)ca>

PGP Key: http://www.rbt.ca/rbtpub.asc