SQL "VIEW" Statement

[dhstein]

MY SQL knowledge is based on Access. I'm reading a book on SQL now where
they discuss the "VIEW" statement. Is this equivalent to a query or is there
something more to it? Thanks for any help on this.

 

[Albert D. Kallal]

MY SQL knowledge is based on Access. I'm reading a book on SQL now where
they discuss the "VIEW" statement. Is this equivalent to a query or is
there
something more to it? Thanks for any help on this.

Yes, they are VERY similar. A view in sql server is some saved sql. And, in
ms-access we can save a query and use that in place of table.

The only real difference here is that in ms-access we can save sql that
updates tables (update/delete quires etc). These types of quires are often
called action quires (action queries don't return records...but they update
or delete data). In sql server, that saved sql is a view,but the sql can't
update data or delete data.

So, to further split hairs, a saved "select" query in ms-access is really
very much the same concept and idea a saved select query in sql server. in
fact in sql server the saved quires are going to be select quires. So, a
select query in ms-access can be considered a view. In fact, for JET4 you
execute a create view command in ms-access....it creates a select query...

So, in ms-access if you hit ctrl-g to get to the debug window and type in:

currentproject.Connection.Execute "create view MyView as select Company from
tblCustomers"

After you execute the above you see a standard query called MyView that is a
select statement. So, even ms-access does support the create view command.
The above command does NOT work if you use the built-in dao connection
UNLESS sql ansi is turned on. so:

currentdb.Execute "create view MyView as select Company from tblCustomers"

Will NOT work unless sql ANSI compatibility is turned on. However, using the
built ADO (currentproject.Connection) object does work regardless if sql
ANSI compatibility is turned on or not...

 

[Tom Wickerath]

A view is saved in the SQL Server (or Oracle) database, and is run on the
data directly, as opposed to a query that is saved and run from within an
Access FE application. You can link to a view just like you would link to a
table.

Here are a few direct quotes from the book "Beginning SQL Server 2005
Express for Developers (from Novice to Professional)", written by Robin
Dewson (published by APress):

Page 268
"If you are familiar with MS Access, views are similar to Access queries."

and

"A view actually stores the query that creates it, and when you execute the
view, the underlying query is the code that is being executed."


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[dhstein]

Thanks Albert and Tom for your responses. That clears it up.

David

 

[David W. Fenton]

Good post, Albert.

Yes, they are VERY similar. A view in sql server is some saved
sql. And, in ms-access we can save a query and use that in place
of table.

One other difference is that you can link to a server-side view, but
you can't link to a saved QueryDef in another Access database. This
limiation can be gotten round by using IN 'C:\MyOther.mdb' in the
FROM clause in a query.

The only real difference here is that in ms-access we can save sql
that updates tables (update/delete quires etc). These types of
quires are often called action quires (action queries don't return
records...but they update or delete data). In sql server, that
saved sql is a view,but the sql can't update data or delete data.

I think a mention of stored procedures would be appropriate here.

In Access, you have saved querydefs, regular queries and action
queries.

In server databases, a regular non-action query is called a VIEW.

Action queries can be saved as stored procedures.

In addition to being like a saved action query, a stored procedure
can also include procedural code and branching logic, something that
is not available in Jet/Access in any form.

[]

So, in ms-access if you hit ctrl-g to get to the debug window and
type in:

currentproject.Connection.Execute "create view MyView as select
Company from tblCustomers"

After you execute the above you see a standard query called MyView
that is a select statement. So, even ms-access does support the
create view command. The above command does NOT work if you use
the built-in dao connection UNLESS sql ansi is turned on. so:

currentdb.Execute "create view MyView as select Company from
tblCustomers"

Will NOT work unless sql ANSI compatibility is turned on. However,
using the built ADO (currentproject.Connection) object does work
regardless if sql ANSI compatibility is turned on or not..

This is to be expected, as CurrentProject.Connection returns an
object of type ADO.Connection, so it should stand to reason that any
SQL you pass to it its Execute method would run under ADO's rules.

By using a "CREATE VIEW" SQL statement, you're getting into the
other kind of SQL, DDL (data definition language). There are three
kinds of SQL:

DDL -- data definition language, for creating and altering the
structure of tables, views and stored procedures.

DML -- SQL statements that alter data, INSERT, UPDATE, DELETE.

SELECT -- SQL statements that return data from tables.

In Access, these distinctions are elided and everything is just a
query.

Also, historically the Access/Jet documentation has downplayed the
use of DDL in favor of DAO. Jet/ACE DDL is not fully compatible with
many other SQL dialects, and not all features of Jet/ACE
tables/fields are manipulable through Jet/ACE DDL.

I expect this is something that is going to change, as well as that
the historical SQL dialect that is based on a snapshot of SQL 89
with interesting additions will probably be replaced sometime soon
as the Access team gets up to speed on managing their own private
version of Jet/ACE. That is, I would expect alterations to Jet/ACE
SQL to make it more compatible with common SQL dialects.

I expect this to be implemented the way MS introduces any such
migration from one set of standards to another, in a manner that is
backwardly compatible. My guess is the so-called SQL 92 mode will be
further extended for even greater compatibility and eventually take
over as the default.

This is not a big deal when you look at the big picture, and my
guess is that it will be implemented in a way that will not cause
incompatibilities. Access developers have been dealing some of these
issues (e.g., wildcards) since ADO, and anyone who has written
passthrough queries has had to address it. Making Jet/ACE SQL more
compatible will be a good thing, as long as there is no sacrifice of
the marvelous little idiosyncracies that it offers that are not
found in all SQL dialects (to list a few, TOP N, TRANSFORM,
DISTINCTROW),

 

[dhstein]

Thanks David

Good post, Albert.

Yes, they are VERY similar. A view in sql server is some saved
sql. And, in ms-access we can save a query and use that in place
of table.

One other difference is that you can link to a server-side view, but
you can't link to a saved QueryDef in another Access database. This
limiation can be gotten round by using IN 'C:\MyOther.mdb' in the
FROM clause in a query.

The only real difference here is that in ms-access we can save sql
that updates tables (update/delete quires etc). These types of
quires are often called action quires (action queries don't return
records...but they update or delete data). In sql server, that
saved sql is a view,but the sql can't update data or delete data.

I think a mention of stored procedures would be appropriate here.

In Access, you have saved querydefs, regular queries and action
queries.

In server databases, a regular non-action query is called a VIEW.

Action queries can be saved as stored procedures.

In addition to being like a saved action query, a stored procedure
can also include procedural code and branching logic, something that
is not available in Jet/Access in any form.

[]

So, in ms-access if you hit ctrl-g to get to the debug window and
type in:

currentproject.Connection.Execute "create view MyView as select
Company from tblCustomers"

After you execute the above you see a standard query called MyView
that is a select statement. So, even ms-access does support the
create view command. The above command does NOT work if you use
the built-in dao connection UNLESS sql ansi is turned on. so:

currentdb.Execute "create view MyView as select Company from
tblCustomers"

Will NOT work unless sql ANSI compatibility is turned on. However,
using the built ADO (currentproject.Connection) object does work
regardless if sql ANSI compatibility is turned on or not..

This is to be expected, as CurrentProject.Connection returns an
object of type ADO.Connection, so it should stand to reason that any
SQL you pass to it its Execute method would run under ADO's rules.

By using a "CREATE VIEW" SQL statement, you're getting into the
other kind of SQL, DDL (data definition language). There are three
kinds of SQL:

DDL -- data definition language, for creating and altering the
structure of tables, views and stored procedures.

DML -- SQL statements that alter data, INSERT, UPDATE, DELETE.

SELECT -- SQL statements that return data from tables.

In Access, these distinctions are elided and everything is just a
query.

Also, historically the Access/Jet documentation has downplayed the
use of DDL in favor of DAO. Jet/ACE DDL is not fully compatible with
many other SQL dialects, and not all features of Jet/ACE
tables/fields are manipulable through Jet/ACE DDL.

I expect this is something that is going to change, as well as that
the historical SQL dialect that is based on a snapshot of SQL 89
with interesting additions will probably be replaced sometime soon
as the Access team gets up to speed on managing their own private
version of Jet/ACE. That is, I would expect alterations to Jet/ACE
SQL to make it more compatible with common SQL dialects.

I expect this to be implemented the way MS introduces any such
migration from one set of standards to another, in a manner that is
backwardly compatible. My guess is the so-called SQL 92 mode will be
further extended for even greater compatibility and eventually take
over as the default.

This is not a big deal when you look at the big picture, and my
guess is that it will be implemented in a way that will not cause
incompatibilities. Access developers have been dealing some of these
issues (e.g., wildcards) since ADO, and anyone who has written
passthrough queries has had to address it. Making Jet/ACE SQL more
compatible will be a good thing, as long as there is no sacrifice of
the marvelous little idiosyncracies that it offers that are not
found in all SQL dialects (to list a few, TOP N, TRANSFORM,
DISTINCTROW),