Perguntas[sql-server](coding)

Eu havia criado uma instância de contêiner docker do SQL Server anteriormente e consegui efetuar login com sucesso. Removi essa imagem e estava migrando o código de um comando docker run para um docker-compose com um Dockerfile de backup.

Tenho o ENV MSSQL_SA_PASSWORD definido no dockerfile, mas agora, quando tento efetuar login, aparece um erro: Falha no login para o usuário 'sa'". Parece que ele está armazenando em cache em algum lugar e não lembro mais qual era a senha.

Como posso resolver isso e fazer com que o contêiner use apenas a senha definida no Dockerfile?

FROM mcr.microsoft.com/mssql/server:2022-latest

COPY source/database/scripts /usr/src/app

ENV MSSQL_SA_PASSWORD "MyPassword1"
ENV ACCEPT_EULA Y

# Run SQL Server process
ENTRYPOINT [ "/opt/mssql/bin/sqlservr" ]

Obrigado

Preciso converter um elemento de registro de data e hora na minha coluna XML para horário UTC no SQL Server 2019.

CREATE TABLE TestTable
   (    
       Id INT IDENTITY(1,1) NOT NULL,
       Data XML NOT NULL
   );

INSERT TestTable (Data) VALUES ('<root>
        <id>1</id>
        <timestamp>2024-08-20T10:55:37+10:00</timestamp>
    </root>');

Quero alterar o registro de data e hora para "2024-08-20T00:55:37Z". Estou brincando com "Data.modify('replace value of' ", mas não consigo encontrar um exemplo em que o novo valor seja baseado no valor antigo e não em uma simples substituição de string.

Tenho uma tabela PaymentItemscom 8 milhões de linhas. 100.000 linhas têm a chave estrangeira PaymentItemGroupId = '662162c6-209c-4594-b081-55b89ce81fda'.

Criei um índice não clusterizado na coluna PaymentItems.Date(ASC) para poder classificar/encontrar entradas mais rapidamente para datas.

Ao executar a seguinte consulta, levará cerca de 3 minutos:

SELECT TOP 10 [p].[Id], [p].[Receivers]
FROM [PaymentItems] AS [p]
WHERE [p].[PaymentItemGroupId] = '662162c6-209c-4594-b081-55b89ce81fda'
ORDER BY [p].[Date]

O interessante é que, sem o TOP 10, levará 18 segundos e retornará todas as 100.000 linhas. Quando eu ordeno decrescente em vez de crescente ( ORDER BY [p].[Date] DESC), levará cerca de 1 segundo. Quando eu removo o índice, também é mais rápido ao classificar de forma crescente.

Analisei o plano de consulta para a consulta lenta e parece que o MS SQL Server não filtra as linhas pela chave estrangeira primeiro, mas classifica todas as 8 milhões de linhas primeiro (varredura de índice não agrupada no Dateíndice).

Na consulta rápida, ele filtrará primeiro as condições where (pesquisa de chaves agrupada).

Há algo que eu possa fazer além de remover o índice para Dateevitar que o SQL Server crie um plano de consulta ruim como esse?

Aqui está o plano de consulta real: https://www.brentozar.com/pastetheplan/?id=xBBArQl9kh

Aqui está o script de criação da tabela:

CREATE TABLE [dbo].[PaymentItems](
    [Id] [uniqueidentifier] NOT NULL,
    [PaymentItemGroupId] [uniqueidentifier] NOT NULL,
    [Date] [datetime2](7) NOT NULL,
 CONSTRAINT [PK_PaymentItems] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_PaymentItems_Date] ON [dbo].[PaymentItems]
(
    [Date] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_PaymentItems_PaymentItemGroupId] ON [dbo].[PaymentItems]
(
    [PaymentItemGroupId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
GO

Eu tenho a seguinte tabela

CREATE TABLE [OMEGACA].[ACC_POL]
(
    [POLICY_ID] [int] IDENTITY(1,1) NOT NULL,
    [POLICY_NAME] [nvarchar](200) NOT NULL,
    [POLICY_DESC] [nvarchar](1000) NULL,
    [STATUS_ID] [int] NOT NULL,
    [RULE_EVAL] [int] NOT NULL,
    [AUDIT_OPTION] [int] NOT NULL,
    [FORMULA] [nvarchar](2000) NULL,
    [DEBUG_LOG] [int] NOT NULL,
    [USER_APPLY] [int] NOT NULL,
    [USE_CACHE] [int] NOT NULL,
    [DATE_UPD] [datetime] NULL,
    [USER_UPD] [nvarchar](200) NULL,
    [DATE_CREATED] [datetime] NOT NULL,
    [USER_CREATED] [nvarchar](200) NOT NULL,
    .....
)

E eu também tenho a seguinte visão:

Quando consulto a visualização V_ACC_POL, obtenho o tipo de dados VARCHARpara a coluna POLICY_NAMEda visualização:

Eu esperava que fosse NVARCHAR(como na tabela base).

Questões:

• Por que a NVARCHARcoluna na tabela base se transforma em uma VARCHARcoluna na exibição?
• Como posso tê-lo na visualização como NVARCHAR?

Usamos o MASKEDrecurso de coluna no SQL Server para ocultar dados confidenciais ao consultar. Mas nos deparamos com um cenário em que o INSERTexecutado como parte da MERGEinstrução é inserir um valor mascarado . Ou seja, o valor recém-inserido é visto como o valor mascarado, mesmo ao consultar a tabela como um usuário com a UNMASKpermissão.

Especificamente, o cenário ocorre quando:

1. Uma coluna é MASKED.
2. O usuário atual não tem UNMASKpermissão.
3. A cláusula MERGEda declaração ONusa a MASKEDcoluna.
4. A MERGEdeclaração não corresponde, então cai na INSERTseção.

Abaixo está uma reprodução mínima do problema.

-- Define a test table with one non-masked and one masked column.
CREATE TABLE TestTable(
     C1 NVARCHAR(100) MASKED WITH (FUNCTION = 'default()'),
     C2 NVARCHAR(100) MASKED WITH (FUNCTION = 'default()'),
     C3 NVARCHAR(100) -- Not masked
);

INSERT INTO TestTable
VALUES ('aaa', 'aaa', 'aaa'), ('bbb', 'bbb', 'bbb')

-- Print the current user and initial table content
SELECT SUSER_NAME() AS LoginName, USER_NAME() AS UserName;
SELECT *, 'No columns should be Masked' FROM TestTable

-- Create users with different permissions
CREATE USER user1 WITHOUT LOGIN
-- Setup permissions (No UNMASK permissions)
GRANT SELECT, UPDATE, INSERT TO user1;   

-- Switch context to the user WITHOUT UNMASK permissions
EXECUTE AS USER = 'user1';  

-- Show the user can only see masked data
SELECT SUSER_NAME() AS LoginName, USER_NAME() AS UserName;
SELECT *, 'Columns 1 & 2 should be Masked' FROM TestTable

-- Execute the merge, matching on the masked column
MERGE TestTable target
USING (
    SELECT * FROM (VALUES 
        ('bbb', 'bbb', 'bbb'), -- Will be merged
        ('ccc', 'ccc', 'ccc'), -- Will be inserted
        ('ddd', 'ddd', 'ddd')) -- Will be inserted
        AS s (C1 ,C2 ,C3)
) AS source
ON (source.C1 = target.C1)
WHEN MATCHED THEN UPDATE SET
    target.C2 = source.C2,
    target.C3 = source.C3
WHEN NOT MATCHED BY TARGET
  THEN INSERT (C1, C2, C3)
  VALUES (source.C1, source.C2, source.C3);

-- A regular insert statement to check if masking applies there
INSERT INTO TestTable
VALUES ('eee', 'eee', 'eee')

-- Print the current user and current table content
SELECT SUSER_NAME() AS LoginName, USER_NAME() AS UserName;
SELECT *, 'Columns 1 & 2 should be Masked' FROM TestTable

REVERT; -- Back to the session user context

-- Print the current user and current table content 
SELECT SUSER_NAME() AS LoginName, USER_NAME() AS UserName;
SELECT *, 'No columns should be Masked' FROM TestTable
  
-- Tidy up
DROP TABLE TestTable;
DROP USER user1;

O resultado da última SELECTdeclaração é mostrado abaixo. Este select está sendo executado como um usuário que tem a UNMASKpermissão, mas as duas linhas inseridas por um usuário sem essa permissão ainda têm a coluna usada como uma chave no MERGEaparentemente mascarado. Eu suspeito que seja porque o valor mascarado foi inserido durante o MERGE, em vez de qualquer problema com permissões.

Este é um problema conhecido? Ou estou fazendo algo errado?

Aqui está uma consulta de procedimento armazenado simplificada que estou usando com Full Text Search:

SELECT 
        c.Id                AS Id,
        cd.Make             AS Make,
        cd.Model            AS Model,
        u.Id                AS UserId,
    FROM Car c
    JOIN CarDetail cd   ON c.Id = cd.CarId
    JOIN CarImage ci    ON c.Id = ci.CarId 
    JOIN User u         ON c.UserId = u.Id
    JOIN CONTAINSTABLE(CarDetail, *, @SearchTerms) AS fti ON cd.Id = fti.[KEY]
    WHERE c.Status = 'Active'

Estou passando um parâmetro @SearchTerms, que às vezes pode ser nulo ou vazio. Estou pensando se é possível juntar o CONTAINSTABLEonly quando o parâmetro tem um valor. Eu tentei isso:

CASE 
    WHEN (@SearchTerms IS NOT NULL) OR (LEN(@SearchTerms)) > 0
    THEN JOIN CONTAINSTABLE(CarDetail, *, @SearchTerms) AS fti ON cd.Id = fti.[KEY] 
END

mas não funcionou. Não tenho certeza se é possível ou se minha sintaxe está errada?

Tenho a seguinte consulta (que funciona e retorna o resultado esperado), mas ela está usando várias subconsultas para obtê-la COUNTe estou preocupado que a consulta seja muito ineficiente da forma como está escrita atualmente:

SELECT 
        c.Id                AS Id,
        cd.Make             AS Make,
        u.Id                AS UserId,
        (SELECT COUNT(*) FROM CarImage WHERE CarId = c.Id) AS ImageCount,
        (SELECT COUNT(*) FROM CarLike WHERE CarId = c.Id) AS LikeCount
    FROM Car c
    JOIN CarDetail cd   ON c.Id = cd.CarId
    JOIN CarImage ci    ON c.Id = ci.CarId 
    JOIN User u         ON c.UserId = u.Id
    LEFT JOIN CarLike cl ON c.Id = cl.CarId
    WHERE c.Status = 'Active'
    GROUP BY 
            c.Id,
            cd.Make,
            u.Id

Minhas tentativas iniciais foram sem usar as subconsultas para o COUNT, o que consegui fazer funcionar até adicionar o , LEFT JOINo que distorceu os resultados de ambas as contagens:

SELECT 
        c.Id                AS Id,
        cd.Make             AS Make,
        u.Id                AS UserId,
        COUNT(ci.CarId) AS ImageCount,
        COUNT(cl.CarId) AS LikeCount
    FROM Car c
    JOIN CarDetail cd   ON c.Id = cd.CarId
    JOIN CarImage ci    ON c.Id = ci.CarId 
    JOIN User u         ON c.UserId = u.Id
    LEFT JOIN CarLike cl ON c.Id = cl.CarId
    WHERE c.Status = 'Active'
    GROUP BY 
            c.Id,
            cd.Make,
            u.Id

Imagino que exista uma maneira de fazer a consulta nº 2 funcionar e que ela seja mais eficiente que a consulta nº 1?

Estou enfrentando um erro de referência não resolvido quando tento compilar meu projeto porque ele contém visualizações que fazem referência a outro banco de dados no mesmo servidor.

Quando tento adicionar uma referência de banco de dados, sou forçado a selecionar um aplicativo da camada de dados (.dacpac), um banco de dados do sistema ou um projeto de banco de dados na solução atual.

Por que não posso usar essas opções:

• O banco de dados do sistema não funciona → O banco de dados externo é um banco de dados do usuário, não master, msdb, model ou tempdb, então esta opção não é válida.

• Não quero adicionar o banco de dados à solução → Este banco de dados é gerenciado separadamente, e incluí-lo no repositório significaria manter um projeto adicional inteiro que não é necessário.

• .dacpac não é prático → O banco de dados referenciado muda com frequência, e manter o .dacpac atualizado no repositório AzDO exigiria atualizações manuais constantes.

Movendo dados do SQL Anywhere para o SQL Server e depois de alguns soluços estava indo bem até que recebi o erro

A coluna "A_Future" não pode ser modificada porque é uma coluna computada ou é o resultado de uma UNION'

Justo, eu estava inserindo dados de SELECT * -- então usei isso para retornar apenas colunas que não foram calculadas.

 'Remove computed columns from the SELECT query
 Dim DR() As DataRow = MSSQLDT.Select("Computed = 'N'")
 Dim vSelectedRows As Integer = DR.Count
 Dim vCurrentRow As Integer = 0
 strSQL = "SELECT "
 For Each Row In DR
 strSQL += Row("Name")
 vCurrentRow += 1
 If vCurrentRow = vSelectedRows Then
     strSQL += " "
 Else
     strSQL += ", "
 End If
Next
strSQL += "FROM " & vTable

Isso retorna a seguinte string de consulta...

SELECT Transaction_ID, Debit, Credit, Paid, P_Description, Document_Date, Supplier_ID, Nominal_Transaction, HOA_Code, Document_Saved, Document_ID, Document_No, Supplier_Inv_No, Type, Paid_Date, Part_Paid, Open_Editing, Editing_Name, Updated_Name, Updated, Reserve_Item, Hold, eCheck_Pending, eSig_Required, eSigOne_ID, eSigTwo_ID FROM A_Purchase_Ledger

.. e não inclui nenhuma coluna calculada. Executei novamente, mas ainda apresentou o mesmo erro (para todas as colunas calculadas)

Microsoft.Data.SqlClient.SqlException (0x80131904): The column "A_Future" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_Current" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_30" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_60" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_90" cannot be modified because it is either a computed column or is the result of a UNION operator.
The column "A_Older" cannot be modified because it is either a computed column or is the result of a UNION operator.

Isso não faz sentido agora, já que não estou tentando inserir dados neles, a menos que haja uma peculiaridade da qual SqlBulkCopyeu não esteja ciente?

Os dados SELECT são adicionados a e DataTableentão vão para esta função

  Public Function BulkUpdate_DataMS(DT As DataTable, HOAID As Integer, IsHASoftware As Boolean, TableName As String) As Boolean
  Try
      Using Conn As New Microsoft.Data.SqlClient.SqlConnection
          If IsHASoftware = True Then
              Conn.ConnectionString = HASConString
          Else
              Conn.ConnectionString = ReturnConnStringMS(HOAID)
          End If
          Conn.Open()
          Using vTrans = Conn.BeginTransaction
              Using vBulk As New Microsoft.Data.SqlClient.SqlBulkCopy(Conn, Microsoft.Data.SqlClient.SqlBulkCopyOptions.Default, vTrans)
                  vBulk.DestinationTableName = TableName
                  vBulk.WriteToServer(DT)
              End Using
              vTrans.Commit()

          End Using
          Conn.Close()

      End Using
      Return True
  Catch ex As Exception
      EmailError(ex, 222, PageName)
      Return False
  End Try
 End Function

Estou tentando criar um script para obter o nome do arquivo de log de um banco de dados, definir como recuperação simples, reduzir o arquivo de log desse banco de dados e definir novamente como completo.
Até agora, eu criei este script:

DECLARE @RestoreDbName nvarchar(2000);
declare @Name nvarchar(2000)

set @restoreDbName = 'dbname'


DECLARE cur CURSOR FOR
SELECT name
FROM [sys].[database_files] 
where [type] = 1 and name like @restoreDbName

ALTER DATABASE @RestoreDbName
SET RECOVERY SIMPLE;  
GO 

OPEN cur
FETCH NEXT FROM cur INTO @Name
WHILE @@FETCH_STATUS = 0
BEGIN
    DBCC SHRINKFILE(@Name, 1)
    FETCH NEXT FROM cur INTO @Name
END
CLOSE cur
DEALLOCATE cur
GO  
       
 ALTER DATABASE @RestoreDbName
 SET RECOVERY FULL;  
 GO  

O script será executado a partir de um PowerShell, não gera nenhuma mensagem de erro, mas quando verifico o arquivo de log do banco de dados continua o mesmo de antes, não foi reduzido.

Alguma sugestão, o que estou fazendo errado?