Hi,
How to find first not null value in column whitout chacking whole table
(if there is a not null value then show me it and stop searching, the
table is quite big)?
thx,
Martin
*** Sent via Developersdex http://www.developersdex.com *** 26  30873 
Martin R (ma******@go2.pl) writes: How to find first not null value in column whitout chacking whole table
(if there is a not null value then show me it and stop searching, the
table is quite big)?
SELECT TOP 1 col FROM tbl WHERE col IS NOT NULL
Note that "first" here is not extremly well-defined, as a table by
a defintion is a unorded set of data.
If there is no index that involves col at row, SQL Server is likely
to scan the clustered index from left to right. (But in theory it
could open parallel steams, and give you a row in the middle.)
If there is an index that involves col, SQL Serer is likely to scan
that index. And if there is an index with col as the first column,
SQL Server is likely to seek that index, and you would get the lowest
value of col. If you then change "SELECT col" to "SELECT *", you may
be back on the table scan again.
If you have any additional criteria to define this "first" value, then
you need to add an ORDER BY clause to the query.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
>> How to find first not null value in column without chacking whole table <<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless. If you want an ordering, then you need to havs a column
that defines that ordering. You must use an ORDER BY clause on a
cursor.
Next, you are talking about SQL as if you were in a file system, where
you read one record at a time and have explicit control flow via
procedural statements. That is also totally wrong; SQL is a
declarative, compiled language.
Let's make a guess, based on nothing you posted, as to what the DDL
looks like:
CREATE TABLE Foobar
(foo_key INTEGER NOT NULL PRIMARY KEY, -- order by him??
klugger INTEGER, -- target column ??
..);
SELECT foo_key
FROM Foobar
WHERE klugger IS NULL
AND foo_key
= (SELECT MIN(foo_key) FROM Foobar);
(if there is a not null value then show me it and stop searching, the
table is quite big)?
Show you the NULL value that does not exist? That makes no sense.
Neither does "stop searching", since SQL is a set-oriented language.
You get the entire result set back; it can be empty or it can have any
number of rows.
You need to read a book on RDBMS basics. You have missed the most
important concepts.
If there is no NULL in klugger, then yuou will get an empty set back.
On 28 Jan 2006 11:51:33 -0800, --CELKO-- wrote: How to find first not null value in column without chacking whole table <<
(snip)Let's make a guess, based on nothing you posted, as to what the DDL
looks like:
CREATE TABLE Foobar
(foo_key INTEGER NOT NULL PRIMARY KEY, -- order by him??
klugger INTEGER, -- target column ??
..);
SELECT foo_key
FROM Foobar
WHERE klugger IS NULL
AND foo_key
= (SELECT MIN(foo_key) FROM Foobar);
HHi Joe,
I do hope that you actually intended to post
SELECT MIN(foo_key)
FROM Foobar
WHERE klugger IS NULL
--
Hugo Kornelis, SQL Server MVP
celko writes: ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.
Really? To me, first, next, and last do have meanings in an RDBMS,
assuming you have an order.
If you want an ordering, then you need to havs a column
that defines that ordering.
Well, you could use a set of columns, function, or a join into another
table to use a column from another table.
You must use an ORDER BY clause on a
cursor.
Really? I didn't see that anywhere. I've used cursors all the time
without an order by function.
my bad.
Next, you are talking about SQL as if you were in a file system, where
you read one record at a time and have explicit control flow via
procedural statements. That is also totally wrong; SQL is a
declarative, compiled language.
Hmmmmm. You can read one record at a time and have explicit control
flow via procedural statements. That IS included in the system. Most of
us consider the concept of "SQL" to include a paradigm for a relational
database. What does this mean? In my paradigm, you get to use tables.
In your's, you just get to write language, compile it, but never run
it.
SELECT foo_key
FROM Foobar
WHERE klugger IS NULL
AND foo_key
= (SELECT MIN(foo_key) FROM Foobar);
(if there is a not null value then show me it and stop searching, the
table is quite big)?
Actually, the above code REQUIRES a complete scan of the table. It
really is pretty bad code. The engine must locate and identify ALL
foo_key's, and figure out which one is lowest with a null.
Show you the NULL value that does not exist? That makes no sense.
Neither does "stop searching", since SQL is a set-oriented language.
You get the entire result set back; it can be empty or it can have any
number of rows.
Actually, it turns out a set CAN contain one row. You can actually even
REQUIRE that set to contain one row!!!!!
You need to read a book on RDBMS basics. You have missed the most
important concepts.
celko, you might read a book on fundamental human communications. You
have missed the most important concepts.
If there is no NULL in klugger, then yuou will get an empty set back.
Well, you could write code to tell the engine to return exactly one row
back, whether it is null or not.
select top 1 fieldname from filename where fieldname is null
returns the "first" null if there is one, and an empty set if not.
Perhaps though I am missing your point celko. If so, could you be more
precise? SQL really lends itself to precise examples, and broad inexact
generalities often confuse the issues.
Thanks, and have a good day!
>> celko, you might read a book on fundamental human communications. You have missed the most important concepts.
Missed the 'most important' concepts, I think celko has missed the 'very
basic' concepts.
Don't let the guy get to you, he doesn't want to let go of 80's programming
models; but more importantly he doesn't want the industry to move on in case
we stop (or start) using the standard that the committee he belonged to
worked on.
He only spouts anything meaningful when he talks about logical models, as
soon as he moves out of that area he shows us just how out of his depth he
really is.
I keep saying to him, go get a job as a junior programmer - i think it would
help him a) communicate better and b) better understand the problems and
architectures of todays environments.
--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Doug" <dr*********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com... celko writes:
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.
Really? To me, first, next, and last do have meanings in an RDBMS,
assuming you have an order.
If you want an ordering, then you need to havs a column
that defines that ordering.
Well, you could use a set of columns, function, or a join into another
table to use a column from another table.
You must use an ORDER BY clause on a
cursor.
Really? I didn't see that anywhere. I've used cursors all the time
without an order by function.
my bad.
Next, you are talking about SQL as if you were in a file system, where
you read one record at a time and have explicit control flow via
procedural statements. That is also totally wrong; SQL is a
declarative, compiled language.
Hmmmmm. You can read one record at a time and have explicit control
flow via procedural statements. That IS included in the system. Most of
us consider the concept of "SQL" to include a paradigm for a relational
database. What does this mean? In my paradigm, you get to use tables.
In your's, you just get to write language, compile it, but never run
it.
SELECT foo_key
FROM Foobar
WHERE klugger IS NULL
AND foo_key
= (SELECT MIN(foo_key) FROM Foobar);
(if there is a not null value then show me it and stop searching, the
table is quite big)?
Actually, the above code REQUIRES a complete scan of the table. It
really is pretty bad code. The engine must locate and identify ALL
foo_key's, and figure out which one is lowest with a null.
Show you the NULL value that does not exist? That makes no sense.
Neither does "stop searching", since SQL is a set-oriented language.
You get the entire result set back; it can be empty or it can have any
number of rows.
Actually, it turns out a set CAN contain one row. You can actually even
REQUIRE that set to contain one row!!!!!
You need to read a book on RDBMS basics. You have missed the most
important concepts.
celko, you might read a book on fundamental human communications. You
have missed the most important concepts.
If there is no NULL in klugger, then yuou will get an empty set back.
Well, you could write code to tell the engine to return exactly one row
back, whether it is null or not.
select top 1 fieldname from filename where fieldname is null
returns the "first" null if there is one, and an empty set if not.
Perhaps though I am missing your point celko. If so, could you be more
precise? SQL really lends itself to precise examples, and broad inexact
generalities often confuse the issues.
Thanks, and have a good day!
"Doug" <dr*********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com... celko writes:
ordering in an RDBMS, so "first", "next" and "last" are totally meaningless.
Really? To me, first, next, and last do have meanings in an RDBMS,
assuming you have an order.
Celko can be a bit of a curmudgeon and hates to admit when he's wrong. But
here he's right.
You do not have a defined order in a table.
If you want an ordering, then you need to havs a column that defines that ordering.
Well, you could use a set of columns, function, or a join into another
table to use a column from another table.
You must use an ORDER BY clause on a
cursor.
Really? I didn't see that anywhere. I've used cursors all the time
without an order by function.
my bad.
Yes it is. You may be able to loop through the cursor set, but each time
you call that cursor you cannot guarantee the order the results will be
returned in w/o an ORDER by statement. Yes, it may seem that you always get
the same order, but that's just an indirect result of the optimizer and can
change.
Next, you are talking about SQL as if you were in a file system, where you read one record at a time and have explicit control flow via
procedural statements. That is also totally wrong; SQL is a
declarative, compiled language.
Hmmmmm. You can read one record at a time and have explicit control
flow via procedural statements. That IS included in the system. Most of
us consider the concept of "SQL" to include a paradigm for a relational
database. What does this mean? In my paradigm, you get to use tables.
In your's, you just get to write language, compile it, but never run
it.
Here is where Celko is getting onto his high horse. Strictly speaking in
regards to SQL-92 and SQL-99 he's pretty much right. Speaking for T-SQL
he's incorrect. He prefers to think everything should match SQL-92/99.
Well, you could write code to tell the engine to return exactly one row
back, whether it is null or not.
select top 1 fieldname from filename where fieldname is null
returns the "first" null if there is one, and an empty set if not.
What defines "first" here though? First in this case is the first one that
the optimizer happens to return. That can chang from call to call. (of
course this particular example is pointless snice if fieldname is NULL you
might as well just say select NULL.
Perhaps though I am missing your point celko. If so, could you be more
precise? SQL really lends itself to precise examples, and broad inexact
generalities often confuse the issues.
Thanks, and have a good day!
Hello,
Good discussions. ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.
Really? To me, first, next, and last do have meanings in an RDBMS,<> assuming you have an order.
Celko can be a bit of a curmudgeon and hates to admit when he's wrong. But
here he's right.
You do not have a defined order in a table.
Hmmmm. I said you CAN define the order that data is retrieved from a
table. celko says you can't. Obviously everyone knows that the table is
not stored in any particular order. That is a misperception from old
timers from the 70's, but most have figured it out by now.You must use an ORDER BY clause on a
cursor.
Really? I didn't see that anywhere. I've used cursors all the time
without an order by function.
my bad.
Yes it is. You may be able to loop through the cursor set, but each time
you call that cursor you cannot guarantee the order the results will be
returned in w/o an ORDER by statement. Yes, it may seem that you
always get
the same order, but that's just an indirect result of the optimizer and
can
change.
hmmm. i use cursors all the time without order. When I do this, I don't
care about the order. There is nothing that says you HAVE to use a
cursor with an order.
Come to think of it, it is very rare that I use a cursor with an order.
I sometimes order the results though.
What defines "first" here though? First in this case is the first one that
the optimizer happens to return. That can chang from call to call.
(of
course this particular example is pointless snice if fieldname is NULL
you
might as well just say select NULL
Read the code again. As i read it, he wanted to know if a key name
existed where something was null. Yes, you can use "exists", but that
can be REALLY slow.
Using "first" with or without an order is a valid method of checking
for the existance fo something. It is an efficient method, and it
solves business problems.
I believe celko is an arrogant, pompous individual who is not nearly as
smart as he thinks he is. if he were somewhat smarter, he would be able
to help people instead of belittling and confusing them.
a goal on these groups is to help and get help. i believe he
intereferes with that.
I meant to put klugger in the outermost SELECT list. Arrgh!
>> To me, first, next, and last do have meanings in an RDBMS,assuming you have an order. <<
Do you also believe in square circles? This is a matter of definition.
I can post Dr. Codd's 12 rules if you have never read them. I've used cursors all the time without an order by function.my bad. <<
That will give you random results, based on the internal state of the
database at the time of the query.
Well, you could write code to tell the engine to return exactly one row back, whether it is null or not. <<
I probably can, but that defeats the whole idea of a set-oriented
language. You wanted a set; an empty set is a set. You are still
thinking in records and files.
Celko, you might read a book on fundamental human communications. You
have missed the most important concepts. <<
Please, please, I have worked for years on my "abrassive Zen Master"
Newsgorup persona. Everyone that takes a live class from me is
surprised that I go one-on-one for hours or days after the class until
someone understands things. My attitude is that you should not use a
Newsgroup in place of an education -- actually read Codd, Date,
Dijkstra, et al before you post. Not the high level stuff, but at
least the foundations so you do not sound like a "flat-earther" looking
for a kludge.
Perhaps though I am missing your point celko. If so, could you be more precise? <<
Get a copy of DATA & DATABASES for the philosophy of RDBMS,
grasshopper. Come back and I will beat you with a stick some more! I've used cursors all the time without an order by function.my bad. <<
That will give you random results, based on the internal state of the
database at the time of the query.
Really???? Wow. Could you point me to the documentation that states
that you will get a different set of data for your cursor if you don't
use an order by statement?
So which is it? Are we set based, or are we "set based, but sets are
different depending on whether they are in order and in a cursor."
Well, you could write code to tell the engine to return exactly one row back, whether it is null or not. <<
I probably can, but that defeats the whole idea of a set-oriented
language. You wanted a set; an empty set is a set. You are still
thinking in records and files.
Hmmm. Would you care to discuss the example of "existance?" To me, an
empty set is different then a non-empty set. They even have words to
differentiate the two.
Celko, you might read a book on fundamental human communications. You
have missed the most important concepts. <<
Please, please, I have worked for years on my "abrassive Zen Master"
Newsgorup persona.
In retrospect, your arrogant and conceited persona couldn't be natural.
It must be cultivated.
My attitude is
fairly obnoxious.
that you should not use a
Newsgroup in place of an education -- actually read Codd, Date,
Dijkstra, et al before you post. Not the high level stuff, but at
least the foundations so you do not sound like a "flat-earther" looking
for a kludge.
Oh, well, it turns out I do have an education, and years ago I did read
the books in question. Eventually, I went on to work with real world
business people to solve real world business problems.
Real world is a lot more interesting then theory. Those danged peoples
come up with so MANY different permutations and desires!!!!!\
I am rusty, and I miss it. Theory is fun too, as is poking fun at
myself !!!!!
doug "obviously hasn't played with 2005 much"
"Doug" <dr*********@hotmail.com> wrote in message
news:11**********************@g43g2000cwa.googlegr oups.com... Hello,
Good discussions.
>>ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.
Really? To me, first, next, and last do have meanings in an RDBMS,<> assuming you have an order.
Celko can be a bit of a curmudgeon and hates to admit when he's wrong.
But here he's right.
You do not have a defined order in a table.
Hmmmm. I said you CAN define the order that data is retrieved from a
table. celko says you can't.
No, Joe said you have to use an ORDER BY. And he's correct.
Obviously everyone knows that the table is
not stored in any particular order. That is a misperception from old
timers from the 70's, but most have figured it out by now.
Is it everyone or most? Trust me, I've had programmers swear to me it
doesn't matter. (Over my objections I saw a piece of code go into
production that assumed it would always return ONE row and if it did return
more than one, the "order" would be fine.. Sure enough 4 years later when
it returned the "wrong' row I was able to point out the error to a different
programmer who immediately fixed the problem. You can guess which
programmer is still with the company and which one is long gone..)
You must use an ORDER BY clause on a
cursor.
Really? I didn't see that anywhere. I've used cursors all the time
without an order by function.
my bad.
Yes it is. You may be able to loop through the cursor set, but each time
you call that cursor you cannot guarantee the order the results will be
returned in w/o an ORDER by statement. Yes, it may seem that you
always get
the same order, but that's just an indirect result of the optimizer and
can
change.
hmmm. i use cursors all the time without order. When I do this, I don't
care about the order. There is nothing that says you HAVE to use a
cursor with an order.
If you want a reproducible meaning of first, second... last. Or do they
mean different things to you? By saying, "first, last" etc you are
suggesting an order.
Come to think of it, it is very rare that I use a cursor with an order.
I sometimes order the results though.
What defines "first" here though? First in this case is the first one
that the optimizer happens to return. That can chang from call to call.
(of
course this particular example is pointless snice if fieldname is NULL
you
might as well just say select NULL
Read the code again. As i read it, he wanted to know if a key name
existed where something was null. Yes, you can use "exists", but that
can be REALLY slow.
Using "first" with or without an order is a valid method of checking
for the existance fo something. It is an efficient method, and it
solves business problems.
The problem *I* have (and I suspect Joe does to) is the use of the word
"first". It's an ordinal. It assumes an "order"
It shows an implicit mindset.
Now, reading your reply here, it appears to me what you're really saying is
"return ANY row that has this as null" which is different. If you
understand the difference (and I'm guessing you do) I'll shut up. :-)
I believe celko is an arrogant, pompous individual who is not nearly as
smart as he thinks he is. if he were somewhat smarter, he would be able
to help people instead of belittling and confusing them.
Well, if folks can get past his pompous attitude, there is value there. He
does have a point which he doesn't express well. If you approach SQL with
the wrong mindset, you end up with horrible code. I've seen programmers who
are great programmers who come to SQL and try to write procedural code,
looping over results using cursors, etc. when a set based alternative is
clearly a better solution. But they can't see that.
Now.. on his table and fieldnaming thing... well... I think he does go a bit
into the deepend on that. :-) (but again, just from responses I've seen
here I can see why things like tblFOO and vwBAR bother him. :-)
a goal on these groups is to help and get help. i believe he
intereferes with that.
>> it turns out I do have an education, and years ago I did read the books in question. Eventually, I went on to work with real world business people to solve real world business problems. <<
Funny, I was a poor boy who had to put himeself thru two Masters
programs at night while working. full-time days. It took 14 years.
Then I went back to teach.
Because of my background, I learned **really** to program in DoD,
medical and other fields where (bad code) = (death of the innocent
people). The kids on newsgroup do not understand what it is like to
Google up the names the dead.
So you haven't really got the necessary foundation experience in programming
then!
It really shows! Go and get a job as a junior programmer and get some needed
experience instead of preaching your class room ideas to us.
Whereas we live in the real world, developing real applications for real
people with real problems you simply confuse people by posting incorrect,
slow, unscalable rubbish.
Perhaps you should just stick to logical database design and leave to
development to people that have been trained and served industrial training
programmes to get where they are.
As for your attitude its out and out unprofessional, there is absolutely no
excuse for it; my guess is that you are a little bully without the bottle to
talk to somebody one to one in the same way you do here, you'd likely get
decked. Instead, you hide behind your little computer belittling people to
feed your own ego.
If you can't keep up with current technology then stop trying to pull other
people back by forcing your antiquated ideas upon them
--
Tony Rogerson
SQL Server MVP http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com... it turns out I do have an education, and years ago I did read the books
in question. Eventually, I went on to work with real world business
people to solve real world business problems. <<
Funny, I was a poor boy who had to put himeself thru two Masters
programs at night while working. full-time days. It took 14 years.
Then I went back to teach.
Because of my background, I learned **really** to program in DoD,
medical and other fields where (bad code) = (death of the innocent
people). The kids on newsgroup do not understand what it is like to
Google up the names the dead.
celko, you are right there. i have never killed anyone with my bad
code. it makes sense that you got out of programming because of killing
off innocents.
best of luck in your new endeavors,
doug
On 30 Jan 2006 19:57:03 -0800, Doug wrote: I've used cursors all the time without an order by function.my bad. <<
That will give you random results, based on the internal state of the
database at the time of the query.
Really???? Wow. Could you point me to the documentation that states
that you will get a different set of data for your cursor if you don't
use an order by statement?
So which is it? Are we set based, or are we "set based, but sets are
different depending on whether they are in order and in a cursor."
Hi Doug,
We are set based. And a set in a relational database is a collection of
rows with no implied order.
A cursor is not a set - it is an operation to turn a set into a series
of rows, with implied order. If you use ORDER BY in the DECLARE CURSOR
statement, the order of the rows in the cursor is defined. Without an
ORDER BY, the optimizer is free to choose an order in which the rows
will be fed to the cursor. And there is no guarantee that the same order
will be used the next time you execute the same statement.
--
Hugo Kornelis, SQL Server MVP
On 30 Jan 2006 17:23:05 -0800, --CELKO-- wrote: I meant to put klugger in the outermost SELECT list. Arrgh!
Hi Joe,
Really? Your SQL skills are rapidly declining then...
You posted: SELECT foo_key
FROM Foobar
WHERE klugger IS NULL
AND foo_key
= (SELECT MIN(foo_key) FROM Foobar);
So, from the remark above, I conclude that you meant to post
SELECT klugger
FROM Foobar
WHERE klugger IS NULL
AND foo_key
= (SELECT MIN(foo_key) FROM Foobar);
However, Martin's requirement was:
How to find first not null value in column without chacking whole table <<
Your first query will either return the first (based on alphabetic
ordering) foo_key, unless klugger in that row is not NULL (in which
case, nothing is returned).
Your second query will return NULL if klugger in the first row (based on
alphabetic ordering of foo_key) is NULL, or nothing otherwise.
To find the first (based on alphabetic ordering) non NULL value is just
as simple as
SELECT MIN(klugger)
FROM Foobar
WHERE klugger IS NOT NULL
--
Hugo Kornelis, SQL Server MVP
SELECT MIN(klugger)
FROM Foobar
WHERE klugger IS NOT NULL
won't that require a table scan unless there is an index on klugger?
>We are set based. And a set in a relational database is a collection of
rows with no implied order. A cursor is not a set - it is an operation to turn a set into a series
of rows, with implied order.
A cursor does NOT require an order. Further, a cursor does NOT require
an implied order. I believe we agree on the above two statements?
If so, then perhaps another description of a cursor might be "an
operation or contstruct that allows the traversing of a set." You use
a cursor when you need to conduct complex logic on each member of a
set. Normally, you start at one end, examine and perform logic on each
member. Then, you proceed to the next member.
The very first member can be thought of as the "first." You might then
proceed to the "next." You usually end up with the "last."
No where in my usage did I define order. Typically, I look at each
member of the set once.
I never have used cursors a lot, but sometimes the business logic, or
program logic, really makes a cursor useful.
Kindest regards,
Doug
On Jan 31 2006, 08:43 pm, "Doug" <dr*********@hotmail.com> wrote in
news:11**********************@g44g2000cwa.googlegr oups.com: We are set based. And a set in a relational database is a collection of
rows with no implied order.
A cursor is not a set - it is an operation to turn a set into a series
of rows, with implied order.
A cursor does NOT require an order. Further, a cursor does NOT require
an implied order. I believe we agree on the above two statements?
If so, then perhaps another description of a cursor might be "an
operation or contstruct that allows the traversing of a set." You use
a cursor when you need to conduct complex logic on each member of a
set. Normally, you start at one end, examine and perform logic on each
member. Then, you proceed to the next member.
The very first member can be thought of as the "first." You might then
proceed to the "next." You usually end up with the "last."
As long as you are talking about "first", "next", and "last", some order
must exist by definition. That may not be an order that you define (with
the ORDER BY clause), but a cursor must have some internal order in order
to work (forgive the pun). When you declare a cursor, you turn a set into
an ordered list of rows, giving meaning to "first", "next", and "last".
Now, if you are saying that no ORDER BY clause is required when declaring a
cursor, I don't think anyone would argue with that. An order would still
exist, you just won't know what it is.
--
remove a 9 to reply by email
On 31 Jan 2006 17:36:03 -0800, Doug wrote: SELECT MIN(klugger)
FROM Foobar
WHERE klugger IS NOT NULL
won't that require a table scan unless there is an index on klugger?
Hi Doug,
Definitely!
--
Hugo Kornelis, SQL Server MVP
On 31 Jan 2006 17:43:22 -0800, Doug wrote: We are set based. And a set in a relational database is a collection ofrows with no implied order.
A cursor is not a set - it is an operation to turn a set into a series
of rows, with implied order.
A cursor does NOT require an order. Further, a cursor does NOT require
an implied order. I believe we agree on the above two statements?
Hi Doug,
Fully on the first; partially on the second.
I can agree that a cursor doesn't *require* an implied order, but I'll
hasten to add that this is because the cursor itself will imply an order
on the set that is defined for the cursor.
In other words: using a cursor will result in an ordering of the result
set - either explicitly specified in an ORDER BY clause, or implicitly
determined by the DBMS during execution of the cursor.
If so, then perhaps another description of a cursor might be "an
operation or contstruct that allows the traversing of a set." You use
a cursor when you need to conduct complex logic on each member of a
set. Normally, you start at one end, examine and perform logic on each
member. Then, you proceed to the next member.
The very first member can be thought of as the "first." You might then
proceed to the "next." You usually end up with the "last."
No where in my usage did I define order. Typically, I look at each
member of the set once.
This all makes sense. And in such a case, I agree that there is no need
to add an ORDER BY clause - in fact, adding one might harm performance
without any gain.
But this is only true if you have no logic that operates on both the
"current" row and some "previous" row, or that shortcuts execution of
the cursor for the "remaining" rows once a specific state is found. As
soon as you add some of that logic, you will also have to add an ORDER
BY clause to your cursor to make sure that the logic is reproducable
and independent of external factors that might induce a new execution
plan.
I never have used cursors a lot, but sometimes the business logic, or
program logic, really makes a cursor useful.
The situations are very rare, but indeed: they do exist.
--
Hugo Kornelis, SQL Server MVP
>> Perhaps though I am missing your point celko. If so, could you be more precise? <<
Just read any book on RDBMS. The Relational model is based on sets.
By definition sets have no ordering. Dr. Codd is a good place to
start.
Thus, an SQL implementationis free to use PHYSICAL storage in any way
it wishes. Contigous storage such as SQL Server is currently using is
only one approach. Teradata uses hashing and can re-arrange physical
storage without telling you. Ingres has several kidns of indexing and
can change the method used based on a statistics. Several other
products will insert new rows over the storage used for deleteed. The
SAND engine assembles rows from compressed bit vectors and has no
physically
contigous storage at all.
You do not know the RM or the SQL standards, so you think that SELECT
TOP 1 is not a dialect, that you must have physically contigous storage
at all, etc. The kind of code you are writing mimicks the way we used
to write for magnetic tape file systems. You need to get more abstract
in your thinking.
Interesting, so all the research on 'ordered sets' amounts to nothing?
Yet another example of your doctorine, Codd gave us great foundations to
work on he did not hand down doctorine.
Google 'ordered sets' for some ***research***. You do not know the RM or the SQL standards, so you think that SELECT
TOP 1 is not a dialect, that you must have physically contigous storage
at all, etc. The kind of code you are writing mimicks the way we used
What if an application requires the top 10 products then? How would you
achieve this? Would you really pass back the 50,000 products to the
application or middle tier (that doesn't scale by the way) or just easily
use SELECT TOP 10 .. FROM... ORDER BY product_sales_units DESC.
Tony Rogerson, SQL Server MVP.
"--CELKO--" <jc*******@earthlink.net> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com... Perhaps though I am missing your point celko. If so, could you be more
precise? <<
Just read any book on RDBMS. The Relational model is based on sets.
By definition sets have no ordering. Dr. Codd is a good place to
start.
Thus, an SQL implementationis free to use PHYSICAL storage in any way
it wishes. Contigous storage such as SQL Server is currently using is
only one approach. Teradata uses hashing and can re-arrange physical
storage without telling you. Ingres has several kidns of indexing and
can change the method used based on a statistics. Several other
products will insert new rows over the storage used for deleteed. The
SAND engine assembles rows from compressed bit vectors and has no
physically
contigous storage at all.
You do not know the RM or the SQL standards, so you think that SELECT
TOP 1 is not a dialect, that you must have physically contigous storage
at all, etc. The kind of code you are writing mimicks the way we used
to write for magnetic tape file systems. You need to get more abstract
in your thinking.
hi celco,
you sure have wandered off into the hinterlands. i've never claimed to
write code for sands and other esoteric environments.
further, i can't find anywhere discussing how the data is physically
stored.
Unless perhaps you are under the impression that the data being
displayed has something to do with how it is physically stored? if so,
you are mistaken.
I'd be curious how you would go about utilizing a cursor without the
use of the "next" concept.
perhaps you have given up coding however after your bad experiences
with the fatalities.
>> i can't find anywhere discussing how the data is physically stored.<<
I have thought about writing a book or collecting white papers that
would give hihg level overviews of all the ways that SQL is phyiscally
implemented. Most people work with one or maybe two products and they
quickly fall into the trap of thinking that their dialect is SQL and
their implementation is the only way to build an RDBMS. Unless perhaps you are under the impression that the data being displayed has something to do with how it is physically stored? if so,you are mistaken. <<
It should not, but it often does in bad code that. The Sybase/SQL
Server family used to do a GROUP BY with a hidden sort, so an ORDER BY
was redundant and dialect speakers woudl forget to add one. They got
screwed in later releases, of course.
Teradata uses hashing and parallel processing, so hen they do a GROUP
BY, the first set of rows that comes back is the smallest hash bucket.
This one of many reasons why Teradata is for Data Warehouses.
I'd be curious how you would go about utilizing a cursor without the use of the "next" concept. <<
Unh? A CURSOR is the only part of SQL where ordering makes sense. And
where performance really goes to hell.
perhaps you have given up coding however after your bad experiences with the fatalities. <<
No, I still go out and fight the good fight. I just charge $1000-$2000
per day for my help now. But when it is a charity, I donate time.
My wife worked for 13 years on a Cancer ward. Do you know how many
people die from bad medicine each year? Compare doctors to guns. Her
solution was to become a Zen monk.
What is funny to me is that when I get called in to repair a disaster,
I go back to the basics that we used for defense and medical
progamming. Not rocket science or somethng new. The current
programmers are jsut plain awaful.
celko,
no one cares how hte data is actually stored. no one cares how teradata
years ago didn't follow anything approaching a standard. my wife is a
nurse also. so what.
no one cares that i wrote sqr into sybase 10 years ago against the main
data sets of sears looking for performance that art anderson couldn't
find.
arrogance irritates me. arrogance with performance is barely
tolerable. if you would even PRETEND to be somewhat humble on
occasion, you might become somewhat tolerable.
if you read your last sentence, you are either admitting you are awful,
or you are not a current program.
Which set do you belong to?
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Tom Esker |
last post by:
I've got Javascript in a form that adds up all of the numbers in a
column of form fields and displays a total. It works great if every
field has an initial value of 0, but if any of them are null,...
|
by: Pekka Henttonen |
last post by:
For some reason SQL0407N error messages never reveal the name of the column
to which a NULL value has been assigned. Here is an example:
SQL0407N Assignment of a NULL value to a NOT NULL column...
|
by: TC |
last post by:
What does it mean for an integer to have a null value? I am trying to use
the DataView.Find method. That method has an integer return type which
contains the "index of the row in the DataView...
|
by: Benny Raymond |
last post by:
reply to: benny@pocketrocks.com if possible:
I'm trying to set up a hierarchy system in this database where each row
can be related to a previous row. The problem is that when I go to...
|
by: rishaan |
last post by:
I have a question about inserting null values in .Net into a column.
As you know the text boxes in .Net have a defined data type of string.
Let us say, on a form the user did not enter a certain...
|
by: phil campaigne |
last post by:
Occasionally I want to store a null value for my java.sql.Time--> Time
column in Postgresql.
update event set game_clock=null where event_id=1;
I can retreive the record with the null value...
|
by: BF |
last post by:
Hi,
I have some tables where I import data in, lots of field have gotten a
NULL value which the application can not handle.
Now can I replace each NULL value with '' in a columns with:
update...
|
by: Eric Layman |
last post by:
Hi everyone,
Im puzzled by a NULL behaviour in SQL 2000 server.
There is a column in the table that does not allow NULL.
During data mining, the staff noted that, for that particular column,...
|
by: billa856 |
last post by:
Hi,
I am trying to insert Null value in column(ShipDate) in my table.That column(ShipDate)'s type id date/time and format is short date.
I am using "" to insert Null in that column(ShipDate)...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |
