A philosophical question about inserts

We have a SQL Server database that can accept data from several
different projects throughout our lab. Is it better to (1) let each
developer create their own prepared SQL statements for inserts or to (2)
hide the schema from them and have them call stored procedures or views
to get the data into the database?

So for example, in the 1st case, the developer's code (e.g. PERL w/
DBI/DBD) would prepare/execute:

INSERT INTO table (col1,col2,col3) VALUES (1,2,3)

and in the 2nd case they would prepare/execute:
EXEC sp_insertdata, @val1 = 1, @val = 2, @val3 = 3

... and the stored procedure does the insert.

The main goal here is to isolate the developer from knowing the database
schema. Only the 'database team' knows the schema and then any changes
are done in one place (i.e. the view/stored procedure) instead of one or
many external applications.

In the 2nd case, the schema is invisible to him/her but if a column was
added to 'table', they would have to change their stored procedure call
all the same.

If there is there an easier way to do this please advise. Some of our
inserts are into tables of 100 or more columns (time-based data) and the
cadence can be sub-second.

Any help appreciated.

Michael Husler

Re: A philosophical question about inserts

Hi Michael,

I would definitely go with stored procedures. Too many benefits, just to
name a few:

- Security - you do not have to manage security at table level, but rather
by granting execute permissions (or using EXECUTE AS if on SQL Server 2005);
no direct access and revealing of your database schema.
- Data integrity - you can control the DML against the data; example: it is
easy to run a direct UPDATE with no WHERE, but in the stored procedure you
can enforce WHERE.
- Maintainability - your stored procedures will be like an additional
application layer where you can easily handle things like error handling,
transactions, etc.
- Performance - you can write the code in the stored procedure in the most
efficient way to access the data; the stored procedure is precompiled code,
the syntax is verified, and the execution query plan can be reused.

If you search Internet you will find many good examples and articles on the
topic.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Re: A philosophical question about inserts

Mike Husler (Michael.P.Husler@noaa.gov) writes:
> We have a SQL Server database that can accept data from several
> different projects throughout our lab. Is it better to (1) let each
> developer create their own prepared SQL statements for inserts or to (2)
> hide the schema from them and have them call stored procedures or views
> to get the data into the database?
>
> So for example, in the 1st case, the developer's code (e.g. PERL w/
> DBI/DBD) would prepare/execute:
>
> INSERT INTO table (col1,col2,col3) VALUES (1,2,3)
>
> and in the 2nd case they would prepare/execute:
> EXEC sp_insertdata, @val1 = 1, @val = 2, @val3 = 3
>
> .. and the stored procedure does the insert.
>
> The main goal here is to isolate the developer from knowing the database
> schema. Only the 'database team' knows the schema and then any changes
> are done in one place (i.e. the view/stored procedure) instead of one or
> many external applications.
>
> In the 2nd case, the schema is invisible to him/her but if a column was
> added to 'table', they would have to change their stored procedure call
> all the same.

Isolating the developer from the schema is a noble goal, but less
easy to implement.

But just because you add a column to a table, the developer may not
need to have to change his call: you could use default values in
either cases. After all, if the developer should add a value, he has
to get it from somewhere.

As long as we only talking insertion, the method that possibly gives
the developer the least to think of terms of SQL is sql_insert. Which
is not in DBI/DBD as far as I know, but which is in Win32::SqlServer,
which is a module for accessing SQL Server but nothing else from Perl.
With sql_insert you only pass a hash of values, and sql_insert will
build the SQL statement from the hash and the table definition (which
is cached).

Then again... I did not understand what you meant with "the cadence can
be sub-second", but if there are lots of data coming, the it is not
a very good idea to run single INSERT statements, with or without
stored procedures.

In case you are interested, Win32::SqlServer is here:
http://www.sommarskog.se/mssqlperl/index.html.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: A philosophical question about inserts

Stored Procedures are the ideal -for all the benefits mentioned above. But
in reality , some sort of Object method whereby the
user can dynamically SELECT the columnns can be more effective. A typical
example , might be an application that stores
matterials information , for example the airlines industry. In that
situation, you've potentially got hundreds of attributes that could be
assigned
to a given material, which then need to be selected from different
perspectives.

--

Jack Vamvas
___________________________________
Search IT jobs from multiple sources- http://www.ITjobfeed.com




"Mike Husler" wrote in message
news:fmiq44$tin$1@news.nems.noaa.gov...
> We have a SQL Server database that can accept data from several different
> projects throughout our lab. Is it better to (1) let each developer
> create their own prepared SQL statements for inserts or to (2) hide the
> schema from them and have them call stored procedures or views to get the
> data into the database?
> So for example, in the 1st case, the developer's code (e.g. PERL w/
> DBI/DBD) would prepare/execute:
>
> INSERT INTO table (col1,col2,col3) VALUES (1,2,3)
>
> and in the 2nd case they would prepare/execute:
> EXEC sp_insertdata, @val1 = 1, @val = 2, @val3 = 3
>
> .. and the stored procedure does the insert.
>
> The main goal here is to isolate the developer from knowing the database
> schema. Only the 'database team' knows the schema and then any changes
> are done in one place (i.e. the view/stored procedure) instead of one or
> many external applications.
>
> In the 2nd case, the schema is invisible to him/her but if a column was
> added to 'table', they would have to change their stored procedure call
> all the same.
>
> If there is there an easier way to do this please advise. Some of our
> inserts are into tables of 100 or more columns (time-based data) and the
> cadence can be sub-second.
>
> Any help appreciated.
>
> Michael Husler