BOL says you should not kill a SELECT statement. Why not?

BOL says this about the KILL command:
Use KILL very carefully, especially when critical processes are
running. You cannot kill your own process. Other processes not to kill
are:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
Those all make sense except for the SELECT but BOL doesn't elaborate on
why you shouldn't kill a SELECT statement. Any ideas why they caution
against this?
ThanksIt must be a mistake. They removed it from 2005 BOL.
<pshroads@.gmail.com> wrote in message
news:1154975355.227889.163170@.m79g2000cwm.googlegroups.com...
> BOL says this about the KILL command:
> Use KILL very carefully, especially when critical processes are
> running. You cannot kill your own process. Other processes not to kill
> are:
> AWAITING COMMAND
> CHECKPOINT SLEEP
> LAZY WRITER
> LOCK MONITOR
> SELECT
> SIGNAL HANDLER
> Those all make sense except for the SELECT but BOL doesn't elaborate on
> why you shouldn't kill a SELECT statement. Any ideas why they caution
> against this?
> Thanks
>

BOL says you should not kill a SELECT statement. Why not?

BOL says this about the KILL command:
Use KILL very carefully, especially when critical processes are
running. You cannot kill your own process. Other processes not to kill
are:
AWAITING COMMAND
CHECKPOINT SLEEP
LAZY WRITER
LOCK MONITOR
SELECT
SIGNAL HANDLER
Those all make sense except for the SELECT but BOL doesn't elaborate on
why you shouldn't kill a SELECT statement. Any ideas why they caution
against this?
ThanksIt must be a mistake. They removed it from 2005 BOL.
<pshroads@.gmail.com> wrote in message
news:1154975355.227889.163170@.m79g2000cwm.googlegroups.com...
> BOL says this about the KILL command:
> Use KILL very carefully, especially when critical processes are
> running. You cannot kill your own process. Other processes not to kill
> are:
> AWAITING COMMAND
> CHECKPOINT SLEEP
> LAZY WRITER
> LOCK MONITOR
> SELECT
> SIGNAL HANDLER
> Those all make sense except for the SELECT but BOL doesn't elaborate on
> why you shouldn't kill a SELECT statement. Any ideas why they caution
> against this?
> Thanks
>

blocking caused from SQL with (nolock) hint

I am seeing blocking in a database that is caused by a SQL statement that
joins two tables with (nolock) hints. How is that possible? I thought
nolock would perform a dirty read and would not block readers. Is that not
the case?
Thanks,
Jay
Jay,
No more information (environment, SQL code, etc) than the question, means
this question is hard to answer.
(1) If your SQL is doing an update, then (of course) it will lock the
resources being updated.
(2) I have also seen a repeated instance of an older version of Access
causing a lock (a SCH-M lock) even though it had no rights to make any
schema change.
Post some more details if you have them.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> I am seeing blocking in a database that is caused by a SQL statement that
> joins two tables with (nolock) hints. How is that possible? I thought
> nolock would perform a dirty read and would not block readers. Is that
not
> the case?
> Thanks,
> Jay
|||Jay P wrote:
> I am seeing blocking in a database that is caused by a SQL statement
> that joins two tables with (nolock) hints. How is that possible? I
> thought nolock would perform a dirty read and would not block
> readers. Is that not the case?
> Thanks,
> Jay
I've seen undesirable results with NOLOCK on temp tables. Is this the
case? Post your SQL please.
David Gugick
Imceda Software
www.imceda.com
|||The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b WHERE
a.col1='x' and b.col2='y' ' I am using a script to check for blocking that
generates a SQL Profiler trace and also using the sp_pss80 script to show
locks and input buffer contents but I'm having problems interpreting the
output. I do know that when I run this statement, I get some blocking going
on and I'm confused by the fact that it's just a SELECT (dirty read) type
operation albeit on a rather large table of appx. 13 million rows and is
doing a index range scan... Thanks for the reply.
"Russell Fields" wrote:

> Jay,
> No more information (environment, SQL code, etc) than the question, means
> this question is hard to answer.
> (1) If your SQL is doing an update, then (of course) it will lock the
> resources being updated.
> (2) I have also seen a repeated instance of an older version of Access
> causing a lock (a SCH-M lock) even though it had no rights to make any
> schema change.
> Post some more details if you have them.
> RLF
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> not
>
>
|||Jay,
Hmmmm....
If the join and select is big enough, SQL Server may need to create
worktables in order to handle the whole operation. It is possible that (if
worktables are being created) that you are doing some blocking on tempdb
system tables. Is that possible?
Beyond that, I have no brilliant ideas, because (as you say) you should be
getting dirty reads without locking.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
> The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b
WHERE
> a.col1='x' and b.col2='y' ' I am using a script to check for blocking
that
> generates a SQL Profiler trace and also using the sp_pss80 script to show
> locks and input buffer contents but I'm having problems interpreting the
> output. I do know that when I run this statement, I get some blocking
going[vbcol=seagreen]
> on and I'm confused by the fact that it's just a SELECT (dirty read) type
> operation albeit on a rather large table of appx. 13 million rows and is
> doing a index range scan... Thanks for the reply.
> "Russell Fields" wrote:
means[vbcol=seagreen]
that[vbcol=seagreen]
thought[vbcol=seagreen]
that[vbcol=seagreen]
|||When worktables are created there should not be blocking.
You could use sp_lock to find out if blocking is caused by any lock
resource, and use select * from sysprocesses to find out the wait type and
wait time. The Books Online has more details on how to use the two.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23ErXqoW3EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Jay,
> Hmmmm....
> If the join and select is big enough, SQL Server may need to create
> worktables in order to handle the whole operation. It is possible that
> (if
> worktables are being created) that you are doing some blocking on tempdb
> system tables. Is that possible?
> Beyond that, I have no brilliant ideas, because (as you say) you should be
> getting dirty reads without locking.
> RLF
>
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
> WHERE
> that
> going
> means
> that
> thought
> that
>

blocking caused from SQL with (nolock) hint

I am seeing blocking in a database that is caused by a SQL statement that
joins two tables with (nolock) hints. How is that possible? I thought
nolock would perform a dirty read and would not block readers. Is that not
the case?
Thanks,
JayJay,
No more information (environment, SQL code, etc) than the question, means
this question is hard to answer.
(1) If your SQL is doing an update, then (of course) it will lock the
resources being updated.
(2) I have also seen a repeated instance of an older version of Access
causing a lock (a SCH-M lock) even though it had no rights to make any
schema change.
Post some more details if you have them.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> I am seeing blocking in a database that is caused by a SQL statement that
> joins two tables with (nolock) hints. How is that possible? I thought
> nolock would perform a dirty read and would not block readers. Is that
not
> the case?
> Thanks,
> Jay|||Jay P wrote:
> I am seeing blocking in a database that is caused by a SQL statement
> that joins two tables with (nolock) hints. How is that possible? I
> thought nolock would perform a dirty read and would not block
> readers. Is that not the case?
> Thanks,
> Jay
I've seen undesirable results with NOLOCK on temp tables. Is this the
case? Post your SQL please.
--
David Gugick
Imceda Software
www.imceda.com|||The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b WHERE
a.col1='x' and b.col2='y' ' I am using a script to check for blocking that
generates a SQL Profiler trace and also using the sp_pss80 script to show
locks and input buffer contents but I'm having problems interpreting the
output. I do know that when I run this statement, I get some blocking going
on and I'm confused by the fact that it's just a SELECT (dirty read) type
operation albeit on a rather large table of appx. 13 million rows and is
doing a index range scan... Thanks for the reply.
"Russell Fields" wrote:
> Jay,
> No more information (environment, SQL code, etc) than the question, means
> this question is hard to answer.
> (1) If your SQL is doing an update, then (of course) it will lock the
> resources being updated.
> (2) I have also seen a repeated instance of an older version of Access
> causing a lock (a SCH-M lock) even though it had no rights to make any
> schema change.
> Post some more details if you have them.
> RLF
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> > I am seeing blocking in a database that is caused by a SQL statement that
> > joins two tables with (nolock) hints. How is that possible? I thought
> > nolock would perform a dirty read and would not block readers. Is that
> not
> > the case?
> >
> > Thanks,
> > Jay
>
>|||Jay,
Hmmmm....
If the join and select is big enough, SQL Server may need to create
worktables in order to handle the whole operation. It is possible that (if
worktables are being created) that you are doing some blocking on tempdb
system tables. Is that possible?
Beyond that, I have no brilliant ideas, because (as you say) you should be
getting dirty reads without locking.
RLF
"Jay P" <Jay P@.discussions.microsoft.com> wrote in message
news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
> The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b
WHERE
> a.col1='x' and b.col2='y' ' I am using a script to check for blocking
that
> generates a SQL Profiler trace and also using the sp_pss80 script to show
> locks and input buffer contents but I'm having problems interpreting the
> output. I do know that when I run this statement, I get some blocking
going
> on and I'm confused by the fact that it's just a SELECT (dirty read) type
> operation albeit on a rather large table of appx. 13 million rows and is
> doing a index range scan... Thanks for the reply.
> "Russell Fields" wrote:
> > Jay,
> >
> > No more information (environment, SQL code, etc) than the question,
means
> > this question is hard to answer.
> >
> > (1) If your SQL is doing an update, then (of course) it will lock the
> > resources being updated.
> >
> > (2) I have also seen a repeated instance of an older version of Access
> > causing a lock (a SCH-M lock) even though it had no rights to make any
> > schema change.
> >
> > Post some more details if you have them.
> >
> > RLF
> > "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> > news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
> > > I am seeing blocking in a database that is caused by a SQL statement
that
> > > joins two tables with (nolock) hints. How is that possible? I
thought
> > > nolock would perform a dirty read and would not block readers. Is
that
> > not
> > > the case?
> > >
> > > Thanks,
> > > Jay
> >
> >
> >|||When worktables are created there should not be blocking.
You could use sp_lock to find out if blocking is caused by any lock
resource, and use select * from sysprocesses to find out the wait type and
wait time. The Books Online has more details on how to use the two.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Russell Fields" <RussellFields@.NoMailPlease.Com> wrote in message
news:%23ErXqoW3EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Jay,
> Hmmmm....
> If the join and select is big enough, SQL Server may need to create
> worktables in order to handle the whole operation. It is possible that
> (if
> worktables are being created) that you are doing some blocking on tempdb
> system tables. Is that possible?
> Beyond that, I have no brilliant ideas, because (as you say) you should be
> getting dirty reads without locking.
> RLF
>
> "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
> news:635050C0-4FA9-4556-96BE-9C679831BC4F@.microsoft.com...
>> The SQL is simply like 'SELECT a.col1, b.col2 FROM tablex a, tabley b
> WHERE
>> a.col1='x' and b.col2='y' ' I am using a script to check for blocking
> that
>> generates a SQL Profiler trace and also using the sp_pss80 script to show
>> locks and input buffer contents but I'm having problems interpreting the
>> output. I do know that when I run this statement, I get some blocking
> going
>> on and I'm confused by the fact that it's just a SELECT (dirty read) type
>> operation albeit on a rather large table of appx. 13 million rows and is
>> doing a index range scan... Thanks for the reply.
>> "Russell Fields" wrote:
>> > Jay,
>> >
>> > No more information (environment, SQL code, etc) than the question,
> means
>> > this question is hard to answer.
>> >
>> > (1) If your SQL is doing an update, then (of course) it will lock the
>> > resources being updated.
>> >
>> > (2) I have also seen a repeated instance of an older version of Access
>> > causing a lock (a SCH-M lock) even though it had no rights to make any
>> > schema change.
>> >
>> > Post some more details if you have them.
>> >
>> > RLF
>> > "Jay P" <Jay P@.discussions.microsoft.com> wrote in message
>> > news:C20A1880-D3EC-498E-B6E1-93F3D79FCA87@.microsoft.com...
>> > > I am seeing blocking in a database that is caused by a SQL statement
> that
>> > > joins two tables with (nolock) hints. How is that possible? I
> thought
>> > > nolock would perform a dirty read and would not block readers. Is
> that
>> > not
>> > > the case?
>> > >
>> > > Thanks,
>> > > Jay
>> >
>> >
>> >
>

BLOB or PIPING a Select

I posted this a few months ago, but at the time did not find a simple method
..
I would like to have a select statement output into a single result...
IE
select email from a.client
rather then return as:
user1@.user.com
user2@.user.com
user3@.user.com
I would like:
user1@.user.comuser1@.user.comuser1@.user.com
Basically treating the result as a single row rather then one for each...
anythoughts or ideas? Am I on crack?
Jordyhttp://support.microsoft.com/newsgr...n-us&sloc=en-us
AMB
"Jordy Boss" wrote:

> I posted this a few months ago, but at the time did not find a simple meth
od...
> I would like to have a select statement output into a single result...
> IE
> select email from a.client
> rather then return as:
> user1@.user.com
> user2@.user.com
> user3@.user.com
> I would like:
> user1@.user.comuser1@.user.comuser1@.user.com
> Basically treating the result as a single row rather then one for each...
> anythoughts or ideas? Am I on crack?
> Jordy

Bizarre SQL statement

I have a SQL UNION (I know I know...they're bad, but trust me, I have to us
e
it) in a stored proc.
Effectively, it liiks like this:
SELECT f1, f2, f3 FROM T1
UNION
SELECT f1, f2, f3 FROM T2
note:
T1 and T2 are not tables, but parameterized query expressions that contain 3
and 4 tables, respectively.
If I just run "SELECT f1, f2, f3 FROM T1" I get 0 results in 2 or 3 seconds
If I just run "SELECT f1, f2, f3 FROM T2" I get 31 results in less than 1
second
If I run:
SELECT f1, f2, f3 FROM T1
UNION
SELECT f1, f2, f3 FROM T2
I wait 28 minutes and nothing happens...
I know that unions deprecate performance, but this is REALLY strange.
Also, this only happens for certian parameters. But its the same SQL that's
running...
What the heck is going on'David Jessee wrote:
> I have a SQL UNION (I know I know...they're bad, but trust me, I have to
use
> it) in a stored proc.
> Effectively, it liiks like this:
> SELECT f1, f2, f3 FROM T1
> UNION
> SELECT f1, f2, f3 FROM T2
> note:
> T1 and T2 are not tables, but parameterized query expressions that contain
3
> and 4 tables, respectively.
> If I just run "SELECT f1, f2, f3 FROM T1" I get 0 results in 2 or 3 second
s
> If I just run "SELECT f1, f2, f3 FROM T2" I get 31 results in less than 1
> second
> If I run:
> SELECT f1, f2, f3 FROM T1
> UNION
> SELECT f1, f2, f3 FROM T2
> I wait 28 minutes and nothing happens...
> I know that unions deprecate performance, but this is REALLY strange.
> Also, this only happens for certian parameters. But its the same SQL that
's
> running...
> What the heck is going on'
try UNION ALL just to see if that helps. Maybe the optimizer in
whatever DB you're using (you didn't say) is trying to do stuff so the
sort is 'faster'|||examnotes <DavidJessee@.discussions.microsoft.com>
wrote in news:F951CFD8-F105-46FB-8D2E-0752C6399B78@.microsoft.com:

> I have a SQL UNION (I know I know...they're bad, but trust me, I have
> to use it) in a stored proc.
> Effectively, it liiks like this:
> SELECT f1, f2, f3 FROM T1
> UNION
> SELECT f1, f2, f3 FROM T2
> note:
> T1 and T2 are not tables, but parameterized query expressions that
> contain 3 and 4 tables, respectively.
Does "T1" and "T2" share some of these tables? If so, are you sure that you
are not experiencing a deadlock? This is not my strongest field, but as far
as I've understood, the two statements that are merged with a union may
actually run in parallell. If some of the values passed to the
parameterized causes a table-lock on a shared table, you could experience
the behavior you describe, or am I completely wrong here?
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||The individual queries are utilizing some of the same tables. However, the
tables are references "WITH (NOLOCK)" so deadlocking shouldn't be an issue,
should it?
"Ole Kristian Bang?s" wrote:

> examnotes <DavidJessee@.discussions.microsoft.com>
> wrote in news:F951CFD8-F105-46FB-8D2E-0752C6399B78@.microsoft.com:
>
> Does "T1" and "T2" share some of these tables? If so, are you sure that yo
u
> are not experiencing a deadlock? This is not my strongest field, but as fa
r
> as I've understood, the two statements that are merged with a union may
> actually run in parallell. If some of the values passed to the
> parameterized causes a table-lock on a shared table, you could experience
> the behavior you describe, or am I completely wrong here?
> --
> Ole Kristian Bang?s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
>|||Well, I'm using a SQL Server database (this snippet of code is inside of a
stored procedure).I'll try the UNION ALL to see if it works. Truth to tell,
I'm hoping it doesn't because each of thequeries can potentially return
duplicate records. Rach of them have DISTINCT clauses in them already. For
this query, placing DISTINCT in each of the queries and them UNIONING them
gives better performance than just relying on the UNOIN to remove duplicates
.
(not my data model...but I have to work with it *ack*)
"wolfing1@.gmail.com" wrote:

> David Jessee wrote:
> try UNION ALL just to see if that helps. Maybe the optimizer in
> whatever DB you're using (you didn't say) is trying to do stuff so the
> sort is 'faster'
>|||examnotes <DavidJessee@.discussions.microsoft.com>
wrote in news:D0476075-74A5-4890-A48B-3048292DE2BD@.microsoft.com:

> The individual queries are utilizing some of the same tables.
> However, the tables are references "WITH (NOLOCK)" so deadlocking
> shouldn't be an issue, should it?
I don't remember when, but I seem to remember that Kimberly Tripp had a
TechNet session or so regarding this, where she specified that even with
nolock table locks can occur. But, as I've said, I do not remember in what
situation.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging|||Are you familiar with query and join hints? Take a look at the execution
plan generated by each individual SELECT in the UNION and try coercing the
optimizer to match the execution plan. Remember also that a UNION requires
a sort of the result set from each SELECT in order to eliminate duplicates.
You could try this:
SELECT DISNTINCT f1, f2, f3 FROM
( SELECT f1, f2, f3 FROM T1
UNION ALL
SELECT f1, f2, f3 FROM T2 ) T3
By the way, I hope you're aware of the dangers of using WITH(NOLOCK).
Except in rare circumstances, WITH(NOLOCK) will make your queries return
incorrect results at lightning speed.
"David Jessee" <DavidJessee@.discussions.microsoft.com> wrote in message
news:D0476075-74A5-4890-A48B-3048292DE2BD@.microsoft.com...
> The individual queries are utilizing some of the same tables. However,
> the
> tables are references "WITH (NOLOCK)" so deadlocking shouldn't be an
> issue,
> should it?
> "Ole Kristian Bangs" wrote:
>

Bit field true/false

When I code my SQL statement and want to check equal condition on a bit
field, should I use True/False or 1/0?
David1/0
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||I would use the numeric one for many pratical reasons (Converting from
client Applications, Case Expressions, the handle of the numeric type, etc.)
Just my practical issues,
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"David C" <dlchase@.lifetimeinc.com> schrieb im Newsbeitrag
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||Before CELKO come in and get you a severe tongue-lashing about using bits
and about using the word 'fields' instead of 'columns'...
There is no boolean data type in SQL server.
So use 1 or 0 to check equality.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||Y/N ?
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||Maybe you should use CHAR(1) with a check constraint IN ('T', 'F').
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>|||Not withstanding Don Celko and his tilting against Bit data types and the wo
rds
"field" and "record", with SQL Server I'll either use the Bit datatype or a
TinyInt with a Check constraint of In(0,1). I'll use Char(1) when the value
means something other than a boolean concept (e.g. true/false, on/off, yes/n
o)
like a Gender column with a check constraint of In('M', 'F').
Thomas
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:Oqi0F5QRFHA.2784@.TK2MSFTNGP12.phx.gbl...
> When I code my SQL statement and want to check equal condition on a bit fi
eld,
> should I use True/False or 1/0?
> David
>|||Agree w/all above, but would add that Bit fields *CANNOT be indexed, and
since 1 to 8 bit fields will t ake up a full byte of storage anyway, I feel
it's generally better to use tinyints, with COnstraint In (0,1) as Thomas
suggested.
* The restriction on not being able to use a bit column in an index, may not
be relevant, if you never use the bit column in a order by, or Where clause,
or anywhere else where it might affect which rows are output or the order in
which they are output, then it doesn't matter... Also, even if you are using
such a query, if the values are close to evenly (50-50) distributed and not
skewed a lot (say, 95% true, 5% false) the query generally won't use an inde
x
anyway.
"David C" wrote:

> When I code my SQL statement and want to check equal condition on a bit
> field, should I use True/False or 1/0?
> David
>
>|||> Agree w/all above, but would add that Bit fields *CANNOT be indexed
This is not true. It is only Enterprise Manager which prevents you from
doing so, but it is certainly valid (and valid doesn't necessarily mean
useful). For more info, see http://www.aspfaq.com/2530|||R U sure? in SQL 2000, at least, the BOL says they can't...
bit data type, described...
bit
Integer data type 1, 0, or NULL.
Remarks
Columns of type bit cannot have indexes on them.
Microsoft? SQL Server? optimizes the storage used for bit columns. If the
re
are 8 or fewer bit columns in a table, the columns are stored as 1 byte. If
there are from 9 through 16 bit columns, they are stored as 2 bytes, and so
on.
... etc...
I'm checking out the reference you posted...
"Aaron [SQL Server MVP]" wrote:

> This is not true. It is only Enterprise Manager which prevents you from
> doing so, but it is certainly valid (and valid doesn't necessarily mean
> useful). For more info, see http://www.aspfaq.com/2530
>
>

bit data type

I run a simple select statement to a table that selects
data. One of the column's data type is "bit" size "1" set
to default to ((-1)).
Here is the problem, when I run a select statement in
query analyzer, all data from that column is "1". When I
run the same query and use DTS to export the data to a
text file, all the data from that column is "true". What
does that mean and how can I get the data to just be "1"?
Please help.
Thanks. John.
The valid values of a BIT column are 0, 1 and NULL, so there's no good
reason to default the column to -1. The -1 will be implicitly cast to 1,
which is one of the peculiarities of the BIT datatype.
DTS assumes that you want BIT columns to be treated as Boolean values and
chooses to export them as the strings "True" and "False". You can avoid this
by casting the value as an INTEGER in your transformation. Alternatively,
choose another numeric type for the column instead of BIT.
David Portas
SQL Server MVP
|||Thank you very much. That makes sense.
John

>--Original Message--
>The valid values of a BIT column are 0, 1 and NULL, so
there's no good
>reason to default the column to -1. The -1 will be
implicitly cast to 1,
>which is one of the peculiarities of the BIT datatype.
>DTS assumes that you want BIT columns to be treated as
Boolean values and
>chooses to export them as the strings "True" and "False".
You can avoid this
>by casting the value as an INTEGER in your
transformation. Alternatively,
>choose another numeric type for the column instead of BIT.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>
|||Hi
VB: -1 = True
All other languages: +1 = True.
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:18ba01c47be5$ba2b8cc0$a601280a@.phx.gbl...[vbcol=seagreen]
> Thank you very much. That makes sense.
> John
>
> there's no good
> implicitly cast to 1,
> Boolean values and
> You can avoid this
> transformation. Alternatively,

bit data type

I run a simple select statement to a table that selects
data. One of the column's data type is "bit" size "1" set
to default to ((-1)).
Here is the problem, when I run a select statement in
query analyzer, all data from that column is "1". When I
run the same query and use DTS to export the data to a
text file, all the data from that column is "true". What
does that mean and how can I get the data to just be "1"?
Please help.
Thanks. John.The valid values of a BIT column are 0, 1 and NULL, so there's no good
reason to default the column to -1. The -1 will be implicitly cast to 1,
which is one of the peculiarities of the BIT datatype.
DTS assumes that you want BIT columns to be treated as Boolean values and
chooses to export them as the strings "True" and "False". You can avoid this
by casting the value as an INTEGER in your transformation. Alternatively,
choose another numeric type for the column instead of BIT.
--
David Portas
SQL Server MVP
--|||Thank you very much. That makes sense.
John
>--Original Message--
>The valid values of a BIT column are 0, 1 and NULL, so
there's no good
>reason to default the column to -1. The -1 will be
implicitly cast to 1,
>which is one of the peculiarities of the BIT datatype.
>DTS assumes that you want BIT columns to be treated as
Boolean values and
>chooses to export them as the strings "True" and "False".
You can avoid this
>by casting the value as an INTEGER in your
transformation. Alternatively,
>choose another numeric type for the column instead of BIT.
>--
>David Portas
>SQL Server MVP
>--
>
>.
>|||Hi
VB: -1 = True
All other languages: +1 = True.
--
Mike Epprecht, Microsoft SQL Server MVP
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John" <anonymous@.discussions.microsoft.com> wrote in message
news:18ba01c47be5$ba2b8cc0$a601280a@.phx.gbl...
> Thank you very much. That makes sense.
> John
>
> >--Original Message--
> >The valid values of a BIT column are 0, 1 and NULL, so
> there's no good
> >reason to default the column to -1. The -1 will be
> implicitly cast to 1,
> >which is one of the peculiarities of the BIT datatype.
> >
> >DTS assumes that you want BIT columns to be treated as
> Boolean values and
> >chooses to export them as the strings "True" and "False".
> You can avoid this
> >by casting the value as an INTEGER in your
> transformation. Alternatively,
> >choose another numeric type for the column instead of BIT.
> >
> >--
> >David Portas
> >SQL Server MVP
> >--
> >
> >
> >.
> >

Bit column with NULL

I have this table called Location. There is a column called IsInactive. The
value can contain a NULL,0, or 1.
Why does this statement work:
SELECT * FROM Location WHERE IsInactive <> 1
I have 3 location records where IsInactive = NULL and it doesn't return
them.
Just curious.
DavidNULL is not <> 1
This evaluates to Unknown.
Try:
SELECT * FROM Location WHERE IsInactive = 0 or IsInactive is null
Or:
SELECT * FROM Location WHERE isnull(IsInactive, 0) = 0
"David Pope" <dpope@.nospam.satx.rr.com> wrote in message
news:uUDKvbvAFHA.2584@.TK2MSFTNGP09.phx.gbl...
> I have this table called Location. There is a column called IsInactive.
The
> value can contain a NULL,0, or 1.
> Why does this statement work:
> SELECT * FROM Location WHERE IsInactive <> 1
> I have 3 location records where IsInactive = NULL and it doesn't return
> them.
> Just curious.
> David
>|||Hello David,
This depends on your ANSI_NULL settings.
If you have ANSI_NULL on, anytime you compare something to a NULL it will
fail. This means that if the value is NULL and you want all rows that <>
1, then it won't be returned.
For more information read up on SET ANSI_NULLS in BOL.
http://msdn.microsoft.com/library/d... />
t_7g32.asp
Aaron Weiker
http://aaronweiker.com/

> I have this table called Location. There is a column called
> IsInactive. The value can contain a NULL,0, or 1.
> Why does this statement work:
> SELECT * FROM Location WHERE IsInactive <> 1
> I have 3 location records where IsInactive = NULL and it doesn't
> return them.
> Just curious.
> David
>|||NULL is basically unknown or not yet filled in...
So the systems response to anything >, =, < or != is never going to respond
with the TRUE.
to get your statement to work try...
SELECT * FROM Location WHERE isnull(IsInactive, 0) <> 1
look up isnull in books on-line (BOL) for more specific information.
Ryan
"David Pope" wrote:

> I have this table called Location. There is a column called IsInactive. Th
e
> value can contain a NULL,0, or 1.
> Why does this statement work:
> SELECT * FROM Location WHERE IsInactive <> 1
> I have 3 location records where IsInactive = NULL and it doesn't return
> them.
> Just curious.
> David
>
>|||1) NULLs do not compare to anything. This is absolute foundations, not
fancy stuff. You should know the rules for NULL before you ever wrote
any code.
2) A proper data model does not use flags; that is assembly language
programming. There are no BOOLEAN types in SQL-92 for a good reason,
having to do with NULLs and 3VL.
For example, your data might want to store the date range that
something a location was active.
Oh, unless you have only one of them, the table name ought to be
"Locations" -- it is set and not a scalar.|||
--CELKO-- wrote:

>1) NULLs do not compare to anything. This is absolute foundations, not
>fancy stuff. You should know the rules for NULL before you ever wrote
>any code.
>2) A proper data model does not use flags; that is assembly language
>programming. There are no BOOLEAN types in SQL-92 for a good reason,
>having to do with NULLs and 3VL.
>
There is a BOOLEAN type in SQL-99. That's not to say we'd ever get
used to a type where NOT(Unknown) is Unknown.
Steve Kass
Drew University

>For example, your data might want to store the date range that
>something a location was active.
>Oh, unless you have only one of them, the table name ought to be
>"Locations" -- it is set and not a scalar.
>
>|||I will tell the developers at Olero Software. =0}
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1106751641.483267.271940@.c13g2000cwb.googlegroups.com...
> 1) NULLs do not compare to anything. This is absolute foundations, not
> fancy stuff. You should know the rules for NULL before you ever wrote
> any code.
> 2) A proper data model does not use flags; that is assembly language
> programming. There are no BOOLEAN types in SQL-92 for a good reason,
> having to do with NULLs and 3VL.
> For example, your data might want to store the date range that
> something a location was active.
> Oh, unless you have only one of them, the table name ought to be
> "Locations" -- it is set and not a scalar.
>