[dba-SQLServer] Dynamic SQL

Hi John:  A few questions about how you do things:

If I understand your first example correctly, you have database per client (is this one large table, or is it a full-blown relational db with many tables, etc.?).  You extract subsets of records repeatedly from that large table.  To do this you build a query (the view), which you use to populate a table (SubTable1).  From that table you select a subset (SubTable1_a), and later you may select another subset (SubTable1_b).  Each of these subsets you persist as physical tables.
In this example, why build the physical table (SubTable1)) when the virtual table (the view) will do the same thing?  The subsets of the table (SubTable1_a and _b) that are actually sent on to the client can be thought of as 'WHERE xyz=123' clauses to the view that was originally used to populate SubTable1, no?  If you kept a record of the where clauses, with other pertinent information, such as date, client, etc., you could reproduce the recordsets at will, and would not need to physically store the data, neither as SubTable1 or as its children _a or _b.

I guess the essence of my question is why is it necessary to build a physical table, when you have a virtual table?

d lewis
------------------------------

Message: 2
Date: Wed, 19 May 2010 16:18:48 -0400
From: jwcolby <jwcolby at colbyconsulting.com>
Subject: [dba-SQLServer] dynamic sql
To: Access Developers discussion and problem solving
        <accessd at databaseadvisors.com>,         Sqlserver-Dba
        <dba-sqlserver at databaseadvisors.com>
Message-ID: <4BF447A8.2050004 at colbyconsulting.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed

Guys,

I use dynamic SQL a lot.  I have received feedback from others on these groups that "there is never
a valid reason for dynamic SQL", there is always a safer "better" way to do this.

I would like to lay out what I do and get some feedback on exactly how I would do what I am doing
without using dynamic SQL.

A couple of examples.

I build an entire database for an order for my client.  Each database has some fairly specific
stuff, as well as data for that order, which I may need to use again for another order.  IOW, I
select X records, send a small subset of those records off to a client, then months down the road
may need to send another subset of those records.

Each order is based on selection criteria, and that criteria is different for each order.  So I
build a table on-the-fly.  I have a common set of fields, then I have the selection fields.  I then
pull all of the data from a view into that custom table.

So I have a view.  I use code to get the names of the fields in that view and I build a table which
has exactly the same fields as the view contains, using dynamic SQL.  I use the view to then append
the data into the table.  Since the table has the same fields as the view, it basically just appends
right in.  Using dynamic SQL I am able to build any select query I need and build a table which
exactly matches that view.

Another example.  I have many different databases which contain millions of name / address /
demographic fields.  For example I have a table of 20 million people who have children, with fields
for whether they children in specific age brackets.  I have another database with 12 million records
of people who have cats and / or dogs.  Another where...

Each of these databases has a name / address component (fields) which are standardized (by me) so
that the name / address field names are identical.  I then have stored procedures which build name /
address tables, append the name / address components out of the central table into a temp table in
sorted order, grab just 2 million record chunks, and BCP the records out to files on disk for
address validation.  There is a similar set of stored procedures where I pull the resulting
validated address fields back in to SQL Server into the same database and perform other processing
inside of the database.

Because I have standardized the field names, I can use dynamic SQL in stored procedures to pass in
the name of the database and a table or view name for the export piece and the import piece as well.
  There are probably 5-10 stored procedures which perform the export process as well as a like
number which performs the import.

In fact I used to have "hard coded" stored procedures in each database which did this stuff, but
maintenance was a nightmare.  If I needed to change anything I had to go into each database and
modify the stored procedures on a case by case basis.  I now have a "master" database where I store
my stored procedures and I can simply pass in the parameters to the stored procedure to tell it what
database / view to use.  One place to edit, one place to go to make modifications.

So that gives an overview of some of the ways that I use dynamic SQL.  I am looking for the "Why I
shouldn't" and what my alternatives are, or comments on when and why dynamic SQL is useful (as I
find it useful) and how to go about making the resulting dynamic SQL safe(r) to use.

Thanks,
--
John W. Colby
www.ColbyConsulting.com


------------------------------


The contents of this e-mail message and its attachments are covered by the Electronic Communications Privacy Act (18 U.S.C. 2510-2521) and are intended solely for the addressee(s) hereof. If you are not the named recipient, or the employee or agent responsible for delivering the message to the intended recipient, or if this message has been addressed to you in error, you are directed not to read, disclose, reproduce, distribute, disseminate or otherwise use this transmission.  If you have received this communication in error, please notify us immediately by return e-mail or by telephone, 530-893-3520, and delete and/or destroy all copies of the message immediately.