|
|
Posted by Erland Sommarskog on 04/17/06 11:40
Serge Rielau (srielau@ca.ibm.com) writes:
> Peter wrote:
>> Serge, what do I miss?
> I think you are using the wrong tool:
> http://msdn2.microsoft.com/en-us/library/ms177544(SQL.90).aspx
> try CREATE SYNONYM
I still haven't really understood why Peter want these views. But you
are right that if he has a one-to-one mapping from table to view, then
synonyms are a better choice. If he is on SQL 2005, that is. (SQL2000
does not have synonyms.)
However, there is also the case of partitioned views, where you have a
suite of identical tables with a CHECK constraint on the first primary-
key column. Such a view would look like:
SELECT * FROM sales2000
UNION ALL
SELECT * FROM sales2001
UNION ALL
...
Of course, you can list all columns here as well, but say that you add two
new columns of the same data type to the tables, and when you change the
view late Friday afternoon when your mind elsewhere, you end up with:
SELECT year, col1, col2, .... newcol1, newcol2 FROM sales2000
UNION ALL
SELECT year, col1, col2, .... newcol2, newcol1 FROM sales2000
UNION ALL
In unfortunate cases, this can lead to errors that can unnoticed for
quite a while, and in the mean while lead to incorrect decisions.
The counter-argument to this is that you may only want to add the
columns to the sales2006 table, but a SELECT * in the view would
force you to add the columns to all tables.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|