Taryn's questions

Eu estava enfrentando alguns problemas de desempenho com uma consulta ontem e, após uma investigação mais aprofundada, notei o que acredito ser um comportamento estranho com um índice columnstore clusterizado do qual estou tentando chegar ao fundo.

A mesa é

CREATE TABLE [dbo].[NetworkVisits](
    [SiteId] [int] NOT NULL,
    [AccountId] [int] NOT NULL,
    [CreationDate] [date] NOT NULL,
    [UserHistoryId] [int] NOT NULL
)

com o índice:

CREATE CLUSTERED COLUMNSTORE INDEX [CCI_NetworkVisits] 
   ON [dbo].[NetworkVisits] WITH (DROP_EXISTING = OFF, COMPRESSION_DELAY = 0) ON [PRIMARY]

Atualmente, a tabela tem 1,3 bilhão de linhas e estamos constantemente inserindo novas linhas nela. Quando digo constantemente, quero dizer o tempo todo. É um fluxo constante de inserção de uma linha de cada vez na tabela.

Insert Into NetworkVisits (SiteId, AccountId, CreationDate, UserHistoryId)
Values (@SiteId, @AccountId, @CreationDate, @UserHistoryId)

Plano de execução aqui

Eu também tenho um trabalho agendado que é executado a cada 4 horas para excluir linhas duplicadas da tabela. A consulta é:

With NetworkVisitsRows
  As (Select SiteId, UserHistoryId, Row_Number() Over (Partition By SiteId, UserHistoryId
                                    Order By CreationDate Asc) RowNum
        From NetworkVisits
       Where CreationDate > GETUTCDATE() - 30)
DELETE
FROM NetworkVisitsRows
WHERE RowNum > 1
Option (MaxDop 48)

O plano de execução foi colado aqui .

Enquanto investigava o problema, notei que a NetworkVisitstabela tinha aproximadamente 2.000 rowgroups, com cerca de 800 deles em estado aberto e nenhum perto do máximo permitido (1048576). Aqui está uma pequena amostra do que eu estava vendo:

Executei uma reorganização no índice, que comprimiu todos, exceto 1 rowgroup, mas esta manhã verifiquei novamente e novamente temos vários rowgroups abertos - aquele que foi criado ontem após a reorganização, depois 3 outros criados aproximadamente na época da exclusão trabalho correu:

TableName       IndexName           type_desc               state_desc  total_rows  deleted_rows    created_time
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        36754       0               2019-12-18 18:30:54.217
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        172103      0               2019-12-18 20:02:06.547
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        132628      0               2019-12-19 04:03:10.713
NetworkVisits   CCI_NetworkVisits   CLUSTERED COLUMNSTORE   OPEN        397718      0               2019-12-19 08:02:13.063

Estou tentando determinar o que possivelmente pode estar causando isso para criar novos rowgroups em vez de usar o existente.

É possivelmente pressão de memória ou contenção entre a inserção e a exclusão? Esse comportamento está documentado em algum lugar?

Estamos executando o SQL Server 2017 CU 16 Enterprise Edition neste servidor.

O INSERTé MAXDOP 0, o DELETEé MAXDOP 48. Os únicos rowgroups fechados são os do inicial BULKLOADe depois do REORG_FORCEDque eu fiz ontem, então os motivos de trim sys.dm_db_column_store_row_group_physical_statssão REORGe NO_TRIMrespectivamente. Não há rowgroups fechados além desses. Não há atualizações sendo executadas nesta tabela. Calculamos em média cerca de 520 execuções por minuto na instrução insert. Não há particionamento na mesa.

Estou ciente de inserções de gotejamento. Fazemos a mesma coisa em outros lugares e não estamos enfrentando o mesmo problema com vários grupos de linhas abertas. Nossa suspeita é que tenha a ver com a exclusão. Cada grupo de linhas recém-criado está próximo ao horário do trabalho de exclusão agendado. Existem apenas dois armazenamentos delta mostrando linhas excluídas. Na verdade, não excluímos muitos dados desta tabela, por exemplo, durante uma execução ontem, ela excluiu 266 linhas.

Estamos nos preparando para realizar uma grande atualização em nossos SQL Servers e estamos percebendo um comportamento incomum com os Grupos de Disponibilidade Distribuídos que estou tentando resolver antes de avançar.

No mês passado, atualizei um servidor secundário remoto do SQL Server 2016 para o SQL Server 2017. Esse servidor faz parte de vários Grupos de Disponibilidade Distribuídos (DAGs) e um Grupo de Disponibilidade (AG) separado . Quando atualizamos este servidor, não sabíamos que ele entraria em um estado ilegível , portanto, durante o mês passado, contamos apenas com o servidor primário.

Como parte da próxima atualização, apliquei o patch CU 4 ao servidor e o reiniciei. Quando o servidor voltou a ficar online, o secundário recém corrigido mostrou que todos os DAGs/AGs estavam sincronizando sem problemas.

No entanto, a primária estava mostrando uma história muito diferente. Foi relatando que

• o AG separado estava sincronizando sem problemas
• mas os DAGs estavam em um estado Não Sincronizado / Não Saudável

Depois de entrar em pânico inicialmente, tentei as seguintes coisas para sincronizar as coisas novamente nos DAGs:

• A partir do primário, parei e retomei a movimentação de dados. Isso não começou a sincronizar os dados.
• No secundário (o que acabei de corrigir) eu corri ALTER DATABASE [<database] SET HADR RESUME;- que executa sem erros, mas não retomou nenhuma sincronização

Minha última tentativa de sincronizar os dados novamente foi fazer login no secundário e reiniciar manualmente o serviço SQL Server. Reiniciar manualmente o serviço parece um pouco extremo, pois eu esperava que o servidor sendo reinicializado fosse suficiente.

Alguém já se deparou com esse problema em que um DAG não inicia a sincronização com um secundário após uma reinicialização? Se sim, como foi resolvido?

Eu verifiquei o log de erros do SQL Server e o visualizador de eventos no servidor secundário, não havia nada fora do comum que eu pudesse ver.

Ao aplicar a UNPIVOTfunção a dados não normalizados, o SQL Server exige que o tipo de dados e o comprimento sejam iguais. Entendo por que o tipo de dados deve ser o mesmo, mas por que o UNPIVOT exige que o comprimento seja o mesmo?

Digamos que eu tenha os seguintes dados de amostra que preciso desfazer:

CREATE TABLE People
(
    PersonId int, 
    Firstname varchar(50), 
    Lastname varchar(25)
)

INSERT INTO People VALUES (1, 'Jim', 'Smith');
INSERT INTO People VALUES (2, 'Jane', 'Jones');
INSERT INTO People VALUES (3, 'Bob', 'Unicorn');

Se eu tentar UNPIVOT as colunas Firstnamee semelhantes a:Lastname

select PersonId, ColumnName, Value  
from People
unpivot
(
  Value 
  FOR ColumnName in (FirstName, LastName)
) unpiv;

O SQL Server gera o erro:

Msg 8167, Nível 16, Estado 1, Linha 6

O tipo de coluna "Sobrenome" está em conflito com o tipo de outras colunas especificadas na lista UNPIVOT.

Para resolver o erro, devemos usar uma subconsulta para primeiro converter a Lastnamecoluna para ter o mesmo comprimento que Firstname:

select PersonId, ColumnName, Value  
from
(
  select personid, 
    firstname, 
    cast(lastname as varchar(50)) lastname
  from People
) d
unpivot
(
  Value FOR 
  ColumnName in (FirstName, LastName)
) unpiv;

Consulte SQL Fiddle com demonstração

Antes de o UNPIVOT ser introduzido no SQL Server 2005, eu usaria um SELECTwith UNION ALLpara desdinamizar as colunas firstname/ lastnamee a consulta seria executada sem a necessidade de converter as colunas para o mesmo comprimento:

select personid, 'firstname' ColumnName, firstname value
from People
union all
select personid, 'LastName', LastName
from People;

Consulte SQL Fiddle com demonstração .

Também podemos desativá-los com sucesso usando CROSS APPLYsem ter o mesmo comprimento no tipo de dados:

select PersonId, columnname, value
from People
cross apply
(
    select 'firstname', firstname union all
    select 'lastname', lastname
) c (columnname, value);

Consulte SQL Fiddle com demonstração .

Eu li através do MSDN, mas não encontrei nada explicando o raciocínio para forçar o comprimento do tipo de dados a ser o mesmo.

Qual é a lógica por trás de exigir o mesmo comprimento ao usar o UNPIVOT?

Eu tenho um procedimento armazenado que será usado para paginar os dados. Um dos requisitos da procedure é ter um parâmetro que será utilizado para ordenar duas colunas de dados, a ordenação deve ser utilizada ao aplicar o row_number().

Alguns dados de exemplo:

CREATE TABLE grp 
(
  [grp_id] uniqueidentifier primary key, 
  [grp_nm] varchar(5) not null, 
  [grp_owner] varchar(200) not null
);

INSERT INTO grp ([grp_id], [grp_nm], [grp_owner])
VALUES
    ('7F5F0F16-4EBE-E211-9C26-78E7D18E1E84', 'test1', 'me'),
    ('1F52A713-EFAC-E211-9C26-78E7D18E1E84', 'test2', 'me'),
    ('D123B48A-63AB-E211-9C26-78E7D18E1E84', 'test3', 'me'),
    ('48361F86-2BC2-E211-9C26-78E7D18E1E84', 'test4', 'me'),
    ('27429A57-93C1-E211-9C26-78E7D18E1E84', 'test5', 'me'),
    ('D5DF9F8E-EDC2-E211-9C26-78E7D18E1E84', 'test6', 'me'),
    ('9A07EA21-1AAD-E211-9C26-78E7D18E1E84', 'test7', 'me');


CREATE TABLE mbr
(
  [grp_id] uniqueidentifier, 
  [mbr_id] int not null primary key, 
  [acct_id] varchar(7) not null, 
  [cst] varchar(4) null
);

INSERT INTO mbr ([grp_id], [mbr_id], [acct_id], [cst])
VALUES
    ('7F5F0F16-4EBE-E211-9C26-78E7D18E1E84', 10, '1', 'AA'),
    ('7F5F0F16-4EBE-E211-9C26-78E7D18E1E84', 11, '2', 'BB'),
    ('1F52A713-EFAC-E211-9C26-78E7D18E1E84', 12, '1234578', 'blah'),
    ('D123B48A-63AB-E211-9C26-78E7D18E1E84', 13, '78', 'test'),
    ('48361F86-2BC2-E211-9C26-78E7D18E1E84', 14, 'x', 'mbr1'),
    ('48361F86-2BC2-E211-9C26-78E7D18E1E84', 15, 'a', 'mbr2'),
    ('27429A57-93C1-E211-9C26-78E7D18E1E84', 16, 'b', 'mbr1'),
    ('27429A57-93C1-E211-9C26-78E7D18E1E84', 17, 'c', 'mbr2'),
    ('D5DF9F8E-EDC2-E211-9C26-78E7D18E1E84', 18, 'a', 'mbr1'),
    ('9A07EA21-1AAD-E211-9C26-78E7D18E1E84', 19, 'a', 'mbr1');

O procedimento e a classificação estão funcionando conforme escrito.

Mas surgiu um problema com a maneira como estamos aplicando o row_number(). O row_number()deve quase simular o ranqueamento que ocorre com dense_rankmas não funciona como esperado devido a ordenação com o parâmetro.

Por exemplo, se eu executar a seguinte consulta:

declare @sort_desc bit = 0

select g.grp_id, g.grp_nm,
    m.mbr_id, m.acct_id, m.cst,
    row_number() over(order by case when @sort_desc = 0 then g.grp_nm end
                              , case when @sort_desc = 0 then m.acct_id end
                              , case when @sort_desc = 1 then g.grp_nm end desc
                              , case when @sort_desc = 1 then m.acct_id end desc) rn,
    dense_rank()  over(order by case when @sort_desc = 0 then g.grp_nm end
                              , case when @sort_desc = 0 then m.acct_id end
                              , case when @sort_desc = 1 then g.grp_nm end desc
                              , case when @sort_desc = 1 then m.acct_id end desc) dr
from grp g
inner join mbr m
  on g.grp_id = m.grp_id;

Consulte SQL Fiddle com Demo (um procedimento armazenado reduzido também está presente)

Eu recebo um resultado de:

|                               GRP_ID | GRP_NM | MBR_ID | ACCT_ID |  CST | RN | DR |
-------------------------------------------------------------------------------------
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1 |     10 |       1 |   AA |  1 |  1 |
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1 |     11 |       2 |   BB |  2 |  2 |
| 1F52A713-EFAC-E211-9C26-78E7D18E1E84 |  test2 |     12 | 1234578 | blah |  3 |  3 |
| D123B48A-63AB-E211-9C26-78E7D18E1E84 |  test3 |     13 |      78 | test |  4 |  4 |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4 |     15 |       a | mbr2 |  5 |  5 |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4 |     14 |       x | mbr1 |  6 |  6 |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5 |     16 |       b | mbr1 |  7 |  7 |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5 |     17 |       c | mbr2 |  8 |  8 |
| D5DF9F8E-EDC2-E211-9C26-78E7D18E1E84 |  test6 |     18 |       a | mbr1 |  9 |  9 |
| 9A07EA21-1AAD-E211-9C26-78E7D18E1E84 |  test7 |     19 |       a | mbr1 | 10 | 10 |

Mas o resultado desejado é:

|                               GRP_ID | GRP_NM | MBR_ID | ACCT_ID |  CST | RN | expR|
-------------------------------------------------------------------------------------
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1 |     10 |       1 |   AA |  1 |  1  |
| 7F5F0F16-4EBE-E211-9C26-78E7D18E1E84 |  test1 |     11 |       2 |   BB |  2 |  1  |
| 1F52A713-EFAC-E211-9C26-78E7D18E1E84 |  test2 |     12 | 1234578 | blah |  3 |  2  |
| D123B48A-63AB-E211-9C26-78E7D18E1E84 |  test3 |     13 |      78 | test |  4 |  3  |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4 |     15 |       a | mbr2 |  5 |  4  |
| 48361F86-2BC2-E211-9C26-78E7D18E1E84 |  test4 |     14 |       x | mbr1 |  6 |  4  |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5 |     16 |       b | mbr1 |  7 |  5  |
| 27429A57-93C1-E211-9C26-78E7D18E1E84 |  test5 |     17 |       c | mbr2 |  8 |  5  |
| D5DF9F8E-EDC2-E211-9C26-78E7D18E1E84 |  test6 |     18 |       a | mbr1 |  9 |  6  |
| 9A07EA21-1AAD-E211-9C26-78E7D18E1E84 |  test7 |     19 |       a | mbr1 | 10 |  7  |

Como você pode ver, os expRvalores das colunas estão aumentando com base em, grp_idmas as linhas ainda estão na ordem de classificação correta. Não sei como obter esse resultado e qualquer sugestão seria ótima.

Sou novo no uso do BCP, mas estou tentando extrair dados gerados por meio de um procedimento armazenado. Esta é uma continuação da minha pergunta original, que era um procedimento armazenado com tabelas temporárias. Este procedimento cresceu um pouco, mas o núcleo é o mesmo, gerar tabelas temporárias, executar SQL dinâmico para preencher as tabelas e retornar dados.

Percebi rapidamente que o bcp não gosta de tabelas temporárias por meio do erro:

SQLState = S0002, NativeError = 208

Erro = [Microsoft][SQL Server Native Client 10.0][SQL Server]Nome de objeto inválido '#SurveyData

Então mudei meu procedimento para remover as tabelas #temp e usar tabelas reais. Mas agora recebo um erro:

SQLState = 37000, NativeError = 156

Erro = [Microsoft][SQL Server Native Client 10.0][SQL Server]Sintaxe incorreta perto da palavra-chave 'as'

Acredito que o problema seja com o SQL dinâmico. Há um asna declaração.

SET @sql = 'INSERT INTO cl.Results ' +
                            ' SELECT s.SurveyId
                                , s.InstanceId
                                , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END 
                                , Cast(s.' + @ColumnName + ' as varchar(1000))
                            FROM cl.SurveyData t 
                            INNER JOIN dbo.' + @TableName + ' s' +
                            ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
                            ' WHERE t.columnName = ''' + @ColumnName + ''''

        exec(@sql)

O código que estou usando no BCP para executar é:

bcp "exec dbo.getresults" queryout "c:\temp\mytext.txt" -S <myserver> -T -c -t^|

Estou aberto a qualquer sugestão sobre como obter esses dados extraídos, mesmo que eu tenha que reescrever o procedimento armazenado.

Não posso usar o BCP para executar um procedimento armazenado com SQL dinâmico? Se não, de que outra forma devo considerar a extração desses dados? O SSIS também não parece gostar das tabelas temporárias ou do SQL dinâmico.

Editar: usandoprint(@sql)

INSERT INTO cl.Results  
SELECT s.SurveyId
    , s.InstanceId
    , CASE WHEN columnName = 'Q_1' THEN REPLACE(columnName, 'Q_', '') ELSE '' END 
    , Cast(s.Q_1 as varchar(1000))
FROM cl.SurveyData t 
INNER JOIN dbo.Library_1 s 
    ON REPLACE(t.tableName, 'Library_', '') = s.SurveyID  WHERE t.columnName = 'Q_1'

INSERT INTO cl.Results  
SELECT s.SurveyId
    , s.InstanceId
    , CASE WHEN columnName = 'Q_2' THEN REPLACE(columnName, 'Q_', '') ELSE '' END 
    , Cast(s.Q_2 as varchar(1000))
FROM cl.SurveyData t 
INNER JOIN dbo.Library_2 s 
    ON REPLACE(t.tableName, 'Library_', '') = s.SurveyID  WHERE t.columnName = 'Q_2'

INSERT INTO cl.Results  
SELECT s.SurveyId
    , s.InstanceId
    , CASE WHEN columnName = 'Q_3' THEN REPLACE(columnName, 'Q_', '') ELSE '' END 
    , Cast(s.Q_3 as varchar(1000))
FROM cl.SurveyData t 
INNER JOIN dbo.Library_2 s 
    ON REPLACE(t.tableName, 'Library_', '') = s.SurveyID  
WHERE t.columnName = 'Q_3'

INSERT INTO cl.Results  
SELECT s.SurveyId
    , s.InstanceId
    , CASE WHEN columnName = 'Q_4' THEN REPLACE(columnName, 'Q_', '') ELSE '' END 
    , Cast(s.Q_4 as varchar(1000))
FROM cl.SurveyData t 
INNER JOIN dbo.Library_2 s 
    ON REPLACE(t.tableName, 'Library_', '') = s.SurveyID  
WHERE t.columnName = 'Q_4'

Editar: Aqui está o script completo que estou executando sem nenhuma tabela temporária:

truncate table cl.Results
truncate table cl.SurveyData

-- insert the survey table structures for use
insert into cl.SurveyData (tableName, columnName, columnId, rownum)
select tables1.name, cols1.name, column_id, ROW_NUMBER() over(order by tables1.name, column_id)
from sys.all_columns cols1
inner join 
(
    SELECT *
    FROM sys.all_objects
    WHERE type = 'U' 
    AND upper(name) like 'LIBRARY%' 
) Tables1
    ON cols1.object_id = tables1.object_id
WHERE cols1.name Like 'Q_%'
ORDER BY tables1.name, column_id;


declare @sql varchar(max) = '';
declare @RowCount int = 1;
declare @TotalRecords int = (SELECT COUNT(*) FROM cl.SurveyData);

Declare @TableName varchar(50) = '';
Declare @ColumnName varchar(50) = '';

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM cl.SurveyData
        WHERE @RowCount = rownum

        SET @sql = 'INSERT INTO cl.Results ' +
                    ' SELECT s.SurveyId
                        , s.InstanceId
                        , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END 
                        , Cast(s.' + @ColumnName + ' as varchar(1000))
                    FROM cl.SurveyData t 
                    INNER JOIN dbo.' + @TableName + ' s' +
                    ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
                    ' WHERE t.columnName = ''' + @ColumnName + ''''

        exec(@sql)

        SET @RowCount = @RowCount + 1       
    END

    SELECT r.SurveyId, 
        r.InstanceId, 
        CASE 
            when chat.DataName = 'BRKR_ACCT_ID' and chat.DataValue is not null then chat.DataValue
            when email.PropName = 'ACCT_ID' and email.PropValue is not null then email.PropValue
        END Account_ID,
        CASE 
            when chat.DataName = 'BRKR_CUST_ID' and chat.DataValue is not null then chat.DataValue
            when email.PropName = 'CUST_ID' and email.PropValue is not null then email.PropValue
        END Cust_ID,
        case 
            when chat.LoginName is null 
            then email.LoginName 
            else chat.LoginName 
        end SchwabId,
        case 
            when chat.CustEmail is null 
            then email.ReplyTo 
            else chat.CustEmail 
        end CustomerEmail,
        sp.DateSent,
        CONVERT(varchar, DATEADD(ms, DATEDIFF(SECOND, sp.datesent, sp.datecompleted) * 1000, 0), 108) ResponseTime,
        r.QuestionNumber,
        r.Response
    FROM cl.Results r
    INNER JOIN dbo.SurveyParam sp
        ON r.InstanceId = sp.InstanceID
        AND r.SurveyId = sp.SurveyID
    -- chat surveys uses Param4
    LEFT JOIN 
    (
        SELECT si.SessionID, 
            si.CustEmail, 
            u.LoginName, 
            ltrim(rtrim(sie.DataValue)) DataValue, 
            ltrim(rtrim(sie.DataName)) DataName
        FROM dbo.SessionInfo si
        LEFT JOIN dbo.Users u
            ON si.LastAgent = u.id
        LEFT JOIN dbo.SessionInfoExternals sie
            ON si.SessionID = sie.SessionID
        WHERE sie.DataName IN ('BRKR_ACCT_ID', 'BRKR_CUST_ID')
    ) chat
        ON sp.Param4 = chat.SessionID
    -- email surveys uses Param3
    LEFT JOIN
    (
        SELECT mm.MsgID, 
            mr.ReplyTo, 
            u.LoginName, 
            ltrim(rtrim(mie.PropValue)) PropValue,
            ltrim(rtrim(mie.PropName)) PropName
        FROM dbo.MailReply mr
        INNER JOIN dbo.MailMessage mm
            ON mr.ReplyToID = mm.MsgID
        LEFT JOIN dbo.Users u
            on mr.AgentID = u.ID
        LEFT JOIN dbo.MsgInfoExternals mie
            ON mm.IncidentID = mie.Instance
        WHERE mie.PropName IN ('CUST_ID', 'ACCT_ID')
    ) email
        ON sp.Param3 = email.MsgID
    WHERE sp.DateCompleted is not null

História rápida, estamos trabalhando com um fornecedor externo que possui um sistema de pesquisa. O sistema não é necessariamente o melhor quando você cria uma nova pesquisa e o sistema cria uma nova tabela, ou seja:

Tables
____
Library_1 -- table for Survey 1
SurveyId int
InstanceId int
Q_1 varchar(50)

Library_2 -- table for Survey 2
SurveyId int
InstanceId int
Q_2 int
Q_3 int
Q_4 varchar(255)

As tabelas são geradas com o SurveyIdno final do nome ( Library_) e as colunas Question são geradas com o QuestionIdno final ( Q_). Para esclarecer, as perguntas são armazenadas em uma tabela separada para que, embora os IDs das perguntas sejam sequenciais, eles não comecem em 1 para cada pesquisa. As colunas de perguntas serão baseadas no id atribuído a elas na tabela.

Parece simples o suficiente para consultar, exceto que precisamos extrair os dados de todas as tabelas de pesquisa para serem enviadas para outro sistema e é aí que entra o problema. Já que as tabelas são criadas automaticamente quando uma nova pesquisa é adicionada pelo front- aplicação final, o outro sistema não pode lidar com esse tipo de estrutura. Eles precisam que os dados sejam consistentes para serem consumidos.

Então, fui encarregado de escrever um procedimento armazenado que extrairá os dados de todas as tabelas do Survey e os colocará no seguinte formato:

SurveyId    InstanceId    QNumber    Response
________    __________    _______    ________
1           1             1          great
1           2             1          the best
2           9             2          10
3           50            50         test

Ao ter os dados de todas as tabelas no mesmo formato, eles podem ser consumidos por qualquer pessoa, não importa quantas tabelas e perguntas de pesquisa existam.

Eu escrevi um procedimento armazenado que parece estar funcionando, mas estou me perguntando se estou faltando alguma coisa ou se há uma maneira melhor de lidar com esse tipo de situação.

Meu código:

declare @sql varchar(max) = ''
declare @RowCount int = 1
declare @TotalRecords int = (SELECT COUNT(*) FROM SurveyData)

Declare @TableName varchar(50) = ''
Declare @ColumnName varchar(50) = ''

WHILE @RowCount <= @TotalRecords
    BEGIN

        SELECT @TableName = tableName, @ColumnName = columnName
        FROM SurveyData
        WHERE @RowCount = rownum


        SET @sql = @sql + 
            ' SELECT s.SurveyId
                , s.InstanceId
                , CASE WHEN columnName = ''' +  @ColumnName + ''' THEN REPLACE(columnName, ''Q_'', '''') ELSE '''' END as QuestionNumber
                , Cast(s.' + @ColumnName + ' as varchar(1000)) as ''Response''
            FROM SurveyData t 
            INNER JOIN ' + @TableName + ' s' +
                ' ON REPLACE(t.tableName, ''Library_'', '''') = s.SurveyID ' +
            ' WHERE t.columnName = ''' + @ColumnName + ''''

        IF @RowCount != @TotalRecords
            BEGIN
                set @sql = @sql + ' UNION ALL'
            END

        SET @RowCount = @RowCount + 1       
    END


exec(@sql)

Eu criei um SQL Fiddle com alguns dados de exemplo e o código.

Existe uma maneira diferente de escrever esse tipo de consulta? Há algum problema perceptível com ele?

Infelizmente, há muitas incógnitas com isso... quantas mesas teremos e quantas perguntas por pesquisa. Eu diria que teremos entre 25 a 50 pesquisas, com 2 a 5 perguntas cada.