Is there a smart set-based solution?

I have a column of starting times below (converted to seconds for
simplicity):
500
505
510
535
910
939
944
977
I need to assign a Group ID to the above values, based on a time
interval of 30 seconds, so the correct result would be:
Group ID
500, 1
505, 1
510, 1
535, 2
910, 3
939, 3
944, 4
977, 5
The value of Group ID isn't important (could be 100, 200, etc.) as long
as values in the same 30-second interval have the same Group ID. When a
new group is started, a new 30-second interval is also started.
I have created a solution using SQL loops (row-based processing) but
was wondering if there is an efficient/elegant set-based solution when
there are thousands of records?
Thanks
MowgliNo. Any set based solution will involve nested loops because the value of
the group id is dependent on the values of the starting time in the previous
rows. This is one of the rare cases in which a cursor will actually perform
faster than a set based solution because it can accomplish what you want in
a single pass through the table.
"Mowgli" <wmasterproxy@.yahoo.co.uk> wrote in message
news:1122677634.927113.124950@.g43g2000cwa.googlegroups.com...
> I have a column of starting times below (converted to seconds for
> simplicity):
> 500
> 505
> 510
> 535
> 910
> 939
> 944
> 977
> I need to assign a Group ID to the above values, based on a time
> interval of 30 seconds, so the correct result would be:
> Group ID
> 500, 1
> 505, 1
> 510, 1
> 535, 2
> 910, 3
> 939, 3
> 944, 4
> 977, 5
> The value of Group ID isn't important (could be 100, 200, etc.) as long
> as values in the same 30-second interval have the same Group ID. When a
> new group is started, a new 30-second interval is also started.
> I have created a solution using SQL loops (row-based processing) but
> was wondering if there is an efficient/elegant set-based solution when
> there are thousands of records?
>
> Thanks
> Mowgli
>|||If there are a lot of indexes on the table, you may want to cache the
results generated by the cursor in a table variable so that they can be
committed in a single set-based statement.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uf4PvFKlFHA.3260@.TK2MSFTNGP10.phx.gbl...
> No. Any set based solution will involve nested loops because the value of
> the group id is dependent on the values of the starting time in the
previous
> rows. This is one of the rare cases in which a cursor will actually
perform
> faster than a set based solution because it can accomplish what you want
in
> a single pass through the table.
> "Mowgli" <wmasterproxy@.yahoo.co.uk> wrote in message
> news:1122677634.927113.124950@.g43g2000cwa.googlegroups.com...
>|||>> I have created a solution using SQL loops (row-based processing) but was wonde
ring if there is an efficient/elegant set-based solution when there are thousands
of records [sic]? <<
Rows are not records. If you keep thinking in the wrong terms, you
will always think that you have to use loops. Create a simple table
for each group and then use a BETWEEN predicate. You can get an entire
year into about a million row table.
CREATE TABLE TimeGroups
(group_id INTEGER NOT NULL PRIMARY KEY
start_time INTEGER NOT NULL,
end_time INTEGER NOT NULL,
CHECK (start_time +30 = end_time));
You should find that this is about 10 times faster than a cursor as the
set gets larger.|||Unfortunately, your solution doesn't answer the original question. How do
you build the table in the first place? In this case a cursor is the
fastest way to find the group id's, because the alternative is a self-join,
which will always require more than one pass through the table. Not every
cursor is evil. Actually, you'll find that the time that it takes for the
cursor based solution increases linearly with the number of rows, whereas
the time that it takes for the set-based solution increases geometrically.
By the way, E. F. Codd used the term "record" in some of his papers. I
wonder, do you add [sic] when you refer to his work?
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1122689807.624405.127910@.g49g2000cwa.googlegroups.com...
was wondering if there is an efficient/elegant set-based solution when there
are thousands of records [sic]? <<
> Rows are not records. If you keep thinking in the wrong terms, you
> will always think that you have to use loops. Create a simple table
> for each group and then use a BETWEEN predicate. You can get an entire
> year into about a million row table.
> CREATE TABLE TimeGroups
> (group_id INTEGER NOT NULL PRIMARY KEY
> start_time INTEGER NOT NULL,
> end_time INTEGER NOT NULL,
> CHECK (start_time +30 = end_time));
> You should find that this is about 10 times faster than a cursor as the
> set gets larger.
>|||Hi Mr. Celko
You wrote... Rows are not records. If you keep thinking in the wrong terms,
you
will always think that you have to use loops.
Could you elaborate on that a bit.
many thanks
Shahriar
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1122689807.624405.127910@.g49g2000cwa.googlegroups.com...
> Rows are not records. If you keep thinking in the wrong terms, you
> will always think that you have to use loops. Create a simple table
> for each group and then use a BETWEEN predicate. You can get an entire
> year into about a million row table.
> CREATE TABLE TimeGroups
> (group_id INTEGER NOT NULL PRIMARY KEY
> start_time INTEGER NOT NULL,
> end_time INTEGER NOT NULL,
> CHECK (start_time +30 = end_time));
> You should find that this is about 10 times faster than a cursor as the
> set gets larger.
>|||Mowgli
I think this may just do it.
Table1 contains the field times.
Table2 contains 2 fields, Times and groupid
insert into table2 (times,groupid) (select times,times/30 from Table1)
Shahriar
"Mowgli" <wmasterproxy@.yahoo.co.uk> wrote in message
news:1122677634.927113.124950@.g43g2000cwa.googlegroups.com...
>I have a column of starting times below (converted to seconds for
> simplicity):
> 500
> 505
> 510
> 535
> 910
> 939
> 944
> 977
> I need to assign a Group ID to the above values, based on a time
> interval of 30 seconds, so the correct result would be:
> Group ID
> 500, 1
> 505, 1
> 510, 1
> 535, 2
> 910, 3
> 939, 3
> 944, 4
> 977, 5
> The value of Group ID isn't important (could be 100, 200, etc.) as long
> as values in the same 30-second interval have the same Group ID. When a
> new group is started, a new 30-second interval is also started.
> I have created a solution using SQL loops (row-based processing) but
> was wondering if there is an efficient/elegant set-based solution when
> there are thousands of records?
>
> Thanks
> Mowgli
>|||I tried that: it doesn't work correctly. The start time for each group
after the first depends on whether the start time of the first row for that
group is more than 30 seconds past the start time of the first row for the
previous group. If you use times/30 the interval that includes 500 runs
from 480 through 509, so the row with the 510 start time is in the next
interval. The expected results clearly show that the rows with 500 and 510
are in the same interval.
There is no set-based operation that can accurately calculate the group id
in a single pass. With a cursor, you can cache the start time of the
current group in a variable, and bump the current group id if the start time
for the current cursor row is more than 30 seconds past the cached start
time for the current group.
"Shahriar" <HelloShahriar@.hotmail.com> wrote in message
news:IqEGe.97$4e6.80@.trnddc04...
> Mowgli
> I think this may just do it.
> Table1 contains the field times.
> Table2 contains 2 fields, Times and groupid
>
> insert into table2 (times,groupid) (select times,times/30 from Table1)
> Shahriar
>
> "Mowgli" <wmasterproxy@.yahoo.co.uk> wrote in message
> news:1122677634.927113.124950@.g43g2000cwa.googlegroups.com...
>|||hi,
if u are not particular about the groupID, i have a solution for u.
but as per the requirement, group ID definately falls in 30sec intervals.
select num, (num-(select min(num)from #test))/30 GroupID from #test
please let me know if this answers your question
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Mowgli" wrote:

> I have a column of starting times below (converted to seconds for
> simplicity):
> 500
> 505
> 510
> 535
> 910
> 939
> 944
> 977
> I need to assign a Group ID to the above values, based on a time
> interval of 30 seconds, so the correct result would be:
> Group ID
> 500, 1
> 505, 1
> 510, 1
> 535, 2
> 910, 3
> 939, 3
> 944, 4
> 977, 5
> The value of Group ID isn't important (could be 100, 200, etc.) as long
> as values in the same 30-second interval have the same Group ID. When a
> new group is started, a new 30-second interval is also started.
> I have created a solution using SQL loops (row-based processing) but
> was wondering if there is an efficient/elegant set-based solution when
> there are thousands of records?
>
> Thanks
> Mowgli
>|||Brian, Shahriar
Thanks for your input and for concentrating on the problem rather than
on my terminology!
The example I gave uses a time interval of 30 seconds but this can also
be variable (20, 40, 60, etc.).
Brian, perhaps you could demonstrate the inefficient set-based solution
to illustrate your point?
... As the issues may not be clear to anyone who hasn't attempted this
type of "real-life" problem.

Is there a list of the various DBCC TRACEON values?

(SQL Server 2000, SP3a)
Hello, all!
I'm interested to learn what the various Trace Flag values are for use with DBCC
TRACEON/OFF.
Thanks for any help you can provide!
John PetersonHi John,
You might find the following links useful:
Some Useful Undocumented SQL Server 2000 Trace Flags
http://www.sql-server-performance.com/ac_2000_trace_flags.asp
SQL Server 7.0 and 2000 Trace Flags
http://www.sql-server-performance.com/rd_traceflags.asp
-Sue
On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>(SQL Server 2000, SP3a)
>Hello, all!
>I'm interested to learn what the various Trace Flag values are for use with DBCC
>TRACEON/OFF.
>Thanks for any help you can provide!
>John Peterson
>|||Thanks, Sue! That's exactly what I was looking for. Though, this still doesn't seem to
be an entirely comprehensive list. Do you know whether and where a full list might be
available?
Thanks again! :-)
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
> Hi John,
> You might find the following links useful:
> Some Useful Undocumented SQL Server 2000 Trace Flags
> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
> SQL Server 7.0 and 2000 Trace Flags
> http://www.sql-server-performance.com/rd_traceflags.asp
> -Sue
> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >(SQL Server 2000, SP3a)
> >
> >Hello, all!
> >
> >I'm interested to learn what the various Trace Flag values are for use with DBCC
> >TRACEON/OFF.
> >
> >Thanks for any help you can provide!
> >
> >John Peterson
> >
>|||There is no full list and many of them are undocumented...so
there isn't a comprehensive documentation of all the trace
flags. Books online has a few of them documented under the
trace flags topic, you can find others by searching the
Knowledge base. In addition some books, links etc have some
listed but no one source of all of the trace flags.
-Sue
On Mon, 27 Oct 2003 12:03:36 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Thanks, Sue! That's exactly what I was looking for. Though, this still doesn't seem to
>be an entirely comprehensive list. Do you know whether and where a full list might be
>available?
>Thanks again! :-)
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
>> Hi John,
>> You might find the following links useful:
>> Some Useful Undocumented SQL Server 2000 Trace Flags
>> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
>> SQL Server 7.0 and 2000 Trace Flags
>> http://www.sql-server-performance.com/rd_traceflags.asp
>> -Sue
>> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
>> <j0hnp@.comcast.net> wrote:
>> >(SQL Server 2000, SP3a)
>> >
>> >Hello, all!
>> >
>> >I'm interested to learn what the various Trace Flag values are for use with DBCC
>> >TRACEON/OFF.
>> >
>> >Thanks for any help you can provide!
>> >
>> >John Peterson
>> >
>|||Heh! That what I was afraid of. ;-)
Thanks again!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:hlrqpvk92veo1l0i4lp6249u1nfhb9i9eo@.4ax.com...
> There is no full list and many of them are undocumented...so
> there isn't a comprehensive documentation of all the trace
> flags. Books online has a few of them documented under the
> trace flags topic, you can find others by searching the
> Knowledge base. In addition some books, links etc have some
> listed but no one source of all of the trace flags.
> -Sue
> On Mon, 27 Oct 2003 12:03:36 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >Thanks, Sue! That's exactly what I was looking for. Though, this still doesn't seem
to
> >be an entirely comprehensive list. Do you know whether and where a full list might be
> >available?
> >
> >Thanks again! :-)
> >
> >
> >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> >news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
> >> Hi John,
> >> You might find the following links useful:
> >> Some Useful Undocumented SQL Server 2000 Trace Flags
> >> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
> >> SQL Server 7.0 and 2000 Trace Flags
> >> http://www.sql-server-performance.com/rd_traceflags.asp
> >>
> >> -Sue
> >>
> >> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
> >> <j0hnp@.comcast.net> wrote:
> >>
> >> >(SQL Server 2000, SP3a)
> >> >
> >> >Hello, all!
> >> >
> >> >I'm interested to learn what the various Trace Flag values are for use with DBCC
> >> >TRACEON/OFF.
> >> >
> >> >Thanks for any help you can provide!
> >> >
> >> >John Peterson
> >> >
> >>
> >
>|||The 'full' list is only available internally in the SQL Server development
group. The reason for this is that the majority of trace flags are
undocumented and usually dangerous (i.e. they may cause SQL Server to crash,
shutdown, or behave in some strange way). You should not turn on trace flags
unless you're absolutely sure what the effect will be.
Thanks and regards,
Paul.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eJ1N01MnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> Heh! That what I was afraid of. ;-)
> Thanks again!
>
> "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> news:hlrqpvk92veo1l0i4lp6249u1nfhb9i9eo@.4ax.com...
> > There is no full list and many of them are undocumented...so
> > there isn't a comprehensive documentation of all the trace
> > flags. Books online has a few of them documented under the
> > trace flags topic, you can find others by searching the
> > Knowledge base. In addition some books, links etc have some
> > listed but no one source of all of the trace flags.
> >
> > -Sue
> >
> > On Mon, 27 Oct 2003 12:03:36 -0700, "John Peterson"
> > <j0hnp@.comcast.net> wrote:
> >
> > >Thanks, Sue! That's exactly what I was looking for. Though, this
still doesn't seem
> to
> > >be an entirely comprehensive list. Do you know whether and where a
full list might be
> > >available?
> > >
> > >Thanks again! :-)
> > >
> > >
> > >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > >news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
> > >> Hi John,
> > >> You might find the following links useful:
> > >> Some Useful Undocumented SQL Server 2000 Trace Flags
> > >> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
> > >> SQL Server 7.0 and 2000 Trace Flags
> > >> http://www.sql-server-performance.com/rd_traceflags.asp
> > >>
> > >> -Sue
> > >>
> > >> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
> > >> <j0hnp@.comcast.net> wrote:
> > >>
> > >> >(SQL Server 2000, SP3a)
> > >> >
> > >> >Hello, all!
> > >> >
> > >> >I'm interested to learn what the various Trace Flag values are for
use with DBCC
> > >> >TRACEON/OFF.
> > >> >
> > >> >Thanks for any help you can provide!
> > >> >
> > >> >John Peterson
> > >> >
> > >>
> > >
> >
>|||Yeah...and I should have included some of what Paul posted
for you. Additionally, traces often change from release to
release so there really is no way to know what might be safe
and what might not be safe from version to version.
If the trace will server some purpose in general use, you
can typically find it in a KB article with instructions on
when it's appropriate to use.
-Sue
On Mon, 27 Oct 2003 14:01:08 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>Heh! That what I was afraid of. ;-)
>Thanks again!
>
>"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>news:hlrqpvk92veo1l0i4lp6249u1nfhb9i9eo@.4ax.com...
>> There is no full list and many of them are undocumented...so
>> there isn't a comprehensive documentation of all the trace
>> flags. Books online has a few of them documented under the
>> trace flags topic, you can find others by searching the
>> Knowledge base. In addition some books, links etc have some
>> listed but no one source of all of the trace flags.
>> -Sue
>> On Mon, 27 Oct 2003 12:03:36 -0700, "John Peterson"
>> <j0hnp@.comcast.net> wrote:
>> >Thanks, Sue! That's exactly what I was looking for. Though, this still doesn't seem
>to
>> >be an entirely comprehensive list. Do you know whether and where a full list might be
>> >available?
>> >
>> >Thanks again! :-)
>> >
>> >
>> >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
>> >news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
>> >> Hi John,
>> >> You might find the following links useful:
>> >> Some Useful Undocumented SQL Server 2000 Trace Flags
>> >> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
>> >> SQL Server 7.0 and 2000 Trace Flags
>> >> http://www.sql-server-performance.com/rd_traceflags.asp
>> >>
>> >> -Sue
>> >>
>> >> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
>> >> <j0hnp@.comcast.net> wrote:
>> >>
>> >> >(SQL Server 2000, SP3a)
>> >> >
>> >> >Hello, all!
>> >> >
>> >> >I'm interested to learn what the various Trace Flag values are for use with DBCC
>> >> >TRACEON/OFF.
>> >> >
>> >> >Thanks for any help you can provide!
>> >> >
>> >> >John Peterson
>> >> >
>> >>
>> >
>|||Thanks, Paul!
My curiosity was piqued by this link that shows a lot of the undocumented DBCC commands,
as I was investigating the DBCC SETINSTANCE (which is the underlying call from the
undocumented sp_user_counter* SPs).
http://www.sqlmag.com/forums/messageview.cfm?catid=13&threadid=9801
I noticed that this list was generated with a Trace Flag of 2520, which wasn't in that
list that Sue provided (the one by Randy Dyess). So I got to thinking, what *other* Trace
Flags are available, but undocumented?
In just some cursory browsing, I learned a number of things. For example, that you can
potentially log deadlock conditions to the error log for all connections, as indicated by
this link to dbforums:
http://65.61.175.198/t894916.html
I was interested to learn what other nifty "goodies" might be available. ;-)
Thanks for the information, and if you have any additional comments, I'd love to hear from
you.
Regards,
John Peterson
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ua$btgNnDHA.2500@.TK2MSFTNGP10.phx.gbl...
> The 'full' list is only available internally in the SQL Server development
> group. The reason for this is that the majority of trace flags are
> undocumented and usually dangerous (i.e. they may cause SQL Server to crash,
> shutdown, or behave in some strange way). You should not turn on trace flags
> unless you're absolutely sure what the effect will be.
> Thanks and regards,
> Paul.
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:eJ1N01MnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > Heh! That what I was afraid of. ;-)
> >
> > Thanks again!
> >
> >
> > "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > news:hlrqpvk92veo1l0i4lp6249u1nfhb9i9eo@.4ax.com...
> > > There is no full list and many of them are undocumented...so
> > > there isn't a comprehensive documentation of all the trace
> > > flags. Books online has a few of them documented under the
> > > trace flags topic, you can find others by searching the
> > > Knowledge base. In addition some books, links etc have some
> > > listed but no one source of all of the trace flags.
> > >
> > > -Sue
> > >
> > > On Mon, 27 Oct 2003 12:03:36 -0700, "John Peterson"
> > > <j0hnp@.comcast.net> wrote:
> > >
> > > >Thanks, Sue! That's exactly what I was looking for. Though, this
> still doesn't seem
> > to
> > > >be an entirely comprehensive list. Do you know whether and where a
> full list might be
> > > >available?
> > > >
> > > >Thanks again! :-)
> > > >
> > > >
> > > >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > > >news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
> > > >> Hi John,
> > > >> You might find the following links useful:
> > > >> Some Useful Undocumented SQL Server 2000 Trace Flags
> > > >> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
> > > >> SQL Server 7.0 and 2000 Trace Flags
> > > >> http://www.sql-server-performance.com/rd_traceflags.asp
> > > >>
> > > >> -Sue
> > > >>
> > > >> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
> > > >> <j0hnp@.comcast.net> wrote:
> > > >>
> > > >> >(SQL Server 2000, SP3a)
> > > >> >
> > > >> >Hello, all!
> > > >> >
> > > >> >I'm interested to learn what the various Trace Flag values are for
> use with DBCC
> > > >> >TRACEON/OFF.
> > > >> >
> > > >> >Thanks for any help you can provide!
> > > >> >
> > > >> >John Peterson
> > > >> >
> > > >>
> > > >
> > >
> >
> >
>|||You're welcome. Be even more vehemently warned away from playing with
unfamiliar, undocumented DBCC commands. Some of these (when used outside
specific conditions related to recovering corrupt or inaccessible databases)
can make your database unusable and unrecoverable.
--
Paul Randal
DBCC Technical Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:e93MX#OnDHA.2424@.TK2MSFTNGP10.phx.gbl...
> Thanks, Paul!
> My curiosity was piqued by this link that shows a lot of the undocumented
DBCC commands,
> as I was investigating the DBCC SETINSTANCE (which is the underlying call
from the
> undocumented sp_user_counter* SPs).
> http://www.sqlmag.com/forums/messageview.cfm?catid=13&threadid=9801
> I noticed that this list was generated with a Trace Flag of 2520, which
wasn't in that
> list that Sue provided (the one by Randy Dyess). So I got to thinking,
what *other* Trace
> Flags are available, but undocumented?
> In just some cursory browsing, I learned a number of things. For example,
that you can
> potentially log deadlock conditions to the error log for all connections,
as indicated by
> this link to dbforums:
> http://65.61.175.198/t894916.html
> I was interested to learn what other nifty "goodies" might be available.
;-)
> Thanks for the information, and if you have any additional comments, I'd
love to hear from
> you.
> Regards,
> John Peterson
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:ua$btgNnDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > The 'full' list is only available internally in the SQL Server
development
> > group. The reason for this is that the majority of trace flags are
> > undocumented and usually dangerous (i.e. they may cause SQL Server to
crash,
> > shutdown, or behave in some strange way). You should not turn on trace
flags
> > unless you're absolutely sure what the effect will be.
> >
> > Thanks and regards,
> >
> > Paul.
> >
> > --
> > Paul Randal
> > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > news:eJ1N01MnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > > Heh! That what I was afraid of. ;-)
> > >
> > > Thanks again!
> > >
> > >
> > > "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > > news:hlrqpvk92veo1l0i4lp6249u1nfhb9i9eo@.4ax.com...
> > > > There is no full list and many of them are undocumented...so
> > > > there isn't a comprehensive documentation of all the trace
> > > > flags. Books online has a few of them documented under the
> > > > trace flags topic, you can find others by searching the
> > > > Knowledge base. In addition some books, links etc have some
> > > > listed but no one source of all of the trace flags.
> > > >
> > > > -Sue
> > > >
> > > > On Mon, 27 Oct 2003 12:03:36 -0700, "John Peterson"
> > > > <j0hnp@.comcast.net> wrote:
> > > >
> > > > >Thanks, Sue! That's exactly what I was looking for. Though, this
> > still doesn't seem
> > > to
> > > > >be an entirely comprehensive list. Do you know whether and where a
> > full list might be
> > > > >available?
> > > > >
> > > > >Thanks again! :-)
> > > > >
> > > > >
> > > > >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > > > >news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
> > > > >> Hi John,
> > > > >> You might find the following links useful:
> > > > >> Some Useful Undocumented SQL Server 2000 Trace Flags
> > > > >> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
> > > > >> SQL Server 7.0 and 2000 Trace Flags
> > > > >> http://www.sql-server-performance.com/rd_traceflags.asp
> > > > >>
> > > > >> -Sue
> > > > >>
> > > > >> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
> > > > >> <j0hnp@.comcast.net> wrote:
> > > > >>
> > > > >> >(SQL Server 2000, SP3a)
> > > > >> >
> > > > >> >Hello, all!
> > > > >> >
> > > > >> >I'm interested to learn what the various Trace Flag values are
for
> > use with DBCC
> > > > >> >TRACEON/OFF.
> > > > >> >
> > > > >> >Thanks for any help you can provide!
> > > > >> >
> > > > >> >John Peterson
> > > > >> >
> > > > >>
> > > > >
> > > >
> > >
> > >
> >
> >
>|||Yes, and some of the (like DBCC PSS) give very valuable information about
for instance logical I/O's per session to mention one, which is unavailable
otherwise (to my knowledge). It's about time that some of these commands
*are* well documented, preferably by the guys who build/maintain them..
--
regards,
Mario
http://www.sqlinternals.com
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ergphuRnDHA.964@.TK2MSFTNGP10.phx.gbl...
> You're welcome. Be even more vehemently warned away from playing with
> unfamiliar, undocumented DBCC commands. Some of these (when used outside
> specific conditions related to recovering corrupt or inaccessible
databases)
> can make your database unusable and unrecoverable.
> --
> Paul Randal
> DBCC Technical Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:e93MX#OnDHA.2424@.TK2MSFTNGP10.phx.gbl...
> > Thanks, Paul!
> >
> > My curiosity was piqued by this link that shows a lot of the
undocumented
> DBCC commands,
> > as I was investigating the DBCC SETINSTANCE (which is the underlying
call
> from the
> > undocumented sp_user_counter* SPs).
> >
> > http://www.sqlmag.com/forums/messageview.cfm?catid=13&threadid=9801
> >
> > I noticed that this list was generated with a Trace Flag of 2520, which
> wasn't in that
> > list that Sue provided (the one by Randy Dyess). So I got to thinking,
> what *other* Trace
> > Flags are available, but undocumented?
> >
> > In just some cursory browsing, I learned a number of things. For
example,
> that you can
> > potentially log deadlock conditions to the error log for all
connections,
> as indicated by
> > this link to dbforums:
> >
> > http://65.61.175.198/t894916.html
> >
> > I was interested to learn what other nifty "goodies" might be available.
> ;-)
> >
> > Thanks for the information, and if you have any additional comments, I'd
> love to hear from
> > you.
> >
> > Regards,
> >
> > John Peterson
> >
> >
> > "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> > news:ua$btgNnDHA.2500@.TK2MSFTNGP10.phx.gbl...
> > > The 'full' list is only available internally in the SQL Server
> development
> > > group. The reason for this is that the majority of trace flags are
> > > undocumented and usually dangerous (i.e. they may cause SQL Server to
> crash,
> > > shutdown, or behave in some strange way). You should not turn on trace
> flags
> > > unless you're absolutely sure what the effect will be.
> > >
> > > Thanks and regards,
> > >
> > > Paul.
> > >
> > > --
> > > Paul Randal
> > > DBCC Technical Lead, Microsoft SQL Server Storage Engine
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > "John Peterson" <j0hnp@.comcast.net> wrote in message
> > > news:eJ1N01MnDHA.2652@.TK2MSFTNGP09.phx.gbl...
> > > > Heh! That what I was afraid of. ;-)
> > > >
> > > > Thanks again!
> > > >
> > > >
> > > > "Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > > > news:hlrqpvk92veo1l0i4lp6249u1nfhb9i9eo@.4ax.com...
> > > > > There is no full list and many of them are undocumented...so
> > > > > there isn't a comprehensive documentation of all the trace
> > > > > flags. Books online has a few of them documented under the
> > > > > trace flags topic, you can find others by searching the
> > > > > Knowledge base. In addition some books, links etc have some
> > > > > listed but no one source of all of the trace flags.
> > > > >
> > > > > -Sue
> > > > >
> > > > > On Mon, 27 Oct 2003 12:03:36 -0700, "John Peterson"
> > > > > <j0hnp@.comcast.net> wrote:
> > > > >
> > > > > >Thanks, Sue! That's exactly what I was looking for. Though,
this
> > > still doesn't seem
> > > > to
> > > > > >be an entirely comprehensive list. Do you know whether and where
a
> > > full list might be
> > > > > >available?
> > > > > >
> > > > > >Thanks again! :-)
> > > > > >
> > > > > >
> > > > > >"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
> > > > > >news:5snqpvkqjfk30dimtq22tl6kdrd6blrmih@.4ax.com...
> > > > > >> Hi John,
> > > > > >> You might find the following links useful:
> > > > > >> Some Useful Undocumented SQL Server 2000 Trace Flags
> > > > > >> http://www.sql-server-performance.com/ac_2000_trace_flags.asp
> > > > > >> SQL Server 7.0 and 2000 Trace Flags
> > > > > >> http://www.sql-server-performance.com/rd_traceflags.asp
> > > > > >>
> > > > > >> -Sue
> > > > > >>
> > > > > >> On Mon, 27 Oct 2003 10:59:22 -0700, "John Peterson"
> > > > > >> <j0hnp@.comcast.net> wrote:
> > > > > >>
> > > > > >> >(SQL Server 2000, SP3a)
> > > > > >> >
> > > > > >> >Hello, all!
> > > > > >> >
> > > > > >> >I'm interested to learn what the various Trace Flag values are
> for
> > > use with DBCC
> > > > > >> >TRACEON/OFF.
> > > > > >> >
> > > > > >> >Thanks for any help you can provide!
> > > > > >> >
> > > > > >> >John Peterson
> > > > > >> >
> > > > > >>
> > > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>

Is not Null clause not working for me

I've got a strange problem with my sql not working. I'm trying to query a
table and have a where clause to return all the values in a field which are
not null(in other words left blank) The query can be seen below.
However this query is still returning all of the records in the table even
though the u_creditor_ref field has mainly null values.
My table looks like the following: -
u_surname u_firstname u_account_name u_creditor_ref
Cairns Stephen
Kelly Michael 456789
McCartney&Son
Walsh John 4565
Has anyone any idea what i'm doing wrong. Im running this query in query
analyzer
Select u_surname, u_firstname, u_account_name, u_creditor_ref
from UserObject
where u_creditor_ref is not nullStephen
I think you have an empty string in the row
Try adding WHERE col <>''
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which
are
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||Perhaps you don't have NULL for those rows. Perhaps it is a blank or similar
? Can you post a
reproduction script?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which ar
e
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||"which are
not null(in other words left blank) "
Keep in mind that "blank" LEN(Column) = 0 , doesnt mean that they are NULL
!
If there is an empty string inserted you have to check for that LEN(
u_creditor_ref) = 0, if you have addtionaly also NULL values in there you
have to add "AND u_creditor_ref IS NULL)
Check the Column for NULL Values With (Select Count(*), CASE u_creditor_ref
WHEN NULL THEN 'NULL' ELSE 'NON-NULL' END From Yourtable Group by CASE
u_creditor_ref WHEN NULL THEN 'NULL' ELSE 'NON-NULL' END)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> I've got a strange problem with my sql not working. I'm trying to query a
> table and have a where clause to return all the values in a field which
> are
> not null(in other words left blank) The query can be seen below.
> However this query is still returning all of the records in the table even
> though the u_creditor_ref field has mainly null values.
> My table looks like the following: -
> u_surname u_firstname u_account_name u_creditor_ref
> Cairns Stephen
> Kelly Michael 456789
> McCartney&Son
> Walsh John 4565
> Has anyone any idea what i'm doing wrong. Im running this query in query
> analyzer
>
> Select u_surname, u_firstname, u_account_name, u_creditor_ref
> from UserObject
> where u_creditor_ref is not null|||That works cheers
"Uri Dimant" wrote:

> Stephen
> I think you have an empty string in the row
> Try adding WHERE col <>''
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
> are
>
>|||Yeah your right. it looked like a null but if i did either of the following
it worked
where u_creditor_ref <>''
or
where u_creditor_ref != ''
which of these is better to use or do they mean the exact same thing?
"Tibor Karaszi" wrote:

> Perhaps you don't have NULL for those rows. Perhaps it is a blank or simil
ar? Can you post a
> reproduction script?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:0CC3BF9D-B337-4611-BDFC-DAA680E9A21A@.microsoft.com...
>
>|||Use <>. They do the same, but <> is ANSI SQL compliant, so you might just wa
nt to get into the
habit...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:90D68211-3C36-44EC-A7A3-5582947F456C@.microsoft.com...
> Yeah your right. it looked like a null but if i did either of the followin
g
> it worked
> where u_creditor_ref <>''
> or
> where u_creditor_ref != ''
> which of these is better to use or do they mean the exact same thing?
> "Tibor Karaszi" wrote:
>|||Use <> for spec purposes.
Jens Suessmeyer.
"Stephen" <Stephen@.discussions.microsoft.com> schrieb im Newsbeitrag
news:90D68211-3C36-44EC-A7A3-5582947F456C@.microsoft.com...
> Yeah your right. it looked like a null but if i did either of the
> following
> it worked
> where u_creditor_ref <>''
> or
> where u_creditor_ref != ''
> which of these is better to use or do they mean the exact same thing?
> "Tibor Karaszi" wrote:
>