Qual é mais eficiente: selecionar no servidor vinculado ou inserir no servidor vinculado?

Suponha que eu tenha que exportar dados de um servidor para outro.

O melhor é usar

• SE você deseja que todos os dados usem Backup/Restauração; BCP OUT & BCP IN ou SSIS
• SE você quiser subconjunto de dados (somente algumas tabelas) use SSIS ou BCP OUT & BCP IN

PARA mover dados, dependendo da quantidade/tamanho dos dados e largura de banda n/w, o servidor vinculado interromperá o desempenho.

Executando no servidor de origem Ou executando no servidor de destino - Qual será mais rápido e consumirá menos recursos no total (tanto no servidor de origem quanto no de destino)?

-- Executando no servidor de origem:

INSERT INTO [DestinationLinkedServer].[DestinationDB].[dbo].[Table]
SELECT a, b, c, ... FROM [dbo].Udf_GetExportData()

Isso é chamado de PUSHING Data, pois você está executando a consulta no servidor de origem e enviando os dados para o servidor de destino. Esta será uma operação cara.

--- executando no servidor de destino

INSERT INTO [dbo].[Table]
SELECT a, b, c, ...
FROM OPENQUERY([OriginLinkedServer],
    'SELECT a, b, c, ... FROM [OriginDB].[dbo].Udf_GetExportData()')

Isso é chamado de PULLING Data, pois você está executando a consulta no servidor de destino e extraindo dados do servidor de origem. Isso será muito mais rápido e menos intensivo em recursos em comparação com o anterior (dependendo de quantos dados estão sendo extraídos).

No caso do método pull, usando o SQL Profiler, você verá que uma única instrução SQL é executada no servidor vinculado (servidor de origem) e o conjunto de resultados é puxado do servidor de origem para o servidor de destino, o que é um grande ganho de desempenho em relação ao PUSH método.

Outro ponto a ser observado é:

Entre o servidor vinculado (convenção de nomenclatura de 4 partes usada servername.databasename.schema.tablename aka Distributed Queries) e OPENQUERY, geralmente OPENQUERY será rápido. Por quê ?

Para Linked Server - o otimizador de consultas cria um plano de execução observando a nomenclatura da consulta e a divide em consultas remotas e locais. As consultas locais são executadas localmente e os dados para consultas remotas são coletados dos servidores remotos, limpos localmente, combinados e apresentados ao usuário final como um único conjunto de registros.

Para OPENQUERY - Executa a consulta de passagem especificada no servidor vinculado especificado. O SQL Server envia consultas de passagem como strings de consulta não interpretadas para uma fonte de dados OLE DB. Portanto, o SQL não aplicará nenhum tipo de lógica na consulta e não tentará estimar o que essa consulta faria, simplesmente passaria a consulta especificada como está para o servidor vinculado de destino. As consultas abertas são úteis quando você não faz referência a vários servidores em uma consulta. Geralmente é rápido, pois o SQL não o divide em várias operações e não executa nenhuma ação local na saída recebida.

Excelentes referências de leitura:

• Servidores vinculados e impacto no desempenho: a direção é importante!
• Consultas distribuídas
• OPENQUERY

Como você está medindo a eficiência? Qual deles será mais rápido? Qual deles consumirá menos recursos no destino? na fonte? Quantas linhas e quais tipos de dados são as colunas nessas linhas? Tem certeza de que pode executar um TVF por meio de um servidor vinculado (o destino é SQL 2008 ou posterior?) ? Como você está garantindo uma migração 1:1 desses dados se estiver extraindo de um TVF?

Tirando essas dúvidas...

Atualização 1

Parece que você está procurando ETL (Extract-Transform-Load). Eu recomendaria o SSIS (SQL Server Integration Services) com o qual você pode extrair os dados da origem, aplicar as transformações necessárias e carregá-las em seu destino. Parece que seria um pacote bastante simples (dependendo das transformações).

A sabedoria convencional afirma que a abordagem do servidor vinculado irá para o link, puxará os dados para o servidor local e, em seguida, aplicará qualquer lógica (filtros, junções, etc.) no servidor local. Há alguma sobrecarga para buscar os dados no servidor vinculado, mas a maior parte do processamento será tratada localmente.

O método OPENQUERY colocará o processamento no servidor remoto e os "resultados filtrados" serão recebidos pelo servidor local.

Parece que mesmo se você pudesse executar um TVF através de um servidor vinculado, você estaria recebendo o pior dos dois mundos, processando remotamente e processando localmente (supondo que você tivesse lógica adicional para aplicar ao conjunto).

Dependendo de como você decidir avançar, eu também procuraria OPENQUERYcomo um meio de importar/exportar dados em massa.

Dito tudo isso ...

Se a origem e o destino no SQL Server (e o destino não for uma versão inferior), por que não fazer um backup e restauração dos dados? Isso seria uma verdadeira migração de dados. Aqui está um código para você.

BACKUP DATABASE <DatabaseName, sysname, DatabaseName>
TO DISK=N'<backup_location, varchar, BackupLocation>.bak'
WITH INIT, FORMAT, COMPRESSION, COPY_ONLY

RESTORE DATABASE <NewDatabaseName, sysname, NewDatabaseName>
FROM DISK = N'<backup_location, varchar, BackupLocation>\
    <DatabaseName, sysname, DatabaseName>.bak'
WITH 
    MOVE '<DataFileName, sysname, DataFileName>' TO '<DataMDFPath, nvarchar(600), DataMDFPath>',
    MOVE '<LogFilePath, sysname, LogFilePath>' TO '<LogLDFPath, nvarchar(600), LogLDFPath>',
    REPLACE;

Você pode consultar esta resposta sobre como usar modelos no SSMS.