Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to think in set based SQL

Author  Topic 

LaurieCox

158 Posts

Posted - 2006-10-31 : 12:43:32
Hi,

Yesterday I asked for help in what I thought was a complicated query (Want to avoid cursors ... need help with query, but obviously wasn't (evidenced by the speed in which my question was answered and a comment made by another poster in the thread).

I have figured out how the query works. I even figured out how to modify it so that the date diff was displayed on the first row instead of the second:


SELECT t1.clt_num, t1.autbeg_dte, t1.autend_dte,
isnull(datediff(day, t1.autend_dte, t2.autbeg_dte), 0)
FROM #testit t1 left join #testit t2
on t1.clt_num = t2.clt_num
and t2.autend_dte = (select min(autend_dte) from #testit x where x.clt_num = t2.clt_num and x.autend_dte > t1.autend_dte)
order by t1.clt_num, t1.autbeg_dte


What I would like to know (if possible) is what “thought process” was used to come up with this solution or do you just “intuit” it (I hope not, because I sure don’t).

I know this is a very vague question, and maybe if I get a few responses I may be able to refine it.

Also can anybody recommend any books or web pages that teach this sort of thing (not syntax … I don’t have a problem with that).

Thanks,

Laurie

X002548
Not Just a Number

15586 Posts

Posted - 2006-10-31 : 12:57:13
MMost application developers are brought on process one row/record at a time. Unless you are using a database, set methodolgy can't be applied there.

To think in terms of sets, or basically mathmatics, you need to determine what the population is that you want to affect.

For example, you want to update everyone in California with something. You could do it one person at a time, but that would take a long time, or you ocould just say, UPDATE Table SET Something = whatever WHERE STATE_CD = 'CA'

HTH



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam



Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-10-31 : 14:09:32
Experience.

I come from a row-by-row background - using ISAM back in the '70s and '80s.

Reading stuff on SQLTeam, and generally "doing it" for a long time means I'm fairly set based now, but not to the extent that folk are here. Mind you, I very rarely use a cursor, and I expect I have enough experience that when I do there probably isn't a set-based solution.

I don't know whether it will help, but considering solutions that use a table of numbers ("Tally Table") might get you onto the right wavelength. For example, the methods used to "split" a CSV into rows.

Have a look at my post of 06/05/2005 03:41:12 on http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50648. Its not my original work, and it takes some understanding, but its VERY quick compared to other processing methods.

You could also look out for articles / books by Joe Celko - he has a book called "SQL for Smarties" which has some examples that seem a weird solution-path for old sequential-programming-hackers!

Kristen
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-10-31 : 16:14:36
quote:
Originally posted by AlgaeSea

What I would like to know (if possible) is what “thought process” was used to come up with this solution or do you just “intuit” it (I hope not, because I sure don’t).

Remember High School math class?
Remember learning "Venn Diagrams"?
Remember how you thought they were totally stupid and a complete waste of time?
Well, that is what SQL programmers do in their heads all day long.

STAR SCHEMAS ARE NOT DATA WAREHOUSES!
Go to Top of Page

Sitka
Aged Yak Warrior

571 Posts

Posted - 2006-10-31 : 16:33:55
Who uses their heads or diagrams.
I need to hold up my hands like I'm holding collections of things.
I guess this is the "mits" approach.

I have a mitfull of these and I need to
change, extract, affect the few of those in that mit that are related to the ones
in the other mit. Kind of chisenbop for SQL folks.

You can't work on one thing at a time, because all the other
items in your hands would scatter all over the floor.

For the original poster, here is a great spot to practice, mix real simple examples, with real high level reading, don't get too frustrated with what you think you know or has worked in the past. There is always a battle between set based and iterative operators.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-10-31 : 17:48:24
quote:
For the original poster, here is a great spot to practice, mix real simple examples, with real high level reading
I agree with this completely, and Joe Celko's SQL For Smarties does this very effectively. Don't let the advanced topics worry you, even if you don't understand them, your brain will work its way up to them (one of my pet peeves are intro books that simplify set-based theory into nothingness).

I also agree with the physical approach: look for real-world examples that could be thought of as sets, and how you deal with them. As far as practice, make a solid effort to NEVER write a cursor again. It's not as hard as you think, and forcing yourself to avoid it makes you learn faster.
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-11-01 : 02:44:33
Asking a question like this means that you are on the right track and in the right state of mind. People come here *all the time* asking for solutions to this and that and most of them get really good answers to their questions. But I don't think very many of them actually take the time to understand what is really going on when someone like Peso or khtan or whoever post some hardcore setbased solution. The same thing happened to me a few years back (and it still happens quite frequently!) and I remember especially once it took me several weeks to grasp what someone here threw together in a matter of minutes.

Becoming a set-oriented developer doesn't come easily and there actually is a very good reason why some people have sql server development as a full time job. What made me "see the light" was when I learned the concept of derived tables and some pretty persistent people here giving me all the help I needed.

In short I can say that SQLTeam and the truly great people that reside here taught me *everything* I know about sql-server admin/development. So if you are willing to learn and are prepared to do the work you are in the best of hands

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-11-01 : 03:59:45
quote:
Yesterday I asked for help in what I thought was a complicated query (Want to avoid cursors ... need help with query, but obviously wasn't (evidenced by the speed in which my question was answered and a comment made by another poster in the thread).


Laurie one of the reasons you got a fast reply was that you provided all the information needed to help you, as mentioned in the post you refer to. All too often people don't (or won't) do that.

With regards to set based thinking - I once went to see Itzik Ben Gan. For me I found just watching him and getting an insight into the way people like him think quite inspirational.

steve

-----------

Don't worry head. The computer will do all the thinking from now on.
Go to Top of Page

Sitka
Aged Yak Warrior

571 Posts

Posted - 2006-11-01 : 15:28:33
Here is a 30000 foot view from Microsofts DLINQ project

Most programs written today manipulate data in one way
or another and often this data is stored in a relational database.
Yet there is a huge divide between modern programming languages
and databases in how they represent and manipulate information. This
impedance mismatch is visible in multiple ways. Most notable is that
programming languages access information in databases through APIs
that require queries to be specified as text strings. These queries
are significant portions of the program logic yet they are opaque to
the language, unable to benefit from compile-time verification and
design-time features like IntelliSenseTM.
Of course the differences go far deeper than that. How information is
represented, the data model, is quite different between the two.
Modern programming languages define information in the form of
objects. Relational databases use rows. Objects have unique identity
as each instance is physically different from another. Rows are
identified by primary key values. Objects have references that
identify and link instances together. Rows are left intentionally
distinct requiring related rows to be tied together loosely using
foreign keys. Objects stand alone, existing as long as they are
still referenced by another object. Rows exist as elements of tables,
vanishing as soon as they are removed.
It is no wonder that applications expected to bridge this gap are
difficult to build and maintain. It would certainly simplify the
equation to get rid of one side or the other. Yet relational
databases provide critical infrastructure for long-term storage and
query processing, and modern programming languages are indispensable
for agile development and rich computation.

Until now, it has been the job of the application developer to
resolve this mismatch in each application separately. The best
solutions so far have been elaborate database abstraction layers that
ferry the information between the application’s domain specific
object models and the tabular representation of the database,
reshaping and reformatting the data each way. Yet by obscuring the
true data source these solutions end up throwing away the most
compelling feature of relational databases; the ability for the data
to be queried.





"it's definitely useless and maybe harmful".
Go to Top of Page

samuelclay
Yak Posting Veteran

71 Posts

Posted - 2006-11-01 : 17:06:39
I want to start by apologizing if you took my comment on your previous post as an insult. I meant quite the opposite. If you have read many posts here, you will know that there are many that say the equivalent of "I am getting bad results from my query, what is my mistake?" without giving data structures, sample data, sample code, output, expected output, or error messages and want people to magically know the answer to their problems. I wasn't commenting on the simplicity of your problem but the fact that you included everything needed to get fast help..

As far as set based queries go, I'd say read lots and practice lots.. The fact that you are asking shows you are interested in learning to do something rather than just have a solution given to you. That, in itself says alot. As mentioned above, the people here are very knowledgable, helpful and patient (the patron saint goes above and beyond in the patience category). As Rob mentions, make the effort to never use cursors. You might have to step back and look at the data in different ways to see different solutions. Like everything in life, there are exceptions to the rule, but problems with cursors as the "only solution" are few and far between.

practice, practice, practice (and come here often)...
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2006-11-01 : 17:12:34
Thinking in sets is a natural instinct...

Imagine if you had 1000 paper files in a cabinet..
Now imagine that you have to move those 1000 papers to another cabinet.

Only a moron would choose to move them one at a time...

DavidM

Production is just another testing cycle
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-11-02 : 01:50:19
Heh byrmol...that is actually a quite cool example...never thought of it that way

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-02 : 01:58:38
Extend that thinking process with "move all paper files from cabinet a to cabinet b where last name begins with s".
Do you find the first paper with s and move that to other cabinet, and look for next s and move that?
Or do you find all paper with s before you move all at one time?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-11-02 : 02:18:20
Wow...this gives a whole new perspective to explaining stuff! Hehe, explaining a clustered index will be a bliss from now on: "...imagine a book with 1000 pages, but all the pages are in random order..."

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-11-02 : 07:41:50
Actually that's not a good example for a clustered index, it will reinforce the idea that you must always have a surrogate primary key and cluster on it. A better example is the phone book: the clustered key is last name, first name, and the primary key is phone number. It's a good example of when to break the clustered primary surrogate key model. It also has structured data, fits x number of rows on a page, and arranges pages in a specific order. You can also easily illustrate range selection, seeks, and B-tree navigation.

Once they get that then you can better illustrate the random page numbers. Although none of these are really set-based concepts, but they help model the data storage better.
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2006-11-02 : 07:50:11
AlgaeSea: here's a great example you can explore in your set-based meditation:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74383
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2006-11-02 : 07:54:56
AlgaeSea: Marks will be awarded for the extent to which your solution is Set Based
Go to Top of Page

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2006-11-02 : 07:58:46
I agree totally with what is posted above. It takes training. Not MCDBA training ... not Books Online training. One of the best posters around here (missing for several years now in the cranberry fields of Wisconsin) went by the handle "setbasedisthetruepath" ... another awesome poster is "Arnold Fribble" ... go back through these forums and read every one of either of their posts. Amazing stuff.

One suggestion I'd like to add (it is obvious) ... whenever you find yourself typing declare cursor or creating a while loop, this should set off a red flag that causes you to stop and try to consider a set based approach. Whenever you find yourself writing a batch with 3 update statements to the same table in a row ... figure out how to consolidate. Get good at testing different ways to write the same query in terms of execution time and plan evaluation.

Another suggestion ... challenge yourself to understand the fundamentals of database systems. Celko's Smarties book are very interesting, but for the real deal, pick up C.J. Date's Introduction to Database Systems. It may take a year to get through Date's book and another 6 months to get through it a second time ... but if you do, you will be light years ahead of the pack in terms of a thinking database designer/programmer.

Jay White
Go to Top of Page

LaurieCox

158 Posts

Posted - 2006-11-02 : 08:27:12
Wow ... lots of stuff to read. I want to respond in more detail, but I have real work to do ... Will come back with a more detailed response later

Thanks,

Laurie
Go to Top of Page
   

- Advertisement -