Cursor, Query, View and Recordset

[sci]

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!

[Erland Sommarskog]

Re: Cursor, Query, View and Recordset

sci (sci998@yahoo.c om) writes:[color=blue]
> What's a cursor?[/color]

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.
[color=blue]
> What's difference between Query and View?[/color]

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.
[color=blue]
> Is a RecordSet just part of a table? Can it be part of a query of view?[/color]

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.
[color=blue]
> If the content in a table changed, is it necessary for a old recordset to
> renew itself by do "Requery()" ?[/color]

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

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[sci]

Re: Cursor, Query, View and Recordset

Erland Sommarskog <sommar@algonet .se> wrote in message
news:Xns9405E8D 1BFD0DYazorman@ 127.0.0.1...[color=blue]
> sci (sci998@yahoo.c om) writes:[color=green]
> > What's a cursor?[/color]
>
> 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.[/color]

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?
[color=blue]
>[color=green]
> > What's difference between Query and View?[/color]
>
> 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.
>[/color]
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?

[Lyndon Hills]

Re: Cursor, Query, View and Recordset

On Tue, 30 Sep 2003 05:13:22 GMT, "sci" <sci998@yahoo.c om> wrote:
[color=blue]
>Erland Sommarskog <sommar@algonet .se> wrote in message
>news:Xns9405E8 D1BFD0DYazorman @127.0.0.1...[color=green]
>> sci (sci998@yahoo.c om) writes:[color=darkred]
>> > What's a cursor?[/color]
>>
>> 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.[/color]
>
>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"?[/color]

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.[color=blue]
>What's "set-based statement"?[/color]

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.[color=blue]
>Why "set-based statement" is faster than cursor?[/color]

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.[color=blue]
>[color=green]
>>[color=darkred]
>> > What's difference between Query and View?[/color]
>>
>> 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.
>>[/color]
>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?
>[/color]

[Erland Sommarskog]

Re: Cursor, Query, View and Recordset

sci (sci998@yahoo.c om) writes:[color=blue]
> What's "result set"?[/color]

Lyndon Hills has already explained this, but permit me to elaborate.
The theory behind relational databases are based on set theory. A table
is a unordered set of data. Thus, the result of a query is also a
table in the logical sense, or a set if you like.
[color=blue]
> What's "set-based statement"?[/color]

A statement which operates on many rows at a time.
[color=blue]
> Why "set-based statement" is faster than cursor?[/color]

I like to supplement Lyndon's answer. He said that tables are fast
because they are indexed, and that is true. However, set-based
statements are faster even if there is no useful index. Say that
we want increaese the salary for all employees with 2%. This can
be done with:

UPDATE employees SET salary = salary * 1.02

The alternative to a set-based statement would be to set up a cursor
and update one row at a time. This would be a lot slower, because there
is a certain overhead to locate a row.

And, as Lyndon so well said: because set-based is what relational
engines are designed for.
[color=blue]
> 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?[/color]
A view can be a query that includes many tables - or other views for
that matter. And, yes, a view can be saved in the database for later
queries.
[color=blue]
> Is a view always to be used in a query, or can it be used somewhere else?[/color]

About anything in a database is being used in a query one way or another.
You don't put data in a database, unless you intended to query it in
some way or another.

Normally, you construct views to give users easier access to data. Not
all systems have views, though. The system I work with does not, for
instance. This is because our users access the database from a GUI,
and do not access the database directly.


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

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp