Best Practices for Design of Views

Hi, I have been using Views for a while now instead of constructing SQL
statements in the application but one the problems is that as the
applications grow the views need more and more columns, alisaes, and
both Lookup values and Foreign key ID for a range of uses.
At least when constructing SQL SELECT in the application each form or
function has a specific need for columns so you dont aim for any
re-usability.
How it is best to manage this when designing views? Showuld there be
one view only per logical entity that has a lot of columns making it
applicable for a whole range of uses, or individual views, one per use
with its own specific columns. If the latter is used I have a problem
naming and remembering names of all the different views.
An example may help illustrate this:
vwInvoices - This currently contains only the columns needed to diplay
a list of Invoices after doing a search, + any columns used in the
search. But when drilling into a single Invoice from the list it will
need many more columns. While the search tool only needs the foreign
key ID of the user who created it (The search tool users a dropdownn
list of users with IDs behind names) , the full detail screen needs to
show the actual user name from the users table.
Shold this view even be used for for selecting one record? Should a
stored procedure be used instead?
Are there any design principles or best practices anyone ccan share for
these issues.
Thanks.hals_left wrote:
> Hi, I have been using Views for a while now instead of constructing SQL
> statements in the application but one the problems is that as the
> applications grow the views need more and more columns, alisaes, and
> both Lookup values and Foreign key ID for a range of uses.
> At least when constructing SQL SELECT in the application each form or
> function has a specific need for columns so you dont aim for any
> re-usability.
> How it is best to manage this when designing views? Showuld there be
> one view only per logical entity that has a lot of columns making it
> applicable for a whole range of uses, or individual views, one per use
> with its own specific columns. If the latter is used I have a problem
> naming and remembering names of all the different views.
> An example may help illustrate this:
> vwInvoices - This currently contains only the columns needed to diplay
> a list of Invoices after doing a search, + any columns used in the
> search. But when drilling into a single Invoice from the list it will
> need many more columns. While the search tool only needs the foreign
> key ID of the user who created it (The search tool users a dropdownn
> list of users with IDs behind names) , the full detail screen needs to
> show the actual user name from the users table.
> Shold this view even be used for for selecting one record? Should a
> stored procedure be used instead?
> Are there any design principles or best practices anyone ccan share for
> these issues.
> Thanks.
Best practice in most environments is that the application should
access the database only through stored procedures, not views. Procs
are the best method to encapsulate logic, facilitate code reuse,
optimise performance and implement security. Views are useful if you
need to share the same query logic in several procs. For most business
process applications it isn't good practice to access views or tables
directly. See:
http://msdn.microsoft.com/library/d...y/en-us/opti...
http://www.sql-server-performance.c..._procedures.asp
http://www.sommarskog.se/dynamic_sql.html
http://weblogs.asp.net/rhoward/arch...1/17/38095.aspx
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Oops. One of the links got scrambled:
http://msdn.microsoft.com/library/d.../>
1a_6x45.asp
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Thanks David.
If I understand correctly, the best practice to display lines for a
single invoice would therefore be to have a stored procedure that
queries a view , rather than have the application construct a WHERE
clause and query the view directly ?
The procedure would be something like this ?
-- Retrieves the Invoice Lines for 1 invoice
CREATE PROCEDURE [dbo].[getSingleInvoiceLines]
@.InvoiceID Int
AS
SELECT [Description],Quantity,UnitCost, Department,Nominal
FROM dbo.vwInvoiceLines
WHERE Invoice=@.InvoiceID
GO
And used something like this in an application ?
set oRS = objConn.Execute ( "dbo.getSingleInvoiceLines " & intInvoice )
// Or with ADODB.Command / Explicit Parameters etc..
If Not oRS.EOF THen
while not oRS.EOF%>
...|||hals_left wrote:
> Thanks David.
> If I understand correctly, the best practice to display lines for a
> single invoice would therefore be to have a stored procedure that
> queries a view , rather than have the application construct a WHERE
> clause and query the view directly ?
> The procedure would be something like this ?
> -- Retrieves the Invoice Lines for 1 invoice
> CREATE PROCEDURE [dbo].[getSingleInvoiceLines]
> @.InvoiceID Int
> AS
> SELECT [Description],Quantity,UnitCost, Department,Nominal
> FROM dbo.vwInvoiceLines
> WHERE Invoice=@.InvoiceID
> GO
> And used something like this in an application ?
> set oRS = objConn.Execute ( "dbo.getSingleInvoiceLines " & intInvoice )
> // Or with ADODB.Command / Explicit Parameters etc..
> If Not oRS.EOF THen
> while not oRS.EOF%>
> ...
Use the ADO parameters collection rather than construct strings
dynamically. Usually you'll also want to put some error handling in
your procs - in fact that's one of the advantages of procs.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--