Advice Requested : Trying to write portable SQL

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]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!
In your case, I'd use CHAR(1) and use a more friendly, readable code
instead of a potenitally mysterious 0/1.
You can use the Mimer SQL Validator to check your code against the
ANSI/ISO standard. Standard not necessarily = Portable of course. :-(
http://developer.mimer.com/validator/
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.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.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog wrote:
> David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> 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, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1149628866.388604.195270@.y43g2000cwc.googlegroups.com...
> Russell Mangel wrote:
>
> 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.
David
David|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> Erland Sommarskog wrote:
> 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...
The problem with char(1) is that there are no obvious values for the
constant. If a programmer writes
WHERE binarycol = 'J'
when the the possible values are Y and N have introduced a bug. That, there
are no universal values for a char(1) column. There is for bit on the other
hand.
And if you think
WHERE bitcol = 1
is cumbersome for some reason, you can in SQL 2005 write:
WHERE bitcol = 'true'
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||David Browne wrote:
> I think the fault lies in the design of SQL for not cleanly accomodating t
he
> 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.
>
Agree absolutely. A Data Implementation sub-language ought to be
included as a hook for translating types and views into their
product-specific structures. It's remarkable to think that if the SQL
standard had specified even a rudimentary "DIL" and if vendors had used
it we could have been spared truckloads or denormalization agony and
the worst of the Kimball dimensional muddle. That simple omission has
perhaps cost $billions for database customers.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks for the reply...
Did I do the check contstraint right? I can't think of anything else to do
here. Besides choosing an int or char(1).
Russell Mangel
Las Vegas, NV