> I am running on MS SQL2005.
>
> I have a table called Companies that has a column (Active), which would be
> either 0 or 1 for either "Active or Not-Active".
>
> I realize that there is a BIT column type in SQL 2005, but I don't want to
> use it. Instead I have chosen to use an 'int' column data type, and then I
> applied a check constraint to ensure that only 0 or 1 is inserted.
>
> I have included some DML, please let me know if there is a better way to
> accomplish my goal.
>
> Thanks
> Russell Mangel
> Las Vegas, NV
>
> CREATE TABLE [dbo].[Companies]
> (
>     [CompanyCode] [varchar](5)  NOT NULL,
>     [CompanyName] [varchar](35)  NOT NULL,
>     [Active] [int] NOT NULL CONSTRAINT [DF_Companies_Active]  DEFAULT ((1)),
>     CONSTRAINT [PK_Companies] PRIMARY KEY CLUSTERED
> (
>     [CompanyCode] ASC
> )   WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
> )    ON [PRIMARY]
>
> ALTER TABLE [dbo].[Companies]  WITH CHECK ADD  CONSTRAINT [CK_Companies]
> CHECK  (([Active]=(0) OR [Active]=(1)))
> GO
> ALTER TABLE [dbo].[Companies] CHECK CONSTRAINT [CK_Companies]

> Erland Sommarskog wrote:
>> David Portas (REMOVE_BEFORE_REPLYING_dpor***@acm.org) writes:
>> > In your case, I'd use CHAR(1) and use a more friendly, readable code
>> > instead of a potenitally mysterious 0/1.
>>
>> My bets are on 0 and 1. That's clearcut. char(1) comes with localisation
>> issues.
>
> Perfectly true that it will be localised, but developing a database
> schema without localisation sounds like a pretty tough call to me. What
> do you do for table and column names? If you need to support developers
> and DBAs with multiple languages then you can put translations in a
> data dictionary. On the other hand if you rely on a good data
> dictionary then I suppose that negates my argument about the potential
> ambiguity of 1/0 anyway...

"David Portas" <REMOVE_BEFORE_REPLYING_dpor***@acm.org> wrote in message
news:1149628866.388604.195270@y43g2000cwc.googlegroups.com...
> Russell Mangel wrote:
>> I am running on MS SQL2005.
>>
>> I have a table called Companies that has a column (Active), which would
>> be
>> either 0 or 1 for either "Active or Not-Active".
>>
>> I realize that there is a BIT column type in SQL 2005, but I don't want
>> to
>> use it. Instead I have chosen to use an 'int' column data type, and then
>> I
>> applied a check constraint to ensure that only 0 or 1 is inserted.
>>
>> I have included some DML, please let me know if there is a better way to
>> accomplish my goal.
>>
>> Thanks
>> Russell Mangel
>> Las Vegas, NV
>>
>> CREATE TABLE [dbo].[Companies]
>> (
>>     [CompanyCode] [varchar](5)  NOT NULL,
>>     [CompanyName] [varchar](35)  NOT NULL,
>>     [Active] [int] NOT NULL CONSTRAINT [DF_Companies_Active]  DEFAULT
>> ((1)),
>>     CONSTRAINT [PK_Companies] PRIMARY KEY CLUSTERED
>> (
>>     [CompanyCode] ASC
>> )   WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
>> )    ON [PRIMARY]
>>
>> ALTER TABLE [dbo].[Companies]  WITH CHECK ADD  CONSTRAINT [CK_Companies]
>> CHECK  (([Active]=(0) OR [Active]=(1)))
>> GO
>> ALTER TABLE [dbo].[Companies] CHECK CONSTRAINT [CK_Companies]
>
>
> It's very hard to write totally portable DDL because virtually all SQL
> DBMSs (SQL Server, Sybase, Oracle, MySQL for example) have extended the
> SQL DDL to define the physical implementation of tables as well as the
> logical model. Crazy but true!
>

>
> I think the fault lies in the design of SQL for not cleanly accomodating the
> physical design of databases.  It's one thing to assume that the DBMS will
> provide a suitable runtime implementation of a SELECT, INSERT, UPDATE or
> DELETE.  Quite another to assume that the an implementation won't need
> additional details for CREATE TABLE, etc.
>
> In fact, CREATE TABLE is just broken, as it badly conflates the type, the
> instance and the implementation.  A TABLE ought to be decalred as an
> instance of a relation "type", with a prescribed mechanism for specifying
> the physical implementation.  SQL shouldn't try model the physical design,
> but at least should provide a standard way for it to be specified.
>