主页 / coding / 问题

问题[sql-server](coding)

Martin Hope
Flea
Asked: 2025-03-09 03:34:28 +0800 CST
  • 5

我之前创建了一个 SQL Server docker 容器实例，并且能够成功登录。我删除了该映像，并将代码从 docker run 命令迁移到具有支持 Dockerfile 的 docker-compose 中。

我在 dockerfile 中设置了 ENV MSSQL_SA_PASSWORD，但是现在，当我尝试登录时，它给出了一个错误：用户“sa”登录失败。似乎它将其缓存在某处，而我不再记得那个密码是什么了。

我该如何解决这个问题并让容器仅使用 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" ]

谢谢

sql-server
Martin Hope
MikeC
Asked: 2025-03-07 16:47:30 +0800 CST
  • 5

我需要将 xml 列中的时间戳元素转换为 SQL Server 2019 中的 UTC 时间。

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>');

我想将时间戳更改为“2024-08-20T00:55:37Z”。我正在使用“Data.modify('replace value of'”，但找不到新值基于旧值而不是简单字符串替换的示例。

sql-server
Martin Hope
Ben5
Asked: 2025-03-07 16:44:10 +0800 CST
  • 8

我有一张PaymentItems有 800 万行的表。其中 100'000 行具有外键PaymentItemGroupId = '662162c6-209c-4594-b081-55b89ce81fda'。

我已经在列PaymentItems.Date（ASC）上创建了一个非聚集索引，以便能够更快地对日期条目进行排序/查找。

运行以下查询时，大约需要 3 分钟：

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

有趣的是，如果没有 TOP 10，它将需要 18 秒并返回所有 100,000 行。当我按降序而不是升序排序时（ORDER BY [p].[Date] DESC），它将花费大约 1 秒。当我删除索引时，按升序排序也会更快。

我分析了慢查询的查询计划，看起来 MS SQL Server 不会首先通过外键过滤行，而是会先对所有 800 万行进行排序（索引扫描非聚集在Date索引上）。

在快速查询中，会先过滤where条件（key lookup clustered）。

除了删除索引之外我还能做什么来Date防止导致 SQL Server 构建这样的错误查询计划？

这是实际的查询计划： https://www.brentozar.com/pastetheplan/? id=xBBArQl9kh

以下是创建表的脚本：

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
sql-server
Martin Hope
altink
Asked: 2025-03-07 07:21:11 +0800 CST
  • 5

我有下表

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,
    .....
)

我还有以下看法：

在此处输入图片描述

当我查询视图时V_ACC_POL，我从视图中获取VARCHAR列的数据类型：POLICY_NAME

在此处输入图片描述

我期望它是NVARCHAR（如在基表中一样）。

问题：

  • 为什么NVARCHAR基表中的列会变成VARCHAR视图中的列？
  • 我怎样才能在视图中看到它NVARCHAR？
sql-server
Martin Hope
Andy Lamb
Asked: 2025-03-04 19:08:55 +0800 CST
  • 9

我们利用MASKEDSQL Server 中的列功能在查询时隐藏敏感数据。但我们遇到过这样一种情况，即INSERT作为语句的一部分执行的MERGE是插入屏蔽UNMASK值。即，即使以具有权限的用户身份查询表，新插入的值也会被视为屏蔽值。

具体来说，这种情况发生在：

  1. 一列是MASKED。
  2. 当前用户没有该UNMASK权限。
  3. 该MERGE语句的ON子句使用了该MASKED列。
  4. 该MERGE语句不匹配，因此落入该INSERT部分。

以下是该问题的简单复现。

-- 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;

最后一条语句的结果SELECT如下所示。此 select 是以具有UNMASK权限的用户身份执行的，但没有该权限的用户插入的两行仍然具有在显然被屏蔽的 中用作键的列MERGE。我怀疑是因为屏蔽值是在 期间插入的MERGE，而不是权限方面的问题。

SQL 结果表，显示了该问题。

这是一个已知问题吗？还是我做错了什么？

sql-server
Martin Hope
PixelPaul
Asked: 2025-02-14 10:02:48 +0800 CST
  • 6

下面是我正在使用的简化的存储过程查询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'

我传入了一个参数@SearchTerms，有时可能为 null 或为空。我想知道是否可以CONTAINSTABLE仅当参数有值时才加入。我试过这个：

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

但没用。不知道这是否可行，还是我的语法错误？

sql-server
Martin Hope
PixelPaul
Asked: 2025-02-13 21:10:22 +0800 CST
  • 6

我有以下查询（它确实有效并返回预期结果）但它使用多个子查询来获取COUNT并且我担心查询会非常低效，因为它目前是这样写的：

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

我最初的尝试是没有使用 的子查询COUNT，它能够正常工作，直到我添加了 ，LEFT JOIN这扭曲了两个计数的结果：

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

我猜测有一种方法可以使查询 #2 正常工作，并且它比查询 #1 更有效率？

sql-server
Martin Hope
banic
Asked: 2025-02-13 05:07:27 +0800 CST
  • 5

当我尝试构建我的项目时，遇到未解决的引用错误，因为我的项目包含引用同一服务器上的另一个数据库的视图。

当我尝试添加数据库引用时，我被迫在当前解决方案中选择数据层应用程序（.dacpac）、系统数据库或数据库项目。

为什么我不能使用这些选项：

  • 系统数据库不起作用→外部数据库是用户数据库，而不是 master、msdb、model 或 tempdb，因此此选项无效。

  • 我不想将数据库添加到解决方案中→该数据库是单独管理的，将其包含在 repo 中意味着维护整个不需要的额外项目。

  • .dacpac 不实用 → 引用的数据库经常变化，并且保持 AzDO 存储库中的 .dacpac 更新需要不断手动更新。

sql-server
Martin Hope
gchq
Asked: 2025-02-07 23:35:05 +0800 CST
  • 3

将数据从 SQL Anywhere 移动到 SQL Server，经过几次故障后，一切进展顺利，直到我收到错误

无法修改列“A_Future”，因为它是计算列或 UNION 的结果’

公平地说，我正在从 SELECT * 插入数据——因此使用它仅返回未计算的列。

 '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

这将返回以下查询字符串...

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

.. 并且它不包含任何计算列。再次运行，但它仍然抛出相同的错误（对于所有计算列）

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.

现在这没有任何意义，因为我没有尝试向其中插入数据，除非存在SqlBulkCopy我不知道的怪癖？

选择数据被添加到DataTable然后它进入这个函数

  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
sql-server
Martin Hope
Trying to be DBA
Asked: 2025-02-07 19:44:31 +0800 CST
  • 6

我正在尝试创建一个脚本来获取数据库日志文件的名称，设置为简单恢复，从该数据库收缩日志文件并重新设置为完整。
到目前为止，我已经想出了这个脚本：

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

该脚本将从 powershell 运行，不会给出任何错误消息，但是当我检查数据库的日志文件时仍然与以前一样，并没有缩小。

有什么建议吗？我做错了什么？

sql-server