|
|
You're in the
sql server (alternate)
group:How to find first not null value in column
sql server (alternate):
[quoted text, click to view]
>> 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.
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
Martin R (martin80@go2.pl) writes:
[quoted text, click to view] > 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, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
[quoted text, click to view]
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 --
celko writes:
[quoted text, click to view] >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. [quoted text, click to view] > 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. [quoted text, click to view] >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. [quoted text, click to view] >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 viaprocedural 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. [quoted text, click to view] >SELECT foo_key FROM FoobarWHERE klugger IS NULL AND foo_key = (SELECT MIN(foo_key) FROM Foobar); [quoted text, click to view] >(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. [quoted text, click to view] >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!!!!! [quoted text, click to view] >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. [quoted text, click to view] > 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!
[quoted text, click to view]
>> 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 [quoted text, click to view] "Doug" <drmiller100@hotmail.com> wrote in message news:1138582937.168569.155440@g14g2000cwa.googlegroups.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! >
Hello,
Good discussions. [quoted text, click to view] >>ordering in an RDBMS, so "first", "next" and "last" are totally > >meaningless. [quoted text, click to view] >> Really? To me, first, next, and last do have meanings in an RDBMS, here he's right.><> assuming you have an order. >Celko can be a bit of a curmudgeon and hates to admit when he's wrong. But [quoted text, click to view] >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. [quoted text, click to view] > >You must use an ORDER BY clause on a > cursor. [quoted text, click to view] > Really? I didn't see that anywhere. I've used cursors all the time you call that cursor you cannot guarantee the order the results will be> without an order by function. > my bad. >Yes it is. You may be able to loop through the cursor set, but each time 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. [quoted text, click to view] >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.
[quoted text, click to view] "Doug" <drmiller100@hotmail.com> wrote in message news:1138582937.168569.155440@g14g2000cwa.googlegroups.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. [quoted text, click to view] > > > 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. [quoted text, click to view] > > >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. [quoted text, click to view] > 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. [quoted text, click to view] > > 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! >
I meant to put klugger in the outermost SELECT list. Arrgh!
[quoted text, click to view]
>> 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. [quoted text, click to view] >> 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. [quoted text, click to view] >> 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. [quoted text, click to view] >> 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. [quoted text, click to view] >> 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!
[quoted text, click to view] >> I've used cursors all the time without an order by function.my bad. << [quoted text, click to view] >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." [quoted text, click to view] >> Well, you could write code to tell the engine to return exactly one row back, whether it is null or not. << language. You wanted a set; an empty set is a set. You are still>I probably can, but that defeats the whole idea of a set-oriented 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. [quoted text, click to view] >> Celko, you might read a book on fundamental human communications. You have missed the most important concepts. <<[quoted text, click to view] >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. [quoted text, click to view] >My attitude is fairly obnoxious. [quoted text, click to view] > 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"
[quoted text, click to view]
>> 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 [quoted text, click to view] "--CELKO--" <jcelko212@earthlink.net> wrote in message news:1138685494.781895.163030@g14g2000cwa.googlegroups.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. >
[quoted text, click to view] "Doug" <drmiller100@hotmail.com> wrote in message news:1138637442.223978.229570@g43g2000cwa.googlegroups.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. [quoted text, click to view] > 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..) [quoted text, click to view] > > >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. [quoted text, click to view] > 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. :-) [quoted text, click to view] > > 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. :-) [quoted text, click to view] > > a goal on these groups is to help and get help. i believe he > intereferes with that. >
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
SELECT MIN(klugger)
FROM Foobar WHERE klugger IS NOT NULL won't that require a table scan unless there is an index on klugger?
[quoted text, click to view]
>We are set based. And a set in a relational database is a collection of rows with no implied order.[quoted text, click to view] >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
[quoted text, click to view]
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: [quoted text, click to view] >>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: [quoted text, click to view] >>> 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 --
[quoted text, click to view]
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. --
On Jan 31 2006, 08:43 pm, "Doug" <drmiller100@hotmail.com> wrote in
news:1138758202.017630.195220@g44g2000cwa.googlegroups.com: [quoted text, click to view] >>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. --
[quoted text, click to view]
>> 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.
[quoted text, click to view]
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! --
[quoted text, click to view]
On 31 Jan 2006 17:43:22 -0800, Doug wrote: >>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? 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. [quoted text, click to view] > >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. [quoted text, click to view] > >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. --
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***. [quoted text, click to view] > 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. [quoted text, click to view] "--CELKO--" <jcelko212@earthlink.net> wrote in message news:1138833567.598027.131260@g14g2000cwa.googlegroups.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.
[quoted text, click to view]
>> 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. [quoted text, click to view] >>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. [quoted text, click to view] >> 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. [quoted text, click to view] >> 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?
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |