| Author |
Topic |
|
Jusvistin
Yak Posting Veteran
81 Posts |
 Posted - 2003-08-07 : 13:00:25
|
| Hello,When I join two tables, what does SQL Server actually do ?Does it assemble a virtual table in memory combining the contents of both tables ?Kevin |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-07 : 13:06:09
|
| It depends on the query. The best way to find out is to display the execution plan in Query Analyzer. It will show you how it joins tables together and whether or not it's using indexes. There may be some use of worktables (actual tables used behind-the-scenes) but it's really not important how the optimizer utilizes them.SQL is designed as a declarative language, unlike VB/C++/Java/.Net languages which are procedural in nature. You describe the query results you want using SQL syntax, and the database software goes out and gets it for you. The internals are hidden and not necessary for you to know...which is a good thing really, as they can differ a lot from one product to the next (or even one release to the next) |
 |
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-08-07 : 13:07:00
|
quote:
Does it assemble a virtual table in memory combining the contents of both tables ?
The answer is yes. I could not tell you how it does it but yes, you can think of the information as being in a virtual table. What the table contains naturally depends on the type of join you use.This is making me look like a fool. ----------------Shadow to Light |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-08-07 : 13:08:57
|
| The whole point of a relational database is that you shouldn't know. The database will perform the join in the most efficient way possible to produce the correct result (well sometimes).It may use a work table but it will depend on the actual query.You can look at the query plan in query analyser which will give a clue as to what is happenning.It can be thought of as creating a cartesian product of the two tables then removing the tuples that don't satisfy the join condition but the server is very unlikely to do that.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-08-07 : 13:09:32
|
quote:
Originally posted by robvolkunlike VB/C++/Java/.Net
Hence the reason SQL Server developers get a lot less money.----------------Shadow to Light |
|
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-07 : 13:10:06
|
| Thanks Rob,I've looked at the execution plans a bit, but the meaning behind all the symbols are not entirely clear to me yet. I do know that a table scan is bad though !Is BOL the best place go to learn about all those symbols and what they actually mean in the physical world ?Kevin |
|
|
|
Amethystium
Aged Yak Warrior
701 Posts |
Posted - 2003-08-07 : 13:20:26
|
quote:
Is BOL the best place go to learn about all those symbols and what they actually mean in the physical world ?
Kevin,You sound very hungry for information and I salute you for it.Search for Execution Plan pane in BOL and click on the link Graphically Displaying the Execution Plan Using SQL Server Query AnalyzerThis will tell you what the little pretty icons mean.Oh, and careful what you say on these forums. People get a bit funny when asked too many simple questions by the same person. I can't put my finger on it but perhaps it is the stress of being in a an office environment when answering questions. (I assume people access this from work at lunch times or after work, like me now  ). .... it's 6.30 p.m. time to shoot off!----------------Shadow to Light |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2003-08-07 : 19:24:58
|
quote:
Oh, and careful what you say on these forums. People get a bit funny when asked too many simple questions by the same person.
I'd tend to disagree with that actually. Some of SQLTeam's best forum contributers started here as total newbies asking a lots of questions. When people get helped, sometimes they stick around and help share their newfound knowledge.The only people that get a bad reaction for asking questions are the ones who are rude or expect free support when they are not prepared to put any effort in themselves. Oh, and people that want their homework done.Kevin, welcome to SQL Team, I hope you enjoy your stay.Damian |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2003-08-08 : 07:10:43
|
The beauty and fun of database work, IMHO, is that usually, there is nothing that is always true.In most procedural languages, you can figure out the best way to perform a particular task and do it that way, always, and never really think about it again.With database work, just about everything depends ....quote:
Originally posted by JusvistinI do know that a table scan is bad though !
That is kinda like saying dynamic sql, cursors and dts are bad. True, in many cases these things are not the best choice, but in some cases they are.When it comes to tuning queries (and I'm guess that is what you are getting at here), it really is as much art and experience as it is science. All the pieces of your data tier are (should probably be) tied together. Adding a covering index here to support this query will likely slow down the INSERT proc over there. So many things to consider.My point: if you are interested in performance tuning, don't start at the atomic level (how SQL performs a hash join on the bit level), rather start at the atmospheric level. Learn normalization and physical server tuning first. Then work you way down to indexing for query tuning and different query writting tricks last. Adding an index to a table in a junk design is simply throwing good money after bad...Jay White{0} |
|
|
|
Jusvistin
Yak Posting Veteran
81 Posts |
Posted - 2003-08-08 : 12:37:08
|
| I asked the question initially for a pretty basic reason, I'm interested in knowing what goes on behind the curtain. Without having a better idea of how/why these SQL statements do what they do, it almost seems like I'm waving a magic wand and doing a little chant in order to get SQL to do my bidding. My initial assesment of how many SQL solutions are created is/was - YUCK, looks a lot like PERL. Looking at some of the code I've seen makes my (average Joe) head hurt. Without knowing the mechanics of how a nested query, correlated query or join does it's magic makes it pretty hard for me to be confident in using them.Most of the material I've come across so far deals with very basic issues - just do this, and this will happen. But when one starts to do anything moderately complicated, things become fuzzy to me.That was the real source of my question.I'm sure the fog will clear, once I have a little more experience under my belt.Thanks,Kevin |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-08-08 : 12:48:46
|
quote:
I asked the question initially for a pretty basic reason, I'm interested in knowing what goes on behind the curtain
Exactly right! You're not telling the computer HOW to do something, you're telling it WHAT to do, or more accurately, WHAT you want. There is a difference even if the semantics don't appear very clear right now.quote:
I'm sure the fog will clear, once I have a little more experience under my belt.
Also exactly right. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2003-08-08 : 13:15:42
|
quote:
Originally posted by MerkinOh, and people that want their homework done.Kevin, welcome to SQL Team, I hope you enjoy your stay.Damian
Notice since schools been out we don't get the same (over and over) question...it's like we know the final exam projects for a dozen schools...And yes Kevin, welcome, and ask as many questions as you like...[EDIT]quote:
With database work, just about everything depends ....quote:--------------------------------------------------------------------------------Originally posted by JusvistinI do know that a table scan is bad though !--------------------------------------------------------------------------------That is kinda like saying dynamic sql, cursors and dts are bad. True, in many cases these things are not the best choice, but in some cases they are.
I love that answer..."It Depends"As for a table scan...let's say you have a know quatity of something..let's say you have a list of people, and you need to get phone numbers...the list is loaded to a table..you join that table to the phonebook table...that table with the people in is going to scan...no matter what...it's a driver...it happens...Any, welcome again[/EDIT]Brett8-)SELECT POST=NewId() |
|
|
|
|
Can someone explain a JOIN more fully ?