Basic q

Hi,

Say if I have query:
"select x,y,z from tables a, b, c where....."

Does that mean all records in three tables a,b,c must be loaded and stored in the memory of server first before the query is executed? If so, as time goes on, more records will be added to these tables, then not enough memory in server machine to store these data, so I have to buy more RAM?

Thanks,
John

no idon't think so.


I've seen that kind of queries used in a database with 100000+ records and they dont seem to load them on to memory everytime they use select statement.

Infact, I don't think they even put tables on to the memory. that's why there is a thing called index.

In 'SQL in simple English part 2/2', says 'The moment you ask a query to select from more than one table, a temporary table is created which has all possible combinations of the contents of the 2 tables'

Not sure how can it be handled if not in memory, it may use hard disk space as swap memory perhaps?

Johnn,

unfortunately, I can not confirm that for you. To me creating a temporary table and copying all the records in it to the temp table is very inefficient way of getting to the data. Imaging a ecommerce site with 10000000 visitors querying the product catalogue..

IMHO, I think the author tried to put it in a simpler words..making it eaiser to understand.

In query analyzer, it is possible to view a thing called action plan, which shows how the sql server engine is going to get the data.

If you do a select query on a table without an index, it looks at each record and go through them individually. Not copy it to a temporary table and then go through them for each query.

HTH...