Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Boolean computed column

289 views
Skip to first unread message

Smokey Grindle

unread,
Jul 10, 2007, 12:14:43 PM7/10/07
to
I have a column in my database I want to be a computed flag based on an
active date.. the Flag column is called active..


basicall I want if the ActiveDate <= getdate() (as in today is after the
item was active) then the active flag is a bit 1, else its a bit 0... I
tried this as a flat out <= statement, got an error by SQL Managment
studio... so how would this be done? thanks!


Aaron Bertrand [SQL Server MVP]

unread,
Jul 10, 2007, 12:24:38 PM7/10/07
to
Why do you need this column stored in the table? Can't you always derive
that information at query time?

Don't use Management Studio's "design" view to enter the formula. Try using
a query window, e.g.

ALTER TABLE dbo.MyTable ADD Flag AS CONVERT(BIT, CASE WHEN ActiveDate <=
GETDATE() THEN 1 ELSE 0 END);

However it would be bette to perform this calculation in a view and query
that, instead of "storing" the data, IMHO.

A


"Smokey Grindle" <nos...@nospam.com> wrote in message
news:%23Rnwu1w...@TK2MSFTNGP02.phx.gbl...

--CELKO--

unread,
Jul 10, 2007, 1:14:22 PM7/10/07
to
>> I have a column in my database I want to be a computed flag based on an active date.. the Flag column is called active.. basically I want if the ActiveDate <= getdate() (as in today is after the

item was active) then the active flag is a bit 1, else its a bit 0...
I tried this as a flat out <= statement, got an error by SQL
Management studio... so how would this be done? <<

This would be done by forgetting all the basic rules for programming
in SQL :)

1) We do not use the proprietary BIT data type that does not exist in
Standard SQL

2) Flags are for assembly language programming and have no place in
RDBMS; that is why SQL has no BOOLEAN data types

3) We do not store computed columns in a table. Write a VIEW and
learn to use CURRENT_TIMESTAMP instead of the old proprietary
getdate() function call to get today's active data.

Smokey Grindle

unread,
Jul 10, 2007, 1:35:43 PM7/10/07
to
A lot of stuff depends on flags true or false, why would I want to spend
32bits of bandwidth to return a 1 for true if I can do it in 1 bit... as for
other SQL why would I care about that right now either if I am targeting my
program at SQL server only?

"--CELKO--" <jcel...@earthlink.net> wrote in message
news:1184087662....@22g2000hsm.googlegroups.com...

--CELKO--

unread,
Jul 10, 2007, 2:55:45 PM7/10/07
to
>> A lot of stuff depends on flags true or false, why would I want to spend 32 bits of bandwidth to return a 1 for true if I can do it in 1 bit...<<

Why a +1 and not a -1? Have you compared VB and C# on this issue? Or
any of the X3J languages? Since SQL uses 3VL, where is your UNKNOWN
and how do you move it to an application.

In the RDBMS world, true and false are at a "higher level" than the
data -- they refer to predicates about that data and are not part of
it.

An RDBMS person designs data, not just queries. You will quickly find
that you need a status code that can expand as "yes/no" becomes "yes/
no/not answered/ impossible because of other answers/ etc." Or you
have one attribute split into "20-questions" with a ton of constraints
to try and co-ordinate them.

>> as for other SQL why would I care about that right now either if I am targeting my program at SQL server only? <<

Professionalism? Maintainable and readable code? "Future Proofing"?
That last one is from Ken Henderson; it means we known that SQL Server
will always be moving toward Standards and not away from them, so
let's avoid problems before they hit us. For example, the bad
programmers who used BIT got caught when the data type went from (0/1)
to (0/1/NULL) as an exact numeric data type.

Smokey Grindle

unread,
Jul 10, 2007, 3:21:52 PM7/10/07
to
What is a bit data type in SQL Server to be used for then? It has to be
there for some reason... Even MS uses the bit type to store true and false
and even encourages it in the .NET SDK with ADO.NET...

"--CELKO--" <jcel...@earthlink.net> wrote in message

news:1184093745.5...@57g2000hsv.googlegroups.com...

Smokey Grindle

unread,
Jul 10, 2007, 4:02:58 PM7/10/07
to
sorry if I sound mad or anything, just gets frustrating when one group of
people teaches you to do something one way then another says no no :)

"--CELKO--" <jcel...@earthlink.net> wrote in message

news:1184093745.5...@57g2000hsv.googlegroups.com...

--CELKO--

unread,
Jul 12, 2007, 11:39:38 PM7/12/07
to
>> What is a bit data type in SQL Server to be used for then? It has to be there for some reason... Even MS uses the bit type to store true and false and even encourages it in the .NET SDK with ADO.NET... <<

It is there for historial reasons. Sybase/SQL Server grew up before
the Standards and was written for 16 bit hardware on a UNIX platform
decades ago.

The main purpose today, now that we understand RDBMS better, is to
lock you into their products.

I do not care about .NET and ADO, or other proprietary front ends for
applications. I am the database guy and I write an RDBMS that can
work with any front end.

Damien

unread,
Jul 13, 2007, 3:27:21 AM7/13/07
to
On Jul 13, 4:39 am, --CELKO-- <jcelko...@earthlink.net> wrote:
[snip]

> I do not care about .NET and ADO, or other proprietary front ends for
> applications. I am the database guy and I write an RDBMS that can
> work with any front end.

So why do you continually harp on about the (no longer existent)
differences in interpretation of bit/boolean by VB and C#? Surely you
shouldn't be worrying about such issues?

Damien

0 new messages