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?

Books on SSIS programming

Hi,

Can you please suggest the books which cover SSIS programming in C#?

Regards,

Gopi

http://www.amazon.com/gp/search/ref=br_ss_hs/102-1412231-0916156?platform=gurupa&url=index%3Dblended&keywords=ssis

-Jamie

Books for Yokun

Hello Guys,
I am completely familiar with DTS and database programming and I'd like to
start learning about Yokun version .Which books do you suggest for Analysis
services and SSIS and Sql server 2005?
ThanksFor AS, you should refer to Chris Webb's list, here:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!322.
entry
For SSIS, I don't think there are any books out yet, but I know that Kirk
Haselden is working on one for release early next year. You'll want to
watch for that one. He's the dev lead on the SSIS team so he knows a thing
or two about the product :)
Finally, for general SQL Server 2005 topics, I would appreciate it if you
would look at the link in my signature!
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"J-T" <J-T@.nospam.com> wrote in message
news:u1kOWdS8FHA.1280@.TK2MSFTNGP10.phx.gbl...
> Hello Guys,
> I am completely familiar with DTS and database programming and I'd like to
> start learning about Yokun version .Which books do you suggest for
> Analysis services and SSIS and Sql server 2005?
> Thanks
>|||Thanks a lot
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:uQOkDgW8FHA.2616@.TK2MSFTNGP15.phx.gbl...
> For AS, you should refer to Chris Webb's list, here:
> http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!32
2.entry
> For SSIS, I don't think there are any books out yet, but I know that Kirk
> Haselden is working on one for release early next year. You'll want to
> watch for that one. He's the dev lead on the SSIS team so he knows a
> thing or two about the product :)
> Finally, for general SQL Server 2005 topics, I would appreciate it if you
> would look at the link in my signature!
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "J-T" <J-T@.nospam.com> wrote in message
> news:u1kOWdS8FHA.1280@.TK2MSFTNGP10.phx.gbl...
>

Book suggestions on T-SQL

Hi All,
I am new to SQL Server but have been doing database programming since last 3 years. I recently attended MOC (Microsfot Official Curriculum) training on SQL Server and have started to use at my company. I am comfortable with SQL but want to dig deeper into T-SQL side. I searched on the Internet but not many good books available in that either they are ranked very low or are very old i.e. written around 1999/2000 or covers SQL Server 2000 as a whole. Can anybody suggest me any T-SQL book which was written recently and focuses purely or majorly on T-SQL?

Thanks to all for your time and advice in advance.

Regards:
PrathmeshKen Henderson. Some of the stuff is from the Guru's Guide to Transact SQL is a little dated but you will learn a lot. He has 2 books after that one I have been meaning to get to that are a little more up to date and I have heard are quite excellant.|||http://www.sqlteam.com/store.asp|||Thanks guys for the suggestions. Ken Henderson's books
1. Guru's Guide to T-SQL
2. Guru's guide to Stored Procedures, XML, HTML

seem to be good. Book 2 is slightly newer than book 1, but seem to have good material coverage. I also went through amazon reviews and TOC and overall the book seems good. As Thrasymachus pointed out, Book 1 is a bit out of date, but seems to be a good starting point.

Once again, thanks for your tips.

Regards.
Prathmesh

Book recommendation on performance

I have the Transact-SQL Programming book from O'Reilly. It was
published in 1999. It states that "SELECT ... INTO" statements end up
locking the entire database of the target table. Since the tempdb is
also involved (in many cases), this creates major deadlocks for the
entire database and all users. It suggests using the "INSERT ...
SELECT" form instead.

Considering that the book is somehow dated, is this recommendation
still valid, especially on target sizes of up to 5 million records?php newbie (newtophp2000@.yahoo.com) writes:
> I have the Transact-SQL Programming book from O'Reilly. It was
> published in 1999. It states that "SELECT ... INTO" statements end up
> locking the entire database of the target table. Since the tempdb is
> also involved (in many cases), this creates major deadlocks for the
> entire database and all users. It suggests using the "INSERT ...
> SELECT" form instead.
> Considering that the book is somehow dated, is this recommendation
> still valid, especially on target sizes of up to 5 million records?

To a large extent, no. The author seems to have had SQL 6.5 in mind, where
SELECT INTO a temptable, indeed to bring a server to a stand still. The
problem is that SELECT INTO creates the temp table, and then goes on to
fill it with data. Since the query is one transaction, it keeps a lock on
the system tables until the query has completed. This was fatal in SQL 6.5
which only had page locks. In SQL7 and SQL2000 where you have row locks,
the impact on other processes is much smaller.

The advantage of SELECT INTO is that is that is minimally logged, so
SELECT INTO #tmp for five million rows can be faster and take less
toll on the server than CREATE TABLE INSERT INTO.

Nevertheless, there are contexts where SELECT INTO is an inferior choice
over CREATE TABLE not talking about a table variable. Say that you already
have a transaction in progress, and you creating many small tables
repeatedly within this transaction. SELECT INTO takes out more locks on
than CREATE TABLE, so you acquire a whole lot more locks with SELECT
INTO, and this can have an impact on performance. Case in point: I had
a procedure which suddently started to perform much slower than before.
This procedure performs some complex data updating in an iterative
fashion. My profiling pointed to a seeminginly innocent query which
appeared to take longer and longer time as the procedure proceeded.
Eventually I found the answer in a trigger (which was not affected by
this query). I had replaced direct deferences to "inserted" with temp
table created through "SELECT * INTO #inserted FROM inserted".

To summarize: SELECT INTO is fine for single-time queries on large
tables. It is bad to have in triggers in tables which maninly are
updated one row at a time.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp