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.
NATIONALCHARACTER 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
binaryprecision * 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.
DOUBLEPRECISION binary mantissa precision 30 47 19. DOUBLE PRECISION binary exponent precision
* 9 20. TIME decimal fractional second precision * 0 21. TIMESTAMP decimal fractional
secondprecision * 6 22. INTERVAL decimal fractional second precision * 6 23. INTERVAL
decimalleading 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
ofFOREIGN 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
columnlist (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:
twicethe 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
by4 plus 1 of each approximate numeric column, 10 for each DATE column, 8 for each TIME column, 14 for each
TIMEWITH 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
fractionalseconds precision, then add that precision plus 1 to the length of the column.
Note2: The length of an SQL statement is defined to be the result of applying the OCTET_LENGTH function (see
Subclause6.6, (numeric value function), of X3.135-1992) to the SQL statement with the SQL statement
consideredto be an instance of a CHARACTER VARYING data type. Note 3: The number of table
referencesin an SQL statement is the sum of: the number of views and base tables named in the statement,
thenumber 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
