sql server (alternate): Could someone help me by answering the questions below?

What's a cursor?
What's difference between Query and View?
Is a RecordSet just part of a table? Can it be part of a query of view?
If the content in a table changed, is it necessary for a old recordset to
renew itself by do "Requery()"?

Thanks for your help!

sci (sci998@yahoo.com) writes:
[quoted text, click to view]

As long as we are talking pure SQL only, a cursor is a means to traverse
a result set one row at a time. This is usually a much slower means of
operation, than to process all rows at once in a set-based statement.
But occasionally logic is such that writing a set-based statement is
very complex.

But when you involve clients, the concept of a "cursor" gets a new
meaning, and it confused me for a long time too.

In a client language like Visual Basic, you must process rows (or records
as they usually once they've reach the client) one by one. But this alright,
because at this point all data is in memory.

With a client-side cursor, ADO gets all data to the client, and all
iteration is done there. A server-side part of the iteration is
carried out on the server. This is usually slower and takes up more
resources, but it depends on the kind of cursor. A forward-only
read-only server-side cursor does not really have a cursor on the SQL
side, whereas a updatable keyset cursor has.

[quoted text, click to view]

You could say that a view is a pre-packaged query. A query is something
like SELECT * FROM x WHERE col = 3. "x" here could be a plain table, or
a view.

In fact what the query returns is yet another table, although it is not
materialized as such. Normally though, you call the result of a query
for a result set.

[quoted text, click to view]

The recordset is just the result-set that has traversed to the client
and ADO. The recordset is the result of a query, and it can be a one-to-one
mapping to a table or a view, but it could be any query.

[quoted text, click to view]

It might be a good idea, but it depends on the logic of the application.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at

[quoted text, click to view]

Thanks for your explanation! This is very helpful for my learning on this
subject.

While reading your post I still have some other questions. Sorry for my
ignorance.

What's "result set"?
What's "set-based statement"?
Why "set-based statement" is faster than cursor?

[quoted text, click to view]
Since we call a view a pre-packaged query, does this mean that we can
constructed a view and save it in a database for it to be used in a query?
Can a view be either constructed from one table or multiple tables? Is a
view always to be used in a query, or can it be used somewhere else?

[quoted text, click to view]

Its how we refer to the results of a SELECT statement. eg. SELECT name
FROM employee WHERE name like 'A%';

this will give up all the rows in the table with names starting with
A. This collection of rows is the result set.
[quoted text, click to view]

Essentially what I showed above. The table called employees has many
names in it. My statement works on only those starting with A, or the
SET of rows meeting this condition.
[quoted text, click to view]

Because that's what a relational database is designed for.

Tables are indexed, which makes finding records fast. Lets assume that
there are 1000 employees of whom 15 start with A. A cursor is going to
go through the whole table reading every row, while the SQL way is to
use a WHERE clause. Assuming that the name column is indexed SQL
server will be able to find all the names starting with A very
quickly. Assume there is a salary column we want to update by a %age.

UPDATE employee SET salary = salary *1.15 WHERE name LIKE 'A%'

The server uses the index to find the bunch of rows who start with A
and performs the update to only those rows.
[quoted text, click to view]