Views based on tables on diff servers

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Views based on tables on diff servers

Views based on tables on diff servers

Rate Topic

Display Mode

Topic Options

Author

Message

var05

Posted Tuesday, December 04, 2012 9:21 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 16, 2013 9:13 AM
Points: 83, Visits: 362

Hi All,

Could you please let me know the possibilities of creating a view based on a table that exists in a different server.

Thanks

Post #1392569

Lowell

Posted Tuesday, December 04, 2012 9:26 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 9:56 AM
Points: 11,619, Visits: 27,681

its certainly possible, you'd probably want to create a linked server to the other server, and then create the view with a full four part naming convention.

CREATE VIEW
AS
SELECT ColumnList
FROM MyLinkedServer.DatabaseName.dbo.TableName
Where SomeColumn = SomeCriteria

now be warned: linked servers can be SLOW, especially if the tables on the linked server are huge...data usually gets copied over to the temp db, and then filtered. that's defintiely the case if you join that linked table to some local table...all the data is copied, then joined, then filtered, then returned as the results of a query.

If you know the data doesn't change much, I'd suggest replicating a copy to a local table instead, and refreshing it on some regular interval.

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son

Post #1392573

« Prev Topic | Next Topic »

Permissions