boolean programming

Is bit the closest to the boolean (true or false) datatype in tsql? Even
though it could be 0,1,null'
TIABIT is a numeric datatype. There is no assignable boolean datatype in SQL
Server - booleans are only valid for expressions.
If BIT doesn't suit then use a CHAR:
... b CHAR(1) NOT NULL CHECK (c IN ('T','F'))
David Portas
SQL Server MVP
--|||Not really, for a column which can have only two values, the best approach
in SQL is to use a CHAR(1) with a CHECK constraint to limit the values.
One could consider the Bit type to be closer to numeric datatypes like INT
or TINYINT since they have common values and several operators overlapping
among them.
Anith|||Hello Anith,
why would a CHAR(1) be the best approach?
Thank you!
---
Daniel Walzenbach
MCP
www.walzenbach.net
"Anith Sen" <anith@.bizdatasolutions.com> schrieb im Newsbeitrag
news:%23y0z1V5kFHA.2860@.TK2MSFTNGP15.phx.gbl...
> Not really, for a column which can have only two values, the best approach
> in SQL is to use a CHAR(1) with a CHECK constraint to limit the values.
> One could consider the Bit type to be closer to numeric datatypes like INT
> or TINYINT since they have common values and several operators overlapping
> among them.
> --
> Anith
>|||> why would a CHAR(1) be the best approach?
Well, I'm not sure of the definition of "best"... but some benefits of
CHAR(1) are:
- you can store T/F or Y/N instead of 0/1.
- you eliminate confusion for VB/Access people, who often expect -1 to mean
true, and this does not work with BIT.
- you can easier implement indexes, group by, etc.
Drawbacks:
- it is no longer language-neutral (if this is an issue) since French would
expect v for vrai, or o for oui. Same goes for several other languages.
- it is subject to the same problems as BIT as far as NULLability goes.
- you lose the potential ability to save space in tables where multiple such
columns exist. Up to 8 BIT columns can share a single byte, whereas
TINYINT/CHAR(1) will always take 1 byte each.|||> Is bit the closest to the boolean (true or false) datatype in tsql? Even
> though it could be 0,1,null'
It really isn't all that difficult to use a BIT column with two-valued logic
as opposed to three-valued logic.
CREATE TABLE dbo.Example
(
TrueFalse BIT NOT NULL
)
Null problem solved, no?|||I actually disagree. I, when looking at boolean datatypes, tend to use
the bit datatype. By making it a NOT NULL column and setting a default
to 0, it should solve your problems. I know for a fact that vb/vb.net
interprets a bit value as boolean. I would recommend explicitly
casting the value to boolean, but it will work.|||The other drawback is that most tools now deal with bit nicely as a boolean
and the char approach requires the UI programmer to handle things
differently than they expect. And too often if the UI developer has to go
out of their way to do something it starts to look like we db folks are
being difficult "again" :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eZwcO36kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Well, I'm not sure of the definition of "best"... but some benefits of
> CHAR(1) are:
> - you can store T/F or Y/N instead of 0/1.
> - you eliminate confusion for VB/Access people, who often expect -1 to
> mean true, and this does not work with BIT.
> - you can easier implement indexes, group by, etc.
> Drawbacks:
> - it is no longer language-neutral (if this is an issue) since French
> would expect v for vrai, or o for oui. Same goes for several other
> languages.
> - it is subject to the same problems as BIT as far as NULLability goes.
> - you lose the potential ability to save space in tables where multiple
> such columns exist. Up to 8 BIT columns can share a single byte, whereas
> TINYINT/CHAR(1) will always take 1 byte each.
>|||Thanks Aaron for your explanation. I understand your point that one would
loose the potential ability to save space in tables but wouldn't bit columns
contradict using indexes? How is your feeling on bilcolumns in case of
performance and usability? And what would be an ideal approach to implement
a bit column?
Thank you!
---
Daniel Walzenbach
MCP
www.walzenbach.net
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> schrieb im
Newsbeitrag news:eZwcO36kFHA.2484@.TK2MSFTNGP15.phx.gbl...
> Well, I'm not sure of the definition of "best"... but some benefits of
> CHAR(1) are:
> - you can store T/F or Y/N instead of 0/1.
> - you eliminate confusion for VB/Access people, who often expect -1 to
> mean true, and this does not work with BIT.
> - you can easier implement indexes, group by, etc.
> Drawbacks:
> - it is no longer language-neutral (if this is an issue) since French
> would expect v for vrai, or o for oui. Same goes for several other
> languages.
> - it is subject to the same problems as BIT as far as NULLability goes.
> - you lose the potential ability to save space in tables where multiple
> such columns exist. Up to 8 BIT columns can share a single byte, whereas
> TINYINT/CHAR(1) will always take 1 byte each.
>|||> wouldn't bit columns contradict using indexes?
Yes, indexes on bit columns are seldom useful, and I have never needed one,
but a lot of people complain that Enterprise Manager won't let you do it
(see http://www.aspfaq.com/2530).

> How is your feeling on bilcolumns in case of performance and usability?
I don't have any problems with them, though there are some minor details you
should be aware of. Again, see http://www.aspfaq.com/2530

> And what would be an ideal approach to implement a bit column?
I don't understand how you would change the approach? You either use BIT or
you don't?

Book Recommendations

Hi,
am after a book or two on TSQL and Stored Procedures, any
recommendations would be much appreciated.
CheersThe Guru's Guide to Transact-SQL
by Ken Henderson
http://www.amazon.com/exec/obidos/t...=glance&s=books
Professional SQL Server 2000 Programming
by Robert Vieira
http://www.amazon.com/exec/obidos/t...=glance&s=books
Inside Microsoft SQL Server 2000
by Kalen Delaney
http://www.amazon.com/exec/obidos/t...=glance&s=books
Microsoft SQL Server 2000 Unleashed (2nd Edition)
by Ray Rankins, Paul Jensen, Paul Bertucci
http://www.amazon.com/exec/obidos/t...=glance&s=books
Microsoft SQL Server 2000 Bible
by Paul Nielsen
http://www.amazon.com/exec/obidos/t...=gla
nce
For absolute beginners, also consider:
Sams Teach Yourself Microsoft SQL Server 2000 in 21 Days
by Richard Waymire
http://www.amazon.com/exec/obidos/t...e.blogspot.com/|||Many thanks,
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1135176704.126957.11030@.o13g2000cwo.googlegroups.com...
> The Guru's Guide to Transact-SQL
> by Ken Henderson
> http://www.amazon.com/exec/obidos/t...=glance&s=books
> Professional SQL Server 2000 Programming
> by Robert Vieira
> http://www.amazon.com/exec/obidos/t...=glance&s=books
> Inside Microsoft SQL Server 2000
> by Kalen Delaney
> http://www.amazon.com/exec/obidos/t...=glance&s=books
> Microsoft SQL Server 2000 Unleashed (2nd Edition)
> by Ray Rankins, Paul Jensen, Paul Bertucci
> http://www.amazon.com/exec/obidos/t...=glance&s=books
> Microsoft SQL Server 2000 Bible
> by Paul Nielsen
> http://www.amazon.com/exec/obidos/t...=g
lance
> For absolute beginners, also consider:
> Sams Teach Yourself Microsoft SQL Server 2000 in 21 Days
> by Richard Waymire
> http://www.amazon.com/exec/obidos/t...e.blogspot.com/
>sql

Boilerplate activation SQL for high-throughput

OK, so assume I am recycling dialogs in my client code, and assume I am doing something similar to get a dialog handle in my TSQL. What should the activated stored procedure that is processing my queue look like if I am expecting thousands of messages per second? Assume also that there is a small bit of logic need to process each individual message? I am building for a high-throughput scenario and would like to get as much as possible out of each second-tier service broker server as possible before the aggregated data is moved up the chain to a master. The first tier is Express on a web server and exists primarily only as a forwarding mechanism.

It depends on what type of logic your activation procedure will be doing. If it is just taking a message off of the queue, then receive the message and log it (or whatever you need to do). It the logic is more intesive, and requires transactions your procedure code will be more complicated. Heres a link to check out activation procedures: http://articles.techrepublic.com.com/5100-9592_11-6156264.html

Blob in Tsql

Hi,
I am writing a tsql in which i am saving contents of one table into another using "insert into.. select".
But it doesnt work for Blobs.. what should i do??
can anyone help me on this.
Thank You.Refer to UPDATETEXT/WRITETEXT/READTEXT under BOL.|||ya.. i have tried with writetext and readtext but am not able to get it right with it.

i have to read a blob from a table and insert it into another table.

the code is as follows:
/**************
DECLARE @.ptrval binary(16)
DECLARE @.destval binary(16)

SELECT @.ptrval = TEXTPTR(icon_file)
FROM subject_master where subject_id=1

READTEXT subject_master.icon_file @.ptrval 1 2000

select @.destval = TEXTPTR(icon_file)
FROM subject_master_ver where subject_id=1

Writetext subject_master_ver.icon_file @.destVal @.ptrval
************/
but it doesnt work...
can u help me out with this...

Thank You.

Originally posted by Satya
Refer to UPDATETEXT/WRITETEXT/READTEXT under BOL.|||Have you tried using DTS in this case, if its a regular process then schedule the job to do so.

HTH
Originally posted by swatisk
ya.. i have tried with writetext and readtext but am not able to get it right with it.

i have to read a blob from a table and insert it into another table.

the code is as follows:
/**************
DECLARE @.ptrval binary(16)
DECLARE @.destval binary(16)

SELECT @.ptrval = TEXTPTR(icon_file)
FROM subject_master where subject_id=1

READTEXT subject_master.icon_file @.ptrval 1 2000

select @.destval = TEXTPTR(icon_file)
FROM subject_master_ver where subject_id=1

Writetext subject_master_ver.icon_file @.destVal @.ptrval
************/
but it doesnt work...
can u help me out with this...

Thank You.|||its not a regular process... it is triggered by the user of the system.
i have not used DTS at all.
by the way what is the problem with my code??
the way i am using writetext,readtext is fine??
i am not able to find any example for writing a blob(image) into a blob(image) of a table... not available in the books online.
pls help.

Thank You.

Originally posted by Satya
Have you tried using DTS in this case, if its a regular process then schedule the job to do so.

HTH|||Try this

http://www.motobit.com/help/ScptUtl/sa307.htm