Boolean computed column

SQL Apprentice Question
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!


Celko Answers
>> 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.


Original Source

SQL Views - embedded view work-a-rounds

SQL Apprentice Question
I've been asked to re-write a sql view. The view itself contains
several calls to other views (embedded). Is there a way to get around
using embedded views. I've written the same query up using temp.
tables but obviously temp. tables can't be used in views?


Is there any special things I should be looking for?


Celko Answers
>> I've been asked to re-write a sql view. The view itself contains several calls[sic: invocations?] to other views (embedded). Is there a way to get around using embedded views. I've written the same query up using temp tables but obviously temp. tables can't be used in views? <<


Nesting VIEWs is a good progrmming practice when it is done right. It
can assure that nobody invents their own definition of something, like
how we compute a tricky formula that can send us all to prison.

Temp tables are a baaaaad idea. The SQL Server model is in violation
of ANSI/ISO and most everyone else's model of them. They are usually
a way to fake a "scratch tape" in a procedural solution, where each
step passes the tape to the next step in a process; SQL is declarative
and we want to write that way.


But the real point is that you never said *why* you want to re-write
this unnamed VIEW. Damn to give advise about anything without any
kind of spec at all ..

real world advise on temp tables please

SQL Apprentice Question
a system with around 1500 users. a "bad" stored procedure that writes 3
#temp_tables. I notice system slow downs when more than 5 - 10 people run the
SP at the same time. Is this to be expected. what are the real world
expectancies of the temdb?

Celko Answers
>> a "bad" stored procedure that writes 3 #temp_tables. I notice system slow downs when more than 5 - 10 people run the SP at the same time. Is this to be expected. what are the real world expectancies of the temdb? <<


Yes, it is expected. The best solution is to re-write the procedure
to use derived tables and subqueries.

Besides being proprietary in both syntax and implementation the # temp
tables are usually a sign of bad programming. They are used as
"scratch tapes" in a routine structured as if it were a 1950's mag
tape batch; each step of a sequential process is written to a scratch
tape (aka # temp table) to be passed to the following step.


Remember coupling and cohesion from that freshman S.E. course?
Temporal coupling?