[dba-SQLServer] Difference between views and queries

jwcolby jwcolby at colbyconsulting.com
Sun Jun 27 19:08:16 CDT 2004


> 1. Working with unlimited licenses and bound forms are not always an
option. Try sixty plus users and a SQL server with a dozen connection
license. Not every client can afford $10,000+, but $1,200.00+ is an easy
swallow.

How much did they pay to develop unbound forms and get it all working.  All
that "bound stuff" that just happens has to be recreated by someone!

John W. Colby 
www.ColbyConsulting.com

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence (AccessD)
Sent: Sunday, June 27, 2004 1:28 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Difference between views and queries


Hi Arthur:

Can not let you get in the last word. Please find my comments inline.

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Arthur
Fuller
Sent: Thursday, June 24, 2004 1:18 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Difference between views and queries


We have serious differences of opinion here, Jim.

1. I have experimented with bound and unbound forms against SQL Server and I
firmly dispute MS's recommendation that bound forms should not be used. I
have several "major" apps running using bound forms and we're experiencing
no problems at all.

<comment>
...and I have found no problems with unbound forms. In a stable environment
both techniques should be very 'problem free'. </comment>

2. In a SQL environment, the concept "bound" is not quite the same as in
Access. For example, I could have a form bound to a multi-table view (which
is therefore not updateable), but that simply means that I write some code
to perform the multi-table updates. It's still a bound form.

<comment>
...with unbound forms this is never an issues as you are handling all the
access data anyways. </comment>

3. I have complete control over every piece of data displayed in a bound
form. All I need to to is create the underlying view/sproc/UDF which for
example retrieves all the relevant fields from all the related tables, then
present them. This is a no-brainer once you understand how Access will deal
with it. You can create a very complex view and then AutoForm it, and then
add the update/insert code you need. Not as simple as a one-table bound form
talking to an Access table, but almost as simple.

<comment>
...I agree, that is a no-brainer.

But there are also other ways of retrieving and handling data, especially
within a 'unbound' format, that can not be fully taken advantage of within a
'bound' environment. Given one example, you can optimize data retrieval by
simply assembling a dynamic recordset list of various fields, that would be
used to link or find a specific form record. Once a form record is selected
from this list, the entire form can then be populated. This methodology can
be highly tuned to the given data set and to client's requirements.
</comment>

4. Before concluding that the methods above were better than the
MS-recommended strategy, I did some benchmarks. I used a class-based
approach (i.e. a class with Get and Put methods). The performance difference
was significant. Once I saw this difference, I decided to put my head
against the grinder and deal with the issues of bound forms. They're not
perfect. Nothing is. But IMO they are WAY better than unbound forms. It all
depends on what you bind them to.

<comment>
...My experience with connecting to SQL server with unbound forms did not
result in the same experience you had. On the other hand, I have not used
the 'Get' and 'Put' methods in any design. (Have you tried using the 'Getex'
and 'Putex' methods which always retrieve values in an array?) I have just
used the 'Insert' and 'Update' methods or passed the variable through
parameters and just allowed a SP to take care of multiple tables. Are you
using ODBC? I once used an ODBC connection to a SQL server and found it was
a dog. Direct ADO-OLE is more appropriate. I have found when attaching new
users performance remain consistent and flat.

Some of the areas and reasons for using unbound forms, you have not
mentioned. I assume you have only been in offices where there is unlimited
connection licenses and with a very stable and simple LAN environments.

1. Working with unlimited licenses and bound forms are not always an option.
Try sixty plus users and a SQL server with a dozen connection license. Not
every client can afford $10,000+, but $1,200.00+ is an easy swallow.

2. Mixed environments that are using Web and Desktop forms or multiple
databases or multiple database types say like SQL and Oracle, are relatively
easy to setup. I could not imagine the same (relative) ease of design in
bound forms.

In conclusion, I agree that you can use bound forms, it is probably more
simple to implement and it can be very functional but when it comes to
flexibility regarding licenses, data handling, data sources and data
delivery, unbound forms are unparalleled. Do not get me wrong. I do not
believe that bound forms do not have their place. Their implementation is
quicker for small contracts but within large complex environments unbound
forms, IMHO, are faster in creation and performance. </comment>

Arthur

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim
Lawrence (AccessD)
Sent: Friday, June 11, 2004 2:08 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Difference between views and queries


Joe:

You should know better than ask that question. It sort of stirs up things.
The following are my personnel guide-lines but they can be changed but there
has to be a very good reason.

1. Bound forms can be used on small Access projects but never on anything
major.

There are a host of pluses and minus on both side.

Facts as I see them:

Bound forms are much easier to implement. The existing operating system
handles access to records, locking, updates while multiple people are
working on a single record, populating the forms, sub-forms etc. When there
are a small number of people accessing a simple MDB database, for example,
performance can be superior.

Unbound forms are harder to implement...properly. All the above benefits,
you as the programmer, must provide. It takes longer to implement and test.
There are a whole range of programming issues that have to be addressed. It
is not for the faint-of-heart or first time developers.

On the plus side of unbound forms, is that you now have complete control
over every piece of data that is displayed on the form or stored in the
database.

1. You can provide an extensive set of business rules, on just a single
field, if required. 2. Now that you control the data-access layer, multiple
data sources can be accessed and easily integrated. For example; you can be
using a SQL, Oracle and MDB data sources simultaneously. 3. Data access can
be merged deep within your code, so as to provide a layer of security. (That
is one of the reasons I do not like ODBC connection because they can be
easily used by any skilled client, to gain direct access to protected data
sources and because they are exposed and not imbedded in your code.) 4. Much
of the issues around record-locking, multi-user and the potential data
over-writing are managed through the bigger data engines like SQL and
Oracle. It is your responsibility to use proper transaction controls,
monitor return codes and take appropriate actions. 5. With you now managing
the data connections, remote sites with unstable or slow access, can be
carefully handled, with virtual no data loss or database corruption. 6.
Because you now precisely handle data access, only the specific data sets
required, to populate the current form and sub-forms, are extracted. Static
data for controls can be pulled once at the beginning of a session and not
as each record is accessed. When it comes to the major sequel databases, the
raw data can be extracted, at the server end through, Stored Procedure and
functions calls. To provide better performance the raw data can then, at the
client side, be grouped and sorted which in some cases will provide up to a
seventy percent performance boost. (Distributive computing). 7. Complete
access control of the data can leverage a small SQL system. For example;
given a SQL DB, only licensed with a dozen connections, sixty plus people
can have, as far as they are concerned, full access. More bang for the buck.

In summary, the downside is that it requires a lot more work to implement an
unbound database, internal documentation and structured code lay-out has to
be very carefully done. I can not stress tidiness and organization enough.
Consider someone who has to go back in to update the code, that someone
might be you.

The upside, is that you, as a programmer, have absolute control, garner
superior performance (on larger systems), better stability and much better
security.

This is my quarter's worth, thank you
Jim


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Joe Rojas
Sent: Friday, June 11, 2004 5:38 AM
To: 'dba-sqlserver at databaseadvisors.com'
Subject: RE: [dba-SQLServer] Difference between views and queries


Hi Jim,

What is the argument for not using bound forms?

JR

-----Original Message-----
From: Jim Lawrence (AccessD) [mailto:accessd at shaw.ca]
Sent: Thursday, June 10, 2004 6:46 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Difference between views and queries

Hi All:

As to all these issues I would like to dump some of my own comments. At the
risk of saying 'I told you so', I firmly believe that users should:

1. Always (or should I say only), use windows authentication and judiciously
distribute security access, to your SQLxx, to only those who need it.
Appropriate passwords and time limits. 1. Never allow access to the data
except through your program. That means NO to ODBC drivers only ADO-OLE. 2.
In 99% of cases, control is handled through SPs. 3. And the biggy never,
never use bound forms to access SQLxx data. I have been whining about that
for years, a position that M$ has also fully supported. 4. You, as the
programmer, should design your program to carefully validate all requests
and data before they are posted.

I may be already talking to the converted so you can ignore this; if
not..Get on the program.

Seeing we at in the midst of an election campaign, a good stump, political
speech is the expected. Jim


-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Francisco H
Tapia
Sent: Thursday, June 10, 2004 1:55 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Difference between views and queries


Andy,
  SQL Server is not Access on steriods, it's a diffrent engine, and thus
requires a new level of thinking towards your database engine.  It's NOT
that you couldn't, you can do anything, heck if you wanted to you can set up
your SQL Server with a blank SA password or SA as the password. For all that
matters, and avoiding all SQL Server authentication, just use NT
authentication and enable guest users :).

There have been quite a few white papers circulating on why you "wouldn't"
use dynamic sql w/ your sql server (check out www.sqlservercentral.com, a
fine resource) but the 2 very critical factors include performance and also
quite possible damage to your data.

It is entirely possible for your sql statement to read in this manner,
lets's say that you are Selecting Data from a table and you have a combobox
to help choose data, so your SQL Statment looks like this:

"Select CompanyName, CompanyAttribute1, pKey FROM tblCompany WHERE
CompanyAttribute1 = '" & me.cboMyBOX & "'", If I was a malicious user on
your system and I have direct access to tables, and if you're doing
statements like the above it is entirely plausible that you also have
"INSERT" statements thus you are giving more than just simple SELECT access
to these tables., thus with some malformed selection I can add this in the
select

'; DELETE FROM tblCompany; SELECT '

your final statement would look like this

Select CompanyName, CompanyAttribute1, pKey FROM tblCompany WHERE
CompanyAttribute1 = ''; DELETE FROM tblCompany; SELECT ''

Now your entire COMPANY table has been wiped out, while it is completely
possible to restore your db up to the minute, you've still lost some
downtime given that you had ONE bad apple in the bunch.

Besides the sql injection threats, you also suffer from a NON-pre-compiled
statement, thus your data could conceptually be returned a lot faster if you
let it, simply by creating a view or stored procedure.  By the way just
because you are using a stored procedure does not make you completly excempt
of sql injections, if you are using dynamic sql within that procedure you
are still open to these kind of attacks and your stored procedure is always
re-comiled and thus suffers from the same performance deficits.


Andy Lacey wrote On 6/10/2004 1:27 PM:

>But, Francisco, if I was porting to SQL Server my Access app which 
>builds SELECT statements dynamically all of the time for many and 
>various situations are you saying I couldn't, or shouldn't or 
>something?
>
>-- Andy Lacey
>http://www.minstersystems.co.uk
>
>
>
>>-----Original Message-----
>>From: dba-sqlserver-bounces at databaseadvisors.com
>>[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of 
>>Francisco H Tapia
>>Sent: 10 June 2004 20:58
>>To: dba-sqlserver at databaseadvisors.com
>>Subject: Re: [dba-SQLServer] Difference between views and queries
>>
>>
>>jwcolby wrote On 6/10/2004 9:33 AM:
>>
>>
>>
>>>Can anyone explain the difference between a view and a query?  Views 
>>>use a query, plus the view keyword.  I have a couple of books that I 
>>>have read the chapter on Views, but I so far haven't managed
>>>
>>>
>>to "get"
>>
>>
>>>why you wouldn't just use the query itself instead of
>>>
>>>
>>turning it into a
>>
>>
>>>view.
>>>
>>>
>>>
>>>
>>A query is a request for an Access Database, however for Sql Server 
>>you would either use a View or Stored Procedure to return the data you 
>>wanted... you are also able to use dynamic SQL to retrieve the 
>>information you need.  ANY request given to the SQL Server engine is 
>>managed by the engine, unless you are running Remote servers (iirc).
>>
>>In Sql Server, it is TABOO, nay, GENERALLY bad practice to use dynamic 
>>sql because of the implication of SQL INJECTION attacks, this poses a 
>>"real" security threat to your database. and your server.
>>
>>another reason to use a VIEW over dynamic sql is that it is 
>>pre-optimized by the SQL Server Engine and thus runs faster and more 
>>efficient.  Additionally if you use Dynamic SQL then your individual 
>>users who access the server will need EXPLICIT "SELECT" permissions by 
>>you, which is another 'bad' practice.  In SQL Server you make data 
>>available to your users via VIEWs and Stored Procedures or some other 
>>secure way in order to protect your tables and it's data.
>>
>>ya get wot I mean?
>>
>>--
>>-Francisco
>>
>>
>>_______________________________________________
>>dba-SQLServer mailing list
>>dba-SQLServer at databaseadvisors.com
>>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>>http://www.databaseadvisors.com
>>
>>
>>
>>
>>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>
>
>
>


--
-Francisco


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com



This electronic transmission is strictly confidential to TNCO, Inc. and
intended solely for the addressee. It may contain information which is
covered by legal, professional, or other privileges. If you are not the
intended addressee, or someone authorized by the intended addressee to
receive transmissions on behalf of the addressee, you must not retain,
disclose in any form, copy, or take any action in reliance on this
transmission. If you have received this transmission in error, please notify
the sender as soon as possible and destroy this message. While TNCO, Inc.
uses virus protection, the recipient should check this email and any
attachments for the presence of viruses. TNCO, Inc. accepts no liability for
any damage caused by any virus transmitted by this email.
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com







More information about the dba-SQLServer mailing list