I decided to push this out to it's own thread. Here's the question, if you are
deciding to abide purely by ANSI SQL 92 standards and are only thinking in terms
of the logical model, how does the modeler decide when to use a SmallInt vs. an
Integer if the allowed range is considered a physical implementation?

A secondary question relates to boolean values. Is the official ANSI solution to
storing boolean values (0 or 1) to use either a full blown Int (of course, since
we are only talking about ANSI, we have no idea how big that is ;->) or a
Char(1)?


Thomas

ANSI / ISO SQL doesn't define absolute numeric precision so the
decisions on which datatype to use can only be made in the context of
an actual implementation. Those physical details are just outside the
scope of the standard. Does it matter?

SQL99 defines a Boolean datatype but SQL Server doesn't support it.

--
David Portas
SQL Server MVP
--

[quoted text, click to view]



By specifying the concept of a SmallInt but providing no information that would
help the modeler choose one over the other, it sounds like the ANSI team made an
outright error in including SmallInt in the first place. Said another way, since
designers are already required to accommodate the given DBMS for things like the
range of values for a SmallInt, where is the problem in using DBMS specific
features like TinyInt? After all, if portability is the holy grail of standards
use, it is already the case that porting a database from one DBMS to another
might break because one vendor used 2-bytes for their SmallInt while another
used 1-byte for their SmallInt. Thus, in terms of data types certainly, it makes
sense to use things like TinyInt to accomplish you goal.



In case you were wondering, I bring this up becuase of a comment Mr. Celko made
about not using SQL's TinyInt datatype.



[quoted text, click to view]


Is that true of SQL 2005 as well?





Thomas