Prev: PROBLEMS TO MODIFY USER BBDD MIGRATED !!!!
Next: Maintenance Plans in SQL 2005
From: Edward on 27 Jul 2010 11:47
See DDL below.

This represents a very simplistic view of my client's system, in which
there are actually about 95 tables and nearly 50 views. The views are
used primarily to provide a real-time snapshot of certain datasets for
auditing purposes. The database has medical applications so it has to
be carefully controlled.

Most of the views include in their JOIN a "nested view" - that is, a
view that contains common columns that are needed for all deriving
views (data items such as Patient Ref, Treatment Date etc.) I've
modelled this with the view CommonView below. The deriving views - an
example of which is given as SpecialView - includes CommonView in the
SELECT and other table or tables in the rest of the SQL joined as
shown.

It became necessary, for one (and only one) of the deriving views, to
expose a new column in the CommonView (see the final DDL at the
bottom) All that has happened is that an extra column (NewColumn3)
has been added to the set.

My client is concerned that the addition of this extra column to the
nested view could have an impact on the other views which derive from
this common view. Years ago, when I was studying discrete
mathematics at University, I believe that I could have formulated a
proof along the lines that the addition to a set does not affect any
intersection provided that the new addition does not appear in the
intersection, or something like that.

What I don't want to do is to allow my client to feel that they have
to retest all the deriving views, as that would be very undesirable in
terms of required effort. Can anyone think of a way of demonstrating
that what common sense tells me is irrefutable is logically provable?

Thanks

Edward

CREATE TABLE [Table_1](
[OldColumn1] [int] NOT NULL,
[OldColumn2] [nchar](10) NULL,
[NewColumn3] [nchar](10) NULL
) ON [PRIMARY]

CREATE TABLE [Table_2](
[C1] [int] NOT NULL,
[C2] [nchar](10) NULL,
[FKOldColumn1] [int] NULL
) ON [PRIMARY]

CREATE VIEW [CommonView]
AS
SELECT OldColumn1, OldColumn2
FROM dbo.Table_1

CREATE VIEW [SpecialView]
AS
SELECT dbo.CommonView.OldColumn1, dbo.CommonView.OldColumn2,
dbo.Table_2.C2
FROM dbo.CommonView INNER JOIN
dbo.Table_2 ON dbo.CommonView.OldColumn1 =
dbo.Table_2.FKOldColumn1


ALTER VIEW [dbo].[CommonView]
AS
SELECT OldColumn1, OldColumn2, NewColumn3
FROM dbo.Table_1
From: Erland Sommarskog on 27 Jul 2010 17:39
Edward (teddysnips(a)hotmail.com) writes:
> My client is concerned that the addition of this extra column to the
> nested view could have an impact on the other views which derive from
> this common view.

Well, if any if the views include "SELECT *", they will be affected,
else they will not. Not at least in terms of logic. Performance could
be affected, though.





--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: PROBLEMS TO MODIFY USER BBDD MIGRATED !!!!
Next: Maintenance Plans in SQL 2005