Injetando informações de contexto de conexão usando uma tabela persistente

Em termos de desempenho, você incorrerá na sobrecarga do DELETEe INSERTtoda vez que a conexão for aberta. Como alternativa, você pode usar a conexão interna CONTEXT_INFO para essa finalidade. O exemplo abaixo armazena as informações em uma estrutura de 48 bytes de comprimento fixo.

EXEC sp_executesql N'
    DECLARE @ContextInfo binary(48);
    SET @ContextInfo = CAST(CAST(@UsernameAttribute AS nchar(8)) + CAST(@Username AS nchar(16)) AS binary(48));
',N'@UsernameAttribute nvarchar(8),@Username nvarchar(16)',@UsernameAttribute=N'Username',@Username=N'domain\username'
GO


CREATE FUNCTION dbo.getCurrentUser()
RETURNS VARCHAR(250)
AS
BEGIN
DECLARE
      @ContextInfo binary(48) = CONTEXT_INFO()
    , @Username nvarchar(16);
    SET @Username = RTRIM(CAST(SUBSTRING(@ContextInfo, 17, 32) AS nvarchar(16)));

    RETURN ISNULL(@Username, SUSER_NAME());

END
GO

Além disso, sp_set_session_context e SESSION_CONTEXT() estão disponíveis no SQL Server 2016 e no Banco de Dados SQL do Azure. Esse é um método muito mais limpo, se disponível para você.

Sugiro pré-alocar linhas na tabela de contexto de uma forma que ajude a minimizar a contenção da página.

Esta é uma das poucas vezes em que realmente recomendo usar um GUID gerado aleatoriamente como a chave de clustering da tabela. Essa chave atuará como um randomizador para o local da página para qualquer SPID específico para reduzir a contenção da página.

CREATE TABLE dbo.ConnectionContextInfo 
(
    SlotID UNIQUEIDENTIFIER 
        CONSTRAINT PK_ConnectionContextInfo 
        PRIMARY KEY CLUSTERED
        DEFAULT (NEWID())
    , SPID INT NOT NULL
    , AttributeName VARCHAR(128) NOT NULL 
    , AttributeValue VARCHAR(255) NULL
    , CONSTRAINT UQ_ConnectionContextInfo_Info 
           UNIQUE(SPID, AttributeName)
);
GO
CREATE INDEX IX_ConnectionContextInfo_Lookups
ON dbo.ConnectionContextInfo(SPID, AttributeName);
GO

Isso preencherá previamente a tabela com as linhas necessárias, uma por combinação de spid/atributo.

;WITH Numbers AS 
(
    SELECT TOP(32767) 
        rn = ROW_NUMBER() OVER (ORDER BY o1.object_id)
    FROM sys.objects o1
        , sys.objects o2
        , sys.objects o3
)
, Attributes AS
(
    SELECT AttrName = N'UserName'
    UNION ALL
    SELECT AttrName = N'SomeOtherAttribute'
)
INSERT INTO dbo.ConnectionContextInfo (SPID, AttributeName) 
SELECT rn
    , AttrName
FROM Numbers
    , Attributes;

Se você realmente precisa usar sp_executesql, eu faria assim:

EXEC sys.sp_executesql N'UPDATE dbo.ConnectionContextInfo 
SET AttributeValue = @AttributeValue
WHERE SPID = @@SPID
    AND AttributeName = @AttributeName;'
    , N'@AttributeName nvarchar(128), @AttributeValue nvarchar(128)'
    , @AttributeName = N'Username'
    , @AttributeValue = N'domain\username';
GO

Os resultados para o meu spid:

SELECT * 
    , plc.*
FROM dbo.ConnectionContextInfo
CROSS APPLY sys.fn_PhysLocCracker(%%PhysLoc%%) plc 
WHERE SPID = @@SPID;

Em vez de usar sp_executesql, recomendo usar um procedimento armazenado para fazer a atualização, para que você possa incluir facilmente algum tratamento de erros e atualizar livremente esse código no lado do servidor sem afetar o cliente. Por exemplo:

IF OBJECT_ID('dbo.UpdateConnectionContextInfo') IS NOT NULL
DROP PROCEDURE UpdateConnectionContextInfo;
GO
CREATE PROCEDURE dbo.UpdateConnectionContextInfo
(
    @AttributeName NVARCHAR(128)
    , @AttributeValue NVARCHAR(255)
)
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE dbo.ConnectionContextInfo 
    SET AttributeValue = @AttributeValue
    WHERE SPID = @@SPID
        AND AttributeName = @AttributeName
    RETURN @@ROWCOUNT;
END
GO

Isso definirá @RetVal como 0 se o @AttributeNamepassado for inválido:

DECLARE @RetVal INT;
EXEC @RetVal = dbo.UpdateConnectionContextInfo 
    @AttributeName = 'UserName', @AttributeValue = 'SomeUser';
SELECT @RetVal;

Eu posso ver três problemas menores e um grande problema com esta abordagem:

Problemas menores:

1. Sua DELETEdeclaração em sua consulta ad hoc usa o seguinte predicado:

   AttributeName = @UsernameAttribute
   

   Em vez disso, você deve apenas filtrar, SPID = @@SPIDpois não deseja valores obsoletos da última instância desse SPID por aí, misturados com seus valores atuais.

2. Em sua ConnectionContextInfotabela, ambas as Attribute%colunas são definidas como VARCHAR, mas na consulta ad hoc você tem os parâmetros definidos como NVARCHARe até prefixa as strings com N. Você deve atualizar a tabela para que também seja definida como NVARCHAR.

3. Dados obsoletos de valores SPID mais altos que são inseridos durante os horários de pico de uso permanecerão por algum tempo, pois não haverá DELETEchamada até a próxima vez que o SPID for usado, o que pode ser nunca. Você pode criar um trabalho do SQL Server Agent para ser executado uma vez por dia e DELETElinhas criadas há X dias.

Problema principal (e uma solução):

Com base no comentário que você fez na resposta de @Dan, você não pode usar CONTEXT_INFOdevido ao tamanho que limita sua capacidade de adicionar mais atributos no futuro, especialmente se estiver usando NVARCHAR.

Felizmente, você não precisa de uma tabela permanente. Você pode simplificar isso um pouco usando uma tabela temporária global. Isso eliminaria a necessidade de DELETElinhas anteriores e provavelmente faria com que um trabalho do SQL Agent limpasse registros obsoletos OU pré-alocasse várias linhas, mas ainda precisaria atualizar todas as linhas correspondentes a esse SPID para uma string vazia ou NULLpor cada conexão.

Ao estabelecer a conexão, basta criar a tabela. Em seguida, insira quaisquer pares de chave/valor que desejar. A tabela será descartada automaticamente quando a conexão for fechada (conexões não agrupadas e agrupadas) ou quando a próxima sessão a reutilizar essa conexão executar sua primeira instrução e o sp_reset_connectionprocesso interno for executado (conexões agrupadas).

Agora, você deve estar se perguntando:

• A tabela temporária não será limpa quando o procedimento armazenado (ou consulta ad hoc) terminar?

  Se fosse uma tabela temporária local (ou seja #Name, ), então sim, ela seria limpa quando o processo/subprocesso em que foi criado terminar e não estará disponível no contexto pai. Mas as Tabelas Temporárias Globais (ou seja, ##Name) sobrevivem ao final do processo em que foram criadas e estão disponíveis no contexto pai.

• Como as tabelas temporárias são globais, elas não podem compartilhar o mesmo nome.

  Correto, usar um nome de tabela padrão na CREATE TABLEinstrução não funcionará porque várias sessões entrarão em conflito umas com as outras. Precisamos apenas de uma maneira de diferenciar o nome da tabela usando algo disponível para a Session, exclusivo da Session, mas não passado do aplicativo porque o código funcionaria apenas do aplicativo e o objetivo é não alterar o código do aplicativo. Portanto, basta anexar o @@SPIDvalor a um prefixo fixo conhecido e, em seguida, você pode inferir o nome da tabela em qualquer ponto dessa sessão.

  Algo como:

  CREATE PROCEDURE dbo.InitializeSessionContext
  AS
  SET NOCOUNT ON;
  
  DECLARE @Query NVARCHAR(MAX),
          @Template NVARCHAR(MAX) = N'
  
  CREATE TABLE ##SessionContext{{SPID}}
  (
    AttributeName NVARCHAR(32) NOT NULL, 
    AttributeValue NVARCHAR(250) NULL,
    Created DATETIME2 NOT NULL CONSTRAINT DF_SessionContext{{SPID}} DEFAULT(GETDATE())
  );';
  
  SET @Query = REPLACE(@Template, N'{{SPID}}', @@SPID);
  
  EXEC(@Query);
  GO
  

• Ao contrário da UDF mostrada na pergunta, essa abordagem requer SQL dinâmico e acesso a tabelas temporárias, nenhuma das quais é permitida.

  Correto, nenhum deles pode ser feito em funções T-SQL, mas podem ser feitos de duas outras maneiras:

  • Use um procedimento armazenado T-SQL para passar o valor de retorno por meio de um OUTPUTparâmetro ou
  • Crie uma UDF escalar SQLCLR. Use a conexão de contexto em processo (ou seja "Context Connection = true", e o assembly pode ser marcado como WITH PERMISSION_SET = SAFE. SQLCLR UDFs podem executar SQL dinâmico e acessar tabelas temporárias locais.