Linked Server Q&As - Focusing on Login Mapping between the Local Server and the Linked Server

1. What’s for? – For distributed queries by accessing data on a remote server

2. Can the remote data source be an Excel file? – Yes, as long as we have an OLE DB provider as the remote data source

3. What are the two basic steps to create a linked server? – create a linked server and configure the logins

4. How to create a linked server? – T-SQL (sp_addlinedserver) or using the SSMS GUI

5. What are the two basic ways to configure logins for a linked server? – Impersonation/Delegation (i.e., self-mapping for Windows logins) and Mapped Logins (for SQL Server Logins on the remote server)

5.1 How to Configure Linked Servers for Delegation

SQL Server can automatically use the Windows security credentials (Windows login name and password) of a user issuing the query on the local server to connect to a linked server when all the following conditions exist:

• A user is connected to SQL Server by using Windows Authentication Mode.
• Security account delegation is available on the client and sending server.
• The provider supports Windows Authentication Mode; for example, SQL Server running on Windows.

See below for details:

SQL Server and Windows can be configured to enable a client connected to an instance of SQL Server to connect to another instance of SQL Server by forwarding the credentials of an authenticated Windows user. This arrangement is known as delegation. Under delegation, the instance of SQL Server to which a Windows user has connected by using Windows Authentication impersonates that user when communicating with another instance. Security account delegation is required for distributed queries when a self-mapping is used for a specific login against a specific linked server.

Delegation Requirements

To illustrate the requirements for delegation, consider the following scenario: A user logs on to a client computer that connects to a server that is running an instance of SQL Server,SQLSERVER1. The user wants to run a distributed query against a database on a linked server, SQLSERVER2. This scenario, in which one computer connects to another computer to connect to a third computer, is called a double hop.

5.1.1 Requirements for the client

• The Windows authenticated login of the user must have access permissions to SQLSERVER1 and SQLSERVER2.
• The user Active Directory property, Account is sensitive and cannot be delegated, must not be selected.
• The client computer must be using TCP/IP or named pipes network connectivity.

5.1.2 Requirements for the First/Middle Server (SQLSERVER1)

• The server must have an SPN (A service principal name is the name by which a client uniquely identifies an instance of a service) registered by the domain administrator.
• The account under which SQL Server is running must be trusted for delegation.
• The server must be using TCP/IP or named pipes network connectivity.
• The second server, SQLSERVER2, must be added as a linked server. This can be done by running the sp_addlinkedserver stored procedure. For example:

EXEC sp_addlinkedserver 'SQLSERVER2', N'SQL Server'

• The linked server logins must be configured for self-mapping. This can be done by running the sp_addlinkedsrvlogin stored procedure. For example:

EXEC sp_addlinkedsrvlogin 'SQLSERVER2', 'true'

5.1.3 Requirements for the Second Server (SQLSERVER2)

• If using TCP/IP network connectivity, the server must have an SPN registered by the domain administrator.
• The server must be using TCP/IP or named pipes network connectivity.

5.2. Mapped Logins – Specifying the login/pw that will be used when the linked server is used

Impersonation/Delegation is the preferred way. But if you cannot do it or it is not desirable, you can use mapped login.

Two ways to create a mapped login:

• T-SQL way - sp_addlinkedsrvlogin

The following example creates a mapping to make sure that the Windows user Domain\Mary connects through to the linked server Accounts by using the login MaryP and password d89q3w4u.

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u';

• SSMS GUI way

(Below is excerpted from http://www.databasejournal.com/features/mssql/article.php/3691721/Setting-up-a-Linked-Server-for-a-Remote-SQL-Server-Instance.htm)

Login mapping is a way to associate a login on the local server, with a login on the remote server. There are two different ways a local login can be mapped to a remote login. The first method is to impersonate, and the second is to associate the local login with a remote login and password. The impersonate option takes the local Windows login and uses it to connect to the linked server. It does this by impersonating the local login. In order for the local server to impersonate, the login requires that delegation be setup between the local server and the linked server. A discussion on delegation is outside the scope of this article. To map a local login you would associate it with a remote login and password. The remote login needs to be a SQL Server Authenticated user on the remote server. The following screen shot shows how I have mapped some local logins to remote logins on SERVER2:

Here I have mapped three different local logins to two different remote logins. The first login mapping is for “DJ\GREG”, which is a Window domain authenticated user that is defined on the local server. I’ve identified the mapping so “DJ\GREG” is to be impersonated when connecting to “SERVER2”. This means anytime “DJ\GREG” is logged onto SERVER1 and issues a linked server query to “SERVER2” those request will connect and run the query on “SERVER2” in the security context of “DJ\GREG”. The second mapping is for “WEB_USER” which is a SQL Server authenticated user. I’ve mapped “WEB_USER” to the same remote login. In doing so, I had to provide the password for login “WEB_USER”. This password must be the password for the “WEB_USER” on linked server, in my case that would be “SERVER2”. The third login mapping demonstrates how you can map multiple local logins to a single remote login. In my example I mapped the Windows domain authenticated login “DJ\LINDA” to the remote login “WEB_USER”. Using mapped logins is a way to identify only those users from the local machine that can connect to the linked server.

In addition to mapping logins, you can also identify how logins that are not defined in the mappings would connect to the linked server. There are four different options that can be used. These four options are the different radio buttons in the screen shot above.

The first option “Not be made” is fairly obvious. When you select this option, any users not identified in the login mappings will not be able to connect to the linked server. The second method “Be made without using a security context” is to be used for connecting to data sources that do not require any authentication, like a text file. If you select this option to connect to a linked server then this has the same effect as selecting the “Not be made” option. The third option “Be made using Login’s current security context” means you want the linked server request to use the Windows account of the login to connect to the linked server. In order for this option to work, your SQL Server machine will need to be able to impersonate a local account. This option is a simple way to identify that all Windows accounts can use a linked server, without mapping each login. However, remember this requires delegation to be set up. The last option “Be made with this security context” is a way to say everyone using this linked server will connect with a single remote login and password to the linked server. The remote login needs to be a SQL Server Authenticated login.

6. What are the two ways to utilize a linked server? – Use four-part-name distributed and OPENQUERY with a pass-through query

6.1 What is QUENQUERY? – for pass-through query on a linked server

OPENQUERY - Executes the specified pass-through query on the specified linked server. This server is an OLE DB data source. OPENQUERY can be referenced in the FROM clause of a query as if it were a table name.

6.2 Syntax

OPENQUERY ( linked_server ,'query' )

6.3 Examples

1) Executing an UPDATE pass-through query

The following example uses a pass-through UPDATE query against the linked server created in example A.

UPDATE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE id = 101')

SET name = 'ADifferentName';

2) Executing an INSERT pass-through query

The following example uses a pass-through INSERT query against the linked server created in example A.

INSERT OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles')

VALUES ('NewTitle');

3) Executing a DELETE pass-through query

The following example uses a pass-through DELETE query to delete the row inserted in example C.

DELETE OPENQUERY (OracleSvr, 'SELECT name FROM joe.titles WHERE name = ''NewTitle''');

6.4 Distributed query (Four-part) or OPENQUERY? Which one is better? Test! Test! Test!

(See http://blogs.msdn.com/b/sqlsakthi/archive/2011/05/09/best-performer-distributed-query-four-part-or-openquery-when-executing-linked-server-queries-in-sql-server.aspx for details)

7. What’s the difference between sp_addlinkedsrvlogin and sp_addremotelogin?

Assume that Server1 is the local server and Server2 is the remote server. In short:

sp_addlinkedsrvlogin create a login mapping, letting local users run distributed queries on the linked server.

sp_addremotelogin also create a login mapping, but letting the remote user run remote procedure calls on the local server.

7.1 sp_addlinkedsrvlogin

7.1.1 Purpose

Creates or updates a mapping between a login on the local instance of SQL Server and a security account on a remote server.

When a user logs on to the local server and executes a distributed query that accesses a table on the linked server, the local server must log on to the linked server on behalf of the user to access that table. Use sp_addlinkedsrvlogin to specify the login credentials that the local server uses to log on to the linked server.

7.1.2 Syntax

sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'

                              [ , [ @useself = ] 'TRUE' | 'FALSE' | NULL ]

                              [ , [ @locallogin = ] 'locallogin' ]

                              [ , [ @rmtuser = ] 'rmtuser' ]

                              [ , [ @rmtpassword = ] 'rmtpassword' ]

A default mapping between all logins on the local server and remote logins on the linked server is automatically created by executing sp_addlinkedserver. The default mapping states that SQL Server uses the user credentials of the local login when connecting to the linked server on behalf of the login. This is equivalent to executing sp_addlinkedsrvlogin with @useself set to true for the linked server, without specifying a local user name. Use sp_addlinkedsrvlogin only to change the default mapping or to add new mappings for specific local logins. To delete the default mapping or any other mapping, use sp_droplinkedsrvlogin.

After the authentication has been performed by the linked server by using the mappings that are defined by executing sp_addlinkedsrvlogin on the local instance of SQL Server, the permissions on individual objects in the remote database are determined by the linked server, not the local server.

sp_addlinkedsrvlogin cannot be executed from within a user-defined transaction.

7.1.3 Examples

1) Connecting all local logins to the linked server by using their own user credentials

The following example creates a mapping to make sure that all logins to the local server connect through to the linked server Accounts by using their own user credentials.

EXEC sp_addlinkedsrvlogin 'Accounts';

Or

EXEC sp_addlinkedsrvlogin 'Accounts', 'true';

If there are explicit mappings created for individual logins, they take precedence over any global mappings that may exist for that linked server

2) Connecting a specific login to the linked server by using different user credentials

The following example creates a mapping to make sure that the Windows user Domain\Mary connects through to the linked server Accounts by using the login MaryP and password d89q3w4u.

EXEC sp_addlinkedsrvlogin 'Accounts', 'false', 'Domain\Mary', 'MaryP', 'd89q3w4u';

Note: This example does not use Windows Authentication. Passwords will be transmitted unencrypted. Passwords may be visible in data source definitions and scripts that are saved to disk, in backups, and in log files. Never use an administrator password in this kind of connection. Consult your network administrator for security guidance specific to your environment.

7.2 sp_addremotelogin

7.2.1 Purpose:

Adds a new remote login ID on the local server. This enables remote servers to connect and execute remote procedure calls (on the local server, I added).

Note: This feature will be removed in the next version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. Use linked servers and linked server stored procedures instead

7.2.2 Syntax

sp_addremotelogin [ @remoteserver = ] 'remoteserver'

                           [ , [ @loginame = ] 'login' ]

                           [ , [ @remotename = ] 'remote_name' ]

                      

[ @remoteserver = ] 'remoteserver'

Is the name of the remote server that the remote login applies to. remoteserver is sysname, with no default. If only remoteserver is specified, all users on remoteserver are mapped to existing logins of the same name on the local server. The server must be known to the local server. This is added by using sp_addserver. When users on remoteserver connect to the local server that is running SQL Server to execute a remote stored procedure, they connect as the local login that matches their own login on remoteserver. remoteserver is the server that initiates the remote procedure call.

[ @loginame = ] 'login'

Is the login ID of the user on the local instance of SQL Server. login is sysname, with a default of NULL. login must already exist on the local instance of SQL Server. If login is specified, all users on remoteserver are mapped to that specific local login. When users on remoteserver connect to the local instance of SQL Server to execute a remote stored procedure, they connect as login.

[ @remotename = ] 'remote_name'

Is the login ID of the user on the remote server. remote_name is sysname, with a default of NULL. remote_name must exist on remoteserver. If remote_name is specified, the specific user remote_name is mapped to login on the local server. When remote_name on remoteserver connects to the local instance of SQL Server to execute a remote stored procedure, it connects as login. The login ID of remote_name can be different from the login ID on the remote server, login.

7.2.3 Return Code Values

0 (success) or 1 (failure)

7.2.4 Examples

1) Mapping one to one

The following example maps remote names to local names when the remote server ACCOUNTS and local server have the same user logins.

EXEC sp_addremotelogin 'ACCOUNTS';

2) Mapping many to one

The following example creates an entry that maps all users from the remote server ACCOUNTS to the local login ID Albert.

EXEC sp_addremotelogin 'ACCOUNTS', 'Albert';

3) Using explicit one-to-one mapping

The following example maps a remote login from the remote user Chris on the remote server ACCOUNTS to the local user salesmgr.

EXEC sp_addremotelogin 'ACCOUNTS', 'salesmgr', 'Chris';

What are the different options to generate an XML Schema Collection?

Option 1 - CREATE XML SCHEMA COLLECTION XXX AS ‘…’

Option 2 - Use Microsoft XSD inference tool 

Option 3 - Use other free and non-free third party tools (see http://stackoverflow.com/questions/74879/any-tools-to-generate-an-xsd-schema-from-an-xml-instance-document)

Option 4 - In BIDS 2005+, copy the xml code from SSMS to the xml editor window in VS or BIDS, from the XML menu, choose Generate Schema. See below for details:

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:

•               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;

GO

UPDATE   HumanResources.vEmployeeDepartmentHistory

SET      StartDate = '20110203' ,

        EndDate = GETDATE()

WHERE    LastName = N'Smith'

        AND FirstName = 'Samantha';

GO

--To insert table data through a view

USE  AdventureWorks2012;

GO

INSERT   INTO HumanResources.vEmployeeDepartmentHistory

        (  Department, GroupName )

VALUES  (  'MyDepartment', 'MyGroup' );

GO

--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;

GO

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

    DROP VIEW dbo.SeattleOnly;

GO

CREATE  VIEW dbo.SeattleOnly

AS

    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;

GO 

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

AS

    --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