<![if !supportLists]>1. <![endif]>Can a view be used to modify the data in the underlying table? - Yes, you can, but with some restrictions:

• Only one base table affected - Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

• No derived columns or aggregate functions - The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:

<![if !supportLists]>o <![endif]>An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.

<![if !supportLists]>o <![endif]>A computation. The column cannot be computed from an expression that uses other columns.

<![if !supportLists]>o <![endif]>Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.

• The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.

• TOP cannot be used with WITH CHECK - TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

• Examples:

--Update data through a view

USE AdventureWorks2012;

UPDATE HumanResources.vEmployeeDepartmentHistory

SET StartDate = '20110203' ,

EndDate = GETDATE()

WHERE LastName = N'Smith'

AND FirstName = 'Samantha';

--To insert table data through a view

USE AdventureWorks2012;

INSERT INTO HumanResources.vEmployeeDepartmentHistory

( Department, GroupName )

VALUES ( 'MyDepartment', 'MyGroup' );

--For deletes, as one deletion from a view will affect multiple columns, you have to use INSTEAD OF trigger for the purpose.

<![if !supportLists]>2. <![endif]>You can modify the data through view, but you, in some cases, should NOT do so if the modification makes the data disappear from the view - The WITH CHECK OPTION can prevent data disappear from the view due to modification through the view

CHECK OPTION - Forces all data modification statements executed against the view to follow the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

Note: Any updates performed directly to a view's underlying tables are not verified against the view, even if CHECK OPTION is specified.

Example 1:
<![if !supportLineBreakNewLine]>
<![endif]>

The following example shows a view named SeattleOnly that references five tables and allows for data modifications to apply only to employees who live in Seattle.

USE AdventureWorks2012;

IF OBJECT_ID('dbo.SeattleOnly', 'V') IS NOT NULL

DROP VIEW dbo.SeattleOnly;

CREATE VIEW dbo.SeattleOnly

SELECT p.LastName ,

p.FirstName ,

e.JobTitle ,

a.City ,

sp.StateProvinceCode

FROM HumanResources.Employee e

INNER JOIN Person.Person p ON p.BusinessEntityID = e.BusinessEntityID

INNER JOIN Person.BusinessEntityAddress bea ON bea.BusinessEntityID = e.BusinessEntityID

INNER JOIN Person.Address a ON a.AddressID = bea.AddressID

INNER JOIN Person.StateProvince sp ON sp.StateProvinceID = a.StateProvinceID

WHERE a.City = 'Seattle'

WITH CHECK OPTION;

Example 2 :( from http://www.devx.com/vb2themax/Tip/18579)

SQL Server's views are update-able, but it is possible that when you insert a new record or update an existing record, the record added or modified doesn't logically belong to the view any longer. For example, consider the following view:

CREATE VIEW authors_CA AS(SELECT * FROM Authors WHERE state='CA')

If you now issue the following command:

UPDATE authors_CA SET state= 'NJ'
<![if !supportLineBreakNewLine]>
<![endif]>

then all the records visible in the view are updated with a different State value, and therefore would disappear from the view itself. This is perfectly legal, but can create subtle programming errors. You can avoid this problem by adding the WITH CHECK OPTION predicate when you create the view:

CREATE VIEW authors_CA AS(SELECT * FROM Authors WHERE state='CA') WITH CHECK OPTION

Now any insert or update operation that makes a record disappear from the view raises a trappable runtime error.

<![if !supportLists]>3. <![endif]>What are the other options to modify data through a view if direct modification is not feasible?

<![if !supportLists]>1) <![endif]>INSTEAD OF Triggers

INSTEAD OF triggers can be created on a view to make a view updatable. The INSTEAD OF trigger is executed instead of the data modification statement on which the trigger is defined. This trigger lets the user specify the set of actions that must happen to process the data modification statement. Therefore, if an INSTEAD OF trigger exists for a view on a specific data modification statement (INSERT, UPDATE, or DELETE), the corresponding view is updatable through that statement.

Examples:

--From Gibson's MCITP book 'SQL Server
2005 Database Developer

CREATE  TABLE dbo.State

    (

      StateID CHAR(2) NOT NULL ,

      CONSTRAINT [PK_State_StateID] PRIMARY KEY CLUSTERED ( StateID )

    );

GO

--Populate the table

INSERT   INTO State VALUES  ( 'VA' );

INSERT   INTO State VALUES  ( 'CA' );

INSERT   INTO State VALUES  ( 'OK' );

INSERT   INTO State VALUES  ( 'WI' );

/************************************/

CREATE  TABLE dbo.ZipState1To5

    (

      ZipState1To5ID INT IDENTITY(1, 1 NOT NULL ,

      ZipCode INT NOT NULL ,

      State CHAR(2) NOT NULL ,

      CONSTRAINT FK_State_State1To5 FOREIGN KEY ( State )

REFERENCES  dbo.State ( StateID ) ,

      CONSTRAINT [PK_ZipState_ZipState1To5ID] PRIMARY KEY CLUSTERED

        (  ZipState1To5ID )

    );

GO

CREATE  TABLE dbo.ZipState6To9

    (

      ZipState6To9ID INT IDENTITY(1, 1) NOT NULL ,

      ZipCode INT NOT NULL ,

      State CHAR(2) NOT NULL ,

      CONSTRAINT FK_State_State6To9 FOREIGN KEY ( State )

REFERENCES  dbo.State ( StateID ) ,

      CONSTRAINT [PK_ZipState_ZipState6To9ID] PRIMARY KEY CLUSTERED

        (  ZipState6To9ID )

    );

--Populate the tables

INSERT   INTO ZipState1To5 (ZipCode, State ) VALUES  ( 23462, 'VA' );

INSERT  INTO ZipState1To5 (ZipCode, State ) VALUES  ( 44562, 'WI' );

INSERT  INTO ZipState6To9 (ZipCode, State ) VALUES  ( 94562, 'CA' );

INSERT   INTO ZipState6To9 (ZipCode, State ) VALUES  ( 74562, 'OK' );

GO

/************************************/

CREATE  VIEW vwZips

AS

    SELECT  ZipState1To5ID AS ZipStateID , ZipCode , State

    FROM    ZipState1To5

    UNION

    SELECT  ZipStateState6To9ID ,ZipCode , State

    FROM    ZipStateAfter5ZipState6To9

/************************************/

SELECT   *

FROM     vwZips

/************************************/

--get an error

UPDATE   vwZips

SET      State = 'VA'

WHERE    ZipCode = 44562

GO

/************************************/

CREATE  TRIGGER trgUpdateVwZips ON vwZips

    INSTEAD OF UPDATE

AS

    DECLARE @Zip INT

    DECLARE @State CHAR(2)

--Get the zip
and state from the inserted table

    SET @Zip = ( SELECT ZipCode

                 FROM   inserted

               )

    SET @State = ( SELECT   State

                 FROM     inserted

                 )

    IF @Zip < 60000

   --ZipCode is in the ZipState1To5 table

        UPDATE  ZipState1To5

        SET     State = @State

        WHERE   ZipCode = @Zip

    ELSE

   --ZipCode is in the ZipState6To9 table

        UPDATE  ZipState6To9

        SET     State = @State

        WHERE   ZipCode = @Zip

/************************************/

       SELECT  *

       FROM    vwZips;

UPDATE   vwZips

       SET     State = 'WI'

       WHERE   ZipCode = 44562;

   SELECT   *

       FROM    vwZips

/************************************/

--Example on Instead Of Insert

    CREATE TABLE dbo.State

        (

          StateID CHAR(2) NOT NULL ,

          CONSTRAINT [PK_State_StateID] PRIMARY KEY CLUSTERED ( StateID )

        );

GO

--Populate the table

INSERT   INTO State VALUES  ( 'VA' );

INSERT   INTO State VALUES  ( 'CA' );

INSERT   INTO State VALUES  ( 'OK' );

INSERT   INTO State VALUES  ( 'WI' );

/************************************/

DROP  TABLE dbo.ZipState

/************************************/

CREATE  TABLE dbo.ZipState

    (

      ZipStateID INT NOT NULL ,

      ZipCode INT NOT NULL ,

      State CHAR(2) NOT NULL ,

      CONSTRAINT FK_State_State FOREIGN KEY ( State ) REFERENCES dbo.State ( StateID ) ,

      CONSTRAINT [PK_ZipState_ZipStateID] PRIMARY KEY CLUSTERED ( ZipStateID )

    );

GO

--Populate the table

INSERT   INTO ZipState

        (  ZipStateID, ZipCode, State )

VALUES  (  1, 23462, 'VA' );

INSERT   INTO ZipState

        (  ZipStateID, ZipCode, State )

VALUES  (  2, 94562, 'CA' );

INSERT   INTO ZipState

        (  ZipStateID, ZipCode, State )

VALUES  (  3, 74562, 'OK' );

INSERT   INTO ZipState

        (  ZipStateID, ZipCode, State )

VALUES  (  4, 54562, 'WI' );

/************************************/

INSERT   INTO ZipState

        (  ZipCode, State )

VALUES  (  54562, 'MM' );

/************************************/

CREATE  TABLE dbo.ZipStateError

    (

      ZipStateErrorID INT IDENTITY(1, 1)

                          NOT NULL ,

      ZipCode INT NOT NULL ,

      State CHAR(2) NOT NULL ,

      CONSTRAINT [PK_ZipState_ZipStateErrorID] PRIMARY KEY CLUSTERED

        (  ZipStateErrorID )

    );

GO

/************************************/

CREATE  TRIGGER trgZipStateError ON ZipState

    INSTEAD OF INSERT

AS

    DECLARE @State CHAR(2)

    DECLARE @Count INT

    SET @State = ( SELECT   State

                   FROM     inserted

                 )

    SET @Count = ( SELECT   COUNT(StateID)

                   FROM     State

                   WHERE    StateID = @State

                 )

    IF @Count = 0

-- Insert the data into the error table

        BEGIN

            INSERT  INTO ZipStateError

                    SELECT  ZipCode ,

                            State

                    FROM    inserted

            RAISERROR ('ZipStateError entry added ', 16, 10) WITH LOG

        END

    ELSE

-- Insert the data into the regular
table

        INSERT  INTO ZipState

                SELECT  ZipCode ,

                        State ,

                        GETDATE()

                FROM    inserted

/************************************/

    SELECT  *

    FROM    ZipState

    SELECT  *

    FROM    ZipStateError

/************************************/

    INSERT  INTO ZipState

            (  ZipCode, State )

    VALUES  ( 54562, 'MM' );

/************************************/

    SELECT  *

    FROM    ZipState;

    SELECT  *

    FROM    ZipStateError;

<![if !supportLists]>2) <![endif]>Partitioned Views

If the view is a partitioned view, the view is updatable, subject to certain restrictions. When it is needed, the Database Engine distinguishes local partitioned views (i.e., the views in which all participating tables and the view are on the same instance of SQL Server), and distributed partitioned views (i.e., the views in which at least one of the tables in the view resides on a different or remote server).

<![if !supportLists]> i. <![endif]>What is a partitioned view?

A partitioned view is a view defined by a UNION ALL of member tables structured in the same way, but stored separately as multiple tables in either the same instance of SQL Server or in a group of autonomous instances of SQL Server servers, called federated database servers.

Note: The preferred method for partitioning data local to one server is through partitioned tables. For more information, see Partitioned Tables and Indexes .

In designing a partitioning scheme, it must be clear what data belongs to each partition. For example, the data for the Customers table is distributed in three member tables in three server locations: Customers_33 on Server1, Customers_66 on Server2, and Customers_99 on Server3.

A partitioned view on Server1 is defined in the following way:

Examples:

-Partitioned view as defined on Server1

CREATE VIEW Customers

--Select from local member table.

SELECT *

FROM CompanyData.dbo.Customers_33

UNION ALL

--Select from member table on Server2.

SELECT *

FROM Server2.CompanyData.dbo.Customers_66

UNION ALL

--Select from member table on Server3.

SELECT *

FROM Server3

<![if !supportLists]> ii. <![endif]>Why use it? - For performances

<![if !supportLists]> iii. <![endif]>Conditions for Modifying Data in Partitioned Views

The following restrictions apply to statements that modify data in partitioned views:

<![if !supportLists]>· <![endif]>The INSERT statement must supply values for all the columns in the view, even if the underlying member tables have a DEFAULT constraint for those columns or if they allow for null values. For those member table columns that have DEFAULT definitions, the statements cannot explicitly use the keyword DEFAULT.

<![if !supportLists]>· <![endif]>The value being inserted into the partitioning column should satisfy at least one of the underlying constraints; otherwise, the insert action will fail with a constraint violation.

<![if !supportLists]>· <![endif]>UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause, even if the column has a DEFAULT value defined in the corresponding member table.

<![if !supportLists]>· <![endif]>Columns in the view that are an identity column in one or more of the member tables cannot be modified by using an INSERT or UPDATE statement.

<![if !supportLists]>· <![endif]>If one of the member tables contains a timestamp column, the data cannot be modified by using an INSERT or UPDATE statement.

<![if !supportLists]>· <![endif]>If one of the member tables contains a trigger or an ON UPDATE CASCADE/SET NULL/SET DEFAULT or ON DELETE CASCADE/SET NULL/SET DEFAULT constraint, the view cannot be modified.

<![if !supportLists]>· <![endif]>INSERT, UPDATE, and DELETE actions against a partitioned view are not allowed if there is a self-join with the same view or with any of the member tables in the statement.

<![if !supportLists]>· <![endif]>Bulk importing data into a partitioned view is unsupported by bcp or the BULK INSERT and INSERT ... SELECT * FROM OPENROWSET(BULK...) statements. However, you can insert multiple rows into a partitioned view by using the INSERT statement.

Note: To update a partitioned view, the user must have INSERT, UPDATE, and DELETE permissions on the member tables.

<![if !supportLists]> iv. <![endif]>Modifying Data in Partitioned Views

A view is considered an updatable partitioned view when the view is a set of SELECT statements whose individual result sets are combined into one using the UNION ALL statement. Each SELECT statement references one SQL Server base table. The table can be either a local table or a linked table referenced by using a four-part name, the OPENROWSET function, or the OPENDATASOURCE function (you cannot use an OPENDATASOURCE or OPENROWSET function that specifies a pass-through query).

Additionally, data modification statements referencing the view must follow the rules defined for INSERT, UPDATE and DELETE statements.

Note: You can modify data through a distributed partitioned view only if you install SQL Server 2005+ Enterprise or SQL Server 2005+ Developer. However, you can modify data through a local partitioned view on any edition of SQL Server 2005+.

INSERT Statements

INSERT statements add data to the member tables through the partitioned view. The INSERT statements must follow to these rules:

<![if !supportLists]>· <![endif]>All columns must be included in the INSERT statement even if the column can be NULL in the base table or has a DEFAULT constraint defined in the base table.

<![if !supportLists]>· <![endif]>The DEFAULT keyword cannot be specified in the VALUES clause of the INSERT statement.

<![if !supportLists]>· <![endif]>INSERT statements must supply a value that satisfies the logic of the CHECK constraint defined on the partitioning column for one of the member tables.

<![if !supportLists]>· <![endif]>INSERT statements are not allowed if a member table contains a column with an identity property.

<![if !supportLists]>· <![endif]>INSERT statements are not allowed if a member table contains a timestamp column.

<![if !supportLists]>· <![endif]>INSERT statements are not allowed if there is a self-join with the same view or any one of the member tables.

UPDATE Statements

UPDATE statements modify data in one or more of the member tables through the partitioned view. The UPDATE statements must follow to these rules:

<![if !supportLists]>· <![endif]>UPDATE statements cannot specify the DEFAULT keyword as a value in the SET clause even if the column has a DEFAULT value defined in the corresponding member table

<![if !supportLists]>· <![endif]>The value of a column with an identity property cannot be changed: however, the other columns can be updated.

<![if !supportLists]>· <![endif]>The value of a PRIMARY KEY cannot be changed if the column contains text, image, or ntext data.

<![if !supportLists]>· <![endif]>Updates are not allowed if a base table contains a timestamp column.

<![if !supportLists]>· <![endif]>Updates are not allowed if there is a self-join with the same view or any one of the member tables.

DELETE Statements

DELETE statements remove data in one or more of the member tables through the partitioned view. DELETE statements are not allowed when there is a self-join with the same view or any one of the member tables.

Examples:

/* The example
below shows the INSERT case. It’s originally from http://sqlblog.com/blogs/piotr_rodak/archive/2010/09/11/partitioned-views.aspx

Initially, we have 13,532 rows in the Sales.CurrencyRate table. In order to demonstrate the
partitioned view, we break the original one ‘giant’ table into 4 partitioned
tables

*/

--13,532 rows in one table

SELECT  COUNT(*)

FROM    Sales.CurrencyRate

--These 13000+ rows are in 4 years from
2001 to 2004. If grouped by year and month, there are 37 rows

SELECT  COUNT(*) NumberOfRates ,

        YEAR([CurrencyRateDate]) RateYear ,

        MONTH([CurrencyRateDate]) RateMonth

FROM    Sales.CurrencyRate

GROUP BY YEAR([CurrencyRateDate]) ,

        MONTH([CurrencyRateDate])

ORDER BY RateYear ,

        RateMonth

--This is the original defintion of the table with foreign keys defined

/*       

 CREATE TABLE [Sales].[CurrencyRate](

[CurrencyRateID]
[int] IDENTITY(1,1)
NOT NULL,

[CurrencyRateDate]
[datetime] NOT NULL,

[FromCurrencyCode]
[nchar](3)  NOT NULL,

[ToCurrencyCode]
[nchar](3)  NOT NULL,

[AverageRate]
[money] NOT NULL,

[EndOfDayRate]
[money] NOT NULL,

[ModifiedDate]
[datetime] NOT NULL
CONSTRAINT [DF_CurrencyRate_ModifiedDate]  DEFAULT (getdate()),

 CONSTRAINT [PK_CurrencyRate_CurrencyRateID]
PRIMARY KEY CLUSTERED

(

[CurrencyRateID]
ASC

)WITH (IGNORE_DUP_KEY
= OFF) ON [PRIMARY]

) ON [PRIMARY]

GO

ALTER TABLE [Sales].[CurrencyRate]  WITH CHECK ADD  CONSTRAINT [FK_CurrencyRate_Currency_FromCurrencyCode] FOREIGN KEY([FromCurrencyCode])

REFERENCES [Sales].[Currency]
([CurrencyCode])

GO

ALTER TABLE [Sales].[CurrencyRate]  WITH CHECK ADD  CONSTRAINT [FK_CurrencyRate_Currency_ToCurrencyCode] FOREIGN KEY([ToCurrencyCode])

REFERENCES [Sales].[Currency]
([CurrencyCode])

GO

*/

/* We need to
break the one giant table into four tables, each for each year represented by CurrencyRateDate column.

If we want to have updateable
partitioned view, we need to modify the constraint to include date part, which
will allow the execution engine to identify partition that has to be modified
during DML operation.

The easiest is to add the CurrencyRateDate column to the primary key.

This is the first table

*/

CREATE TABLE [Sales].[CurrencyRate_2001]

    (

      [CurrencyRateID] [int] NOT NULL ,--Note the
IDENTITY has been removed in the partitioned view.

      [CurrencyRateDate] [datetime] NOT NULL ,

      [FromCurrencyCode] [nchar](3) NOT NULL ,

      [ToCurrencyCode] [nchar](3) NOT NULL ,

      [AverageRate] [money] NOT NULL ,

      [EndOfDayRate] [money] NOT NULL ,

      [ModifiedDate] [datetime]

        NOT NULL

        CONSTRAINT [DF_CurrencyRate_2001_ModifiedDate] DEFAULT ( GETDATE() ) ,

      CONSTRAINT CHK_CurrencyRateDate_2001 CHECK ( CurrencyRateDate >= '20010101'

                                                   AND CurrencyRateDate < '20020101' ) ,

      CONSTRAINT [PK_CurrencyRate_2001_CurrencyRate_2001ID] PRIMARY KEY CLUSTERED

        ( [CurrencyRateID] ASC, [CurrencyRateDate] ASC )

    )

ON  [PRIMARY]

GO

--Note: these FKs must be defined as
well

ALTER TABLE [Sales].[CurrencyRate_2001]  WITH CHECK ADD  CONSTRAINT [FK_CurrencyRate_2001_Currency_FromCurrencyCode] FOREIGN KEY([FromCurrencyCode])

REFERENCES [Sales].[Currency] ([CurrencyCode])

GO

ALTER TABLE [Sales].[CurrencyRate_2001]  WITH CHECK ADD  CONSTRAINT [FK_CurrencyRate_2001_Currency_ToCurrencyCode] FOREIGN KEY([ToCurrencyCode])

REFERENCES [Sales].[Currency] ([CurrencyCode])

GO

/****** This is
the 2nd table ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [Sales].[CurrencyRate_2002]

    (

      [CurrencyRateID] [int] NOT NULL ,

      [CurrencyRateDate] [datetime] NOT NULL ,

      [FromCurrencyCode] [nchar](3) NOT NULL ,

      [ToCurrencyCode] [nchar](3) NOT NULL ,

      [AverageRate] [money] NOT NULL ,

      [EndOfDayRate] [money] NOT NULL ,

      [ModifiedDate] [datetime]

        NOT NULL

        CONSTRAINT [DF_CurrencyRate_2002_ModifiedDate] DEFAULT ( GETDATE() ) ,

      CONSTRAINT CHK_CurrencyRateDate_2002 CHECK ( CurrencyRateDate >= '20020101'

                                                   AND CurrencyRateDate < '20030101' ) ,

      CONSTRAINT [PK_CurrencyRate_2002_CurrencyRate_2002ID] PRIMARY KEY CLUSTERED

        ( [CurrencyRateID] ASC, [CurrencyRateDate] ASC )

        WITH ( IGNORE_DUP_KEY = OFF ) ON [PRIMARY]

    )

ON  [PRIMARY]

GO

ALTER TABLE [Sales].[CurrencyRate_2002]  WITH CHECK ADD  CONSTRAINT [FK_CurrencyRate_2002_Currency_FromCurrencyCode] FOREIGN KEY([FromCurrencyCode])

REFERENCES [Sales].[Currency] ([CurrencyCode])

GO

ALTER TABLE [Sales].[CurrencyRate_2002]  WITH CHECK ADD  CONSTRAINT [FK_CurrencyRate_2002_Currency_ToCurrencyCode] FOREIGN KEY([ToCurrencyCode])

REFERENCES [Sales].[Currency] ([CurrencyCode])

GO

/****** 3rd table ******/

CREATE TABLE [Sales].[CurrencyRate_2003]

    (

      [CurrencyRateID] [int] NOT NULL ,

      [CurrencyRateDate] [datetime] NOT NULL ,

      [FromCurrencyCode] [nchar](3) NOT NULL ,

      [ToCurrencyCode] [nchar](3) NOT NULL ,

      [AverageRate] [money] NOT NULL ,

      [EndOfDayRate] [money] NOT NULL ,

      [ModifiedDate] [datetime]

        NOT NULL

        CONSTRAINT [DF_CurrencyRate_2003_ModifiedDate] DEFAULT ( GETDATE() ) ,

      CONSTRAINT CHK_CurrencyRateDate_2003 CHECK ( CurrencyRateDate >= '20030101'

                                                   AND CurrencyRateDate < '20040101' ) ,

      CONSTRAINT [PK_CurrencyRate_2003_CurrencyRate_2003ID] PRIMARY KEY CLUSTERED

        ( [CurrencyRateID] ASC, [CurrencyRateDate] ASC )

        WITH ( IGNORE_DUP_KEY = OFF ) ON [PRIMARY]

    )

ON  [PRIMARY]

GO

ALTER TABLE [Sales].[CurrencyRate_2003]  WITH CHECK ADD  CONSTRAINT [FK_CurrencyRate_2003_Currency_FromCurrencyCode] FOREIGN KEY([FromCurrencyCode])

REFERENCES [Sales].[Currency] ([CurrencyCode])

GO

ALTER TABLE [Sales].[CurrencyRate_2003]  WITH CHECK ADD  CONSTRAINT [FK_CurrencyRate_2003_Currency_ToCurrencyCode] FOREIGN KEY([ToCurrencyCode])

REFERENCES [Sales].[Currency] ([CurrencyCode])

GO

/****** 4th table ******/

CREATE TABLE [Sales].[CurrencyRate_2004]

    (

      [CurrencyRateID] [int] NOT NULL ,

      [CurrencyRateDate] [datetime] NOT NULL ,

      [FromCurrencyCode] [nchar](3) NOT NULL ,

      [ToCurrencyCode] [nchar](3) NOT NULL ,

      [AverageRate] [money] NOT NULL ,

      [EndOfDayRate] [money] NOT NULL ,

      [ModifiedDate] [datetime]

        NOT NULL

        CONSTRAINT [DF_CurrencyRate_2004_ModifiedDate] DEFAULT ( GETDATE() ) ,

      CONSTRAINT CHK_CurrencyRateDate_2004 CHECK ( CurrencyRateDate >= '20040101'

                                                   AND CurrencyRateDate < '20050101' ) ,

      CONSTRAINT [PK_CurrencyRate_2004_CurrencyRate_2004ID] PRIMARY KEY CLUSTERED

        ( [CurrencyRateID] ASC, [CurrencyRateDate] ASC )

        WITH ( IGNORE_DUP_KEY = OFF ) ON [PRIMARY]

    )

ON  [PRIMARY]

GO

ALTER TABLE [Sales].[CurrencyRate_2004]  WITH CHECK ADD  CONSTRAINT [FK_CurrencyRate_2004_Currency_FromCurrencyCode] FOREIGN KEY([FromCurrencyCode])

REFERENCES [Sales].[Currency] ([CurrencyCode])

GO

ALTER TABLE [Sales].[CurrencyRate_2004]  WITH CHECK ADD  CONSTRAINT [FK_CurrencyRate_2004_Currency_ToCurrencyCode] FOREIGN KEY([ToCurrencyCode])

REFERENCES [Sales].[Currency] ([CurrencyCode])

GO

--- Now create the view

CREATE VIEW Sales.vCurrencyRate

AS

    SELECT  *

    FROM    Sales.CurrencyRate_2001

    UNION ALL

    SELECT  *

    FROM    Sales.CurrencyRate_2002

    UNION ALL

    SELECT  *

    FROM    Sales.CurrencyRate_2003

    UNION ALL

    SELECT  *

    FROM    Sales.CurrencyRate_2004

GO

---Finally, we can insert data into the
base table through the view

-- But be aware: the data is inserted
into the paritioned table, not the original giant
table.

INSERT  Sales.vCurrencyRate

        ( [CurrencyRateID] ,

          [CurrencyRateDate] ,

          [FromCurrencyCode] ,

          [ToCurrencyCode] ,

          [AverageRate] ,

          [EndOfDayRate] ,

          [ModifiedDate]

        )

        SELECT  [CurrencyRateID] ,

                [CurrencyRateDate] ,

                [FromCurrencyCode] ,

                [ToCurrencyCode] ,

                [AverageRate] ,

                [EndOfDayRate] ,

                [ModifiedDate]

        FROM    Sales.CurrencyRate

GO

--VERIFY THE DATA are inserted into the underlyinig partitioned tables through the view.

SELECT * FROM Sales.vCurrencyRate

SELECT * FROM Sales.CurrencyRate_2001

SELECT * FROM Sales.CurrencyRate_2002

SELECT * FROM Sales.CurrencyRate_2003

SELECT * FROM Sales.CurrencyRate_2004

--The original table is not affacted.

SELECT * FROM Sales.CurrencyRate

---cleanup

DROP VIEW Sales.vCurrencyRate

DROP TABLE Sales.CurrencyRate_2001

DROP TABLE Sales.CurrencyRate_2002

DROP TABLE Sales.CurrencyRate_2003

DROP TABLE Sales.CurrencyRate_2004

On the Journey to be a SQL Server Professional

How to modify data through a view?

<![if !supportLists]>1. <![endif]>Can a view be used to modify the data in the underlying table? - Yes, you can, but with some restrictions:

<![if !supportLists]>2. <![endif]>You can modify the data through view, but you, in some cases, should NOT do so if the modification makes the data disappear from the view - The WITH CHECK OPTION can prevent data disappear from the view due to modification through the view

<![if !supportLists]>3. <![endif]>What are the other options to modify data through a view if direct modification is not feasible?

<![if !supportLists]>1) <![endif]>INSTEAD OF Triggers

<![if !supportLists]>2) <![endif]>Partitioned Views

<![if !supportLists]> i. <![endif]>What is a partitioned view?

<![if !supportLists]> ii. <![endif]>Why use it? - For performances

<![if !supportLists]> iii. <![endif]>Conditions for Modifying Data in Partitioned Views

<![if !supportLists]> iv. <![endif]>Modifying Data in Partitioned Views